De-Register Database Vault on RAC DB

Overview:

In this article we explain the process of  how to De-Register DBV on RAC DB.

Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs. Application data can be protected with policies that control access by setting up the rules.

Environment Details

Hostname	Node1 : db01 Node 2: db02
Database version	11.2.0.4
Database Environment	OMFDB

Disable DB vault 

Login with DBVOWNER to DB and disable the Rules

$sqlplus -s / as sysdba

SQL>set lines 200

SQL>set pages 0

SQL>set feed off

SQL>select status from dba_registry where COMP_NAME='Oracle Database Vault';

STATUS

-----------

VALID

SQL>conn dbvowner/******

Connected.

SQL>BEGIN

DBMS_MACADM.UPDATE_COMMAND_RULE(

  command         => 'CONNECT',

  rule_set_name   =>  'LIMIT_DIP_MONITOR_USER',

  object_owner => NULL,

  object_name => NULL,

  enabled         => DBMS_MACUTL.G_NO);    ----> it will disable the DBV rule and it will allow to application to connect db

END;

/

PL/SQL procedure successfully completed.

SQL>BEGIN

  DBMS_MACADM.UPDATE_RULE_SET (

         rule_set_name => 'LIMIT_DIP_MONITOR_USER',

         description => 'Limit DIP_MONITOR_USER Access to certain IP',

         enabled => DVSYS.DBMS_MACUTL.G_NO,

         eval_options  => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ANY,

         audit_options => DVSYS.DBMS_MACUTL.G_RULESET_AUDIT_FAIL,

         fail_options => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,

         fail_message => 'Rule Set Limit DIP_MONITOR_USER Access failed.',

         fail_code => -20001,

         handler_options  => DVSYS.DBMS_MACUTL.G_RULESET_HANDLER_OFF,

         handler         => NULL);

END;

/

PL/SQL procedure successfully completed.

SQL> conn dbvowner/*******

Connected.

SQL>  select ENABLED from dba_dv_rule_set where RULE_SET_NAME='LIMIT_DIP_MONITOR_USER';

E

-

N

Run following scripts to remove Vault.

SQL> set line 200

SQL> SELECT HOST_NAME,name,INSTANCE_NAME,open_mode,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM gv$database,sys.gv_$instance;

HOST_NAME               NAME      INSTANCE_NAME    OPEN_MODE      DB Startup Time

---------------------------------------------------------------- --------- ---------------- 

dm01.tfn.com           OMFDB  omfdb01        READ WRITE           14-JAN-2017 

dm01.tfn.com           OMFDB  omfdb02        READ WRITE           14-JAN-2017 

SQL> update dvsys.config$ set status=0;

1 row updated.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/dvremov.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row deleted.

1 row deleted.

PL/SQL procedure successfully completed.

Drop DBV users and grant back the roles and privs to DBA

SQL> drop user dbvowner cascade;

User dropped.

SQL> drop user dbvacctmgr cascade;

User dropped.

SQL> grant BECOME USER to DBA;

Grant succeeded.

SQL> grant SELECT ANY TRANSACTION to DBA;

Grant succeeded.

SQL> grant CREATE ANY JOB to DBA;

Grant succeeded.

SQL> grant CREATE EXTERNAL JOB to DBA;

Grant succeeded.

SQL> grant EXECUTE ANY PROGRAM to DBA;

Grant succeeded.

SQL> grant EXECUTE ANY CLASS to DBA;

Grant succeeded.

SQL> grant MANAGE SCHEDULER to DBA;

Grant succeeded.

SQL> grant DEQUEUE ANY QUEUE to DBA;

Grant succeeded.

SQL> grant ENQUEUE ANY QUEUE to DBA;

Grant succeeded.

SQL> grant MANAGE ANY QUEUE to DBA;

Grant succeeded.

SQL> grant BECOME USER to IMP_FULL_DATABASE;

Grant succeeded.

SQL> grant MANAGE ANY QUEUE to IMP_FULL_DATABASE;

Grant succeeded.

SQL> grant DBA to INFA_ADMIN;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR_D to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR_LOGREP_DICT to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_LOGMNR_SESSION to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON DBMS_FILE_TRANSFER to EXECUTE_CATALOG_ROLE;

Grant succeeded.

SQL> grant EXECUTE ON UTL_FILE to PUBLIC;

Grant succeeded.

SQL> grant CREATE ANY JOB to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant CREATE EXTERNAL JOB to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant EXECUTE ANY PROGRAM to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant EXECUTE ANY CLASS to SCHEDULER_ADMIN;

Grant succeeded.

SQL> grant MANAGE SCHEDULER to SCHEDULER_ADMIN;

Grant succeeded.

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=FALSE SCOPE=SPFILE sid='*';

System altered.

SQL> ALTER SYSTEM SET RECYCLEBIN='ON' SCOPE=SPFILE sid='*';

System altered.

SQL> ALTER SYSTEM SET SQL92_SECURITY=FALSE SCOPE=SPFILE sid='*';

System altered.

Check the status of DBV 

SQL> select status from dba_registry where COMP_NAME='Oracle Database Vault';

no rows selected

exit

Bounce the DB 

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

$ srvctl stop database -d omfdb

$ srvctl status database -d omfdb

Instance omfdb1 is not running on node dm01

Instance omfdb2 is not running on node dm02

$ srvctl start database -d omfdb

$ srvctl status database -d omfdb

Instance omfdb1 is running on node dm01

Instance omfdb2 is running on node dm02

Verify DBV removed completely.

SQL> select status from dba_registry where COMP_NAME='Oracle Database Vault';

no rows selected

exit

Conculsion:

In this article we learnt the process of  how to De-Register DBV on RAC DB.