The Geek Diary

What are Command Rules in oracle Database

by

Command rules do not have a name. They are known by the unique combination of a command, an object owner, and an object name. Therefore, you can create multiple SELECT command rules—for example, as long as the object owner and object name attributes are different in these command rules.

command rules oracle database

1. Command: The command that is being protected against. This includes:

  • Most DDL (CREATE, ALTER, DROP, and TRUNCATE)
  • ALTER SYSTEM – EXECUTE
  • SELECT, INSERT, UPDATE, and DELETE

2. Status: Indicates whether the command rule is currently in effect or not. To disable the effects of a command rule, set this to Disabled.
3. Object Owner: The owner of the object or objects that this rule applies to. This is not applicable in some cases, such as with the FLASHBACK DATABASE command.
4. Object Name: The object within the Object Owner schema that is being protected. It can be a wildcard, which means all objects in the Object Owner schema.
5. Rule Set: The name of the rule set that protects these objects from this command. If the rule set evaluates to TRUE at the time of attempting the command, the command succeeds. Otherwise, a rule set violation error is returned.

Use Case

In this example, there is a user who is normally privileged to alter a table. However, administratively, this user should not do it. There is a user who is specifically assigned these duties. The steps for the example are as follows:

1. The OE user alters the OE.ORDERS table by adding a column. This user is allowed to do this, because she is the owner of the schema. But in this particular organization, duties are assigned such that a different user is in charge of any tables related to orders.

SQL> ALTER TABLE oe.orders ADD (my_code VARCHAR2(10));
Table altered.

2. The user with database design duties for the ORDERS tables is OE_ORDERS_DBA. He keeps a watch on the table design, making sure that no one else is changing it. He notices that a new column is added and decides that he needs something enforced at the database level.

3. The ORDERS table administrator asks for a role to be created and granted only to himself or herself.

SQL> CREATE ROLE ORDER_APP_DBA;
SQL> GRANT order_app_dba TO OE_ORDERS_DBA;

4. The Database Vault Owner creates a command rule that protects the ALTER TABLE command from being executed on any tables in the OE schema that begin with the string ORDER. This includes the ORDERS and ORDER_ITEMS tables. In the process, a rule set called OE_Order_Designer is created. It has a single rule: isOrderAppDBA. This rule’s expression ensures that the requesting user has the ORDER_APP_DBA role, which is created in step 3.

dvsys.dbms_macutl.user_has_role_varchar('ORDER_APP_DBA')='Y'

5. After OE_ORDERS_DBA removes the new column from the ORDERS table, the OE user again attempts to add the column. But this time, there is a command rule violation for “alter table on OE.ORDERS.” The OE user is not able to alter this table.

 SQL> ALTER TABLE oe.orders ADD (my_code NUMBER);
ORA-47400: Command Rule Violation for alter table on OE.ORDERS

6. The OE user now attempts to add this new column to the ORDER_ITEMS table. Again, there is a command rule violation. The wildcard caused this command rule to be applied to both the ORDERS and ORDER_ITEMS tables.

SQL> ALTER TABLE   ADD (my_code NUMBER); 
ORA-47400: Command Rule Violation for alter table on
    OE.ORDER_ITEMS

Scope of Command Rules

Command rules can be categorized as follows:

  • Command rules that have a system-wide scope. With this type, you can only create one command rule for each database instance. Examples are command rules for the ALTER SYSTEM and CONNECT statements.
  • Command rules that are schema-specific. An example is creating a command rule for the DROP TABLE statement.
  • Command rules that are object-specific. An example is creating a command rule for the DROP TABLE statement with a specific table included in the command rule definition.

In a multitenant environment, if you want to create for the CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE and DROP PLUGGABLE DATABASE statements you can create ALTER PLUGGABLE DATABASE, and DROP PLUGGABLE DATABASE statements, you can create them in the root so that they can be applied to the entire multitenant environment.

Disallowing ALTER TABLE in a Schema

A common compliance requirement is to prevent a user from adding columns to a table. The object name is set to the % wildcard, meaning that all tables in OE are protected. Rule Set is set to Disabled, indicating that the Disabled rule set must be satisfied for the ALTER TABLE statement to proceed. The Disabled rule set, which is delivered with Database Vault, contains a single rule called False, which has the expression 1=0. But this is never true, so the command rule in the slide always prohibits altering any OE tables.

Delivered Command Rules

The default behavior of the Oracle-provided command rules is as follows:

1. Account management–related command rules: The Database Vault Account manager is the only user allowed to deal with accounts and profiles:

  • ALTER PROFILE
  • CREATE PROFILE
  • CREATE USER
  • DROP PROFILE
  • DROP USER

2. ALTER SYSTEM command rule: This command rule is used to restrict the system parameters that can be changed. By default, this command rule uses the Allow System Parameters rule set, which checks to see whether the o7_dictionary_accessibility parameter is set to TRUE. This rule set can be customized or you can create your own rule set.

3. ALTER USER privileges: This defines the list of users who can do such things as change a user’s password, profile, lock status, and default tablespace. A user can alter his or her own account. The Database Account Manager user can alter other users except those with the DV_OWNER or DV_ACCTMGR roles. Users cannot alter the DVSYS user.

Reports and Views

Reports and a View Related to Command Rules:

  • Command Rule Audit Report: Lists audit records generated by command-rule processing operations
  • Command Rule Configuration Issues Report: Tracks rule violations, in addition to other configuration issues that the command rule may have
  • Object Privilege Reports: Lists object privileges that the command rule affects
  • Sensitive Objects Reports: Lists objects that the command rule affects
  • Rule Set Configuration Issues Report: Lists rules sets that have no rules defined or enabled, which may affect the command rules that use them
  • The DBA_DV_COMMAND_RULE view contains information about all the Database Vault command rules in the database.

Command Rule API

The three functions for dealing with command rules are listed as follows. They are in the DVSYS schema and are part of the DBMS_MACADM package. Therefore, they must be qualified with DVSYS.DBMS_MACADM.

To create a command rule, use:

CREATE_COMMAND_RULE (command VARCHAR2, rule_set_name VARCHAR2, object_owner VARCHAR2, object_name VARCHAR2, enabled VARCHAR2)

To update a command rule (identified by the combination of command, object_owner, and object_name), use the following:

UPDATE_COMMAND_RULE (command VARCHAR2, rule_set_name VARCHAR2, object_owner VARCHAR2, object_name VARCHAR2, enabled VARCHAR2)
Note: All parameters are required, even if their values are not to be changed from their current values.

To delete a command rule, use:

DELETE_COMMAND_RULE (command VARCHAR2, object_owner VARCHAR2, object_name VARCHAR2)

Note: You must issue a COMMIT statement after calling each of these functions for the changes to take effect. You must also make a call to dvsys.dbms_macadm.SYNC_RULES for the API changes to be reflected in the DVA:

exec  dvsys.dbms_macadm.SYNC_RULES;

The command rule API provides functions that enable you to:

  • Create a command rule
  • Update a command rule
  • Delete a command rule
SQL> exec DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE ('DROP TABLE','Am HR User','HR','%','N')
SQL> exec DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE ('DROP TABLE','Am HR User','HR','%','Y') 
SQL> exec DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE ('DROP TABLE','HR','%')

You May Also Like