Database audit table best practice

Privileged users can create policies that track the changes that all users, including other privileged users, make in the database.

Except where noted, this part describes how to use pure unified auditing, in which all audit records are centralized in one place.

• What Is Auditing?
  Auditing is the monitoring and recording of database activity, from both database users and nondatabase users.
• Why Is Auditing Used?
  You typically use auditing to monitor user activity.
• Best Practices for Auditing
  You should follow best practices guidelines for auditing.
• What Is Unified Auditing?
  In unified auditing, the unified audit trail captures audit information from a variety of sources.
• Benefits of the Unified Audit Trail
  The benefits of a unified audit trail are many.
• Checking if Your Database Has Migrated to Pure Unified Auditing
  The V$OPTION dynamic view indicates if your database has been migrated to using pure unified auditing (that is, traditional auditing is turned off).
• Mixed Mode Auditing
  Mixed mode auditing is the default auditing in a newly installed database.
• Who Can Perform Auditing?
  Oracle provides two roles for users who perform auditing: AUDIT_ADMIN and AUDIT_VIEWER .
• Unified Auditing in a Multitenant Environment
  You can apply audit settings to individual PDBs or to the CDB, depending on the type of policy.
• Auditing in a Distributed Database
  Auditing is site autonomous in that a database instance audits only the statements issued by directly connected users.

Related Topics

26.1 What Is Auditing?

Auditing is the monitoring and recording of database activity, from both database users and nondatabase users.

"Nondatabase users" refers to application users who are recognized in the database using the CLIENT_IDENTIFIER attribute. To audit this type of user, you can use a unified audit policy condition, a fine-grained audit policy, or Oracle Database Real Application Security.

This guide describes how to use unified auditing to create policies that consolidate audit trails from different Oracle Database components, such as fine-grained auditing or Oracle Database Vault, into one consolidated audit trail. This audit trail is viewable in the UNIFIED_AUDIT_TRAIL data dictionary view. (Other unified audit trail views, such as AUDIT_UNIFIED_POLICIES , are available.) A consolidated audit data trail enables you to run analysis reports on an entire set of audit data in one operation, rather than having to first gather them into one location before performing the analysis. Audit mining tools such as Oracle Audit Vault can look at one location rather than several in order to gather audit records. A unified audit trail ensures that the audit information is consistently formatted and contains consistent fields.

Alternatively, you can use traditional auditing, which is described in the Oracle Database release 11.2 Oracle Database Security Guide .

You can base auditing on individual actions, such as the type of SQL statement executed, or on combinations of session metadata that can include the user name, application, time, and so on.

You can configure auditing for both successful and failed activities, and include or exclude specific users from the audit. In a multitenant environment, you can audit individual actions of the pluggable database (PDB) or individual actions in the entire multitenant container database (CDB). In addition to auditing the standard activities the database provides, auditing can include activities from Oracle Database Real Application Security, Oracle Recovery Manager, Oracle Data Pump, Oracle Data Mining, Oracle Database Vault, Oracle Label Security, and Oracle SQL*Loader direct path events.

Auditing is enabled by default. All audit records are written to the unified audit trail in a uniform format and are made available through the UNIFIED_AUDIT_TRAIL view. These records reside in the AUDSYS schema. The audit records are stored in the SYSAUX tablespace by default. Oracle recommends that you configure a different tablespace for the unified audit trail, which you can do by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure. Be aware that for Oracle Database Standard Edition and Express Edition, but not for Enterprise Edition, you can only associate the tablespace for unified auditing once. You should perform this association before you generate any audit records for the unified audit trail. After you have associated the tablespace, you cannot modify it because partitioning is only supported on Enterprise Edition.

You can configure auditing by using any of the following methods:

• Group audit settings into one unified audit policy. You can create one or more unified audit policies that define all the audit settings that your database needs. Auditing Activities with Unified Audit Policies and the AUDIT Statement describes how to accomplish this.
• Use one of the default unified audit policies. Oracle Database provides predefined unified audit policies that encompass the standard audit settings that most regulatory agencies require. See Auditing Activities with the Predefined Unified Audit Policies.
• Create fine-grained audit policies. You can create fine-grained audit policies that capture data such as the time an action occurred. See Auditing Specific Activities with Fine-Grained Auditing.

Oracle recommends that you audit your databases. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act. This enables you to monitor business operations, and find any activities that may deviate from company policy. Doing so translates into tightly controlled access to your database and the application software, ensuring that patches are applied on schedule and preventing ad hoc changes. By creating effective audit policies, you can generate an audit record for audit and compliance personnel. Be selective with auditing and ensure that it meets your business compliance needs.

26.2 Why Is Auditing Used?

You typically use auditing to monitor user activity.

Auditing can be used to accomplish the following:

• Enable accountability for actions. These include actions taken in a particular schema, table, or row, or affecting specific content.
• Deter users (or others, such as intruders) from inappropriate actions based on their accountability.
• Investigate suspicious activity. For example, if a user is deleting data from tables, then a security administrator can audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
• Notify an auditor of the actions of an unauthorized user. For example, an unauthorized user could be changing or deleting data, or the user has more privileges than expected, which can lead to reassessing user authorizations.
• Support post-incident investigations.
• Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
• Detect problems with an authorization or access control implementation. For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies generate audit records, then you will know the other security controls are not properly implemented.
• Address auditing requirements for compliance. Regulations such as the following have common auditing-related requirements:
  • Sarbanes-Oxley Act
  • Health Insurance Portability and Accountability Act (HIPAA)
  • International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II)
  • Japan Privacy Law
  • European Union Directive on Privacy and Electronic Communications

  26.3 Best Practices for Auditing

  You should follow best practices guidelines for auditing.

  • As a general rule, design your auditing strategy to collect the amount of information that you need to meet compliance requirements, but focus on activities that cause the greatest security concerns. For example, auditing every table in the database is not practical, but auditing tables with columns that contain sensitive data, such as salaries, is. With both unified and fine-grained auditing, there are mechanisms you can use to design audit policies that focus on specific activities to audit.
  • Periodically archive and purge the audit trail data. You can use the DBMS_AUDIT_MGMT package to purge audit records in several different ways. You should regularly review the collected audit records and establish a system for collecting and retaining audit records based on your site's retention policies. In addition to DBMS_AUDIT_MGMT , Oracle Data Safe and Oracle Audit Vault and Database Firewall provide features that enable you manage the archiving and purging of audit trail data.

  Related Topics

  • Guidelines for Auditing
  • Purging Audit Trail Records