Creating and using Oracle Database Vault rulesets

As their name denotes, rulesets are a collection of rules that in turn consist of logical statements, which might evaluate to true or false. Because of their capacity for evaluation, rulesets can be associated with command rules, realm authorization, and factor assignment, as well as secure application roles.

Getting ready

In this recipe we will create two rulesets:

  • The first ruleset will allow the selection of emp_detail_view from the vw_america and vw_europe users only, and no other user will be allowed to select from this view.
  • The second ruleset will limit the creation of views for reporting, only at the end of the month. In these recipes, we will re-use the two command rules created in the previous recipe.

How to do it...

Rules sets can be defined by using the PL/SQL Oracle Database Vault administrative packages or by using DVA:

  1. Log in with ODVA_OWNER in DVA Console.
  2. In the Database Vault Feature Administration panel, click on Rule Sets.
  3. In the Rule Sets page, click on the Create button.
  4. In the Create Rule Set page, make the following changes:
    • In the General panel for Name, enter Reporting from HR Views
    • In the Status panel select Enabled
    • For Evaluation Options select Any True
    • In the Audit Options panel choose Audit Disabled
    • In the Error Handling Options panel, choose Show Error Message for Error Handling, for Fail Code enter 20998, for Fail Message enter You are not allowed to report from this view, and for Custom Event Handler Option select Handler disabled
    • Finally click on the OK button
    How to do it...
  5. In the Rule Sets page, select Report from HR views and click on the Edit button. In the Rules Associated To the Rule Set panel, click on the Create button.
    How to do it...
  6. Next we will create two simple rules for this ruleset, which evaluates the connected user as follows. Type Evaluate VW_AMERICA user as the name and SYS_CONTEXT('USERENV','SESSION_USER')='VW_AMERICA' as the rule expression, as follows:
    How to do it...
  7. Next create a second rule, type Evaluate VW_EUROPE user as the name and SYS_CONTEXT('USERENV', 'SESSION_USER')= 'VW_EUROPE' as the rule expression, as follows:
    How to do it...
  8. Click on the OK button. You will get back to the Rule Sets page; click on the OK button again.
  9. At this step, we will re-use the SELECT command rule defined in the previous recipe. It will be associated with the Report from HR views ruleset.
  10. Next go to the Command Rules page, choose the SELECT command rule, and click on the Edit button.
  11. In the Rule Set panel, choose Report from HR views and click OK, as shown in the following screenshot:
    How to do it...
  12. From sqlplus, connect as the HR user and issue a SELECT against EMP_DETAILS_VIEW, as follows: 
    SQL> conn HR
Enter password:
Connected.

SQL> select first_name,last_name from emp_Details_view where employee_id=100;
select first_name,last_name from hr.emp_Details_view where employee_id=100
                                 *
ERROR at line 1:
ORA-47306: 20998: You are not allowed to report from this view

    As we can see, the ruleset is in effect.

  13. Now connect as vw_america and issue the same SELECT as that used in the previous step, as follows:
    SQL> conn vw_america
Enter password:
Connected.


SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;


FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
  14. Connect as vw_europe and issue the SELECT again:
    SQL> conn vw_europe
Enter password:
Connected.
SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;


FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King

SQL>

    Next, we will create a ruleset associated with the CREATE VIEW command rule.

  15. Navigate to the Rule Sets page and click on the Create button. Then make the following changes:
    • Type Create views for end of the month reporting as Name
    • In the Status panel select Enabled
    • For Evaluation Options select All True
    • In Audit Options choose Audit Disabled
    • In Error Handling Options, choose Show Error Message for Error Handling, for Fail code enter 20999, enter You are not allowed to create reports until the end of the month in the Fail Message box, and for Custom Event Handler Option choose Handler disabled:
    How to do it...
  16. Select the Create views for end of the month reporting ruleset and click on the Edit button.
  17. In the Rules Associated To The Rule Set panel, click on the Create button.
  18. In the Create Rule page, type Evaluate HR user as the name and SYS_CONTEXT('USERENV','SESSION_USER')='HR' as the rule expression as follows, and click on the OK button.
    How to do it...
  19. In the Rules Associated to The Rule Set panel, click on the Add Existing Rules button. In the Rules to Add panel from the Available Rules listbox, select Is Last Day of Month and move to the Selected Rules listbox and click on the OK button, as follows:
    How to do it...
  20. Click on the OK button in the Edit Rule Set to finalize the modifications made to the Create views for end of the month reporting ruleset.
  21. Next go to command rules and associate the CREATE VIEW command rule with the Create views for end of the month reporting ruleset, as follows:
    How to do it...
  22. Connect as the user HR and try to create a view named salaries_and_commisions, as follows:
    SQL> conn HR
Enter password:
Connected.

SQL> create or replace view salaries_and_commissions as select first_name,last_name,salary,commission_pct from employees where commission_pct is not null;
create or replace view salaries_and_commissions as select first_name,last_name,salary,commission_pct from employees where commission_pct is not null

                          *
ERROR at line 1:
ORA-47306: 20999: You are not allowed to create reports until the end of the
month


SQL>
  23. Check your current date, and modify your system date to fall on the end of the month:
    SQL> select sysdate from dual;

SYSDATE
---------
16-APR-12

SQL>
  24. Modify your system date. We have the option to modify the system time or by using ALTER SYSTEM SET FIXED_DATE=<desired date>:
    SQL> select sysdate from dual;

SYSDATE
---------
30-APR-12

SQL>
  25. Now try again to create the salaries_and_commissions view:
    SQL> create or replace view salaries_and_commissions as select first_name,last_name,salary,commission_pct from employees where commission_pct is not null;

View created.

SQL>

Since we have changed the system date to the last day of the month and are connected as the user HR, the view is created. After testing, you should reset the system date back to the current date.

How it works...

The rules contained in a ruleset will be evaluated based on Evaluation Options that can be set to All True or Any True. If we use All True, then all the rules will be evaluated, and if one rule is returning FALSE, then the evaluation stops there and the operation will be denied. Otherwise if all the rules return TRUE, then the overall return will also be TRUE and the operation is allowed. If we use Any True, the evaluation stops at the first occurrence of the TRUE condition for any of the rules defined in the ruleset.

There's more...

Here we also have default rulesets, which are deployed during Oracle Vault installation. More information about command rules can be found by running the Oracle Vault-related reports and by querying the DBA_DV_RULE, DBA_DV_RULE_SETS, and DBA_DV_RULE_SET_RULE dictionary views.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.143.22.23