1. Auditor's Guide
  2. Creating Audit Policies for Oracle Databases

6 Creating Audit Policies for Oracle Databases

You can retrieve and provision audit configurations for an Oracle Database.

6.1 About Audit Policies

Using the Audit Vault Server console, you can retrieve audit policies from Oracle Database 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.

  • Unified audit policies
  • SQL statements
  • Schema objects
  • Privileges
  • Fine-grained auditing

6.2 General Steps for Creating Audit Policies for Oracle Databases

To create audit policies for Oracle databases, you retrieve the target Oracle Database audit policy settings, modify them, and provision the policy.

The general steps that you follow are:
  1. Retrieve the current audit policy settings from the target Oracle database, and specify which of the current settings are needed.
  2. If necessary, define more audit settings to add to the needed settings.
  3. For unified auditing, select the necessary unified audit policy.
  4. Provision the audit policy to the target database. The policy settings you specified as needed, and the new ones you created, then become the policies in use in the database.

6.3 Retrieving and Modifying Audit Policies from an Oracle Database

You can retrieve audit policies from Oracle database.

6.3.1 Understanding the Columns on the Audit Policies Tab

When you retrieve audit policies from a target Oracle Database, you see the state of the database audit policies at that point in time.

Click the Policies tab in the Audit Vault Server console. The Audit Policies tab in the left navigation menu is displayed by default. This page contains a list of Oracle Database targets. It also lists the time at which the audit policies were last provisioned and retrieved.

Table 6-1 describes the columns shown in the Audit Policies page.

Table 6-1 Fields under Audit Policies tab

Column Description

Target

Name of the target.

Last Retrieved

The time that the audit information for the selected database was last retrieved.

Last Provisioned

The time that the audit settings were last provisioned to the database from Oracle Audit Vault and Database Firewall.

6.3.2 Retrieving Audit Policies from Multiple Oracle Databases

You can retrieve audit policies from several Oracle Database targets at once. You can schedule audit setting retrievals for individual targets.

After patching to Oracle AVDF 20.12, you will need to
  1. Rerun the Oracle privileges script for successful audit policy retrieval for container database targets. For more information see Oracle Database Setup Scripts.
  2. Retrieve audit policies before provisioning or viewing audit policies. For more information see Retrieving and Modifying Audit Policies from an Oracle Database
Prerequisite:
  • Ensure the target user has sufficient privileges granted for audit policy management. An administrator can grant these using the Oracle Database Setup Scripts.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.

    This page lists a summary of audit policies at this point in time for all targets with their status.

  3. In the Target column, select the check boxes for the target databases that you want to retrieve audit policies. You can only see the Oracle database targets to which you have access.

    Note:

    • Traditional auditing is supported for all versions of Oracle Database supported by Oracle AVDF 20.
    • Unified Auditing is supported for Oracle Database versions starting from 12.2.0.1.
  4. Click Retrieve button.

    To check the status of the retrieval, click the Settings tab. Then click on Jobs tab in the left navigation menu. When the audit policies retrieval is complete, the Audit Settings is displayed on this page under Job Type column.

    See Also:

6.3.3 Scheduling the Retrieval of Audit Settings for an Oracle Database

To retrieve audit policy settings for an Oracle Database, schedule an audit policy retrieval job for the target.

After patching to Oracle AVDF 20.12, you will need to
  1. Rerun the Oracle privileges script for successful audit policy retrieval for container database targets. For more information see Oracle Database Setup Scripts.
  2. Retrieve audit policies before provisioning or viewing audit policies. For more information see Retrieving and Modifying Audit Policies from an Oracle Database
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click the Targets tab.
  3. Click the Schedule Retrieval Jobs icon for the target.

  4. On the Schedule Retrieval Jobs page, select one of the following options under Audit Policy:

    • To run the job immediately, select Retrieve Immediately.

    • To schedule the job or change an existing schedule, follow these steps:

      1. Select Create/Update Schedule.
      2. Select Enable.
      3. Enter the start date and time and the repetition frequency.
  5. Click Save.

Related Topics

6.4 Provisioning Unified Audit Policies

Learn about provisioning Unified Audit policies.

After patching to Oracle AVDF 20.12, you will need to
  1. Rerun the Oracle privileges script for successful audit policy retrieval for container database targets. For more information see Oracle Database Setup Scripts.
  2. Retrieve audit policies before provisioning or viewing audit policies. For more information see Retrieving and Modifying Audit Policies from an Oracle Database

6.4.1 Basic Auditing

Learn about basic auditing. It captures logon events, critical activity and schema changes.

The basic audit policy Critical Database Activity is defined as follows and is enabled for all the database users. 


CREATE AUDIT POLICY ORA_AV$_CRITICAL_DB_ACTIVITY
PRIVILEGES EXEMPT ACCESS POLICY,EXEMPT REDACTION POLICY,
    ADMINISTER KEY MANAGEMENT,EXPORT FULL DATABASE,IMPORT FULL DATABASE,
    CREATE PUBLIC DATABASE LINK, ALTER PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK,
    CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM,
    SELECT ANY DICTIONARY, ADMINISTER DATABASE TRIGGER,
    PURGE DBA_RECYCLEBIN, LOGMINING
