How to use Standard Auditing of Oracle to audit
general database activities?

Standard Auditing of Oracle helps you audit SQL statements, schema, objects, privileges, network and multi-tier activities.The Standard Audit records are stored within the database or can be written to an Operating System file and can be viewed in one of the following ways:

• DBA_AUDIT_TRAIL view (contains Standard audit records from SYS.AUD$ table)
• DBA_COMMON_AUDIT_TRAIL view (contains both Standard and fine-grained audit records)
• V$XML_AUDIT_TRAIL view (contains Standard Audit records directed to the Operating System audit trail).

Steps to enable Standard Audit Trail

• Start Database Control.

• To enable data audit, login using the SYSDBA privilege using the following command,

  sys/<password> as sysdba.

• Click on Server tab. The Server Subpage appears.

• Open Database Configuration section and click on Initialization Parameters.

  • If you have installed Oracle Database using Server Parameters file, click SPFile to open SPFile subpage. Otherwise, please go to the next step.

• In the Initialization Parameters page, enter AUDIT_TRAIL in the Name field and click Go.(In case you want to audit the system admin's activities, you have to specify AUDIT_SYS_OPERATION parameter in the Name field).

• In the Value field, enter one of the below parameters to create the required audit trail:

  • DB: This parameter records all the database activities and writes them to the SYS.AUD$ table except for those records that are always written to Operating System files.

  • NONE: This parameter disables Standard Auditing in Oracle database.

    1. Note: If you have created your Oracle database using Database Configuration Assistant, the Value field will be set to DB by default. Otherwise it is set to NONE.

  • BD, EXTENDED: This parameter performs the same function as the DB parameter. Additionally, it writes data to the Sql_Bind and Sql_Text CLOB type columns present in the SYS.AUD$ table.

  • XML: This parameter writes all the audit records of your Oracle database, except Sql_Text and Sql_Bind CLOB type columns to the Operating System audit record files in the XML format.

  • XML, EXTENDED: This parameter performs the same function as the XML parameter. Additionally, it writes data to the Sql_Bind and Sql_Text CLOB type columns present in the SYS.AUD$ table.

  • OS: This value writes all the Standard Audit records to the Operating System files. This value is used when the database auditor is distinct from your database administrator. As DB admins can view and edit even the audit record table present within the database, it is recommended to write all audit records to OS files for enhanced security.

• Click Apply.

• If you have enabled or disabled auditing for the entire database by changing the Value field, you have to restart your Oracle database instance for the changes to be applied.

You can follow the below steps to restart your Oracle database:

• Click on Database Instance link (present in the top left corner of the page)
• Click on Home.
• In the Database Control home page that appears, click on General tab.
• Click on Shutdown.
• In the Startup/Shutdown page that appears, enter your login credentials to shutdown the database.
• Start your Oracle instance again and check if the required changes are applied.

Auditing your Oracle database Instance using a log management solution.

In Standard Auditing, you have to manually enable and create audit trails to audit and monitor database activities. While auditing SQL statements, Standard Audit records do not contain the before and after values for records that were modified using UPDATE statement. The records are stored in tables, within the database, which consists of multiple columns. You have to use SQL queries to interpret and analyze these tables to retrieve the necessary information. Also, the audit trails have to be purged from time to time when the audit record tables run short of storage space. If the audit record table becomes full and no more records can be added to it, the auditing comes to a halt until records are purged. This is a major downfall when it comes to auditing your Oracle instance using Standard Audit. To avoid such mishaps, you can use a log management solution.

EventLog Analyzer is a log management solution that can collect all your Oracle database logs in a centralized location, parse, and analyze them to provide insightful reports about your database activities. Some of the important reports include Created Databases, Dropped Databases, Altered Databases, Created Clusters, Dropped Clusters, Altered Clusters, Created Tables, Dropped Tables, Altered Tables, Selected Tables, Inserted Tables, Updated Tables and Deleted Tables. You can configure real-time alerts to notify DB admins via SMS and email in case of an incident or attack. Click here to see EventLog Analyzer's Oracle database auditing capabilities.