Rem
Rem $Header: user_create_view_user.sql 25-aug-2005.03:25:09 kmanicka Exp $
Rem
Rem user_create_view_user.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      user_create_view_user.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kmanicka    08/25/05 - generate view user password during out of box 
Rem    jsadras     06/08/05 - cutover to use change password api
Rem    mgoodric    01/26/05 - remove MGMT_ECM_MD_ALL_TBL_COLUMNS from view user 
Rem    rzazueta    12/10/04 - Modify select stmt 
Rem    lgloyd      11/29/04 - expose tgt_guid type to mgmt_view 
Rem    mgoodric    10/28/04 - add more grants for MGMT_VIEW user 
Rem    mgoodric    10/23/04 - grant MGMT_VIEW_UTIL to view user 
Rem    jabramso    10/07/04 - grant VIEW_ANY_REPORT to view user 
Rem    rpinnama    12/12/03 - Use create or replace while creating synonymns 
Rem    gan         11/14/03 - set verify off 
Rem    rpinnama    07/01/03 - Make SYS and SYSTEM EM users here
Rem    rpinnama    02/27/03 - Remove hardcoded password
Rem    rpinnama    01/21/03 - Generate the password and create the user
Rem    aholser     05/20/02 - fix error.
Rem    rpinnama    05/16/02 - rpinnama_reorg_rep_scripts_2
Rem    rpinnama    05/16/02 - Created
Rem

Rem
Rem This SQL script assumes the following arguments
Rem  1. Repository users password
Rem
DEFINE EM_REPOS_PWD  = "&1"
DEFINE EM_ECHO_SQL   = "&2"
DEFINE EM_REPOS_USER = "&3"

set verify off

create public synonym SETEMVIEWUSERCONTEXT FOR SETEMVIEWUSERCONTEXT;
create public synonym MGMT_VIEW_UTIL FOR MGMT_VIEW_UTIL;
create public synonym MGMT_MESSAGES FOR MGMT_MESSAGES;


DECLARE
  def_tablespace  VARCHAR2(30);
  temp_tablespace VARCHAR2(30);
  view_user       VARCHAR2(30);
  view_user_pwd   VARCHAR2(30);
  view_credentials  SMP_EMD_NVPAIR_ARRAY;
  err number;
BEGIN
    FOR crec in (SELECT object_name FROM all_objects
                 WHERE object_name like 'MGMT$%' AND object_type='VIEW' AND owner='&EM_REPOS_USER')

    LOOP
      BEGIN
        EXECUTE IMMEDIATE 'GRANT SELECT ON ' || crec.object_name || ' TO MGMT_USER';
        EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ' || crec.object_name || ' FOR '||crec.object_name;
      EXCEPTION 
        -- if the synonym already exists, continue
        WHEN OTHERS THEN
          err := SQLCODE;
          IF err = -955
          THEN 
             NULL;
          END IF;
      END;
    END LOOP;

    -- Get the default tablespace and temporary tablespace
    SELECT default_tablespace, temporary_tablespace
    INTO def_tablespace, temp_tablespace 
    FROM user_users;
    
    -- Get view user
    view_user     := MGMT_VIEW_PRIV.MGMT_VIEW_USER ;
    
    -- View User Password is assumed to be MD5 hash of the SYSMAN Password
    -- This is part of the seed database and will be changed during out of box creation.
    view_user_pwd := MGMT_VIEW_PRIV.random_password('&EM_REPOS_PWD') ;

     -- Create the view user
    EXECUTE IMMEDIATE 'CREATE USER ' || view_user || ' IDENTIFIED BY ' ||
      view_user_pwd || ' DEFAULT TABLESPACE ' || def_tablespace ||
      ' TEMPORARY TABLESPACE ' || temp_tablespace;

    -- Grant MGMT_USER role to view user
    EXECUTE IMMEDIATE 'GRANT MGMT_USER TO ' || view_user;

    -- Add MGMT_VIEW user to MGMT_CREATED_USERS, so that this user
    -- is deleted during DropRep
    INSERT INTO MGMT_CREATED_USERS (user_name,system_user)
      VALUES(view_user,0);
    COMMIT;

    -- Grant view any target em system privilege to view user
    MGMT_USER.GRANT_PRIV(view_user, MGMT_USER.VIEW_ANY_TARGET);
    COMMIT;

    -- Grant view any report em system privilege to view user
    MGMT_USER.GRANT_PRIV(view_user, MGMT_USER.VIEW_ANY_REPORT);
    COMMIT;

    -- Grant execute privilege on SETEMVIEWUSERCONTEXT to view user
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON SETEMVIEWUSERCONTEXT TO ' || view_user;

    -- Grant execute privilege on MGMT_VIEW_UTIL to view user
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON MGMT_VIEW_UTIL TO ' || view_user;

    -- Grant select privilege on MGMT_MESSAGES to view user
    EXECUTE IMMEDIATE 'GRANT SELECT ON MGMT_MESSAGES TO ' || view_user;

    -- Grant the MGMT_IP_TGT_GUID_ARRAY type to view user
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON MGMT_IP_TGT_GUID_ARRAY TO ' || view_user;
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM MGMT_IP_TGT_GUID_ARRAY FOR MGMT_IP_TGT_GUID_ARRAY';
    

    COMMIT;
END;
/
set verify &EM_ECHO_SQL
show errors;


BEGIN

  mgmt_user.make_em_user('SYS');
  mgmt_user.make_em_user('SYSTEM');

  COMMIT;

END;
/
show errors;