Command Rules

5 Command Rules

A command rule protects Oracle Database SQL statements that affect one or more database objects. These statements can include SELECT, ALTER SYSTEM, database definition language (DDL), and data manipulation language (DML) statements. To customize and enforce the command rule, you associate it with a rule set, which is a collection of one or more rules. The command rule is enforced at run time. Command rules affect anyone who tries to use the SQL statements it protects, regardless of the realm in which the object exists.

5.1 Creating a Command Rule to Prevent Destructive Actions

Creating a command rule is optional, but it can help protect database objects and data against malicious activity or mistakes by privileged users. Command rules can prevent destructive commands like TRUNCATETABLE, DROP INDEX, DROP PROCEDURE.

For example, prevent the HR schema from dropping a table:

Connect as C##JSMITH:
```
connect c##jsmith
```
Disable HR's ability to DROP TABLE:
```
BEGIN
    DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(  
        command        => 'DROP TABLE' 
        ,object_owner  => 'HR' 
        ,object_name   => '%' 
        ,rule_set_name => 'Disabled' 
        ,enabled       => dbms_macutl.g_yes);
END;
/
```
As a result of this command rule, all users who perform the DROP TABLE command on HR schema objects will be considered in violation of the Database Vault command rule as the rule set specifies the command should be Disabled.

Because the result of the Disabled rule set will always return false, the command rule will disable DROP TABLE commands on the HR schema for all database users, including the HR database schema itself.

The rule_set_name of Disabled may appear counterintuitive because neither the Database Vault command rule nor the rule set are disabled. Instead, the Disabled rule set contains a rule with a rule expression that will always returns false, never allowing the command associated with the rule set to run.
You can view the rule expression SQL using the following:
```
select rule_name, rule_expr from dba_dv_rule_set_rule where rule_set_name = 'Disabled';
```
You will see the following output:
```
RULE_NAME            RULE_EXPR
-------------------- --------------------
False                1=0
```
If you do not want to disable DROP TABLE commands on the HR schema in all situations, you can use a custom Database Vault rule set to identify when the command can be used.
If you want to switch the command rule to be in simulation mode, instead of enforced, set the ENABLED parameter to DBMS_MACUTL.G_SIMULATION instead of DBMS_MACUTL.G_YES:
```
BEGIN
    DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(  
        command        => 'DROP TABLE' 
        ,object_owner  => 'HR' 
        ,object_name   => '%' 
        ,rule_set_name => 'Disabled' 
        ,enabled       => dbms_macutl.g_simulation);
END;
/
```
Simulation (non-enforcement) mode: Oracle Database Vault simulation mode allows you to identify which users are accessing data you want to protect with an Oracle Database Vault realm. Only violations of the Database Vault realm authorization list will be recorded.
To identify the users who violated the command rule, query the DVSYS.DBA_DV_SIMULATION_LOG view.
```
select username, command, violation_type, sqltext from DVSYS.DBA_DV_SIMULATION_LOG;
```

For more information on Oracle Database Vault simulation mode, see the Troubleshooting and Tracing Errors section in the Oracle Database Vault Administrator’s Guide.

5.2 Creating a Command Rule That Allows Actions from Specified IP Addresses Only

You can create a command rule that allows specific commands to be performed from limited IP addresses.

For example, the HR schema to drop a table only if the command is run from an approved IP address:

Connect as C##JSMITH:
```
connect c##jsmith
```
Create a rule that meets your acceptance criteria:
```
BEGIN
    DBMS_MACADM.CREATE_RULE( 
        rule_name  => 'Trusted IP Address'
        ,rule_expr => 'sys_context(''userenv'',''ip_address'') = ''<IP Address>'' ');
END;
/
```
The rule will return TRUE only if the IP address of the connected user's session equals the IP address in the rule expression. This could be an IN list or a not equals. You could compare a hostname instead or a portion of a hostname.

Create a rule set:

BEGIN
    DVSYS.DBMS_MACADM.CREATE_RULE_SET( 
        rule_set_name    => 'Trusted Rule Set' 
        ,description     => 'A rule set for controlling access by IP address' 
        ,enabled         => 'Y' 
        ,eval_options    => dbms_macutl.g_ruleset_eval_any 
        ,audit_options   => null 
        ,fail_options    => dbms_macutl.g_ruleset_fail_show 
        ,fail_message    => 'Access is blocked. Contact the IT helpdesk.' 
        ,fail_code       => '-20000' 
        ,handler_options => dbms_macutl.g_ruleset_handler_off 
        ,handler         => null 
        ,is_static       => true);
END;
/

Rule sets have the option of returning the general error message or a custom error message. In the example below, you provide a detailed error message to the end user that tells them to speak to their security team.

