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_EMP table in the root container and also the HR.L_EMP table in PDB21.

    
    $ 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_ADMIN to verify the status of DV_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 PDB21 as C##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 PDB21 as C##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##TEST1 tables 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 PDB21 as C##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 PDB21 as C##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##TEST1 tables 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 PDB21 as C##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 PDB21 as C##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##TEST1 tables in PDB21.

    
    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 PDB21 as C##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 PDB21 as C##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##TEST1 tables in PDB21.

    
    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 PDB21 as C##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 PDB21 as C##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##TEST1 tables 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 PDB21 as C##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 PDB21 as C##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##TEST1 tables 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 PDB21 as C##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 PDB21 as C##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##TEST1 tables in PDB21.

    
    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 PDB21 as C##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 PDB21 as C##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##TEST1 tables in PDB21.

    
    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 PDB21 as C##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 PDB21 as C##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_OPERATION is TRUE, then data of common users objects is accessible.

  • If local realms had been created when DV_ALLOW_COMMON_OPERATION was set to FALSE, 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.sh script 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
    
    $