Control Points After DB Vault Installation to Oracle 12c Database

Capture

After DB vault installation to Oracle12c database you should check some important points. At this note I will give some explanations about these post-operations .

If you want to learn DB vault Installation ; Please look at DB Vault Installation post

1. Check Invalid objects one more time;

Normally System Objects would be made valid with this script

@?/rdbms/admin/utlrp.sql

But you should check application objects as well. Because some application objects can not be valid for some reasons. You should report all these changes.

As you know before DB Vault installation we created a table to hold all invalid objects at the database ( the name of this table a_dba_objects). Now take one more sample for invalid object to compare it before image.

SQL> create table b_dba_objects as select owner,object_name,object_type from dba_objects where status=’INVALID’ and object_type <> ‘SYNONYM’ ;

Table created.

Now compare two tables after and before tables;

SQL> select * from a_dba_objects minus select * from b_dba_objects
2 ;

no rows selected

Difference should be null as you expected; If there are some changes you should try to solve it. Maybe one more compilation is required.

2. Check Oracle Components 

After DB vaults installation there can be changes at some Oracle component status.Take a copy of dba_registry view  and control the status of each components.

SQL> create table b_dba_registry as select * from dba_registry;

Table created.

SQL> column comp_name format a50
SQL> column status format a10
SQL> select comp_name, status from dba_registry;

COMP_NAME STATUS
————————————————– ———-
Oracle Database Vault VALID
Oracle Application Express VALID
Oracle Label Security VALID
Spatial VALID
Oracle Multimedia VALID
Oracle Text VALID
Oracle Workspace Manager VALID
Oracle XML Database VALID
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
Oracle Real Application Clusters OPTION OFF

16 rows selected.

3. Make a copy of views about privileges

This is required operation; Maybe for somehow some privileges may change during installation and this causes some problems at your applications. At the same time you should copy all Oracle parameters into a table.

SQL> create table b_dba_network_acls as select * FROM cdb_network_acls;

Table created.

SQL> create table b_dba_network_acl_privileges as select * from cdb_network_acl_privileges;

Table created.

SQL> create table b_gv$parameter as select * from gv$parameter ;

Table created.

SQL> create table b_dba_tab_privs as Select * from dba_tab_privs;

Table created.

SQL> create table b_dba_sys_privs as Select * from dba_sys_privs;

Table created.
SQL> create table b_dba_role_privs as Select * from dba_role_privs;

Table created.

 

4. Re-grant all privileges which are revoked during DB Vault Installation

During DB vault installation Oracle revoke some system and objects privileges from some roles and Public. This situation can create problems at your application. Therefore If you want to re-grant all these privileges you can use below script.

connect sys as sysdba

Grant EXECUTE on SYS.DBMS_FILE_TRANSFER to EXECUTE_CATALOG_ROLE ;
Grant EXECUTE on SYS.DBMS_LOGMNR to EXECUTE_CATALOG_ROLE ;
Grant EXECUTE on SYS.DBMS_LOGMNR_D to EXECUTE_CATALOG_ROLE ;
Grant EXECUTE on SYS.DBMS_LOGMNR_LOGREP_DICT to EXECUTE_CATALOG_ROLE ;
Grant EXECUTE on SYS.DBMS_LOGMNR_SESSION to EXECUTE_CATALOG_ROLE ;
Grant EXECUTE on SYS.UTL_FILE to PUBLIC ;
Grant BECOME USER to DBA ;
Grant CREATE ANY JOB to DBA ;
Grant CREATE EXTERNAL JOB to DBA ;
Grant DEQUEUE ANY QUEUE to DBA ;
Grant ENQUEUE ANY QUEUE to DBA ;
Grant EXECUTE ANY CLASS to DBA ;
Grant EXECUTE ANY PROGRAM to DBA ;
Grant MANAGE ANY QUEUE to DBA ;
Grant MANAGE SCHEDULER to DBA ;
Grant SELECT ANY TRANSACTION to DBA ;
Grant BECOME USER to IMP_FULL_DATABASE ;
Grant MANAGE ANY QUEUE to IMP_FULL_DATABASE ;
Grant CREATE ANY JOB to SCHEDULER_ADMIN ;
Grant CREATE EXTERNAL JOB to SCHEDULER_ADMIN ;
Grant EXECUTE ANY CLASS to SCHEDULER_ADMIN ;
Grant EXECUTE ANY PROGRAM to SCHEDULER_ADMIN ;
Grant MANAGE SCHEDULER to SCHEDULER_ADMIN ;

