Topics
Using the Audit Vault Server console, you can retrieve audit policies from Oracle database secured targets. You can then modify the policies or create new ones, and then provision them to the Oracle databases. You can retrieve and modify the following types of Oracle Database audit policies.
SQL statements
Schema objects
Privileges
Fine-grained auditing
Capture rules (for redo log file activities)
In general, to create audit policies for Oracle databases, you perform the following steps as described in this chapter:
Topics
Each time you retrieve the audit settings from a secured target Oracle database, you see the state of the database audit settings at that point in time. The Audit Settings page (displayed when you click the Policy tab in the Audit Vault Server console) shows an overview of the audit settings in use at secured target Oracle Databases, and shows any differences between those and the settings you have set as needed in your Oracle AVDF audit policies for those databases. You can then specify which of the current settings are needed.
Table 4-1 describes the columns shown in the Audit Settings Page.
Table 4-1 Fields Under Apply Audit Settings in the Audit Settings Page
Column | Description |
---|---|
Target Name |
Name of the secured target |
In Use |
Number of audit settings in use in the secured target |
Needed |
Number of audit settings you (the auditor) specified as needed |
Conflict |
The difference between the audit settings in use at the database and the number specified as needed in your Oracle AVDF audit policy for this database. If this number is greater than zero, new audit settings may have been created at the database since you last provisioned the audit policy from Oracle AVDF. You may also have selected more audit settings as needed or not needed since you last provisioned the audit policy. To resolve the problem, you can specify whether new audit settings are needed and/or provision the policy again. This brings the number in the Problem column back to zero. |
Last Retrieved |
The time that the audit information for the selected database was last retrieved |
As Provisioned |
The time that the audit settings were last provisioned to the database from Oracle AVDF |
After you retrieve the audit settings from the secured target Oracle database, you can view and modify them as needed. Remember that you are modifying audit settings in use at the time you retrieved them. If you think they may have changed, you should retrieve them again.
Log in to the Audit Vault Server console as an auditor.
Click the Secured Targets tab, and then click the name of the Oracle Database secured target you want.
In the Audit Settings page, click the name of the secured target database you want.
The Audit Settings Overview page for this secured target appears, showing the audit settings in use and marked as needed for these audit types:
Statement
Object
Privilege
FGA
Capture Rule
To update settings for any audit type, click its link, for example, Object.
The Audit Settings page for that audit type appears, listing the current audit settings. The second column displays a problem icon if there is a difference between the setting at the secured target database, and the setting in Oracle Audit Vault and Database Firewall.
Select the check boxes for each audit setting you determine is needed, then click Set as Needed.
To remove audit settings, select the check boxes for the ones you want to remove, then click Set as Not Needed.
To create new audit settings for this audit type (for example, Statement), click Create.
Topics
Once you have retrieved audit policy settings from the secured target Oracle database, and selected which of the settings in use are needed, you can also create new policy settings for the Oracle database.
Topics
Statement auditing audits SQL statements by type of statement, not by the specific schema objects on which the statement operates. Statement auditing can be broad or focused (for example, by auditing the activities of all database users or only a select list of users). Typically broad statement auditing audits the use of several types of related actions for each option. These statements are in the following categories:
Data definition statements (DDL). For example, AUDIT TABLE
audits all CREATE TABLE
and DROP TABLE
statements. AUDIT TABLE
tracks several DDL statements regardless of the table on which they are issued. You can also set statement auditing to audit selected users or every user in the database.
Data manipulation statements (DML). For example, AUDIT SELECT TABLE
audits all SELECT ... FROM TABLE
or SELECT ... FROM VIEW
statements, regardless of the table or view.
Table 4-2 lists the columns used in the Statement page.
Table 4-2 Columns in the Statement Audit Settings Page
Column | Description |
---|---|
(Leftmost column) |
A checkbox for selecting the audit setting |
Problem icon |
An exclamation mark icon indicates one of the following conditions:
|
Setting |
The statement that is audited |
In Use |
The arrow points upward if the setting is active in the secured target database, and downward if it has not been provisioned or is not active. |
Needed |
The arrow points upward if the audit setting is marked as needed in Oracle AVDF, and downward if the audit setting is marked as not needed. If an audit setting that is not in use is set to needed, the In Use arrow points up after provisioning. If an audit setting that is in use is set to not needed, the audit setting is no longer displayed after provisioning. |
Audit granularity |
The granularity of auditing: |
Execution Condition |
The execution condition audited: |
Proxy User |
The proxy user for the database, if any |
User |
The user to which this setting applies, if any |
Topics
Schema object auditing is the auditing of specific statements on a particular schema object, such as AUDIT SELECT ON HR.EMPLOYEES
. Schema object auditing is very focused, auditing only a specific statement on a specific schema object for all users of the database.
For example, object auditing can audit all SELECT
and DML statements permitted by object privileges, such as SELECT
or DELETE
statements on a given table. The GRANT
and REVOKE
statements that control those privileges are also audited.
Object auditing lets you audit the use of powerful database commands that enable users to view or delete very sensitive and private data. You can audit statements that reference tables, views, sequences, standalone stored procedures or functions, and packages.
Oracle Database sets schema object audit options for all users of the database. You cannot set these options for a specific list of users.
Table 4-3 lists the columns used in the Object page.
Table 4-3 Columns in the Object Audit Settings Page
Column | Description |
---|---|
(Leftmost column) |
A checkbox for selecting the audit setting |
Problem icon |
An exclamation mark icon indicates one of the following conditions:
|
Setting |
The statement that is audited |
In Use |
The arrow points upward if the setting is active in the secured target database, and downward if it has not been provisioned or is not active. |
Needed |
The arrow points upward if the audit setting is marked as needed in Oracle AVDF, and downward if the audit setting is marked as not needed. If an audit setting that is not in use is set to needed, the In Use arrow points up after provisioning. If an audit setting that is in use is set to not needed, the audit setting is no longer displayed after provisioning. |
Object Type |
The object (such as a database table) to which this setting applies |
Object Owner Name |
The database schema to which this setting applies |
Object Name |
The name of the object in the specified schema. |
Audit granularity |
The granularity of auditing: |
Execution Condition |
The execution condition audited: |
Topics
Privilege auditing is the auditing of SQL statements that use a system privilege. You can audit the use of any system privilege. Like statement auditing, privilege auditing can audit the activities of all database users or only a specified list of users.
For example, if you enable AUDIT SELECT ANY TABLE
, Oracle Database audits all SELECT
tablename
statements issued by users who have the SELECT ANY TABLE
privilege. This type of auditing is very important for the Sarbanes-Oxley (SOX) Act compliance requirements. Sarbanes-Oxley and other compliance regulations require the privileged user be audited for inappropriate data changes or fraudulent changes to records.
Privilege auditing audits the use of powerful system privileges enabling corresponding actions, such as AUDIT CREATE TABLE
. If you set both similar statement and privilege audit options, then only a single audit record is generated. For example, if the statement clause TABLE
and the system privilege CREATE TABLE
are both audited, then only a single audit record is generated each time a table is created. The statement auditing clause, TABLE
, audits CREATE TABLE
, ALTER TABLE
, and DROP TABLE
statements. However, the privilege auditing option, CREATE TABLE
, audits only CREATE TABLE
statements, because only the CREATE TABLE
statement requires the CREATE TABLE
privilege.
Privilege auditing does not occur if the action is already permitted by the existing owner and schema object privileges. Privilege auditing is triggered only if these privileges are insufficient, that is, only if what makes the action possible is a system privilege.
Privilege auditing is more focused than statement auditing for the following reasons:
It audits only a specific type of SQL statement, not a related list of statements.
It audits only the use of the target privilege.
Table 4-4 lists the columns used in the Privilege Audit Settings page.
Table 4-4 Columns in the Privilege Audit Settings Page
Column | Description |
---|---|
(Leftmost column) |
A checkbox for selecting the audit setting |
Problem icon |
An exclamation mark icon indicates one of the following conditions:
|
Setting |
The statement that is audited |
In Use |
The arrow points upward if the setting is active in the secured target database, and downward if it has not been provisioned or is not active. |
Needed |
The arrow points upward if the audit setting is marked as needed in Oracle AVDF, and downward if the audit setting is marked as not needed. If an audit setting that is not in use is set to needed, the In Use arrow points up after provisioning. If an audit setting that is in use is set to not needed, the audit setting is no longer displayed after provisioning. |
Audit granularity |
The granularity of auditing: |
Execution Condition |
The execution condition audited: |
User |
The user to which this setting applies, if any |
Proxy User |
The proxy user for the database, if any |
Topics
Fine-grained auditing (FGA) enables you to create a policy that defines specific conditions that must exist for the audit to occur. For example, fine-grained auditing lets you audit the following types of activities:
Accessing a table between 9 p.m. and 6 a.m. or on Saturday and Sunday
Using an IP address from outside the corporate network
Selecting or updating a table column
Modifying a value in a table column
A fine-grained audit policy provides granular auditing of select, insert, update, and delete operations. Furthermore, you reduce the amount of audit information generated by restricting auditing to only the conditions that you want to audit. This creates a more meaningful audit trail that supports compliance requirements. For example, a central tax authority can use fine-grained auditing to track access to tax returns to guard against employee snooping, with enough detail to determine what data was accessed. It is not enough to know that a specific user used the SELECT
privilege on a particular table. Fine-grained auditing provides a deeper audit, such as when the user queried the table or the computer IP address of the user who performed the action.
When you define the fine-grained audit policy, you can target one or more specific columns, called a relevant column, to be audited if a condition is met. This feature enables you to focus on particularly important, sensitive, or privacy-related data to audit, such as the data in columns that hold credit card numbers, patient diagnoses, Social Security numbers, and so on. A relevant-column audit helps reduce the instances of false or unnecessary audit records, because the audit is triggered only when a particular column is referenced in the query.
You further can fine-tune the audit to specific columns and rows by adding a condition to the audit policy. For example, suppose you enter the following fields in the Create Fine Grained Audit page:
Condition: department_id = 50
Columns: salary, commission_pct
This setting audits anyone who tries to select data from the salary
and commission_pct
columns of employees in Department 50.
If you do not specify a relevant column, then Oracle Database applies the audit to all the columns in the table; that is, auditing occurs whenever any specified statement type affects any column, whether or not any rows are returned.
In a fine-grained audit policy, you can specify an event handler to process an audit event. The event handler provides flexibility in determining how to handle a triggering audit event. For example, it could write the audit event to a special audit table for further analysis, or it could send a pager or an email alert to a security administrator. This feature enables you to fine-tune audit responses to appropriate levels of escalation.
For additional flexibility in implementation, you can employ a user-defined function to determine the policy condition, and identify a relevant column for auditing (audit column). For example, the function could allow unaudited access to any salary as long as the user is accessing data within the company, but specify audited access to executive-level salaries when they are accessed from outside the company.
Table 4-5 lists the columns used in the Fine-Grained Audit Settings page.
Table 4-5 Columns in the Fine-Grained Audit Settings Page
Field | Description |
---|---|
(Leftmost column) |
A checkbox for selecting the audit setting |
Problem icon |
An exclamation mark icon indicates one of the following conditions:
|
Policy Name |
The name of this fine-grained audit policy |
In Use |
The arrow points upward if the setting is active in the secured target and downward if it has not been provisioned or is not active. |
Needed |
The arrow points upward if the audit setting is marked as needed in Oracle AVDF, and downward if the audit setting is marked as not needed. If an audit setting that is not in use is set to needed, the In Use arrow points up after provisioning. If an audit settings that is in use is set to not needed, the audit setting is no longer displayed after provisioning. |
Object Owner |
The schema to which this audit setting applies |
Object |
The object, in the specified schema, to which this audit setting applies |
Statement Types |
The SQL statement to which this audit setting applies. Values are:
|
Columns |
The database columns being audited, also referred to as the relevant columns. If this field is empty, all columns are audited. |
Topics
You can create a capture rule to track before and after value changes in the database redo log files. The capture rule specifies DML and DDL changes that should be checked when Oracle Database scans the database redo log. You can apply the capture rule to an individual table, a schema, or globally to the entire database. Unlike statement, object, privilege, and fine-grained audit policies, you do not retrieve and activate capture rule settings from a secured target, because you cannot create them there. You only can create the capture rule in the Audit Vault Server console.
Note:
In the secured target database, ensure that the table that you plan to use for the redo log file audit is not listed in the DBA_STREAMS_UNSUPPORTED
data dictionary view.
Table 4-6 lists the columns used in the Capture Rule page.
Table 4-6 Columns in the Capture Rule Page
Column | Description |
---|---|
(Leftmost column) |
A checkbox for selecting the audit setting |
Problem icon |
An exclamation mark icon indicates one of the following conditions:
|
Rule Type |
The types of capture rules are as follows:
|
In Use |
The arrow points upward if the setting is active in the secured target and downward if it has not been provisioned or is not active. |
Needed |
The arrow points upward if the audit setting is marked as needed in Oracle AVDF, and downward if the audit setting is marked as not needed. If an audit setting that is not in use is set to needed, the In Use arrow points up after provisioning. If an audit setting that is in use is set to not needed, the audit setting is no longer displayed after provisioning. |
Schema |
Indicates the schema to which this rule applies |
Table |
For table capture rules, this fields indicates the table to which this rule applies. |
Statement Type |
Indicates the type of statements that are audited: |
After you have updated and/or created the audit policies for a secured target Oracle Database, you can provision the audit policy changes to that database.
You can provision the audit policy settings in the following ways:
Caution:
Any audit setting that is not indicated as Needed in the Audit Vault Server console will be turned off on the secured target. See "Specifying Which Audit Settings Are Needed".
You can export audit policy settings for a secured target to a SQL script from Oracle Audit Vault and Database Firewall. Then you can give the script to a database administrator for the secured target Oracle Database to use to update the audit settings on that database.
To export the audit settings to a SQL script for a secured target database:
You can provision the audit policy settings directly from the Audit Vault Server to the secured target Oracle database. This updates the audit settings in the secured target without the intervention of a database administrator. However, a database administrator can modify or delete these audit settings, as well as add new ones. For this reason, you should periodically retrieve the settings to ensure that you have the latest audit settings.