Oracle Database Vault in 12.2.0.1 - Part 2

Database Vault REALM is created in this blog using the command instead of GUI.

Note: This code is for test education purposes only.

User Case: 

A realm HR_Realm created. Access to all HR tables and object types is granted.

SQL> CONN c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> EXEC  dvsys.dbms_macadm.create_realm ( realm_name =>'HR_Realm',description =>'Restrict DBA access to HR mode',enabled =>'Y', audit_options => 1 );

PL/SQL procedure successfully completed.

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

Add the HR schema all object to the new realm

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

SQL> EXEC dvsys.dbms_macadm.add_object_to_realm ( realm_name =>'HR_Realm',object_owner =>'HR',object_name =>'%',object_type =>'%');

PL/SQL procedure successfully completed.

SQL> conn c##dbmanager1/PAkistan123##@BILL_PDB1

Connected.

SQL>  create user scott identified by PAkistan123##;

User created.

SQL> grant connect, create session to scott;

Grant succeeded.

GRANT SELECT ANY TABLE TO SCOTT;

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

Authorize a user, such as scott, as a Participant | Owner to access the realm HR_REALM

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

SQL> CONN c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> BEGIN

DVSYS.DBMS_MACADM.UPDATE_REALM_AUTH(

realm_name => 'HR_Realm',

grantee => 'SCOTT',

rule_set_name =>NULL,

auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);

END;

/

OR

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'HR_Realm', grantee => 'SCOTT');

PL/SQL procedure successfully completed.

-- Add objects authorization to REALM

EXEC DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'HR_Realm',object_owner => 'HR',object_name => '%',object_type => '%');

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

Verify REALM usage using SOCTT

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

SQL> conn scott/PAkistan123##@BILL_PDB1

Connected.

SQL> select count(*) from hr.employees;

  COUNT(*)

----------

       107

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

Revoke authorization 

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

EXEC dbms_macadm.delete_auth_from_realm(realm_name => 'HR_Realm', grantee => 'SCOTT');

Now try as Scott user:

select count(*) from hr.employees

                        *

ERROR at line 1:

ORA-01031: insufficient privileges

Now Reassign:

SQL>  EXEC dbms_macadm.add_auth_to_realm(realm_name => 'HR_Realm', grantee => 'SCOTT');

PL/SQL procedure successfully completed.

SQL>

Now try again as Scott user:

SQL> /

select count(*) from hr.employees

  COUNT(*)

----------

       107

================================================================

Now execute:

SQL> EXEC DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(realm_name=>'HR_Realm',object_owner=>'HR',object_name=>'%',object_type=>'%');

PL/SQL procedure successfully completed.

SQL>

---The reason is that, SCOTT have select any privileges

----Very important: SCOTT user have SELECT ANY TABLE privilege, but important thing it is authorized using HR_REALM

SQL> conn scott/PAkistan123##@BILL_PDB1

Connected.

SQL> select count(*) from hr.employees;

  COUNT(*)

----------

       107

EXEC DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'HR_Realm',object_owner => 'HR',object_name => 'EMPLOYEES',object_type => '%');

Check Part 3 Command Rule Creation: https://hayatkhan75.blogspot.com/2022/08/oracle-database-vault-in-12201-part-3.html

Credit goes:

https://blog.actorsfit.com/a?ID=01450-3eca16ce-66a2-47e5-8671-1a072f790062

https://mithun168.wordpress.com/2019/02/25/oracle-db-vault-realm-creation/

https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html

https://www.thegeekdiary.com/how-realms-work-in-oracle-database-vault/

https://www.dbi-services.com/blog/oracle-database-vault-realm-in-a-pluggable-database/