5. Disable Default Realms and Command Rules

After DB Vault installation some pre-defined Realms and Command rules is created by Oracle. If this is your first installation you want to disable some Realms and Command Rules. Beacause these pre-defined Realms and Command rules can create some problems at your application. This control point completely depends on your application. But I will give you below script to disable all Realms and Command Rules . After some time you can enable these rules step bye step by checking your application .

connect dvowner

select * from dvsys.DBA_DV_REALM ;

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Database Vault’,
description => ‘Defines the realm for the Oracle Database Vault schemas – DVSYS, DVF and LBACSYS where Database Vault access control configuration and roles are contained.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Database Vault Account Management’,
description => ‘Defines the realm for administrators who create and manage database accounts and profiles.’,
enabled => ‘N’,
audit_options => 1);
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Enterprise Manager’,
description => ‘Defines the Enterprise Manager monitoring and management realm.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Default Schema Protection Realm’,
description => ‘Defines the realm for the Oracle Default schemas.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle System Privilege and Role Management Realm’,
description => ‘Defines the realm to control granting of system privileges and database administrator roles.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Default Component Protection Realm’,
description => ‘Defines the realm to protect default components of the Oracle database.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle System Privilege and Role Management Realm’,
description => ‘Defines the realm to control granting of system privileges and database administrator roles.’,
enabled => ‘N’,
audit_options => 1);
END;
/
commit;
select * from dvsys.DBA_DV_COMMAND_RULE;

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER PROFILE’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER SYSTEM’,
rule_set_name => ‘Allow Fine Grained Control of System Parameters’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER USER’,
rule_set_name => ‘Can Maintain Own Account’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘CHANGE PASSWORD’,
rule_set_name => ‘Can Maintain Own Account’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘CREATE PROFILE’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘CREATE USER’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘DROP PROFILE’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘DROP USER’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

commit;

select * from dvsys.DBA_DV_REALM ;

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Database Vault’,
description => ‘Defines the realm for the Oracle Database Vault schemas – DVSYS, DVF and LBACSYS where Database Vault access control configuration and roles are contained.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Database Vault Account Management’,
description => ‘Defines the realm for administrators who create and manage database accounts and profiles.’,
enabled => ‘N’,
audit_options => 1);
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Enterprise Manager’,
description => ‘Defines the Enterprise Manager monitoring and management realm.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Default Schema Protection Realm’,
description => ‘Defines the realm for the Oracle Default schemas.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle System Privilege and Role Management Realm’,
description => ‘Defines the realm to control granting of system privileges and database administrator roles.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle Default Component Protection Realm’,
description => ‘Defines the realm to protect default components of the Oracle database.’,
enabled => ‘N’,
audit_options => 1);
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_REALM(
realm_name => ‘Oracle System Privilege and Role Management Realm’,
description => ‘Defines the realm to control granting of system privileges and database administrator roles.’,
enabled => ‘N’,
audit_options => 1);
END;
/

select * from dvsys.DBA_DV_COMMAND_RULE;

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER PROFILE’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER SYSTEM’,
rule_set_name => ‘Allow Fine Grained Control of System Parameters’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER USER’,
rule_set_name => ‘Can Maintain Own Account’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘CHANGE PASSWORD’,
rule_set_name => ‘Can Maintain Own Account’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘CREATE PROFILE’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘CREATE USER’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘DROP PROFILE’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘DROP USER’,
rule_set_name => ‘Can Maintain Accounts/Profiles’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

commit;

 

6. Make Recyclebin On

After DB vault installation Oracle makes recyclebin off for some security reason. If you want you can make it on  again. You can use below script.

Connect dvowner

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER SYSTEM’,
rule_set_name => ‘Allow Fine Grained Control of System Parameters’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘N’);
commit;
END;
/

Connect sys as sysdba

alter system set recyclebin=on scope=spfile;

startup force;

You can make ALTER SYSTEM Command Rule enable again

Connect dvowner

BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => ‘ALTER SYSTEM’,
rule_set_name => ‘Allow Fine Grained Control of System Parameters’,
object_owner => ‘%’,
object_name => ‘%’,
enabled => ‘Y’);
commit;
END;
/

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s