Rem Copyright (c) 2004, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem    NAME
Rem      catmacg.sql
Rem    DESCRIPTION
Rem      data vault support script
Rem    NOTES
Rem      Must be compiled after dbms_output package.
Rem      Found on asktom.oracle.com
Rem    MODIFIED (MM/DD/YY)
Rem    jibyun    05/03/11 - Backport jibyun_bug-12356827 from main
Rem    jibyun    03/02/11 - Backport jibyun_bug-11662436 from main
Rem    jheng     01/24/11 - Backport jheng_bug-7137958 from main
Rem    jheng     02/18/09 - Grant select on dba_dv_job_auth to dv_secanalyst
Rem    clei      12/10/08 - DV_PATCH -> DV_PATCH_ADMIN
Rem    jibyun    05/09/08 - Bug 7550987: Create DV_STREAMS_ADMIN role
Rem    jibyun    10/18/08 - Bug 7489862: Add admin option to the grants of
Rem                         dv_admin, dv_secanalyst, dv_public to dv_owner
Rem    jsamuel   10/28/08 - remove error messages
Rem    ruparame  08/18/08 - Bug 7319691: Create DV_MONITOR role
Rem    clei      08/28/08 - bug 6435192: add dv_patch role
Rem    pknaggs   07/07/08 - bug 6938028: add Factor and Role support for DVPS.
Rem    youyang   05/22/08 - Bug fix:7022650, update dv_secanalyst role to read
Rem                         the dvsys.audit_trail$ table
Rem    pknaggs   04/11/08 - bug 6938028: Database Vault protected schema.
Rem    jibyun    10/31/07 - To fix Bug 6441524
Rem    jciminsk  05/02/06 - cleanup embedded file boilerplate 
Rem    jciminsk  05/02/06 - created admin/catmacg.sql 
Rem    sgaetjen  08/11/05 - sgaetjen_dvschema
Rem    sgaetjen  08/05/05 - Merge into ADE with Protected Schema 
Rem    sgaetjen  07/28/05 - dos2unix
Rem    raustin   01/31/05 - Created spec



CREATE OR REPLACE VIEW DVSYS.dv$out
AS
SELECT ROWNUM lineno, dbms_macout.get_line( ROWNUM ) text
   FROM all_objects
  WHERE ROWNUM < ( SELECT dbms_macout.get_line_count FROM dual );



Rem
Rem
Rem
Rem    DESCRIPTION
Rem      Creates DV roles 
Rem
Rem
Rem
Rem
Rem

BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_secanalyst';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/

BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_monitor';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/

BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_admin';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_owner';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_acctmgr';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_public';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_patch_admin';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN    
EXECUTE IMMEDIATE 'CREATE ROLE dv_streams_admin';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE; 
     END IF;
END;   
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_goldengate_admin';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_xstream_admin';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE dv_goldengate_redo_access';
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -1921) THEN NULL; --role already created
     ELSE RAISE;
     END IF;
END;
/

Rem
Rem
Rem
Rem    DESCRIPTION
Rem      Grants to DV_PUBLIC for DVSYS functions
Rem
Rem
Rem
Rem

-- procedures and functions
GRANT EXECUTE ON dvsys.get_factor TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.get_factor_label TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.set_factor TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.get_trust_level TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.get_trust_level_for_identity TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.role_is_enabled TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.predicate_true TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.is_rls_authorized_by_realm TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.is_secure_application_role TO DV_PUBLIC
/
-- packages
GRANT EXECUTE ON dvsys.dbms_macsec_roles TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.dbms_macols_session TO DV_PUBLIC
/
GRANT EXECUTE ON dvsys.dbms_macutl TO DV_PUBLIC
/


GRANT DV_PUBLIC TO PUBLIC
/
Rem
Rem
Rem
Rem    DESCRIPTION
Rem      Creates  PUBLIC synonyms for DVSYS objects
Rem
Rem
Rem
Rem

-- packages
CREATE OR REPLACE PUBLIC SYNONYM dbms_macadm FOR dvsys.dbms_macadm
/

CREATE OR REPLACE PUBLIC SYNONYM dbms_macsec_roles FOR dvsys.dbms_macsec_roles
/

CREATE OR REPLACE PUBLIC SYNONYM dbms_macutl FOR dvsys.dbms_macutl
/

-- procedures and functions
CREATE OR REPLACE PUBLIC SYNONYM get_factor FOR dvsys.get_factor
/

CREATE OR REPLACE PUBLIC SYNONYM get_factor_label FOR dvsys.get_factor_label
/