ACTIONS CREATE USER, ALTER USER, DROP USER,
        CREATE ROLE, DROP ROLE, ALTER ROLE, SET ROLE, GRANT, REVOKE,
        CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
        CREATE PLUGGABLE DATABASE, DROP PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE,
        CREATE LOCKDOWN PROFILE, ALTER LOCKDOWN PROFILE, DROP LOCKDOWN PROFILE,
        ALTER DATABASE, ALTER SYSTEM,
        CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE,
        CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT,
        CREATE DIRECTORY, DROP DIRECTORY,
        CREATE DISK GROUP,ALTER DISK GROUP,DROP DISK GROUP,
        CREATE PFILE,CREATE SPFILE
ACTIONS COMPONENT = datapump EXPORT,IMPORT
ACTIONS COMPONENT = DIRECT_LOAD LOAD;
 
AUDIT POLICY ORA_AV$_CRITICAL_DB_ACTIVITY;
-- enabled for all users

The basic audit policy Logon/Logoff Events audits logon and logoff activities for database users except for a specified list of users and audits all unsuccessful logons and logoffs.

The following policies are provisioned on the target database for logon events category:


CREATE AUDIT POLICY ORA_AV$_LOGON_EVENTS ACTIONS LOGON,LOGOFF;
CREATE AUDIT POLICY ORA_AV$_LOGON_FAILURE ACTIONS LOGON,LOGOFF;

AUDIT POLICY ORA_AV$_LOGON_EVENTS EXCEPT <comma separated user list>;
AUDIT POLICY ORA_AVS$_LOGON_FAILURE whenever not successful;

The basic audit policy Database Schema is defined as follows and is enabled for all the database users. 


CREATE AUDIT POLICY ORA_AV$_DB_SCHEMA_CHANGES
PRIVILEGES
        CREATE EXTERNAL JOB, CREATE JOB, CREATE ANY JOB
ACTIONS CREATE PROCEDURE, DROP PROCEDURE, ALTER PROCEDURE,
        CREATE PACKAGE, ALTER PACKAGE, DROP PACKAGE, CREATE PACKAGE BODY, ALTER PACKAGE BODY, DROP PACKAGE BODY,
        CREATE FUNCTION, DROP FUNCTION, ALTER FUNCTION,
        CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER,
        CREATE LIBRARY, ALTER LIBRARY, DROP LIBRARY,
        CREATE SYNONYM, DROP SYNONYM,
        CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE,
        CREATE DATABASE LINK, ALTER DATABASE LINK, DROP DATABASE LINK,
        CREATE INDEX, ALTER INDEX, DROP INDEX,
        CREATE OUTLINE, ALTER OUTLINE,DROP OUTLINE,
        CREATE CONTEXT, DROP CONTEXT,
        CREATE ATTRIBUTE DIMENSION,ALTER ATTRIBUTE DIMENSION,DROP ATTRIBUTE DIMENSION,
        CREATE DIMENSION,ALTER DIMENSION,DROP DIMENSION,
        CREATE INDEXTYPE,ALTER INDEXTYPE,DROP INDEXTYPE,
        CREATE OPERATOR,ALTER OPERATOR,DROP OPERATOR,
        CREATE JAVA,ALTER JAVA,DROP JAVA,
        CREATE MINING MODEL,ALTER MINING MODEL,DROP MINING MODEL,
        CREATE TYPE BODY,ALTER TYPE BODY,DROP TYPE BODY,
        CREATE TYPE,ALTER TYPE,DROP TYPE,
        CREATE MATERIALIZED VIEW,ALTER MATERIALIZED VIEW,DROP MATERIALIZED VIEW ,
        CREATE MATERIALIZED VIEW LOG, ALTER MATERIALIZED VIEW LOG,DROP MATERIALIZED VIEW LOG,
        CREATE MATERIALIZED ZONEMAP, ALTER MATERIALIZED ZONEMAP,DROP MATERIALIZED ZONEMAP,
        CREATE VIEW, ALTER VIEW, DROP VIEW,CREATE ANALYTIC VIEW, ALTER ANALYTIC VIEW, DROP ANALYTIC VIEW,
        CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE,
        CREATE CLUSTER, ALTER CLUSTER, DROP CLUSTER,TRUNCATE CLUSTER;
 
AUDIT POLICY ORA_AV$_DB_SCHEMA_CHANGES;
-- enabled for all users

6.4.2 Admin Activity Auditing Policy

Learn about provisioning the Admin Activity Auditing policy.

The Admin Activity Auditing policy lets you audit all activities by privileged administrators. These administrators can make significant changes to the wider system. A database administrator (DBA) can have access to sensitive data that is not protected by realms, and can exfiltrate. The Admin Activity auditing policy audits all activities for non Oracle maintained user who has one of the following privileges or roles.

  • Admin privileges:

    SYSOPER, SYSDG, SYSKM, SYSRAC, and SYSBACKUP

  • Roles:

    DBA, DATAPUMP_EXP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE, EXP_FULL_DATABASE, IMP_FULL_DATABASE

