Rem
Rem $Header: admin_mgmt_grants.sql 26-apr-2005.14:32:58 gsbhatia Exp $
Rem
Rem admin_mgmt_grants.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_mgmt_grants.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    gsbhatia    04/26/05 - gsbhatia_bug-4314977
Rem    gsbhatia    04/25/05 - Created
Rem


Rem
Rem This SQL script assumes the following arguments
Rem  1. Repository user
Rem

DEFINE EM_REPOS_USER = "&1"
DEFINE EM_ECHO_SQL = "OFF"

set verify off

DECLARE
  view_user       VARCHAR2(30);
  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;

    view_user := MGMT_VIEW_PRIV.MGMT_VIEW_USER;    

    -- 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;