How to Use DVSYS.DBMS_MACADM procedures to Prohibit Data Access to Users

A realm is created on HR schema objects : how to prohibit the access to HR objects to the accounts SYSTEM and SYS without using the Database Vault Administrator GUI tool (dva) ?

1. Realm creation and schema objects selection :

SQL> execute dvsys.dbms_macadm.CREATE_REALM('Test HR','Protects data from DBA access','Y', 1);

SQL>  execute DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM ( 'Test HR', 'HR', '%', '%');

or

begin
dvsys.dbms_macadm.add_object_to_realm ( realm_name => 'Test HR'  , object_owner => 'HR'  ,
object_name => '%'  ,
object_type => '%');
end;
/

2. Tests : connect as SYSTEM user to check that SYSTEM user has no access to HR data :

SQL> conn system/manager
Connected.

SQL> select * from HR.dept;

DEPTNO DNAME LOC
----------   -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

Connect as SYS user to check that SYS user has no access to HR data :

SQL> conn sys/xxx as sysdba
Connected

SQL> select * from HR.dept;

select * from HR.dept
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

1. Check  if the user SYSTEM user has been granted the object privilege SELECT on HR.DEPT  table.

Yes , it had been granted. Hence the realm does not prohibit the SYSTEM user from selecting data from HR table as Realm only protects data from being accessed with system privileges.

2. Revoke the object privilege on HR schema tables from SYSTEM user.

SQL> conn sys/xxx as sysdba
Connected.

SQL> revoke select on hr.dept from system;
Revoke succeeded.

SQL> conn dvowner/xxx
Connected.

SQL> execute dvsys.dbms_macadm.CREATE_REALM('Test HR','Protects data from DBA access','Y', 1);
PL/SQL procedure successfully completed.

SQL> execute DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM ('Test HR', 'HR', '%', '%');
PL/SQL procedure successfully completed.

SQL> conn system/xxx
Connected.

SQL> select * from HR.dept;
select * from HR.dept
*
ERROR at line 1:
ORA-01031: insufficient privileges

user_has_auth_in_realm