Note:

  • Non Oracle maintained users are subset of users from dba_users group with oracle_maintained flag set to N.

  • Oracle maintained users are subset of users from dba_users group with oracle_maintained flag set to Y.

  • See My Oracle Support to download and apply the RDBMS patch 21493004 on the target Oracle Database. Apply the patch to audit only top level statements with unified auditing. This patch must be applied on Oracle Database targets (version 12.2). Also apply the patch on Oracle Database targets (versions prior to 18c). In case this patch is not applied, the following error message is observed in the Audit Vault Server console when All Admin Activity policy is attempted for provisioning:

    Unable to provision All Admin Activity audit policy on the target database. Refer to Admin Activity Audit Policy section in Auditor's Guide for details.

    Instructions for finding patches on My Oracle Support: How to find a patch on My Oracle Support

The following audit policy gets provisioned on the target database (version 12.2.0.1 or greater): 


CREATE AUDIT POLICY ORA_AV$_ADMIN_USER_ACTIVITY ACTIONS ALL
WHEN SYS_CONTEXT('USERENV','CURRENT_USER') NOT IN
(<list of oracle maintained users>) EVALUATE PER STATEMENT

AUDIT POLICY ORA_AV$_ADMIN_USER_ACTIVITY BY USERS WITH GRANTED ROLES DBA,
DATAPUMP_EXP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE, EXP_FULL_DATABASE, 
IMP_FULL_DATABASE;

AUDIT POLICY ORA_AV$_ADMIN_USER_ACTIVITY BY PUBLIC, SYSDG, SYSKM, SYSRAC, SYSBACKUP;

Note:

To get the list of Oracle maintained users, run query:

SELECT username from dba_users where oracle_maintained = 'Y'

On Oracle Database 19c and above, the system provisions an additional audit policy to monitor all top level activities of SYS as shown here: 

CREATE AUDIT POLICY ORA_AV$_SYS_TOP_ACTIVITY ACTIONS ALL ONLY TOPLEVEL;
AUDIT POLICY ORA_AV$_SYS_TOP_ACTIVITY by SYS;

Starting Oracle AVDF release 20.3, the following audit policy gets provisioned on the target database:

CREATE AUDIT POLICY ORA_AV$_ADMIN_USER_ACTIVITY ACTIONS ALL WHEN
SYS_CONTEXT('USERENV','CURRENT_USER') NOT IN(<list of oracle maintained
users>) EVALUATE PER STATEMENT ONLY TOPLEVEL;

After upgrading from Oracle AVDF releases 20.1 or 20.2 to 20.3, follow these steps before provisioning unified audit policies. Also apply the patch on Oracle Database targets (versions prior to 18c) to audit only top level statements with unified auditing. See My Oracle Support to download and apply the RDBMS patch 21493004 on the target Oracle Database.

  1. Log in to the Audit Vault Server console as auditor.
  2. Click Policies tab.
  3. The Audit Policies tab in the left navigation menu is selected by default.
  4. Click the specific target.
  5. The policy details page is displayed. Disable the Admin User Activity category.

  6. Log in to the target database as the target user and run these commands:

    DROP AUDIT POLICY ORA_AV$_ADMIN_USER_ACTIVITY;
    DROP AUDIT POLICY ORA_AV$_SYS_TOP_ACTIVITY;
  7. Return to the Audit Policies sub tab in the Audit Vault Server console.
  8. Select the specific policy and click Retrieve button in the top right corner.
  9. Enable the Admin User Activity category.

6.4.3 User Activity Auditing Policy

Learn about provisioning the User Activity Auditing policy.

The User Activity Auditing policy tracks all activity by users who may have access to sensitive data or who are under observation. These users could be “non-admin but privileged” users. When enabling this policy in the interface, you must specify non-Oracle maintained users to audit.

The following audit policy gets provisioned on the target database:


CREATE AUDIT POLICY ORA_AV$_USER_ACTIVITY ACTIONS ALL
WHEN SYS_CONTEXT('USERENV','CURRENT_USER') NOT IN
(<list of oracle maintained users>) EVALUATE PER STATEMENT

AUDIT POLICY ORA_AV$_USER_ACTIVITY BY <comma-separated non-Oracle maintained user list>

6.4.4 Audit Compliance Standards

Learn about audit compliance standards supported in Oracle AVDF.

6.4.4.1 Center for Internet Security Recommendations Unified Audit Policy

Learn about provisioning Center for Internet Security Recommendations (CIS) unified audit policy.

The Center for Internet Security Recommendations (CIS) unified audit policy is a predefined policy (ORA_CIS_RECOMMENDATIONS) in Oracle Database specifically designed to perform audits that the CIS recommends.

You can enable or disable this policy, along with other policies provided by Oracle Audit Vault and Database Firewall. This policy tracks many activities and can help you evaluate whether you are adhering to database compliance requirements. For example, you can track when a user, database link, profile, or procedure is created, altered, or dropped.

CIS is a world-recognized organization that provides consensus-based best practices for helping organizations assess and improve their cyber security posture. They provide resources, such as configuration assessment tools, secure configuration benchmarks, security metrics, and certifications. One of the main objectives of the organization is to help businesses prioritize what they need to do for security, and they strive to provide recommendations in simple, non-technical terms.

6.4.4.2 Security Technical Implementation Guidelines (STIG)

