Practice: Preventing Local Users from Blocking Common Operations - Realms
Overview
This practice shows how to prevent local users from creating Oracle Database Vault controls on common users objects which would prevent common users from accessing local data in their own schema in PDBs. A PDB local Database Vault Owner can create a realm around common Oracle schemas such as DVSYS or CTXSYS and prevent them from functioning correctly. For the purposes of this practice, the C##TEST1 custom schema is created in CDB root to show this feature.
Before starting any new practice, refer to the Practices Environment recommendations.
Step 1 : Configure and enable Database Vault at the CDB and PDB levels
-
Configure and enable Database Vault at the CDB root level and at the PDB level. The script creates the
HR.G_EMPtable in the root container and also theHR.L_EMPtable inPDB21.$ cd /home/oracle/labs/M104781GC10 $ /home/oracle/labs/M104781GC10/setup_DV.sh $ ./setup_DV_CDB.sh ... SQL> create user c##sec_admin identified by password container=ALL; User created. SQL> grant create session, set container, restricted session, DV_OWNER to c##sec_admin container=ALL; Grant succeeded. SQL> drop user c##accts_admin cascade; drop user c##accts_admin cascade * ERROR at line 1: ORA-01918: user 'C##ACCTS_ADMIN' does not exist SQL> create user c##accts_admin identified by password container=ALL; User created. SQL> grant create session, set container, DV_ACCTMGR to c##accts_admin container=ALL; Grant succeeded. SQL> grant select on sys.dba_dv_status to c##accts_admin container=ALL; Grant succeeded. SQL> EXIT ... Copyright (c) 1982, 2020, Oracle. All rights reserved. Last Successful login time: Tue Feb 18 2020 08:26:21 +00:00 SQL> DROP TABLE g_emp; Table dropped. SQL> CREATE TABLE g_emp(name CHAR(10), salary NUMBER) ; Table created. SQL> INSERT INTO g_emp values('EMP_GLOBAL',1000); 1 row created. SQL> COMMIT; Commit complete. SQL> EXIT Copyright (c) 1982, 2020, Oracle. All rights reserved. Last Successful login time: Tue Feb 18 2020 08:27:54 +00:00 Connected to: SQL> DROP TABLE l_emp; Table dropped. SQL> CREATE TABLE l_emp(name CHAR(10), salary NUMBER); Table created. SQL> INSERT INTO l_emp values('EMP_LOCAL',2000); 1 row created. SQL> COMMIT; Commit complete. SQL> EXIT Copyright (c) 1982, 2020, Oracle. All rights reserved. Last Successful login time: Tue Feb 18 2020 08:27:54 +00:00 Connected to: SQL> DROP TABLE l_tab; Table dropped. SQL> CREATE TABLE l_tab(code NUMBER); Table created. SQL> INSERT INTO l_tab values(1); 1 row created. SQL> INSERT INTO l_tab values(2); 1 row created. SQL> COMMIT; Commit complete. SQL> EXIT $
Step 2 : Test table data accessibility with no realm on common objects
-
Connect to the CDB root as
C##SEC_ADMINto verify the status ofDV_ALLOW_COMMON_OPERATION. This is the default behavior: it allows local users to create Database Vault controls on common users objects.$ sqlplus c##sec_admin Enter password: SQL> SELECT * FROM DVSYS.DBA_DV_COMMON_OPERATION_STATUS; NAME STATU ------------------------- ----- DV_ALLOW_COMMON_OPERATION FALSE SQL> -
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL>
Step 3 : Test table data accessibility with a common regular or mandatory realm on common objects
-
Create a common regular realm on
C##TEST1tables in the CDB root.SQL> CONNECT c##sec_admin Enter password: Connected. SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Root Test Realm', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 0); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Root Test Realm', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; SELECT * FROM c##test1.g_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Drop the realm.
SQL> CONNECT c##sec_admin Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Root Test Realm') PL/SQL procedure successfully completed. SQL>
-
-
Create a common mandatory realm on
C##TEST1tables in the CDB root.SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Root Test Realm', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 1); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Root Test Realm', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; SELECT * FROM c##test1.g_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; SELECT * FROM c##test1.g_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL>
-
-
-
Drop the realm.
SQL> CONNECT c##sec_admin Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Root Test Realm') PL/SQL procedure successfully completed. SQL>
-
Step 4 : Test table data accessibility on common objects with a PDB regular or mandatory realm
-
Create a PDB regular realm on
C##TEST1tables inPDB21.SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Test Realm', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 0); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Test Realm', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; SELECT * FROM c##test1.l_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Drop the realm.
SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Test Realm') PL/SQL procedure successfully completed. SQL>
-
-
Create a PDB mandatory realm on
C##TEST1tables inPDB21.SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Test Realm', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 1); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Test Realm', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; SELECT * FROM c##test1.l_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; SELECT * FROM c##test1.l_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Drop the realm.
SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Test Realm') PL/SQL procedure successfully completed. SQL>
-
Step 5 : Restrict local users from creating Oracle Database Vault controls on common objects
SQL> CONNECT c##sec_admin
Enter password:
Connected.
SQL> SELECT * FROM DVSYS.DBA_DV_COMMON_OPERATION_STATUS;
NAME STATU
------------------------- -----
DV_ALLOW_COMMON_OPERATION FALSE
SQL> EXEC DBMS_MACADM.ALLOW_COMMON_OPERATION
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DVSYS.DBA_DV_COMMON_OPERATION_STATUS;
NAME STATU
------------------------- -----
DV_ALLOW_COMMON_OPERATION TRUE
SQL>Step 6 : Test table data accessibility with a common regular or mandatory realm on common objects
-
Create a common regular realm on
C##TEST1tables in the CDB root.SQL> CONNECT c##sec_admin Enter password: Connected. SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Root Test Realm', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 0); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Root Test Realm', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; SELECT * FROM c##test1.g_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Drop the realm.
SQL> CONNECT c##sec_admin Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Root Test Realm') PL/SQL procedure successfully completed. SQL>
-
-
Create a common mandatory realm on
C##TEST1tables in the CDB root.SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Root Test Realm', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 1); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Root Test Realm', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; SELECT * FROM c##test1.g_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; SELECT * FROM c##test1.g_emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL>
-
-
-
Drop the realm.
SQL> CONNECT c##sec_admin Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Root Test Realm') PL/SQL procedure successfully completed. SQL>
-
Step 7 : Test table data accessibility on common objects with a PDB regular or mandatory realm
-
Create a PDB regular realm on
C##TEST1tables inPDB21.SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Test Realm1', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 0); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Test Realm1', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 BEGIN * ERROR at line 1: ORA-47286: cannot add %, C##TEST1.% to a realm ORA-06512: at "DVSYS.DBMS_MACADM", line 1059 ORA-06512: at line 2 SQL> !oerr ora 47286 47286, 00000, "cannot add %s, %s.%s to a realm" // *Cause: When ALLOW COMMON OPERATION was set to TRUE, a smaller scope user was not allowed to add a larger scope user's object or a larger scope role to a realm. // *Action: When ALLOW COMMON OPERATION is TRUE, do not add a larger scope user's object or a larger scope role to a realm. SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Drop the realm.
SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Test Realm1') PL/SQL procedure successfully completed. SQL>
-
-
Create a PDB mandatory realm on
C##TEST1tables inPDB21.SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> BEGIN DBMS_MACADM.CREATE_REALM( realm_name => 'Test Realm1', description => 'Test Realm description', enabled => DBMS_MACUTL.G_YES, audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, realm_type => 1); END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> BEGIN DBMS_MACADM.ADD_OBJECT_TO_REALM( realm_name => 'Test Realm1', object_owner => 'C##TEST1', object_name => '%', object_type => '%'); END; / 2 3 4 5 6 7 8 BEGIN * ERROR at line 1: ORA-47286: cannot add %, C##TEST1.% to a realm ORA-06512: at "DVSYS.DBMS_MACADM", line 1059 ORA-06512: at line 2 SQL>-
Connect to the CDB root as
C##TEST1, the table common owner.SQL> CONNECT c##test1 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to the CDB root as
C##TEST2, another common user.SQL> CONNECT c##test2 Enter password: Connected. SQL> SELECT * FROM c##test1.g_emp; NAME SALARY ---------- ---------- EMP_GLOBAL 1000 SQL> -
Connect to
PDB21asC##TEST1, the table common owner.SQL> CONNECT c##test1@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Connect to
PDB21asC##TEST2, another common user.SQL> CONNECT c##test2@PDB21 Enter password: Connected. SQL> SELECT * FROM c##test1.l_emp; NAME SALARY ---------- ---------- EMP_LOCAL 2000 SQL> -
Drop the realm.
SQL> CONNECT sec_admin@PDB21 Enter password: Connected. SQL> EXEC DBMS_MACADM.DELETE_REALM_CASCADE('Test Realm1') PL/SQL procedure successfully completed. SQL> EXIT $
-
Step 8 : Summary
Let's summarize the behavior of data access on common users objects in PDBs when you switch the DV_ALLOW_COMMON_OPERATION value.
FALSE |
TRUE |
|||||
|---|---|---|---|---|---|---|
C##TEST1 |
C##TEST2 |
C##TEST1 |
C##TEST2 | |||
| Common Regular or Mandatory Realm in CDB root | No change | No change | No change | No change | ||
| PDB Regular Realm | Access | Blocked | Access | Access | ||
| PDB Mandatory Realm | Blocked | Blocked | Access | Access |
-
If you create a regular or mandatory realm in the CDB root and a regular or mandatory PDB realm, and if
DV_ALLOW_COMMON_OPERATIONisTRUE, then data of common users objects is accessible. -
If local realms had been created when
DV_ALLOW_COMMON_OPERATIONwas set toFALSE, they would still exist after the new control but enforcement would be ignored.
Step 9 : Disable Database Vault in both the PDB and the CDB root
-
Run the
disable_DV.shscript to disable Database Vault in both the PDB and the CDB root.$ /home/oracle/labs/M104781GC10/disable_DV.sh ... SQL> exec DVSYS.DBMS_MACADM.DISABLE_DV PL/SQL procedure successfully completed. SQL> exit Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: SQL> shutdown abort ORACLE instance shut down. SQL> exit Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> STARTUP ORACLE instance started. Total System Global Area 851439688 bytes Fixed Size 9691208 bytes Variable Size 423624704 bytes Database Buffers 281018368 bytes Redo Buffers 19664896 bytes In-Memory Area 117440512 bytes Database mounted. Database opened. SQL> exit $