Rem drv: <create type="post_creation" params="EM_REPOS_MODE,EM_REPOS_USER" pos="availability/availability_post_creation.sql-"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/user_model/user_model_post_creation.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/20 16:22:09 nmathuri Exp $
Rem
Rem user_model_post_creation.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      user_model_post_creation.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    07/03/05 - New repmgr header impl 
Rem    dcawley     03/14/05 - Upper case repository owner 
Rem    dcawley     03/03/05 - Change predicate function for stored reports 
Rem    dcawley     02/17/05 - Add target delete exceptions 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    dcawley     01/11/05 - Rename callback 
Rem    dcawley     01/06/05 - Add target deletion callback 
Rem    rzazueta    11/01/04 - Add EM_REPOS_USER parameter 
Rem    dcawley     10/07/04 - Remove manage any user 
Rem    jabramso    10/07/04 - add VIEW_ANY_REPORT system privilege 
Rem    dcawley     09/09/04 - Make SYS and SYSTEM super users 
Rem    dcawley     07/29/04 - Add new policies 
Rem    dcawley     07/23/04 - New privs 
Rem    gan         07/02/04 - create user context 
Rem    dcawley     11/17/03 - Rename predicate functions 
Rem    dcawley     11/13/03 - Change predicate names 
Rem    dcawley     09/01/03 - Add jobs policy 
Rem    rpinnama    07/25/03 - Set VPD policy based on repository mode
Rem    rpinnama    07/01/03 - Ignore errors during make_em_user
Rem    rpinnama    04/18/03 - Make SYS and SYSTEM as EM users
Rem    dcawley     03/10/03 - Use V$OPTION
Rem    dcawley     03/06/03 - Enable VPD is dbms_rls is present
Rem    dcawley     12/12/02 - Add job privileges
Rem    dcawley     10/11/02 - Add record to MGMT_CREATED_USERS
Rem    rpinnama    10/14/02 - Use log registration API
Rem    dcawley     10/08/02 - Add entry to mgmt_performance_name
Rem    aholser     09/06/02 - remove create_target_in_group grant from manage_target_group
Rem    dcawley     08/06/02 - Add EM_MONITOR
Rem    skini       07/03/02 - Move login assistant code here
Rem    dcawley     06/13/02 - Add USE_ANY_BEACON
Rem    dcawley     05/20/02 - Do not create email device.
Rem    rpinnama    05/16/02 - 
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

Rem
Rem This script requires the following arguments
Rem  1. The mode of the repository
Rem  2. Name of the repository user
Rem
DEFINE EM_REPOS_MODE            = "&1"
DEFINE EM_REPOS_USER            = "&2"


BEGIN

-- SYSTEM Privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('CREATE_ANY_ROLE', MGMT_USER.SYSTEM_PRIVILEGE, 
'Ability to create any role in the system');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('CREATE_ANY_PRIVILEGE', MGMT_USER.SYSTEM_PRIVILEGE, 
'Ability to create any privilege in the system');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('CREATE_TARGET', MGMT_USER.SYSTEM_PRIVILEGE, 
'Ability to create a target');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('DELETE_ANY_TARGET', MGMT_USER.SYSTEM_PRIVILEGE, 
'Ability to delete any target in the system');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) 
VALUES ('VIEW_ANY_TARGET', MGMT_USER.SYSTEM_PRIVILEGE,
'Ability to view any target');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) 
VALUES ('USE_ANY_BEACON', MGMT_USER.SYSTEM_PRIVILEGE,
'Ability to register with any Beacon');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) 
VALUES ('EM_MONITOR', MGMT_USER.SYSTEM_PRIVILEGE,
'Ability to view any EM Repository targets');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) 
VALUES ('PUBLISH_REPORT', MGMT_USER.SYSTEM_PRIVILEGE,
'Ability to publish reports for public viewing');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('SUPER_USER', MGMT_USER.SYSTEM_PRIVILEGE, 
'Provides all the privileges to any target in the system');