Learn about enabling Security Technical Implementation Guidelines (STIG) unified audit policy.

Starting Oracle AVDF 20.5, Security Technical Implementation Guidelines (STIG) unified audit policy is available for provisioning. This functionality can be enabled on Oracle Database targets to implement Security Technical Implementation Guidelines (STIG) audit requirements. Security Technical Implementation Guidelines (STIG) can be enabled on Oracle Database targets starting with version 21.

When Security Technical Implementation Guidelines (STIG) is provisioned, the following unified policies are enabled on the target database.

Predefined Audit Policies Name Can be enabled for users Event Condition

ORA_STIG_RECOMMENDATIONS

All users
  • Success
  • Failure

ORA_LOGON_LOGOFF

All users
  • Success
  • Failure

ORA_ALL_TOPLEVEL_ACTIONS

Privileged users
  • Success
  • Failure

You can fetch the privileged user list identified by the user entitlement job or provide your own list. You can enable or disable this policy, along with other policies provided by Oracle AVDF. This policy tracks many activities and can help you evaluate whether you are adhering to STIG compliance requirements or not.

Follow these steps to enable STIG audit policy. These options are applicable only for ORA_ALL_TOPLEVEL_ACTIONS audit policy.

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Policies tab. The Audit Policies tab in the left navigation menu is selected by default.

    Note:

    In case the Last Retrieved timestamp is greater than two hours, then do an audit retrieval first.
  3. Click the name of Oracle Database target for which you want to provision the audit policies.

    Unified Auditing sub tab in the main page is selected by default.

  4. Under Audit Compliance Standards, click the checkbox against Security Technical Implementation Guidelines (STIG) to enable this functionality.
  5. In case ORA_ALL_TOPLEVEL_ACTIONS under Oracle Predefined Policies is already applied for the specific set of users or roles, then a confirmation dialog is displayed with a message.
    ORA_ALL_TOP_LEVEL_ACTIONS policy is enabled on the target database for specific users/roles. Enabling STIG Compliance will override those changes. Do you want to continue?
  6. Click on OK or Cancel. Upon clicking Cancel, the checkbox against Security Technical Implementation Guidelines (STIG) will be unchecked and you will not be able to enable STIG.
  7. In case the checkbox against Security Technical Implementation Guidelines (STIG) is checked, then it can be modified. An edit icon appears next to it. Click on the edit icon. The Configure STIG dialog is displayed.
  8. You can provide the privileged users for enabling the policy using either or both of the below options:
    • Privileged Users identified by User Entitlements
    • Include Privileged Users

    Note:

    • If you choose Privileged Users identified by User Entitlements, then ORA_ALL_TOPLEVEL_ACTIONS policy is enabled for all the privileged users identified by the user entitlement job. The Last Retrieved timestamp displays the time of the last retrieved user entitlement job. If the last retrieved time is greater than a day, then it is better to perform the user entitlement retrieval again before provisioning the Security Technical Implementation Guidelines (STIG) so that you have the latest list of privileged users.
    • If the Last Retrieved time is null, then it means that user entitlement was never retrieved. You need to retrieve user entitlement before provisioning Security Technical Implementation Guidelines (STIG). See Retrieving User Entitlement Data for Oracle Database Targets for more information.
    • If you choose Privileged Users identified by User Entitlements, then you can automatically apply the STIG policy for latest list of privileged users. Click the checkbox against Automatically update ORA_ALL_TOPLEVEL_ACTIONS when Privileged Users change. When this option is selected, whenever a user entitlement job runs and if the privileged users have changed, then the ORA_ALL_TOPLEVEL_ACTIONS audit policy is enabled again for the latest list of privileged users. You can schedule the user entitlement job so that STIG is enabled for all privileged users.
    • If you choose Include Privileged Users option, then manually enter the list of privileged users.
  9. Click Save.
  10. Click Provision Unified Policy button in the top right corner to provision the policy.
  11. To check the provisioning status, click the Settings tab. Then click on Jobs tab in the left navigation menu. When the job is submitted successfully, the Unified Audit Policy job is displayed on the page under Job Type column.

6.4.5 User-defined and Oracle Pre-defined Unified Policies

Learn about provisioning custom and Oracle pre-seeded unified policies.

You can enable Oracle Pre-defined Policies or User-defined Policies that you created on your target database using Oracle Audit Vault and Database Firewall. The following are Oracle Pre-defined Policies:

  • ORA_ACCOUNT_MGMT
  • ORA_DATABASE_PARAMETER
  • ORA_SECURECONFIG
  • ORA_DV_AUDPOL
  • ORA_DV_AUDPOL2 (Oracle Database 12.2.0.1 and later)
  • ORA_RAS_POLICY_MGMT
  • ORA_RAS_SESSION_MGMT
  • ORA_LOGON_FAILURES

Starting Oracle AVDF 20.4, User-defined and Oracle Pre-defined Unified policies can be enforced on users, roles, and on specific event conditions (successful, unsuccessful, or both).

Note:

In case the policies have been enabled or disabled directly from the target database after the last retrieval, then they have to be retrieved again. This is done to fetch the updated list of the unified audit policies that are enabled or disabled on the target database.