CREATE OR REPLACE PUBLIC SYNONYM set_factor FOR dvsys.set_factor
/

CREATE OR REPLACE PUBLIC SYNONYM get_trust_level FOR dvsys.get_trust_level
/

CREATE OR REPLACE PUBLIC SYNONYM get_trust_level_for_identity FOR dvsys.get_trust_level_for_identity
/

CREATE OR REPLACE PUBLIC SYNONYM role_is_enabled FOR dvsys.role_is_enabled
/

CREATE OR REPLACE PUBLIC SYNONYM is_secure_application_role FOR dvsys.is_secure_application_role
/

-- the statement above will be invalidated by the change in public synonym here
ALTER PACKAGE dvsys.dbms_macols COMPILE BODY
/

ALTER PACKAGE dvsys.dbms_macols_session COMPILE BODY
/

Rem
Rem
Rem
Rem    DESCRIPTION
Rem      Creates roles and grants for DVSYS Administration role (DV_ADMIN)
Rem
Rem
Rem
Rem
Rem

-- give the manager table and view access
GRANT dv_secanalyst TO dv_admin
/

-- packages
-- we only want them to be able to use the API for CRUD
-- the triggers use the rest of these packages
-- the dbms_macsec_roles is granted to public
GRANT EXECUTE ON dvsys.dbms_macadm TO dv_admin
/

GRANT EXECUTE ON dvsys.dbms_macout TO dv_admin
/

GRANT SELECT ON dvsys.dv$out TO dv_admin
/

-- tables need to remove dep. in UI for views and can remove these
GRANT SELECT ON dvsys.audit_trail$ TO dv_admin;
GRANT SELECT ON dvsys.code$ TO dv_admin;
GRANT SELECT ON dvsys.code_t$ TO dv_admin;
GRANT SELECT ON dvsys.command_rule$ TO dv_admin;
GRANT SELECT ON dvsys.document$ TO dv_admin;
GRANT SELECT ON dvsys.factor$ TO dv_admin;
GRANT SELECT ON dvsys.factor_t$ TO dv_admin;
GRANT SELECT ON dvsys.factor_link$ TO dv_admin;
GRANT SELECT ON dvsys.factor_scope$ TO dv_admin;
GRANT SELECT ON dvsys.factor_type$ TO dv_admin;
GRANT SELECT ON dvsys.factor_type_t$ TO dv_admin;
GRANT SELECT ON dvsys.identity$ TO dv_admin;
GRANT SELECT ON dvsys.identity_map$ TO dv_admin;
GRANT SELECT ON dvsys.mac_policy$ TO dv_admin;
GRANT SELECT ON dvsys.mac_policy_factor$ TO dv_admin;
GRANT SELECT ON dvsys.policy_label$ TO dv_admin;
GRANT SELECT ON dvsys.realm$ TO dv_admin;
GRANT SELECT ON dvsys.realm_t$ TO dv_admin;
GRANT SELECT ON dvsys.realm_auth$ TO dv_admin;
GRANT SELECT ON dvsys.realm_object$ TO dv_admin;
GRANT SELECT ON dvsys.realm_command_rule$ TO dv_admin;
GRANT SELECT ON dvsys.role$ TO dv_admin;
GRANT SELECT ON dvsys.rule$ TO dv_admin;
GRANT SELECT ON dvsys.rule_t$ TO dv_admin;
GRANT SELECT ON dvsys.rule_set$ TO dv_admin;
GRANT SELECT ON dvsys.rule_set_t$ TO dv_admin;
GRANT SELECT ON dvsys.monitor_rule$ TO dv_admin;
GRANT SELECT ON dvsys.monitor_rule_t$ TO dv_admin;
GRANT SELECT ON dvsys.rule_set_rule$ TO dv_admin;
GRANT SELECT ON dvsys.dv_auth$ to dv_admin;
Rem
Rem
Rem
Rem    DESCRIPTION
Rem      Grants for DV roles (DV_OWNER,DV_SECANALYST) on DV objects
Rem
Rem
Rem
Rem
Rem