-- TARGET Privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('VIEW_TARGET', MGMT_USER.TARGET_PRIVILEGE, 
'Ability to view properties, inventory and monitor information about a target');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('OPERATOR_TARGET', MGMT_USER.TARGET_PRIVILEGE, 
'Ability to startup, shutdown and modify monitoring configuration');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('MAINTAIN_TARGET', MGMT_USER.TARGET_PRIVILEGE, 
'Ability to view properties, inventory and monitor information about a target');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('CLONE_FROM_TARGET', MGMT_USER.TARGET_PRIVILEGE, 
'Ability to patch software/data and clone configuration/data');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('FULL_TARGET', MGMT_USER.TARGET_PRIVILEGE, 
'Includes all target privileges and the ability to delete the target and configure credentials');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) 
VALUES ('VIEW_ANY_REPORT', MGMT_USER.SYSTEM_PRIVILEGE,
'Ability to view any report');

-- TARGET_GROUP Privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) 
VALUES ('CREATE_TARGET_IN_GROUP', MGMT_USER.TARGET_GROUP_PRIVILEGE, 
'Ability to create targets in a target group and grant privileges on the group'); 

-- JOB Privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('VIEW_JOB', MGMT_USER.JOB_PRIVILEGE, 
'Ability to view, and do a create like on a job');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('FULL_JOB', MGMT_USER.JOB_PRIVILEGE, 
'Ability to submit, modify, do a create like and delete a job');

-- Report Definition privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('VIEW_REPORT', MGMT_USER.REPORT_PRIVILEGE, 
'Ability to view report definition and stored reports, generate on demand reports and do a create like');

-- Template privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('VIEW_TEMPLATE', MGMT_USER.TEMPLATE_PRIVILEGE, 
'Ability to view a template and apply it to any target on which you have OPERATOR_TARGET');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('FULL_TEMPLATE', MGMT_USER.TEMPLATE_PRIVILEGE, 
'Ability to view a template and apply it to any target on which you have OPERATOR_TARGET');

-- Corrective Action privileges
INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('VIEW_CA', MGMT_USER.CA_PRIVILEGE, 
'Internal privilege, not for granting');

INSERT INTO MGMT_PRIVS (priv_name, priv_type, description)
VALUES ('FULL_CA', MGMT_USER.CA_PRIVILEGE, 
'Internal privilege, not for granting');

END;
/

rem
rem Privilege includes
rem 

-- FULL_TARGET gets all the the other TARGET privileges
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_TARGET', 'VIEW_TARGET');
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_TARGET', 'OPERATOR_TARGET');
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_TARGET', 'MAINTAIN_TARGET');
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_TARGET', 'CLONE_FROM_TARGET');

-- Every TARGET privilege includes VIEW_TARGET
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('OPERATOR_TARGET', 'VIEW_TARGET');
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('MAINTAIN_TARGET', 'VIEW_TARGET');
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('CLONE_FROM_TARGET', 'VIEW_TARGET');
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('CREATE_TARGET_IN_GROUP','VIEW_TARGET'); 

-- The MAINTAIN_TARGET privilege gives you the OPERATOR_TARGET privilege
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('MAINTAIN_TARGET', 'OPERATOR_TARGET');

rem The VIEW_ANY_TARGET System privilege includes the EM_MONITOR System
rem  privilege
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('VIEW_ANY_TARGET', 'EM_MONITOR');

-- FULL_JOB includes VIEW_JOB
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_JOB', 'VIEW_JOB');

-- FULL_TEMPLATE includes VIEW_TEMPLATE
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_TEMPLATE', 'VIEW_TEMPLATE');

-- FULL_CA includes VIEW_CA
INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_CA', 'VIEW_CA');