Follow these steps to edit the audit policy for users or roles:

  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab. The Audit Policies tab in the left navigation menu is selected by default.
  3. Click on the name of the specific Oracle Database target for which you want to provision the audit policies.
  4. Unified Auditing sub tab in the main page is selected by default.
  5. If the checkbox against any pre-defined or user-defined policy is checked, then it can be modified. An edit icon appears next to it.
  6. Click on the edit icon. The Configure Policy dialog is displayed.

  7. Based on users, the audit policy can be enabled for:

    • All users
    • Only a specific set of users and/or roles
    • All users except a specific set of users

  8. Based on event status, the audit policy can be enabled for:

    • Successful events
    • Unsuccessful events
    • Both successful and unsuccessful events
  9. If you choose to modify or enable for only a specific set of users and/or roles, then a table appears. This table has two columns. You can add additional users and event status details. You can also delete any existing entries.
  10. If you choose to modify or enable for all users and exclude only a specific set of users, then a table component appears. You can move users from the Available column to Excluded column to exclude specific users. You can also select the event status (successful, unsuccessful, both) beneath the table.
  11. Choose or edit the values according to your specification in the dialog.
  12. Click Save.
  13. Click Provision Unified Policy to provision the policy.
  14. Check the Jobs section for the status.

Note:

To view the list of roles, follow the steps for Retrieving User Entitlement Data for Oracle Database Targets.

6.4.6 Provisioning Unified Audit Policies from the Audit Vault Server

You can provision unified audit policies from the Audit Vault Server to the Oracle Database target.

This updates the audit policies in the target without the intervention of a database administrator. However, a database administrator can modify or delete these audit policies, as well as add new ones. For this reason, you should periodically retrieve the settings to ensure that you have the latest audit policies.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Policies tab.

    The Audit Settings page is displayed, showing the Oracle database targets to which you have access.

  3. Click the name of a target database.
  4. Click Unified Auditing sub tab on the main page.
  5. Select one or more check boxes to enable or disable the policy.
  6. Click Provision Unified Policy button in the top right corner.

    To check the provisioning status, click the Settings tab. Then click on Jobs tab in the left navigation menu. When the job is submitted successfully, the Unified Audit Policy job is displayed on the page under Job Type column.

    Note:

    Ensure the target user has sufficient privileges granted for audit policy management. This can be accomplished by running Oracle Database Setup Scripts.

    For Unified audit policy retrieval and provisioning for CDBs and PDBs, the audit policies can be provisioned or retrieved by treating every PDB as an independent target. A CDB can be registered for audit policy management. While provisioning a CDB, audit policies can be included for CDB only, or for CDB and all PDBs. In case CDB and all PDBs are selected, it propogates policies for all the PDB instances. Log in to the Audit Vault Server console and click the Policies tab. In the left navigation menu, select Audit Policies. Under the Unified Auditing sub tab and under the Core Policies section of the main page, there are two radio buttons at the bottom:

    1. Container & All Pluggable Databases
    2. Container Database Only

    Choose these options accordingly and complete the audit policy provisioning. Upon successfully provisioning of the audit policies for a CDB target, the buttons are disabled and cannot be selected again.

    See Also:

6.5 Provisioning Traditional Audit Policies

Traditional audit policies are used to monitor SQL statements, schema objects, privileges, and fine-grained auditing.

After patching to Oracle AVDF 20.12, you will need to
  1. Rerun the Oracle privileges script for successful audit policy retrieval for container database targets. For more information see Oracle Database Setup Scripts.
  2. Retrieve audit policies before provisioning or viewing audit policies. For more information see Retrieving and Modifying Audit Policies from an Oracle Database

6.5.1 About Creating Audit Policy Settings

After you retrieve audit policy settings from the target Oracle database, and selected the settings you need, you can create new policy settings for the Oracle database.

Caution:

  • Any audit setting that is not indicated as Needed in the Audit Vault Server console will be turned off on the target. See "Specifying which Audit Policies are needed".

  • After you have updated and/or created the audit policies for a target Oracle Database, you can provision the audit policy changes to that database.

6.5.2 Specifying which Audit Policies are needed

After you retrieve the audit policies from the target Oracle Database, you can view and modify them as needed.

Remember that you are modifying audit policies in use at the time you retrieved them. If you think they may have changed, you should retrieve them again.

  1. Log in to the Audit Vault Server console as an auditor.

  2. Click Policies tab. The Audit Policies tab in the left navigation menu is selected by default.

  3. Click on the name of the specific Oracle Database target for which you want to change the audit policies.

  4. An overview page for the target displays two sections:

    • Unified Auditing
    • Traditional Auditing

  5. Click Traditional Auditing. It displays the audit policies in use and marked as needed for the audit types:

    • Statement

    • Object

    • Privilege

    • FGA

  6. Click on the link of the specific audit type to update the settings. For example, Object.

    The Object audit policies for the specific target is displayed on the page. This is the current audit policies. The second column displays a problem icon if there is a difference between the setting at the target database, and the setting in Oracle Audit Vault and Database Firewall.

  7. Select the check boxes for each audit setting you determine is needed, then click Set as Needed button on the top right corner.

  8. To remove audit policies, select the check boxes for the ones you want to remove, then click Set as Not Needed.

  9. To create new audit policies for the audit type, click Create.

