Oracle Database Vaulting in RAC & Single-Instance Databases.

A heartfelt thankyou to everyone who has supported and guided me along the way.

Database Vaulting in RAC & Single-Instance Databases Step-by-Step Guide.

Oracle Database Vault is a security feature that protects Application data and protect sensitive information or data from unauthorized access. Even users with high-level rights, such as database administrators or DBAs, cannot access the data.

Prevent unauthorized to sensitive data, even SYS and DBA privileges user user can't access the data.
SQL instructions that may affect the database's and application's availability and security can be managed with Oracle Database Vault. Before every SQL command, such as CONNECT to the database, DROP TABLE, TRUNCATE TABLE, and DROP TABLESPACE, is executed, Oracle Database Vault Command Rules provide another layer of regulations and checks. By establishing a trusted path between the application and the database, the Command Rules can be used to limit database access to a certain subnet, application server, and program. It is possible to enforce SQL command controls within the database by using built-in elements such IP address, host name, and session user name.

Prerequisites :-

Applying Database vault patch on database . (34836063 - Database Vault Patch for 19C - According to your database version).
Make sure that the account of application schemas are open. To get status of database users use below command

select username,account_status,profile from dba_users;

To Check object count under users use below command

select count(*) , owner from dba_segments where owner in('<owner_name>') group by owner;

col OWNER for a30
select count(*) , owner from dba_segments where owner in
('DVF','DVSYS') group by owner;

To check roles assigned to users use below command .
Select * from dba_role_privs where grantee in('<grantee>') order by GRANTEE;

col GRANTEE for a30
col PRIVILEGE for a30
Select * from dba_role_privs where grantee in ('DVF','DVSYS') order by GRANTEE;

Check privileges on table to users

select distinct grantee,PRIVILEGE from dba_tab_privs where grantee in ('DVF','DVSYS') order by GRANTEE;

Make sure that the application schemas should be independent schemas, (Here independent schemas means, one schema should not be able to access objects of other schemas.)
To perform Dictionary and Object status gather using below command you can use also parallel. It is not mandatory you can SKIP also.

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

If DICTIONARY_STATS & OBJECTS_STATS taken more time (1hr ) then LOCK the below tables and again start the GATHER STATS of DICTIONARY & OBJECTS. Here we have lock below table due to our SYSAUX tablespace space is around 1.5 TB, so that's why we have lock the below table, if you want then you can skip also.

exec dbms_stats.lock_table_stats('SYS','WRH$_ACTIVE_SESSION_HISTORY');
exec dbms_stats.lock_table_stats('SYS','WRH$_FILESTATXS');
exec dbms_stats.lock_table_stats('SYS','WRH$_EVENT_HISTOGRAM');
exec dbms_stats.lock_table_stats('SYS','WRH$_LATCH');
exec dbms_stats.lock_table_stats('SYS','WRH$_SQLSTAT');
exec dbms_stats.lock_table_stats('SYS','WRP$_REPORTS_DETAILS');

Once the stats gather done then unlock the above table using below command .

exec dbms_stats.unlock_table_stats('SYS','WRH$_ACTIVE_SESSION_HISTORY');
exec dbms_stats.unlock_table_stats('SYS','WRH$_FILESTATXS');
exec dbms_stats.unlock_table_stats('SYS','WRH$_EVENT_HISTOGRAM');
exec dbms_stats.unlock_table_stats('SYS','WRH$_LATCH');
exec dbms_stats.unlock_table_stats('SYS','WRH$_SQLSTAT');
exec dbms_stats.unlock_table_stats('SYS','WRP$_REPORTS_DETAILS');

Install Oracle Label Security by executing the catols.sql script.

SQL> @$ORACLE_HOME/rdbms/admin/catols.sql
Oracle Label Security must be installed before you can use Oracle Database Vault.

Install Oracle Database Vault by executing the catmac.sql script. ( After you run this command you have to put your TABLESPACE name like DEFAULT or Any tablespace_name (Minimum 10GB above) & TEMP )