rem
rem Grant SUPER_USER privilege to user creating the package and to SYS
rem and SYSTEM
rem
INSERT INTO MGMT_PRIV_GRANTS (GRANTEE, PRIV_NAME, GRANTEE_IS_ROLE)
VALUES ('&EM_REPOS_USER', 'SUPER_USER', 0);

INSERT INTO MGMT_PRIV_GRANTS (GRANTEE, PRIV_NAME, GRANTEE_IS_ROLE)
VALUES ('SYS', 'SUPER_USER', 0);

INSERT INTO MGMT_PRIV_GRANTS (GRANTEE, PRIV_NAME, GRANTEE_IS_ROLE)
VALUES ('SYSTEM', 'SUPER_USER', 0);

rem
rem Create a notification profile for the package owner
rem
INSERT INTO MGMT_NOTIFY_PROFILES (PROFILE_NAME) VALUES ('&EM_REPOS_USER');

rem
rem Setup the repository owner as a system user
rem
BEGIN
INSERT INTO MGMT_CREATED_USERS (USER_NAME, SYSTEM_USER) 
VALUES (UPPER(TRIM('&EM_REPOS_USER')), MGMT_USER.SYSTEM_USER );
END;
/

COMMIT;

--
-- Register all login assistants here
--
BEGIN
DECLARE l_login_assistants SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
  BEGIN
    MGMT_LOGIN_ASSISTANT.register_login_assistant(MGMT_LOGIN_ASSISTANT.REP_LOGIN_ASSISTANT,
    'oracle.sysman.emSDK.sec.auth.RepositoryLoginAssistant');

    MGMT_LOGIN_ASSISTANT.register_login_assistant(MGMT_LOGIN_ASSISTANT.ARU_LOGIN_ASSISTANT,
    'oracle.sysman.emSDK.sec.auth.ARULoginAssistant');

    -- Note: ARU authentication is disabled by default. To enable it,
    -- call MGMT_LOGIN_ASSISTANT.configure_aru_authentication with the
    -- right parameters and then call login_assistant_order to change
    -- the authentication order, if required
    l_login_assistants.extend(1);
    l_login_assistants(1) := MGMT_LOGIN_ASSISTANT.REP_LOGIN_ASSISTANT;
    MGMT_LOGIN_ASSISTANT.login_assistant_order(l_login_assistants);
    COMMIT;

  END;
END;
/

BEGIN

  mgmt_log.register_logging_module (MGMT_USER.USERMODEL_MODULE_NAME, 
				    'User Model');

  em_target.add_tgt_pre_deletion_callback('MGMT_USER.HANDLE_TARGET_DELETED');

  INSERT INTO MGMT_TARGET_DELETE_EXCEPTIONS (table_name)
  VALUES ('MGMT_USER_TARGETS');

  COMMIT;

END;
/



-- Enable the VPD policy if the DBMS_RLS package is present
DECLARE
 fga_option VARCHAR2(32);
 no_policy EXCEPTION;
 PRAGMA EXCEPTION_INIT(no_policy, -28102);