See Also:

6.5.3 Creating Audit Policies for SQL Statements

Auditors can create and manage audit policies for SQL statements.

6.5.3.1 About SQL Statement Auditing

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.

6.5.3.2 Defining SQL Statement Audit Settings

Any auditor can define a SQL statement audit policy.

  1. Log in to the Audit Vault Server console as an auditor.
  2. If necessary, retrieve and update the current audit settings.
  3. Click the Policies tab. The Audit Policies tab in the left navigation menu is selected by default.
  4. Click on a specific Oracle Database target.
  5. An overview page for the target displays two sections:
    • Unified Auditing
    • Traditional Auditing
  6. Click Traditional Auditing. Click Statement in the column Audit Type.

    The statement audit settings of the specific target is displayed.

  7. Click the Create button.
  8. In this page, define the audit policy as follows:

    • Audit Actions By - Choose the users to audit:

      • Both: Audits all users, including proxy users.

      • Proxy: Audits the proxy user for the database. When you select this option, the Proxy User field appears, in which you must specify at least one user.

      • User: Audits the user to which this setting applies. If you select this option, you must select a user from the Users drop-down list.

    • Execution Condition - Choose one of the following:

      • Both: Audits both successful and failed statements

      • On Success: Audits the statement if it is successful

      • On Failure: Audits the statement if it fails

    • DML Audit Granularity - Choose audit granularity for DML statements:

      • Access: Creates an audit record each time the operation occurs

      • Session: Creates an audit record the first time an operation occurs in the current session

      DDL statements are always audited by access.

    • Statements Audit Type - Select the SQL statements to audit by double clicking a statement type to move it to the box on the right. You can use the double arrows to move all statements to the right or back to the left.

  9. Click Save.
6.5.3.3 Understanding the Statement Audit Settings

The Statement Audit Settings page shows status information such as whether the statement is audited or if the statement audit policy is active.

Table 6-2 lists the columns used in the Statement page.

Table 6-2 Columns in the Statement Audit Settings

Column Description

(Left most column)

A checkbox for selecting the audit setting.

(Problem icon)

An exclamation mark icon indicates one of the following conditions:

  • The setting is marked as needed in Oracle Audit Vault and Database Firewall, but is not in use in the target database.

  • The setting is in use at the target database, but is not marked as needed in Oracle Audit Vault and Database Firewall.

Setting

The statement that is audited.

In Use

A green check mark indicates if the setting is active in the target database. A red cross mark indicates if it has not been provisioned or is not active.

Needed

A green check mark indicates if the audit setting is marked as needed in Oracle Audit Vault and Database Firewall. A red cross mark indicates if the audit setting is marked as not needed.

If an audit setting that is not in use is set to needed, a green check mark appears after provisioning in the In Use column. 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: ACCESS or SESSION

Execution Condition

The execution condition audited: SUCCESS, FAILURE, or BOTH

Proxy User

The proxy user for the database, if any.

User

The user to which this setting applies, if any.

6.5.4 Creating Audit Policies for Schema Objects

Auditors can create and manage schema object audit policies.

6.5.4.1 About Schema Object Auditing

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.

6.5.4.2 Defining Schema Object Audit Settings

Any auditor can define a schema object audit policy.

  1. Log in to the Audit Vault console as an auditor.
  2. If necessary, retrieve and update the current audit settings.
  3. Click Policies tab. The Audit Policies tab in the left navigation menu is selected by default.
  4. Click on a specific target Oracle database.

    An overview page for the target displays two sections:

    • Unified Auditing
    • Traditional Auditing
  5. Click on Traditional Auditing.
  6. Click Object to display the Object Audit Settings of the specific target.
  7. Click the Create button.
  8. In the Object Audit Settings page, define the settings as follows:

    • Object Type - Select the type of object to audit from the drop-down list, such as TABLE, LOB, RULE, or VIEW.

    • Object - Select a specific object of the object type you selected.

    • Execution Condition - Choose one of the following:

      • Both: Audits both successful and failed statements

      • On Success: Audits the statement if it is successful

      • On Failure: Audits the statement if it fails

    • DML Audit Granularity - Choose audit granularity for DML statements:

      • Access: Creates an audit record each time the operation occurs

      • Session: Creates an audit record the first time an operation occurs in the current session

      DDL statements are always audited by access.

    • Statements Audit Type - Select the SQL statements to audit by double clicking a statement type to move it to the box on the right. You can use the double arrows to move all statements to the right or back to the left.

  9. Click Save.

    The newly defined object audit settings is added to the Object Audit Settings page.

    See Also:

6.5.4.3 Understanding the Object Audit Settings Page

The Object Audit Settings page shows object status information such as the object that is being audited and whether the policy is active.

Table 6-3 lists the columns used in the Object page.

Table 6-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:

  • The setting is marked as needed in Oracle Audit Vault and Database Firewall, but is not in use in the target database.

  • The setting is in use at the target database, but is not marked as needed in Oracle Audit Vault and Database Firewall.

Setting

The statement that is audited

In Use

The arrow points upward if the setting is active in the 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 Audit Vault and Database Firewall, 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.

Name

