Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_grants_repos_user.sql /st_emcore_10.2.0.4.2db11.2/2 2009/06/20 21:53:24 nmathuri Exp $
Rem
Rem admin_grants_repos_user.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      admin_grants_repos_user.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      grants to repository owner
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nmathuri    06/19/09 - LRG 3936118 Grant unmilited quota to sysman on SYAUX
Rem    pshishir    08/13/07 - Bug 6314129 
Rem    minfan      05/23/07 - Bug 5894442
Rem    minfan      05/23/07 - Bug 5894442
Rem    minfan      05/23/07 - Bug 5894442
Rem    minfan      05/23/07 - Bug 5894442
Rem    shianand    10/20/06 - fix bug 5482793
Rem    shianand    07/21/06 - Backport shianand_bug-5346292 from main 
Rem    shianand    07/06/06 - fix bug 5346292, fix encrypt, decrypt from 
Rem                           rootkit attacks 
Rem    bmallipe    08/11/06 - fixing for DBControl upgrade
Rem    jsadras     03/03/06 - Created
Rem
Rem TBD: note this file contains one grant as of now, The idea is to move
Rem all sysman grants to this file 11G so it can be run during install or upgrade

-- Grant privs on AUX_STATS$ table as its req to execute dbms_stats.gather_system_stats
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.AUX_STATS$ TO &EM_REPOS_USER;

GRANT CREATE PUBLIC SYNONYM TO &EM_REPOS_USER ;

--
-- This grant is needed to create DB File for Auditing framework
--
GRANT EXECUTE ON sys.UTL_FILE TO &EM_REPOS_USER;

-- This check is for supporting DBConsole since Grid Control can use 9.2 database which doesnt have DBMS_CRYPTO

BEGIN
  IF ('&EM_REPOS_MODE' != 'CENTRAL') THEN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_CRYPTO TO &EM_REPOS_USER';
  END IF;
END;
/

--
-- This grant is needed otherwise granting select access on views which
-- refer to dbms_lob function fail with ORA-01720 
-- An example of such an view is MGMT$DELTA_COMPONENTS
--
GRANT EXECUTE ON SYS.DBMS_LOB to &EM_REPOS_USER with grant option;

--
-- This grant is necessary sine the user model packages use
-- this package
--
GRANT EXECUTE ON SYS.DBMS_LOCK to &EM_REPOS_USER;

GRANT EXECUTE ON SYS.DBMS_UTILITY to &EM_REPOS_USER;

GRANT SELECT_CATALOG_ROLE to &EM_REPOS_USER WITH admin option;

GRANT RESOURCE to &EM_REPOS_USER;

GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO &EM_REPOS_USER;

DECLARE
  l_sql_err_code NUMBER := 0;
BEGIN
    IF ('&EM_REPOS_MODE' != 'CENTRAL') 
    THEN
       EXECUTE IMMEDIATE 'REVOKE create user FROM &EM_REPOS_USER';
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_sql_err_code := SQLCODE;
    -- Ignore the ORA-01952 : system privileges not granted error.
    IF (l_sql_err_code != -1952) THEN
      -- Raise all other errors
      RAISE;
    END IF;
END;
/


DECLARE
  l_sql_err_code NUMBER := 0;
BEGIN
    IF ('&EM_REPOS_MODE' != 'CENTRAL')
    THEN
      EXECUTE IMMEDIATE 'REVOKE alter user FROM &EM_REPOS_USER';
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_sql_err_code := SQLCODE;
    -- Ignore the ORA-01952 : system privileges not granted error.
    IF (l_sql_err_code != -1952) THEN
      -- Raise all other errors
      RAISE;
    END IF;
END;
/

DECLARE
  l_sql_err_code NUMBER := 0;
BEGIN
    IF ('&EM_REPOS_MODE' != 'CENTRAL')
    THEN
      EXECUTE IMMEDIATE 'REVOKE drop user FROM &EM_REPOS_USER';
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_sql_err_code := SQLCODE;
    -- Ignore the ORA-01952 : system privileges not granted error.
    IF (l_sql_err_code != -1952) THEN
      -- Raise all other errors
      RAISE;
    END IF;
END;
/

DECLARE
  l_sql_err_code NUMBER := 0;
BEGIN
    IF ('&EM_REPOS_MODE' != 'CENTRAL')
    THEN
      EXECUTE IMMEDIATE 'REVOKE CREATE ANY TABLE FROM &EM_REPOS_USER';
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_sql_err_code := SQLCODE;
    -- Ignore the ORA-01952 : system privileges not granted error.
    IF (l_sql_err_code != -1952) THEN
      -- Raise all other errors
      RAISE;
    END IF;
END;
/


--
-- This revoke is required here to make sure that the dba role is revoked
-- even during repository upgrade scenarios
--

DECLARE
  l_sql_err_code NUMBER := 0;
BEGIN
  IF ('&EM_REPOS_MODE' != 'CENTRAL')
  THEN
    EXECUTE IMMEDIATE 'REVOKE dba from &EM_REPOS_USER';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_sql_err_code := SQLCODE;
    -- Ignore the ORA-01951 : ROLE DBA not granted error.
    IF (l_sql_err_code != -1951) THEN
      -- Raise all other errors
      RAISE;
    END IF;

END;
/

-- Grant the unlimited quota to SYSMAN on SYSAUX 
-- tablespace 
BEGIN
  IF ('&EM_REPOS_MODE' != 'CENTRAL')
  THEN
     EXECUTE IMMEDIATE 'ALTER USER &EM_REPOS_USER QUOTA UNLIMITED ON &EM_TABLESPACE_NAME';
  END IF;
END;
/

show errors;