Guidelines for Auditing

Oracle provides guidelines for auditing.

Manageability of Audited Information

Although auditing is relatively inexpensive, limit the number of audited events as much as possible.

This minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.

Follow these guidelines when devising an auditing strategy:

  1. Evaluate your reason for auditing.

    After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

    For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing strategy might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

  2. Audit knowledgeably.

    Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and using valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.

    For example, if you are auditing to gather information about database activity, then determine exactly what types of activities you want to track, audit only the activities of interest, and audit only for the amount of time necessary to gather the information that you want. As another example, do not audit objects if you are only interested in logical I/O information for each session.

  3. Before you implement an auditing strategy, consult your legal department.

    You should have the legal department of your organization review your audit strategy. Because your auditing will monitor other users in your organization, you must ensure that you are correctly following the compliance and corporate policy of your site.

Audits of Typical Database Activity

Oracle provides guidelines for when you must gather historical information about particular database activities.

  1. Audit only pertinent actions.

    At a minimum, audit user access, the use of system privileges, and changes to the database schema structure. To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities. Remember also that auditing too much can affect database performance.

    For example, auditing changes to all tables in a database produces far too many audit trail records and can slow down database performance. However, auditing changes to critical tables, such as salaries in a Human Resources table, is useful.

    You can audit specific actions by using fine-grained auditing, which is described in Auditing Specific Activities with Fine-Grained Auditing.

  2. Archive audit records and purge the audit trail.

    After you collect the required information, archive the audit records of interest and then purge the audit trail of this information. See the following sections:

  3. Remember your company’s privacy considerations.

    Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company policy concerns.

  4. Check the Oracle Database log files for additional audit information.

    The log files generated by Oracle Database contain useful information that you can use when auditing a database. For example, an Oracle database creates an alert file to record STARTUP and SHUTDOWN operations, and structural changes such as adding data files to the database.

    For example, if you want to audit committed or rolled back transactions, you can use the redo log files.

  5. To reduce the size of the audit trail and recursive SQL statements, audit only top-level statements.

    If you have concerns that the unified audit policy that you create will generate a very large number of records, then include the ONLY TOPLEVEL clause in the CREATE AUDIT POLICY statement. For example, an audit of the DBMS_STATS.GATHER_DATABASE_STATS SQL statement can generate thousands of audit records. You can audit top-level statements from all users, including user SYS.

Audits of Suspicious Database Activity

Oracle provides guidelines for when you audit to monitor suspicious database activity.

  1. First audit generally, and then specifically.

    When you start to audit for suspicious database activity, often not much information is available to target specific users or schema objects. Therefore, audit generally first, that is, by using the unified audit policies.Configuring Audit Policies explains how you can audit SQL statements, schema objects, privileges, and so on.

    After you have recorded and analyzed the preliminary audit information, alter your audit policies to audit specific actions and privileges. You can add conditions to your policies to exclude unnecessary audit records. You an also use the EXCEPT clause in the AUDIT POLICY statement to exclude specific users who do not need to be audited. See Auditing Activities with Unified Audit Policies and the AUDIT Statement for more information about unified audit policies.

    You can use fine-grained auditing, which is described in Auditing Specific Activities with Fine-Grained Auditing, to audit specific actions.

    Continue this process until you have gathered enough evidence to draw conclusions about the origin of the suspicious database activity.

  2. Audit common suspicious activities.

    Common suspicious activities are as follows:

    • Users who access the database during unusual hours

    • Multiple failed user login attempts

    • Login attempts by non-existent users

    In addition, be aware that sensitive data, such as credit card numbers, can appear in the audit trail columns, such as SQL text when used in the SQL query. You should also monitor users who share accounts or multiple users who are logging in from the same IP address. You can query the UNIFIED_AUDIT_TRAIL data dictionary view to find this kind of activity. For a very granular approach, create fine-grained audit policies.

Audits of Sensitive Data

Oracle recommends that you include the ACTIONS ALL clause when you create unified audit policies on sensitive objects.

Including this clause ensures the generation of audit record for both direct access and indirect access of these sensitive objects. Only use ACTIONS ALL for the audit of sensitive objects.

Recommended Audit Settings

Oracle provides predefined policies that contain recommended audit settings that apply to most sites.

For example:

Best Practices for Querying the UNIFIED_AUDIT_TRAIL Data Dictionary View

To get the best results from querying the UNIFIED_AUDIT_TRAIL data dictionary view, you should follow these guidelines.

  1. Ensure the statistics of unified audit internal table are up to date.

    Execute the DBMS_STATS.GATHER_TABLE_STATS procedure on the AUD$UNIFIED table in the AUDSYS schema to ensure that the unified audit table statistics are updated before you query the UNIFIED_AUDIT_TRAIL data dictionary view.

  2. Load the unified audit records that were written to operating system spillover files.

    You can do this either explicitly or by configuring an Oracle Scheduler job, using the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure.

  3. When the number of records in the unified audit trail reaches a significantly large number (for example, a million), then initiate the proper archiving and purging mechanisms.

    Archiving and purging the unified audit trial reduces the amount of data that otherwise could grow and cause read performance problems. Oracle recommends that you configure standard purging policies. The purging policies that you create will depend on the rate of audit records that are generated on your system. Frequent purges are required for high audit record generation rates.

  4. Move the unified audit trail to a custom tablespace.

    Using a custom tablespace enables you to better manage audit data and reduces the impact on other objects in the SYSAUX tablespace. By default, the unified audit trail records are written to the SYSAUX tablespace. To use a different tablespace, execute the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

  5. When you query the UNIFIED_AUDIT_TRAIL data dictionary view, include the EVENT_TIMESTAMP_UTC column in a WHERE clause.

    The EVENT_TIMESTAMP_UTC column records the timestamp of audited events in the UTC timezone. Including this column in the query helps to achieve the partition pruning, and thus improves read performance of the UNIFIED_AUDIT_TRAIL view.