Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_create_repos_user.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/20 16:22:09 nmathuri Exp $
Rem
Rem admin_create_repos_user.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      admin_create_repos_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    scgrover    07/06/05 - add alter session priv 
Rem    pmodi       06/01/05 - Bug:4392834 - Grant priv on AUS_STATS$ tab 
Rem    rzazueta    08/20/04 - Remove quota unlimited on temp tablespace 
Rem                           altogether 
Rem    rzazueta    05/11/04 - Remove quota unlimited on temp tablespace for dbconsole 
Rem    rzazueta    01/05/04 - Add quotes to create user stmt 
Rem    gan         11/14/03 - set verify off 
Rem    rpinnama    10/15/03 - No need to grant javasyspriv 
Rem    rpinnama    07/25/03 - Grant VPD role based on repository mode
Rem    rpinnama    07/03/03 - Conditionally grant execute on dbms_shared_pool
Rem    edemembe    07/01/03 - Adding partition support
Rem    dcawley     03/10/03 - Check for fine grained access control being installed
Rem    dcawley     03/07/03 - Use dymanic SQL for DBMS_RLS grant
Rem    rpinnama    02/25/03 - Remove exit at the end
Rem    dcawley     09/27/02 - Grant create user and drop user
Rem    edemembe    07/12/02 - Adding automatic online index rebuilds
Rem    skini       06/24/02 - Give MGMT_REP exec perms on DBMS_LOCK
Rem    rpinnama    06/03/02 - rpinnama_reorg_rep_scripts_3
Rem    rpinnama    05/17/02 - Created
Rem


Rem 
Rem Requires the following SQL arguments
Rem   1. Name of the repository user.. 
Rem   2. Repository users password
Rem   3. Default table space of repository user
Rem   4. Temp tablespace name of repository user.
Rem

define EM_REPOS_USER            = "&1"
define EM_REPOS_PWD             = "&2"
define EM_TABLESPACE_NAME       = "&3"
define EM_TEMP_TABLESPACE_NAME  = "&4"
define EM_REPOS_MODE            = "&5"
define EM_ECHO_SQL              = "&6"

set verify off
--
-- Create the repository user, using the specified tablespaces.
--
CREATE USER &EM_REPOS_USER
 IDENTIFIED BY "&EM_REPOS_PWD"
 DEFAULT TABLESPACE &EM_TABLESPACE_NAME
  QUOTA UNLIMITED ON &EM_TABLESPACE_NAME
 TEMPORARY TABLESPACE &EM_TEMP_TABLESPACE_NAME;

set verify &EM_ECHO_SQL

--
-- Grant the user appropriate privileges/roles to repository user.
--
-- GRANT dba, create user, alter user, drop user, alter session TO &EM_REPOS_USER;
GRANT alter session TO &EM_REPOS_USER;

GRANT SELECT ON SYS.USER_TAB_COLUMNS to &EM_REPOS_USER;

-- This grant will not work in the case of a Standard Edition Database
-- which does not have the DBMS_RLS package installed
DECLARE
 fga_option VARCHAR2(32);
BEGIN

  -- SELECT value INTO fga_option FROM V$OPTION
  -- WHERE parameter = 'Fine-grained access control';

  -- If fine grained access control is available
  -- IF fga_option = 'TRUE'
  IF '&EM_REPOS_MODE' = 'CENTRAL'
  THEN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_RLS to &EM_REPOS_USER';
  END IF;
END;
/

GRANT EXECUTE ON DBMS_AQ to &EM_REPOS_USER;
GRANT EXECUTE ON DBMS_JOB to &EM_REPOS_USER;
GRANT SELECT ANY DICTIONARY to &EM_REPOS_USER;
-- GRANT CREATE ANY TABLE to &EM_REPOS_USER;
show errors;

-- Grant privs on AUX_STATS$ table as its req to execute dbms_stats.gather_system_stats

GRANT SELECT, INSERT, UPDATE, DELETE ON AUX_STATS$ TO &EM_REPOS_USER;

--
-- Grant the user execute privileges on the shared pool object.
--
-- This grant will not work if the shared pool package is not created.
-- GRANT EXECUTE ON sys.dbms_shared_pool TO &EM_REPOS_USER;
DECLARE
 l_object_exists VARCHAR2(32);
BEGIN
  SELECT count(*) INTO l_object_exists 
    FROM all_objects
  WHERE object_name = 'DBMS_SHARED_POOL'
    AND object_type = 'PACKAGE';

  -- If shared pool package exists, grant execute on that.
  IF (l_object_exists > 0) THEN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON sys.dbms_shared_pool TO &EM_REPOS_USER';
  END IF;
END;
/
GRANT EXECUTE ON sys.dbms_redefinition TO &EM_REPOS_USER;

--
-- 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 DBMS_LOB to &EM_REPOS_USER with grant option;

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

-- Following privileges are required by EM_REPOS_USER
-- as he is not DBA any more

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;