Oracle Vault has an integrated reporting system that can be used for generating reports for specific Oracle Database Vault components, and for general database security. In the next series of recipes, we will generate some specific Oracle Database Vault reports as well as some reports related to general database security.
In the previous recipes, we have created all the Oracle Vault objects with the audit options disabled. During this series of recipes, we will enable the Audit Options to Audit On Success or Failure on the realms and command rules created earlier, and we will generate several related audit reports. We will also generate some general database security reports related to privileges, audit, passwords, and so on.
The reporting system provided by Oracle Database Vault is a built in component of Oracle Database Vault Administrator Console:
- Navigate to the Realm page, check HR_TABLES_REALM
,and click on Edit. In the Audit Options panel, check Audit on Success or Failure and click OK:
- Perform the same steps with HR_VIEWS_REALM:
- Navigate to the Rule Set page and check Report from HR Views. In the panel Audit Options check Audit on Success or Failure and click on OK, as follows:
- Repeat the same steps with the
Create views for end of the month reportingruleset. - Next we will generate some audit information. Try to violate the realm authorizations by issuing a
SELECTagainst theemployeestable, with the usersystem:SQL> conn system Enter password: Connected. SQL> select first_name from hr.employees where employee_id=100; select first_name from hr.employees where employee_id=100 * ERROR at line 1: ORA-01031: insufficient privileges SQL>
- Next, issue the same statement connected as the user
HR:SQL> conn HR Enter password: Connected. SQL> select first_name from hr.employees where employee_id=100; FIRST_NAME -------------------- Steven SQL>
- Again, as the user
systemtry to issue aSELECTagainstemp_details_view:SQL> conn system Enter password: Connected. SQL> select first_name from hr.emp_details_view where employee_id=100; select first_name from hr.emp_details_view where employee_id=100 * ERROR at line 1: ORA-01031: insufficient privileges SQL>
- As the user
vw_europeissue the sameSELECT:SQL> conn vw_europe/ Enter password: Connected. SQL> select first_name from hr.emp_details_view where employee_id=100; FIRST_NAME -------------------- Steven SQL>
- Try to issue the same
SELECTas the userHR:SQL> conn HR Enter password: Connected. SQL> select first_name from hr.emp_details_view where employee_id=100; select first_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 SQL>
Here we violated the
Report from HRviews ruleset. - Next try to create a simple view as the user
HR, to violate theCreate views for end of the month reportingruleset:SQL> create or replace view names_view as select first_name,last_name from employees; create or replace view names_view as select first_name,last_name from employees * ERROR at line 1: ORA-47306: 20999: You are not allowed to create reports until the end of the month SQL>
- Navigate to Database Vault Reports, and from the available report types, check Realm Audit and click on the Run Report button, as follows:
- The report results show both the failed and the succeeded operations:
The return code 1031 is identical to
ORA-01031: insufficient privileges. - Now we will proceed to run reports on ruleset access. Based on the fact that the validation is made at the command-rule level, we will find the related reports in this category, as follows:
- The report results show both the failed and the succeeded operations:
- From this step on we will generate general security reports. In this category, we will find several reports grouped as follows:
- Next click on the Expand All link. We will run a report from the first category Object Privilege Reports by selecting Object Access by PUBLIC in it, as follows:
- The report results for the user
SYSwill look like the following screenshot:
- Next issue a report from Powerful Database Accounts and Roles Reports by selecting Accounts with DBA Role, as follows:
- The report results for DBA role will look as follows:
- For our last example we will generate an audit report, shown as follows, by selecting Core Database Audit Trail from Security Audit Report category:
Reports can be created and generated by the users with the DV_OWNER, DV_SECANALYST, and DV_ADMIN roles.
As we have seen, there are plenty of security reports that may be generated. It is recommended that you run and review the security reports at regular intervals. This is especially important if you have reason to suspect that there may have been attempts to access any sensitive data, that is, being protected by Oracle Database Vault features described in this chapter, and especially if there is high suspicion related to attempts to access sensitive data.