Practice: Preventing Local Users from Blocking Common Operations - Command Rules

Overview

This practice shows how to prevent local users from creating Oracle Database Vault controls on common users which would prevent them from performing commands on their own objects or even from logging in to the PDB in which their objects reside.

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 C##TEST1 and C##TEST2 common users.

    
    $ cd /home/oracle/labs/M104781GC10
    $ /home/oracle/labs/M104781GC10/setup_DV_CR.sh
    
    Copyright (c) 1982, 2020, Oracle.  All rights reserved.
    
    SQL> drop user c##sec_admin cascade;
    drop user c##sec_admin cascade
              *
    ERROR at line 1:
    ORA-01918: user 'C##SEC_ADMIN' does not exist
    
    
    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.
    
    
    Connected to:
    
    SQL> GRANT dba to c##test1 CONTAINER=ALL;
    
    Grant succeeded.
    
    ...
    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 CDB and PDB connections with no command rule on common users

  • 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 such as command rules.

    
    $ sqlplus c##sec_admin
    Enter password:
    
    SQL> SELECT * FROM DVSYS.DBA_DV_COMMON_OPERATION_STATUS;
    
    NAME                      STATU
    ------------------------- -----
    DV_ALLOW_COMMON_OPERATION FALSE
    
    SQL>

    If the status is set to TRUE, set it to FALSE with the following command:

    
    SQL> EXEC DBMS_MACADM.ALLOW_COMMON_OPERATION (FALSE)
    
    PL/SQL procedure successfully completed.
    
    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.

    
    SQL> CONNECT c##test1
    Enter password:
    Connected.
    SQL>
  • Connect to PDB21 as C##TEST1.

    
    SQL> CONNECT c##test1@PDB21
    Enter password:
    Connected.
    SQL>

Step 3 : Test CDB and PDB connections with a command rule in CDB root on common users

  • Create a command rule on C##TEST1 in the CDB root.

    
    SQL> CONNECT c##sec_admin
    Enter password:
    Connected.
    SQL> BEGIN
     DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
      rule_set_name   => 'Disabled',
      user_name       => 'C##TEST1',
      enabled         => 'y',
      scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
    END;
    /  2    3    4    5    6    7    8
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • Connect to the CDB root as C##TEST1.

    
    SQL> CONNECT c##test1
    Enter password:
    ERROR:
    ORA-47400: Command Rule violation for CONNECT on LOGON
    
    Warning: You are no longer connected to ORACLE.
    
    SQL> !oerr ora 47400
    47400, 00000, "Command Rule violation for %s on %s"
    // *Cause: An operation that was attempted failed due to a command rule
    //         violation
    // *Action: Ensure you have sufficient privileges for this operation retry
    //          the operation
    
    SQL>
  • Connect to PDB21 as C##TEST1.

    
    SQL> CONNECT c##test1@PDB21
    Enter password:
    Connected.
    SQL>
  • Drop the command rule.

    
    SQL> CONNECT c##sec_admin
    Enter password:
    Connected.
    SQL> EXEC DBMS_MACADM.DELETE_CONNECT_COMMAND_RULE('C##TEST1',DBMS_MACUTL.G_SCOPE_LOCAL)
    
    PL/SQL procedure successfully completed.
    
    SQL>

Step 4 : Test CDB and PDB connections with a command rule in the PDB on common users

  • Create a command rule on C##TEST1 in PDB21.

    
    SQL> CONNECT c##sec_admin@PDB21
    Enter password:
    Connected.
    SQL> BEGIN
     DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
      rule_set_name   => 'Disabled',
      user_name       => 'C##TEST1',
      enabled         => 'y',
      scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
    END;
    /  2    3    4    5    6    7    8
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • Connect to the CDB root as C##TEST1.

    
    SQL> CONNECT c##test1
    Enter password:
    Connected.
    SQL>
  • Connect to PDB21 as C##TEST1.

    
    SQL> CONNECT c##test1@PDB21
    ERROR:
    ORA-47400: Command Rule violation for CONNECT on LOGON
    
    Warning: You are no longer connected to ORACLE.
    
    SQL>
  • Drop the command rule.

    
    SQL> CONNECT c##sec_admin@PDB21
    Enter password:
    Connected.
    SQL> EXEC DBMS_MACADM.DELETE_CONNECT_COMMAND_RULE('C##TEST1',DBMS_MACUTL.G_SCOPE_LOCAL)
    
    PL/SQL procedure successfully completed.
    
    SQL>