The name of the object in the specified schema.

Type

The object (such as a database table) to which this setting applies

Owner Name

The database schema to which this setting applies

Audit Granularity

The granularity of auditing: ACCESS or SESSION

Execution Condition

The execution condition audited: SUCCESS, FAILURE, or BOTH

6.5.5 Creating Audit Policies for Privileges

Auditors can create and manage privilege audit policies.

6.5.5.1 About Privilege Auditing

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.

6.5.5.2 Defining Privilege Audit Settings

Any auditor can define a privilege audit policy.

  1. Log in to the Audit Vault Server console as an auditor.
  2. If necessary, retrieve and update the current audit settings.
  3. Click Policies tab. The Audit Policies tab in the left navigation menu is selected by default.
  4. Click on the name of the specific Oracle Database target for which you want to change the audit settings.
  5. An overview page for the target displays two sections:
    • Unified Auditing
    • Traditional Auditing
  6. Click Privilege.

    The Privilege Audit Settings for a specific target is displayed.

  7. Click the Create button.
  8. In the Create Privilege Audit Settings page, define the privilege audit policy as follows:

    • Audited By - Choose the users to audit:

      • Both: Audits all users, including proxy users.

      • Proxy: Audits the proxy user for the database. When you select this option, the Proxy Users field appears, in which you must specify at least one user. To display a list of proxy users and their targets for selection, click up-arrow icon on the right of the field.

      • User: Audits the user to which this setting applies. When you select this option, the Users field appears, and you must specify a user from the drop-down list.

    • Execution Condition - Choose one of the following:

      • Both: Audits both successful and failed privilege use

      • On Success: Audits the privilege use if it is successful

      • On Failure: Audits the privilege use if it fails

    • DML Audit Granularity - Choose audit granularity for DML statements:

      • Access: Creates an audit record each time the operation occurs

      • Session: Creates an audit record the first time an operation occurs in the current session

      DDL statements are always audited by access.

    • Statements Audit Type - Select the privileges to audit by double clicking a statement type to move it to the box on the right.

      You can use the double arrows to move all statements to the right or back to the left.

  9. Click Save.

    The newly defined privilege audit settings is added to the list in the Privilege Audit Settings page.

    See Also:

6.5.5.3 Understanding the Privilege Audit Settings Page

The Privilege Audit Settings page shows status information such as the privileges being audited and whether an audit policy is active.

Table 6-4 lists the columns used in the Privilege Audit Settings page.

Table 6-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:

  • The setting is marked as needed in Oracle Audit Vault and Database Firewall, but is not in use in the target database.

  • The setting is in use at the target database, but is not marked as needed in Oracle Audit Vault and Database Firewall.

Setting

The statement that is audited

In Use

The arrow points upward if the setting is active in the 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 Audit Vault and Database Firewall, 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: BY ACCESS or BY SESSION

Execution Condition

The execution condition audited: SUCCESS, FAILURE, or BOTH

User

The user to which this setting applies, if any

Proxy User

The proxy user for the database, if any

6.5.6 Creating Audit Policies for Fine-Grained Auditing (FGA)

Auditors can create and manage fine-grained audit policies.

6.5.6.1 About Fine-Grained Auditing

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.

6.5.6.2 Using Event Handlers in Fine-Grained Auditing

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.

6.5.6.3 Auditing Specific Columns and Rows

A fine-grained audit policy 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.

6.5.6.4 Defining Fine-Grained Audit Settings

Any auditor can define a fine-grained audit policy.

  1. Log in to the Audit Vault Server console as an auditor.
  2. If necessary, retrieve and update the current audit settings.
  3. Click Policies tab. The Audit Policies tab in the left navigation menu is selected by default.
  4. Click on the name of the specific Oracle Database target for which you want to change the audit settings.

    An overview page for the target displays two sections:

    • Unified Auditing
    • Traditional Auditing
  5. Click on Traditional Auditing.
  6. Click FGA. It displays the Fine Grained Audit Settings page of the specific target.
  7. Click the Create button.
  8. Define the audit policy as follows:

    • Policy Name - Enter a name for this fine-grained audit policy.

    • Audit Trail - Select from one of the following audit trail types:

      Definition Description

      Database

      Writes the policy records to the database audit trail SYS.FGA_LOG$ system table.

      Database with SQL Text

      Performs the same function as the Database option, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.FGA_LOG$ table.

      XML

      Writes the policy records to an operating system XML file. To find the location of this file, a database administrator can run the following command in SQL*Plus:

      SQL> SHOW PARAMETER AUDIT_FILE_DEST

      XML with SQL Text

      Performs the same function as the XML option, but also includes all columns of the audit trail, including SQLTEXT and SQLBIND values.

      WARNING:

      Be aware that sensitive data, such as credit card numbers, appear in the audit trail if you collect SQL text.

    • Schema - Select a schema to audit.

    • Objects - Select an object to audit.

    • Statements - Select one or more SQL statements to be audited. The available options are: DELETE, INSERT, SELECT, or UPDATE.

    • Columns - (Optional) Enter the names of the database columns (relevant columns) to audit. Separate each column name with a comma. If you enter more than one column, select All or Any as the condition that triggers this policy.

    • Conditions - (Optional) Enter a boolean condition to filter row data. For example, department_id = 50 .

      If this field is blank or null, auditing occurs regardless of condition.

    • Handler Schema - (Required if you specify an event handler function) Enter the name of the schema account in which the event handler was created. For example: SEC_MGR

    • Handler Package - (Required if you specify an event handler function) Enter the name of the package in which the event handler was created. For example: OE_FGA_POLICIES

    • Handler Function - (Optional) Enter the name of the event handler. For example: CHECK_OE_VIOLATIONS

  9. Click Save.