BEGIN

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

  DELETE FROM MGMT_PARAMETERS 
  WHERE parameter_name = MGMT_USER.TARGET_POLICY_ENABLED;

  -- If fine grained access control is available
  -- IF fga_option = 'TRUE'
  IF '&EM_REPOS_MODE' = EMD_MAINTENANCE.G_COMPONENT_MODE_CENTRAL
  THEN
    -- Drop the old policy on the targets table
    BEGIN
      dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_TARGETS', 
                           MGMT_USER.EM_TARGET_POLICY_NAME); 
    EXCEPTION
    WHEN no_policy
    THEN
      -- No policy so ignore
      NULL;
    END;

    -- Add the new policy to the targets table
    dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_TARGETS', MGMT_USER.EM_TARGET_POLICY_NAME, 
                        '&EM_REPOS_USER', 'MGMT_USER.EM47455450524544494341544554',
                        'SELECT', TRUE);

    -- Drop the old policy on the jobs table
    BEGIN
      dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_JOB', MGMT_USER.EM_JOB_POLICY_NAME);
    EXCEPTION
    WHEN no_policy
    THEN
      -- No policy so ignore
      NULL;
    END;

    -- Add the new policy on the jobs table
    dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_JOB', MGMT_USER.EM_JOB_POLICY_NAME, '&EM_REPOS_USER',
                        'MGMT_USER.EM4745545052454449434154454A',
                        'SELECT', TRUE);

    -- Drop the old policy on the templates table
    BEGIN
      dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_TEMPLATES', 
                           MGMT_USER.EM_TEMPLATE_POLICY_NAME);
    EXCEPTION
    WHEN no_policy
    THEN
      -- No policy so ignore
      NULL;
    END;
                                                                                
    -- Add the new policy on the templates table
    dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_TEMPLATES', 
                        MGMT_USER.EM_TEMPLATE_POLICY_NAME, '&EM_REPOS_USER',
                        'MGMT_USER.GET_TEMPLATE_PREDICATE',
                        'SELECT', TRUE);

    -- Drop the old policy on the report definition table
    BEGIN
      dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_IP_REPORT_DEF', 
                           MGMT_USER.EM_REPORT_DEF_POLICY_NAME);
    EXCEPTION
    WHEN no_policy
    THEN
      -- No policy so ignore
      NULL;
    END;
                                                                                
    -- Add the new policy on the report definition table
    dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_IP_REPORT_DEF', 
                        MGMT_USER.EM_REPORT_DEF_POLICY_NAME, '&EM_REPOS_USER',
                        'MGMT_USER.GET_REPORT_DEF_PREDICATE',
                        'SELECT', TRUE);

    -- Drop the old policy on the stored report table
    BEGIN
      dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_IP_STORED_REPORT',
                           MGMT_USER.EM_REPORT_POLICY_NAME);
    EXCEPTION
    WHEN no_policy
    THEN
      -- No policy so ignore
      NULL;
    END;
                                                                                
    -- Add the new policy on the stored report table
    dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_IP_STORED_REPORT',
                        MGMT_USER.EM_REPORT_POLICY_NAME, '&EM_REPOS_USER',
                        'MGMT_USER.GET_STORED_REPORT_PREDICATE',
                        'SELECT', TRUE);

    -- Drop the old policy on the corrective actions table
    BEGIN
      dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_CORRECTIVE_ACTION', 
                           MGMT_USER.EM_CA_POLICY_NAME);
    EXCEPTION
    WHEN no_policy
    THEN
      -- No policy so ignore
      NULL;
    END;
                                                                                
    -- Add the new policy on the corrective actions table
    dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_CORRECTIVE_ACTION', 
                        MGMT_USER.EM_CA_POLICY_NAME, '&EM_REPOS_USER',
                        'MGMT_USER.GET_CA_PREDICATE',
                        'SELECT', TRUE);

    INSERT INTO mgmt_parameters  
     (parameter_name, parameter_value, parameter_comment, internal_flag) 
    VALUES 
     (MGMT_USER.TARGET_POLICY_ENABLED, 'Y', 
      'Indicates whether fine grained access control will be used', 1);
    -- SET EM USER CONTEXT FOR REPOS USER
    SETEMUSERCONTEXT('&EM_REPOS_USER', MGMT_USER.OP_SET_IDENTIFIER);

  ELSE
    INSERT INTO mgmt_parameters  
     (parameter_name, parameter_value, parameter_comment, internal_flag) 
    VALUES 
     (MGMT_USER.TARGET_POLICY_ENABLED, 'N', 
      'Indicates whether fine grained access control will be used', 1);
  END IF;

  COMMIT;

END;
/

-- Add a new system scoped privilege for DB11.2 
@@&&EM_SQL_ROOT/core/10.2.0.4.2/user_model/user_model_data_upgrade.sql