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 :
or
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 :
Connected.
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 :
Connected
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.
Connected.
Revoke succeeded.
Connected.
PL/SQL procedure successfully completed.
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