Step 5 : Prevent local users from creating Oracle Database Vault controls on common users that prevent them from logging in to the PDB

  • Connect to the CDB root as C##SEC_ADMIN and switch the behavior of DV_ALLOW_COMMON_OPERATION.

    
    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>

    You can execute this procedure without including any parameter to achieve a TRUE result.

Step 6 : Test CDB and PDB connections with a command rule in the CDB root on common users

  • Create a command rule on C##TEST1 in the CDB root.

    
    SQL> CONNECT c##sec_admin
    Enter password:
    Connected.
    SQL> BEGIN
    DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
      rule_set_name   => 'Disabled',
      user_name       => 'C##TEST1',
      enabled         => 'y',
      scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
    END;
    /  2    3    4    5    6    7    8
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • Connect to the CDB root as C##TEST1.

    
    SQL> CONNECT c##test1
    Enter password:
    ERROR:
    ORA-47400: Command Rule violation for CONNECT on LOGON
    
    Warning: You are no longer connected to ORACLE.
    
    SQL> !oerr ora 47400
    47400, 00000, "Command Rule violation for %s on %s"
    // *Cause: An operation that was attempted failed due to a command rule
    //         violation
    // *Action: Ensure you have sufficient privileges for this operation retry
    //          the operation
    
    SQL>
  • Connect to PDB21 as C##TEST1.

    
    SQL> CONNECT c##test1@PDB21
    Enter password:
    Connected.
    SQL>
  • Drop the command rule.

    
    SQL> CONNECT c##sec_admin
    Enter password:
    Connected.
    SQL> EXEC DBMS_MACADM.DELETE_CONNECT_COMMAND_RULE('C##TEST1',DBMS_MACUTL.G_SCOPE_LOCAL)
    
    PL/SQL procedure successfully completed.
    
    SQL>

Step 7 : Test CDB and PDB connections with a command rule in a PDB on common users

  • Create a command rule on C##TEST1 in PDB21.

    
    SQL> CONNECT sec_admin@PDB21
    Enter password:
    Connected.
    SQL> BEGIN
     DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
      rule_set_name   => 'Disabled',
      user_name       => 'C##TEST1',
      enabled         => 'y',
      scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
    END;
    /  2    3    4    5    6    7    8
    BEGIN
    *
    ERROR at line 1:
    ORA-47110: cannot create command rules for C##TEST1.%
    ORA-06512: at "DVSYS.DBMS_MACADM", line 1872
    ORA-06512: at "DVSYS.DBMS_MACADM", line 2263
    ORA-06512: at line 2
    
    SQL> !oerr ORA 47110
    47110, 00000, "cannot create command rules for %s.%s"
    // *Cause: When ALLOW COMMON OPERATION was set to TRUE, a smaller scope user was not allowed to create command rules on a larger scope user's object.
    // *Action: When ALLOW COMMON OPERATION is TRUE, do not create command rules on a larger scope user's object.
    
    SQL>
  • Connect to the CDB root as C##TEST1.

    
    SQL> CONNECT c##test1
    Enter password:
    Connected.
    SQL>
  • Connect to PDB21 as C##TEST1.

    
    SQL> CONNECT c##test1@PDB21
    Enter password:
    Connected.
    SQL>  EXIT
    $

Step 8 : Summary

Database Vault not only blocks inappropriate command rules from being created once DBMS_MACADM.ALLOW_COMMON_OPERATION is set to TRUE, but existing local command rules created when DBMS_MACADM.ALLOW_COMMON_OPERATION was set to FALSE fall under the control. Existing local command rules still exist but enforcement is ignored.

Step 9 : Disable Database Vault in both the PDB and the CDB root


$ /home/oracle/labs/M104781GC10/disable_DV.sh

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 06 2020 15:23:56 +00:00

Connected to:

SQL> exec DVSYS.DBMS_MACADM.DISABLE_DV

PL/SQL procedure successfully completed.

SQL> exit


Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 06 2020 15:23:58 +00:00

Connected to:


SQL> exec DVSYS.DBMS_MACADM.DISABLE_DV

PL/SQL procedure successfully completed.

SQL> exit

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 6442447392 bytes
Fixed Size                  9581088 bytes
Variable Size            1090519040 bytes
Database Buffers         5318377472 bytes
Redo Buffers               23969792 bytes
Database mounted.
Database opened.
SQL> ALTER PLUGGABLE DATABASE all OPEN;

Pluggable database altered.

SQL> exit
$