SQL> @$ORACLE_HOME/rdbms/admin/catmac.sql
At the Enter value for 1 prompt, enter the default tablespace -> TSIDX_1M
At the Enter value for 2 prompt, enter the temporary tablespace -> TEMP1

Precheck : -

Before proceeding with Implementing DB vault on database, make sure to have below prechecks of the Database:

ps -ef | grep pmon
ps -ef | grep -i tns
hostname -i
sqlplus -v
df -kh
uname -a

set pages 2000 lines 300
select name,open_mode,database_role from v$database;
select name,open_mode,log_mode,database_role from gv$database;
select INST_ID,instance_name,host_name from gv$instance;
SELECT INST_ID, NAME,OPEN_MODE FROM GV$PDBS ;
select inst_id,con_id, name, open_mode, RESTRICTED,total_size from gv$pdbs;
select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup ;

archive log list ;
show parameter cpu
show parameter comp
show parameter optimizer
show parameter _dest_
show parameter db_name
show parameter instance_name
show parameter sec

select sum(bytes/1024/1024/1024) from dba_segments;
select sum(bytes/1024/1024/1024) from dba_data_files;
col comp_name for a30
col COMP_NAME format a30
col VERSION format a30
col STATUS format a30
select COMP_NAME,VERSION,STATUS from dba_registry;
select count(*) from dba_objects where status='INVALID';
select object_name,object_type,owner from dba_objects where status='INVALID';
select count(1) from sys.aud$;
select username,account_status from dba_users;
select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';
select RESOURCE_NAME,LIMIT from dba_profiles where profile='VAULT_PROFILE';

set linesize 2000
set lines 1000 pages 499
column realm_name format a40
column col1 format a30
column col2 format a30 Heading "Owner / Grantee "
column col3 format a30 Heading "Object Type/Rule Set Name"
column col4 format a30 Heading "Object Name/Auth Options"
break on realm_name skip 3
select * from (
SELECT realm_Name , 'protected objects' col5, owner col2 , object_type col3 ,object_name col4
FROM dvsys.dba_dv_realm_object
union
select REALM_NAME ,'authorizations' col5, GRANTEE col2 ,AUTH_RULE_SET_NAME col3 , AUTH_OPTIONS col4
from dvsys.dba_dv_realm_auth ) where realm_name like '%access%'
order by realm_name asc ,col5 desc
/
FROM dvsys.dba_dv_realm_object;

set linesize 2000
set lines 1000 pages 499
column COMMAND format a30
column RULE_SET_NAME format a30
column OBJECT_OWNER format a30
column OBJECT_NAME format a30
column ENABLED format a30
column PRIVILEGE_SCOPE format a30
select * from dvsys.DBA_DV_COMMAND_RULE;

column RULE_SET_NAME format a30
column RULE_NAME format a50
column RULE_EXPR format a60
column ENABLED format a8
column RULE_ORDER format 9999
break on RULE_SET_NAME skip 3
select * from dvsys.DBA_DV_RULE_SET_RULE;

select name from DBA_DV_RULE ;
select RULE_SET_NAME,RULE_NAME,RULE_EXPR from DBA_DV_RULE_SET_RULE ;
select name,RULE_EXPR from DVSYS.DBA_DV_RULE ;
SELECT REALM_NAME, GRANTEE, AUTH_RULE_SET_NAME FROM DVSYS.DBA_DV_REALM_AUTH;
select * from dba_role_privs where granted_role='DV_ACCTMGR';
select * from audit_unified_enabled_policies;
select count (*) from dvsys.AUDIT_TRAIL$;

Implementation of Database Vault : -

STEP : 01

First create a profile named VAULT_PROFILE for the schemas which will be needed for DB Vaulting.

SQL> create profile vault_profile limit
PASSWORD_LIFE_TIME unlimited
PASSWORD_VERIFY_FUNCTION NULL; 2 3
Profile created.
SQL>

STEP : 02

Alter the VAULT_PROFILE with the commands given below command:

