Rem
Rem $Header: admin_post_import.sql 11-jan-2005.14:42:42 rpinnama Exp $
Rem
Rem admin_post_import.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_post_import.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    rpinnama    01/11/05 - rpinnama_bug-4069591
Rem    rpinnama    12/30/04 - Created
Rem

Rem
Rem This script requires the following SQL*Plus variables to be set
Rem  EM_REPOS_USER Name of the repoistory user
Rem  EM_TABLESPACE_NAME Name of the tablespace
Rem  EM_TEMP_TABLESPACE_NAME Name of the temporary table space
Rem


Rem
Rem Post-import, the following actions have to be performed
Rem   Create public synonyms
Rem   Recompile and re-pin packages
Rem   Create MGMT_VIEW user and grant select on mgmt$ views
Rem   Create EM users
Rem   Enable VPD policy
Rem   Start notification AQ
Rem



Rem Create public synonyms
CREATE PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS FOR MGMT_BLACKOUT_STATE;
CREATE PUBLIC SYNONYM SETEMVIEWUSERCONTEXT FOR SETEMVIEWUSERCONTEXT;



Rem Recompile packages
@@&EM_SQL_ROOT/core/latest/admin/admin_recompile_invalid.sql &&EM_REPOS_USER

Rem Recompile packages
@@&EM_SQL_ROOT/core/latest/admin/admin_pin_plsql.sql



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 tname from tab where tname like 'MGMT$%' and TABTYPE='VIEW')
    LOOP
      BEGIN
        EXECUTE IMMEDIATE 'GRANT SELECT ON ' || crec.tname || ' TO MGMT_USER';
        EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || crec.tname || ' FOR '||crec.tname;
      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;

    -- Dont set the password, just use the existing password.
    -- COMMIT;

    -- Get the user name and password for view user..
    MGMT_VIEW_PRIV.GET_VIEW_USER_CREDS(view_user, view_user_pwd);

    -- Create the view user
    EXECUTE IMMEDIATE 'ALTER USER MGMT_VIEW IDENTIFIED BY ' || view_user_pwd ;

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

    -- The following Inserts are not required as the imported DB
    -- will already have them
    -- 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 execute privilge on SETEMVIEWUSERCONTEXT to view user
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON SETEMVIEWUSERCONTEXT TO ' || view_user;

    COMMIT;
END;
/
show errors;

Rem Create other users
BEGIN
  BEGIN
    FOR crec IN (SELECT user_name FROM MGMT_CREATED_USERS
                  WHERE system_user = 0
                    AND user_name NOT IN ('MGMT_VIEW') )
    LOOP
      EXECUTE IMMEDIATE 'CREATE USER ' || crec.user_name ||
                        ' IDENTIFIED BY ' ||  crec.user_name ||
                        ' DEFAULT TABLESPACE &&EM_TABLESPACE_NAME ' ||
                        ' TEMPORARY TABLESPACE &&EM_TEMP_TABLESPACE_NAME ';

      EXECUTE IMMEDIATE 'GRANT CONNECT, RESOURCE TO ' || crec.user_name ;

    END LOOP;
  END;
END;
/




PROMPT Enable VPD policy
Rem BEGIN
Rem   MGMT_USER.ADD_SECURITY_POLICIES();
Rem END;
Rem /
Rem 

PROMPT Start notification queue
BEGIN
  DBMS_AQADM.START_QUEUE(queue_name => 'MGMT_NOTIFY_Q');
END;
/