Command rules can be used in Oracle Vault to restrict and protect database objects against DDL and DML statements, by imposing specific rules. Usually command rules are associated with rulesets, a subject covered in the next recipe. This association is made to permit or restrict certain statements following a logical rule at runtime.
In this recipe, we will create a command rule that will control attempts to select the EMP_DETAILS_VIEW by using the PL/SQL interface and a command rule that controls the use of the CREATE VIEW statement by using DVA. We will be using these two command rules again, in the Creating and using Oracle Vault rulesets recipe given later.
This can be done using the PL/SQL interface, as follows:
- Connect as the Oracle Vault Owner user and create the command rule, which controls the
SELECTstatements againstEMP_DETAILS_VIEW:SQL> begin dbms_macadm.create_command_rule (command=>'SELECT',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'EMP_DETAILS_VIEW',enabled=>dbms_macutl.g_yes); end; 2 / PL/SQL procedure successfully completed. SQL>
- As the user
HR, issue aSELECTagainstEMP_DETAILS_VIEWto see if the command rule is in effect:SQL> conn HR Enter password: Connected. SQL> select first_name,last_name from emp_details_view where employee_id=100 2 ; select first_name,last_name from emp_details_view where employee_id=100 * ERROR at line 1: ORA-01031: insufficient privileges SQL>
This is done using Oracle Vault Administration Console, as follows:
- Connect as the
ODVA_OWNERuser and click on Command Rules from the Database Vault Feature Administration panel. - In the Command Rules page click Create. In the Command combobox select CREATE VIEW. Click on the Enabled radio button from the Status panel. In the Applicability panel, choose HR as Object Owner and enter
%for Object Name. As Rule Set choose Disabled. Finally click on the OK button.
- From sqlplus, connect as the user
HRand try to create a simple view, as follows:SQL> conn HR Enter password: Connected. SQL> create or replace view test_Command_rule as select first_name,last_name fr om employees; create or replace view test_Command_rule as select first_name,last_name from employees * ERROR at line 1: ORA-47400: Command Rule violation for CREATE VIEW on HR.TEST_COMMAND_RULE SQL>
We can see that our command rule related to the
CREATE VIEWstatement is in effect. - In this step, we will show how to disable the CREATE VIEW command rule. Now go back to the Command Rules page, choose the CREATE VIEW command, and click on the Edit button as follows:
- In the Status panel, choose Disabled and click on the OK button:
- Now that the command rule is disabled, try to create a view:
SQL> create or replace view test_Command_rule as select first_name,last_name from employees 2 ; View created. SQL>
Command rules have precedence over normal privileges. For example, if a user has the privilege to select from specific tables, and we declare a SELECT command rule on these tables and associate it with the Disable ruleset, that user will not have the right to use SELECT on those tables. As we already underlined, command rules are usually created and associated with rulesets and become operational as the rules' logic directs them.
A command rule can be defined in terms of its applicability at different levels, such as at the instance, schema, and object level. Instance-level command rules will affect the CONNECT and ALTER SYSTEM statements. Schema-level command rules can be defined on all the objects within the schema, using % as the object name in the command-rule definition. Object-level command rules will affect just the statements issued against the object specified in the command-rule definition.
Similarly with the default realms, there are a collection of default command rules such as ALTER PROFILE, ALTER SYSTEM, ALTER USER, CREATE PROFILE, CREATE USER, DROP USER, and DROP PROFILE.
These command rules are associated with rulesets as follows:
ALTER SYSTEMwith theAllow Fine Grained Control of System ParametersrulesetALTER USERwithCan Maintain Own Account- All the rest are associated with the
Can Maintain Accounts/Profilesruleset
More information about the command rules can be found by running the Oracle Vault-related reports and by querying DBA_DV_COMMAND_RULE.