SQL>
SQL> alter profile vault_profile limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER unlimited
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME unlimited
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS unlimited
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Profile altered.
SQL>

STEP : 03

Check the details of profile VAULT_PROFILE by using below command.

select RESOURCE_NAME,LIMIT from dba_profiles where profile='VAULT_PROFILE';

STEP : 04

Create users DVOWNER, DVMANAGER, DVOWNER_BKP, DVMANAGER_BKP in profile VAULT_PROFILE by using below command.
Oracle strongly recommends that you create two accounts for each role. One account, the primary named user account, will be used on a day-to-day basis and the other account will be used as a backup account in case the password of the primary account is lost and must be reset.

create user DVOWNER identified by "&passwrd" default tablespace SYSAUX profile VAULT_PROFILE;

DVOWNER / DVADMIN :-

It is the primary administrative role in Oracle Database Vault. It allows the user to configure and manage all aspects of Oracle Database Vault.
Create, modify, and delete Database Vault realms, which are used to protect database objects.
Define rules that control the execution of SQL commands based on various conditions.
Set up factors (e.g., IP addresses, time of day) and use them in rules to control access to data and operations.
This role is usually assigned to senior database administrators or security officers responsible for configuring and maintaining Database Vault policies.

create user DVMANAGER identified by "&passwrd" default tablespace SYSAUX profile VAULT_PROFILE;

DVMANAGER : -

It is a more restricted administrative role in Oracle Database Vault. It allows the user to perform certain management tasks but with fewer privileges than DVOWNER .
Similar to DVOWNER , but with limited capabilities.
Limited ability to create or modify command rules.
This role might be assigned to junior administrators or specific users who need to manage certain aspects of Database Vault without having full administrative privileges.

create user DVOWNER_BKP identified by "&passwrd" default tablespace SYSAUX profile VAULT_PROFILE;
create user DVMANAGER_BKP identified by "&passwrd" default tablespace SYSAUX profile VAULT_PROFILE;

DVOWNER_BKP / DVMANAGER_BKP : -

DVOWNER_BKP is backup of DVOWNER & DVMANAGER_BKP is backup of DVMANAGER for future purpose.

STEP : 05

Grant create session to all the Vault users created above by using below command.

SQL> grant create session to DVOWNER,DVMANAGER,DVOWNER_BKP,DVMANAGER_BKP;

STEP : 06

Configure Database Vault using the two user accounts (DVOWNER and DVMANAGER) using below command.

01:23:51 SQL> begin
dvsys.configure_dv (
dvowner_uname => 'DVOWNER',
dvacctmgr_uname => 'DVMANAGER');
end;
/
01:23:52 2 01:23:52 3 01:23:52 4 01:23:52 5 01:23:52 6
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.72
01:23:56 SQL>

STEP : 07

Connect to the DVOWNER user with which you just configured the Oracle Database Vault to enable the Database Vault using below command.

01:24:49 SQL> conn dvowner
Enter password:
Connected.
01:25:11 SQL>
01:25:21 SQL> exec dbms_macadm.enable_dv;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
01:25:32 SQL>

STEP : 08

After Enable the Database Vault then check the status using below command.

01:25:33 SQL> select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';
INST_ID PARAMETER VALUE CON_ID
---------- -------------------------------------------------------
1 Oracle Label Security TRUE 0
1 Oracle Database Vault FALSE 0
2 rows selected.
Elapsed: 00:00:00.01
01:25:44 SQL>

If you after Enable the database The "Oracle Database Vault " parameter value must be TRUE but here the case it is showing FALSE because it reflecting AFTER the restart the database.

STEP : 09

Connect with sysdba privileges and take restart of Database also check the parameter of Vault in v$option view suing below command.

01:25:44 SQL> conn / as sysdba
Connected.
01:25:51 SQL>

01:25:53 SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
01:26:21 SQL>

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.0308E+11 bytes
Fixed Size 8967264 bytes
Variable Size 1.5569E+10 bytes
Database Buffers 8.7242E+10 bytes
Redo Buffers 259465216 bytes
Database mounted.
Database opened.