Add the rule from step two to the rule set:
```
BEGIN
    DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(  
        rule_set_name  => 'Trusted Rule Set' 
        ,rule_name     => 'Trusted IP Address');
END;
/
```
Rule sets can consist of multiple rules that all must be met (true) or at least one rule must be true. Rules can be used in multiple rule sets. However, if you change the rule then the changes will apply to all rule sets the rule is used in.

Update the existing command rule to use the new rule set:

BEGIN
    DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(  
        command        => 'DROP TABLE' 
        ,object_owner  => 'HR' 
        ,object_name   => '%' 
        ,rule_set_name => 'Trusted Rule Set' 
        ,enabled       => dbms_macutl.g_yes);
END;
/

5.3 Creating a Command Rule to Control Application Authentication

Minimize the risk stolen or misused database application credentials may pose, by creating a Database Vault command rule to control how application credentials are used.

Stolen application credentials are one of the biggest risks to your organization's data. Application credentials often stored in configuration files, or scripts, and are frequently known by developers and administrators, making it difficult to identify the person using those credentials.

As C##JSMITH you will create a Database Vault rule, rule set, and command rule to limit how the HR application schema can connect to the Oracle Database.

Connect as C##JSMITH:
```
connect c##jsmith@pdb_name
```

Create these four rules:

BEGIN
    DBMS_MACADM.CREATE_RULE( 
        rule_name  => 'Trusted Application IP Address'
        ,rule_expr => 'sys_context(''userenv'',''ip_address'') = ''<IP Address>'' ');
END;
/

BEGIN
    DBMS_MACADM.CREATE_RULE( 
        rule_name  => 'Trusted Application Hostname'
        ,rule_expr => 'sys_context(''userenv'',''host'') = ''appserver'' ');
END;
/

BEGIN
    DBMS_MACADM.CREATE_RULE( 
        rule_name  => 'Trusted Application OS USer'
        ,rule_expr => 'sys_context(''userenv'',''os_user'') = ''appuser'' ');
END;
/

BEGIN
    DBMS_MACADM.CREATE_RULE( 
        rule_name  => 'Trusted Application Program'
        ,rule_expr => 'sys_context(''userenv'',''client_program_name'') = ''<client program name>'' ');
END;
/

Create the rule set:

BEGIN
    DVSYS.DBMS_MACADM.CREATE_RULE_SET( 
        rule_set_name    => 'Trusted Application Path' 
        ,description     => 'Controlling access to the application' 
        ,enabled         => 'Y' 
        ,eval_options    => dbms_macutl.g_ruleset_eval_all 
        ,audit_options   => null 
        ,fail_options    => dbms_macutl.g_ruleset_fail_show 
        ,fail_message    => 'Unauthorized application usage. Contact the IT helpdesk.' 
        ,fail_code       => '-20000' 
        ,handler_options => dbms_macutl.g_ruleset_handler_off 
        ,handler         => null 
        ,is_static       => true);
END;
/

By setting the EVAL_OPTIONS parameter to ALL, all rules must evaluate to true for this rule set to evaluate to true.

Add each of the rules from step to the rule set:

BEGIN
    DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(  
        rule_set_name  => 'Trusted Application Path' 
        ,rule_name     => 'Trusted Application IP Address');
END;
/

BEGIN
    DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(  
        rule_set_name  => 'Trusted Application Path' 
        ,rule_name     => 'Trusted Application Hostname');
END;
/

BEGIN
    DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(  
        rule_set_name  => 'Trusted Application Path' 
        ,rule_name     => 'Trusted Application OS User');
END;
/

BEGIN
    DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(  
        rule_set_name  => 'Trusted Application Path' 
        ,rule_name     => 'Trusted Application Program);
END;
/

Create a command rule that will evaluate the Trusted Application Path rule set when HR attempts to connect:

BEGIN
    DVSYS.DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(  
        user_name      => 'HR' 
        ,rule_set_name => 'Trusted Application Path' 
        ,enabled       => dbms_macutl.g_yes);
END;
/

Attempt to connect as HR:

connect hr@pdb_name

You will see the custom error message:

ORA-47306: 20000: Unauthorized application usage. Contact the IT helpdesk.

Disable the command rule you just created to continue with other examples in this guide:

Connect as C##JSMITH:
```
connect c##jsmith@pdb_name
```

Disable the command:

BEGIN
    DVSYS.DBMS_MACADM.UPDATE_CONNECT_COMMAND_RULE(  
        user_name      => 'HR' 
        ,rule_set_name => 'Trusted Application Path' 
        ,enabled       => dbms_macutl.g_no);
END;
/

Verify the command rule on connect is disabled:
```
SELECT ENABLED FROM DBA_DV_COMMAND_RULE WHERE COMMAND = 'CONNECT' AND OBJECT_OWNER = 'HR';
```
You will see:
```
ENABLED
__________
N
```