-- DV_MONITOR
GRANT SELECT ON dvsys.audit_trail$ TO dv_monitor;
GRANT SELECT ON dvsys.dv$realm_auth TO dv_monitor;
GRANT SELECT ON dvsys.dv$rule_set TO dv_monitor;
GRANT SELECT ON dvsys.dv$rule_set_rule TO dv_monitor;
GRANT SELECT ON dvsys.dv$realm_object TO dv_monitor;
GRANT SELECT ON dvsys.dv$sys_grantee TO dv_monitor;
GRANT SELECT ON dvsys.dv$sys_object_owner TO dv_monitor;
GRANT SELECT ON dvsys.dv$command_rule TO dv_monitor;
GRANT SELECT ON dvsys.dba_dv_code TO dv_monitor;
GRANT SELECT ON dvsys.dba_dv_command_rule TO dv_monitor;
GRANT SELECT ON dvsys.audit_trail$ to dv_monitor;
GRANT SELECT ON dvsys.dba_dv_job_auth to dv_monitor;
GRANT SELECT ON dvsys.dba_dv_datapump_auth to dv_monitor;

-- DV_SECANALYST
GRANT SELECT ON dvsys.dba_dv_job_auth TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_datapump_auth TO dv_secanalyst;
GRANT SELECT ON dvsys.audit_trail$ TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_code TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_command_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_document TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_factor TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_factor_link TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_factor_scope TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_factor_type TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_identity TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_identity_map TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_mac_policy TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_mac_policy_factor TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_monitor_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_policy_label TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_realm TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_realm_auth TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_realm_object TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_realm_command_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_role TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_rule_set TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_rule_set_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_user_privs TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_pub_privs TO dv_secanalyst;
GRANT SELECT ON dvsys.dba_dv_user_privs_all TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$code TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$command_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$document TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$factor TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$factor_link TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$factor_scope TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$factor_type TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$identity TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$identity_map TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$mac_policy TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$mac_policy_factor TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$monitor_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$ols_policy TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$ols_policy_label TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$policy_label TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$realm TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$realm_auth TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$realm_object TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$realm_command_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$role TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$rule_set TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$rule_set_rule TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$sys_grantee TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$sys_object TO dv_secanalyst;
GRANT SELECT ON dvsys.dv$sys_object_owner TO dv_secanalyst;

-- DV_OWNER
-- give the manager table and view access and execute privs that the DV administrator role has
GRANT dv_admin TO dv_owner with admin option;
GRANT dv_patch_admin to dv_owner with admin option;
GRANT dv_streams_admin to dv_owner with admin option;
GRANT dv_secanalyst TO dv_owner with admin option;
GRANT dv_public TO dv_owner with admin option;
GRANT dv_monitor to dv_owner with admin option;
GRANT dv_goldengate_admin to dv_owner with admin option;
GRANT dv_xstream_admin to dv_owner with admin option;
GRANT dv_goldengate_redo_access to dv_owner with admin option;

-- The SELECT privilege on the Datapump views needs to be granted 
-- to the dv_owner, for macsys to be able to use Datapump to export 
-- the Protected Schema.
--
grant SELECT on dvsys.ku$_dv_realm_view           to dv_owner;
grant SELECT on dvsys.ku$_dv_realm_member_view    to dv_owner;
grant SELECT on dvsys.ku$_dv_realm_auth_view      to dv_owner;
grant SELECT on dvsys.ku$_dv_isr_view             to dv_owner;
grant SELECT on dvsys.ku$_dv_isrm_view            to dv_owner;
grant SELECT on dvsys.ku$_dv_rule_view            to dv_owner;
grant SELECT on dvsys.ku$_dv_rule_set_view        to dv_owner;
grant SELECT on dvsys.ku$_dv_rule_set_member_view to dv_owner;
grant SELECT on dvsys.ku$_dv_command_rule_view    to dv_owner;
grant SELECT on dvsys.ku$_dv_role_view            to dv_owner;
grant SELECT on dvsys.ku$_dv_factor_view          to dv_owner;
grant SELECT on dvsys.ku$_dv_factor_link_view     to dv_owner;
grant SELECT on dvsys.ku$_dv_factor_type_view     to dv_owner;
grant SELECT on dvsys.ku$_dv_identity_view        to dv_owner;
grant SELECT on dvsys.ku$_dv_identity_map_view    to dv_owner;

-- As Streams APIs (MAINTAIN_*) use Datapump during instantiation,
-- DV_STREAMS_ADMIN need to have access to these views.
grant SELECT on dvsys.ku$_dv_realm_view           to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_realm_member_view    to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_realm_auth_view      to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_isr_view             to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_isrm_view            to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_rule_view            to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_rule_set_view        to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_rule_set_member_view to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_command_rule_view    to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_role_view            to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_factor_view          to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_factor_link_view     to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_factor_type_view     to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_identity_view        to dv_streams_admin;
grant SELECT on dvsys.ku$_dv_identity_map_view    to dv_streams_admin;