6.5.6.5 Understanding the Fine-Grained Audit Settings Page

The Fine-Grained Audit Settings page shows status information, such as the object to which the policy applies.

Table 6-5 lists the columns used in the Fine-Grained Audit Settings page.

Table 6-5 Columns in the Fine-Grained Audit Settings Page

Field Description

(Leftmost column)

A checkbox for selecting the audit setting

Problem

An exclamation mark icon indicates one of the following conditions:

  • The setting is marked as needed in Oracle Audit Vault and Database Firewall, but is not in use in the target database.

  • The setting is in use at the target database, but is not marked as needed in Oracle Audit Vault and Database Firewall.

Name

The name of this fine-grained audit policy

In Use

The arrow points upward if the setting is active in the 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 Audit Vault and Database Firewall, 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:

  • S: SELECT

  • I: INSERT

  • U: UPDATE

  • D: DELETE

  • M: MERGE

Columns

The database columns being audited, also referred to as the relevant columns. If this field is empty, all columns are audited.

6.5.7 Exporting Audit Settings to a SQL Script

You can export audit policy settings for a target to a SQL script from Oracle Audit Vault and Database Firewall.

Then you can give the script to a database administrator for the target Oracle Database to use to update the audit settings on that database.
  1. Log in to the Audit Vault console as an auditor.
  2. Click Policies tab. The Audit Policies tab in the left navigation menu is selected by default.

    The page displays a list of Oracle Database targets to which you have access.

  3. Click the name of the specific Oracle Database target.

    The audit settings overview for the database appears on the page. Unified Auditing sub tab is selected by default.

  4. Click Traditional Auditing sub tab.
  5. Select one or more check boxes for the audit types that you want to export: Statement, Object, Privilege, or FGA.
  6. Click Export/Provision.

    The Export/Provision Audit Settings page appears. It displays the exportable audit commands.

  7. Click the Export radio button, then click the Export button in the top right corner.
  8. Click OK to confirm.
  9. Save the SQL file to a location on your system.
  10. Give the saved script to the database administrator for that target.

    The database administrator can then apply the policies to the target. To verify that the settings have been updated, you can retrieve the audit settings.

    See Also:

6.5.8 Provisioning Traditional Audit Policies from the Audit Vault Server

You can provision the traditional audit policy settings directly from the Audit Vault Server to the target Oracle Database.

This updates the audit policies in the target without the intervention of a database administrator. However, a database administrator can modify or delete these audit policies, as well as add new ones. For this reason, you should periodically retrieve the settings to ensure that you have the latest audit policies.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click Policies tab.

    The Audit Policies tab in the left navigation menu is selected by default. It displays Oracle Database targets to which you have access.

  3. Click the name of the specific target database.
  4. Click Traditional Auditing sub tab on the main page.
  5. Select one or more check boxes for the audit types that you want to provision: Statement, Object, Privilege, or FGA.
  6. Click Provision Traditional Policy button.

    The Export/Provision Audit Settings page appears. It displays the exportable audit commands, and allowing you to verify them before provisioning. The audit settings can be either exported to a file or provisioned directly to the target.

  7. Click the Provision radio button below the box.
  8. Ensure the target user has sufficient privileges granted for audit policy management using the privilege script.

    Note:

    Ensure the target user has sufficient privileges granted for audit policy management. This can be accomplished by running Oracle Database Setup Scripts.
  9. Click the Provision button in the top right corner.
  10. Click OK to confirm.

    To check the provisioning status, click the Settings tab. Then click on Jobs tab in the left navigation menu. When the job is submitted successfully, the Audit Settings job is displayed on the page under Job Type column.

    See Also:

6.6 Viewing Unified Audit Policies

Starting in Oracle AVDF 20.12, you can view the audit policies that are enabled on your target databases.

Prerequisites

After patching to Oracle AVDF 20.12, you will need to
  1. Rerun the Oracle privileges script for successful audit policy retrieval for container database targets. For more information see Oracle Database Setup Scripts.
  2. Retrieve audit policies before provisioning or viewing audit policies. For more information see Retrieving and Modifying Audit Policies from an Oracle Database

Procedure

  1. Log in to the Audit Vault Server Console as an auditor.

  2. Click the Policies tab.
  3. Select the database(s) you would like to view the audit policies for.
  4. Click View Policies.

    You will see a table specifying the details of the audit policies for the selected target database(s).

    For a CDB$ROOT target, you can see the list of common policies that have been enabled on the container database (CDB) and the pluggable database(s) (PDB) and the local policies of each individual PDB as well. This will show the policies of all PDBs that have been set up when the audit retrieval was done. If a PDB is added or removed, perform audit retrieval again to see the latest list.