STEP : 10

SQL> select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';
INST_ID PARAMETER VALUE CON_ID
---------- -------------------------------------------------------
1 Oracle Label Security TRUE 0
1 Oracle Database Vault TRUE 0
2 rows selected.
Elapsed: 00:00:00.01
SQL>

NOW "Oracle Database Vault" is TRUE means Database vaulting is ENABLED.

Here in RAC environment you can put all this command on only one-node then once the Database Vaulting is ENABLED the restart the database one-by-one or you can use SRVCTL command for same.

INST_ID PARAMETER VALUE CON_ID
---------- ------------------------------- ------------- ----------
3 Oracle Label Security TRUE 0
3 Oracle Database Vault TRUE 0
2 Oracle Label Security TRUE 0
2 Oracle Database Vault TRUE 0
1 Oracle Label Security TRUE 0
1 Oracle Database Vault TRUE 0

STEP : 11

Once Vaulting is ENABLED then follow below Sequence .

First Connect to sqlplus / as sysdba
Second Connect to the DVWONER
Third Create a REALM.
Fourth Add object inside the Realm and provide Object_Owner.
Fifth Adding an authentication mechanism to specific Realm.
Sixth add Participant to Realm.
Seventh According to give the other permission as per requirements.

Above mention steps you can run only DVWONER.

NOTE : - Don't share your DVOWNER & DVMANAGER password to other.

This is my Application user : APPLICATION1 , APPLICATION2

This is my Normal User : GANESH, SUSHANT

STEP : 12

oracle@SERVER:/home/oracle#sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 8 01:31:23 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL> conn dvowner
Enter password:
Connected.
SQL>

CREATE REALM : -

A realm is a protective/logical boundary around a set of database objects, such as schemas, tables, or PL/SQL packages. Within this boundary, access is tightly controlled.
You could create a realm around a schema that contains sensitive customer information, ensuring that only authorized users or applications can access it, regardless of their database privileges.

STEP : 13

SQL>begin
dvsys.dbms_macadm.create_realm(realm_name => 'APPLICATION1 access',description =>'APPLICATION1 access control',enabled=> dbms_macutl.g_yes,audit_options =>DBMS_MACUTL.G_REALM_AUDIT_FAIL,realm_type=>1);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

SQL>begin
dvsys.dbms_macadm.create_realm(realm_name => 'APPLICATION2 access',description =>'APPLICATION2 access control',enabled=> dbms_macutl.g_yes,audit_options =>DBMS_MACUTL.G_REALM_AUDIT_FAIL,realm_type=>1);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

STEP : 14

ADD OBJECT TO REALM : -

SQL>begin
dvsys.dbms_macadm.add_object_to_realm(realm_name => 'APPLICATION1 access',object_owner =>'APPLICATION1 ',object_name=>'%',object_type=>'%');
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

SQL>begin
dvsys.dbms_macadm.add_object_to_realm(realm_name => 'APPLICATION2 access',object_owner =>'APPLICATION2',object_name=>'%',object_type=>'%');
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

Here you can mention particular single object in one-by-one (object_name='<Mentition_object_name>'' ) or else you can object_name=>'%' it means all object that present in Application owner.

STEP : 15

ADD AUTHENTICATION TO REALM : -

SQL>begin
dvsys.dbms_macadm.ADD_AUTH_TO_REALM(realm_name => 'APPLICATION1 access',grantee=>'APPLICATION1 ',AUTH_OPTIONS=>DBMS_MACUTL.G_REALM_AUTH_OWNER);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

SQL>begin
dvsys.dbms_macadm.ADD_AUTH_TO_REALM(realm_name => 'APPLICATION2 access',grantee=>'APPLICATION2',AUTH_OPTIONS=>DBMS_MACUTL.G_REALM_AUTH_OWNER);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

STEP : 16

ADD PARTICIPANT TO REALM : -

Here inside object Who can access the object. Now in below command Application1 access by GANESH and Application2 access by Sushant. You can parse multiple user as per your requirements.

SQL> begin dvsys.dbms_macadm.ADD_AUTH_TO_REALM(realm_name => 'APPLICATION1 access',grantee=>'GANESH',AUTH_OPTIONS=>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

SQL>begin
dvsys.dbms_macadm.ADD_AUTH_TO_REALM(realm_name => 'APPLICATION2 access',grantee=>'GANESH',AUTH_OPTIONS=>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

SQL>begin
dvsys.dbms_macadm.ADD_AUTH_TO_REALM(realm_name => 'APPLICATION1 access',grantee=>'SUSHANT',AUTH_OPTIONS=>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

STEP : 17

OHER TO ADD IN REALM : -

FOR DATAPUMP : -

A database administrator wants to export or import the contents of an entire database. In addition to the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles and the authorization granted by the DBMS_MACADM.AUTHORIZE_DATAPUMP_USER procedure, you must grant this user (or a role) the SYS role. ( For Future purpose if you forget DVOWNER and DVMANGER password then you can take full export using database and Rebuild the same without vaulting).

SQL> exec dbms_macadm.authorize_datapump_user('SYS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL>

SQL> exec dbms_macadm.authorize_datapump_user('APPLICATYION1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

FOR The SYS user must be authorized to perform Data Definition Language (DDL) operations in the SYSTEM schema, with below command authorize ddl commands.

SQL> execute dbms_macadm.authorize_ddl('SYS', 'SYSTEM');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>

STEP : 18

FOR SCHEDULAR JOB : -

SQL> EXEC DBMS_MACADM.AUTHORIZE_SCHEDULER_USER('SYS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>

STEP : 19

FOR GOLDENGATE : -

SQL>grant DV_GOLDENGATE_ADMIN,DV_GOLDENGATE_REDO_ACCESS to GGUSER;
Grant succeeded.

Elapsed: 00:00:00.03
SQL>

SQL >BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM
(realm_name => 'Oracle Default Component Protection Realm',grantee => 'GGUSER' ,auth_options => 1);
END ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

SQL>EXEC DBMS_MACADM.ADD_AUTH_TO_REALM('APPLICATION1' access','GGUSER');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

Here you can parse the multiple application SCHEMA as per requirement of GOLDENGATE.

STEP : 20

FOR SAFE SIDE IN FUTURE : -

SQL> grant dv_owner to DVOWNER_BKP;
Grant succeeded.
Elapsed: 00:00:00.06
SQL>

SQL>conn DVOWNER_BKP
Enter password:
Connected.
SQL>
SQL>grant DV_ACCTMGR to DVMANAGER,DVMANAGER_BKP;
Grant succeeded.

Elapsed: 00:00:00.03
SQL>

SQL> conn dvowner_bkp
Enter password:
Connected.
SQL> set linesize 2000
set lines 1000 pages 499
column realm_name format a40
column col1 format a30
column col2 format a30 Heading "Owner / Grantee "
column col3 format a30 Heading "Object Type/Rule Set Name"
column col4 format a30 Heading "Object Name/Auth Options"
break on realm_name skip 3
select * from (
SELECT realm_Name , 'protected objects' col5, owner col2 , object_type col3 ,object_name col4
FROM dvsys.dba_dv_realm_object
union
select REALM_NAME ,'authorizations' col5, GRANTEE col2 ,AUTH_RULE_SET_NAME col3 , AUTH_OPTIONS col4
from dvsys.dba_dv_realm_auth ) where realm_name like '%access%'
order by realm_name asc ,col5 desc
SQL>
REALM_NAME COL5 Owner / Grantee Object Type/Rule Set Name Object Name/Auth Options
--------------------- ----------------- ------------------------ ----- ------------ ------------------------------
APPLICATION1 access protected objects APPLICATION1 % %
authorizations APPLICATION1 Owner
authorizations GANESH Participant

APPLICATION2 access protected objects APPLICATION2 % %
authorizations APPLICATION2 Owner
authorizations SUSHANT Participant
authorizations GANESH Participant
10 rows selected.
Elapsed: 00:00:00.16
SQL>

Using above command you can sea the output in details. This output only show on DVOWNER,DVMANAGER ,DVOWNER_BKP and DVMANAGER_BKP not other user can't access the data.

QUESTIONS : -

01. How to add and remove user in Realm in oracle database vault ?

e.g. ADD SYS user in realm.

SQL> conn dvowner
Enter password:
Connected.
SQL>

SQL>begin
dvsys.dbms_macadm.ADD_AUTH_TO_REALM(realm_name => 'APPLICATION2 access',grantee=>'SYS',AUTH_OPTIONS=>DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

Remove SYS user in realm.

SQL> conn dvowner
Enter password:
Connected.
SQL>

SQL>begin
dvsys.dbms_macadm.DELETE_AUTH_TO_REALM(realm_name => 'APPLICATION2 access',grantee=>'SYS');
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>

02. How to disable database vault ?

First connect as DVOWNER and run below command (exec dbms_macadm.disable_dv;) after that connected to the SYSDBA and restart the database after that you can see the database vaulting is permanently disable. In RAC environment after executing the Disable command then restart the database one-by-one or use SRVCTL command.

SQL> conn dvowner

Enter password:

Connected.

SQL>

SQL> exec dbms_macadm.disable_dv;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
SQL>

02. How to SYS get all DVOWNER privilege's in oracle database vault ? .

Provide Below grant to SYS user.

SQL> conn dvowner
Enter password:
Connected.
SQL> GRANT DV_ADMIN TO SYS ;
Grant succeeded.

Elapsed: 00:00:00.03
SQL>

POSTCHECK : -

After Implementation of database vaulting then start the post check of database using below commands . Once the post-check is done then compare the Pre-check & Post-check for your better understanding.

SQL> conn dvowner_bkp
Enter password:
Connected.
SQL>

col grantee format a30
col schema format a30
select * from dvsys.dba_dv_job_auth;

Listing the Database Vault realms : -

set linesize 2000
set lines 1000 pages 499
column realm_name format a40
column col1 format a30
column col2 format a30 Heading "Owner / Grantee "
column col3 format a30 Heading "Object Type/Rule Set Name"
column col4 format a30 Heading "Object Name/Auth Options"
break on realm_name skip 3
select * from (
SELECT realm_Name , 'protected objects' col5, owner col2 , object_type col3 ,object_name col4
FROM dvsys.dba_dv_realm_object
union
select REALM_NAME ,'authorizations' col5, GRANTEE col2 ,AUTH_RULE_SET_NAME col3 , AUTH_OPTIONS col4
from dvsys.dba_dv_realm_auth ) where realm_name like '%access%'
order by realm_name asc ,col5 desc
/

Listing the Database Vault command rules :-

set linesize 2000
set lines 1000 pages 499
column COMMAND format a30
column RULE_SET_NAME format a30
column OBJECT_OWNER format a30
column OBJECT_NAME format a30
column ENABLED format a30
column PRIVILEGE_SCOPE format a30
select * from dvsys.DBA_DV_COMMAND_RULE;

Listing the Database Vault rules and rule sets :-

column RULE_SET_NAME format a30
column RULE_NAME format a50
column RULE_EXPR format a60
column ENABLED format a8
column RULE_ORDER format 9999
break on RULE_SET_NAME skip 3
select * from dvsys.DBA_DV_RULE_SET_RULE;
select name from DBA_DV_RULE ;
select RULE_SET_NAME,RULE_NAME,RULE_EXPR from DBA_DV_RULE_SET_RULE ;
select name,RULE_EXPR from DVSYS.DBA_DV_RULE ;
SELECT REALM_NAME, GRANTEE, AUTH_RULE_SET_NAME FROM DVSYS.DBA_DV_REALM_AUTH;
select * from dba_role_privs where granted_role='DV_ACCTMGR';

--------------------------------- Thanks for Reading ----------------------------------

If you face any issues related to database administration, feel free to write down your queries in the comments. I will do my best to resolve your issues and get back to you as soon as possible.