Accessing and Viewing the Audit Trail in Oracle

Generating audit trail reports in Oracle 12c is a critical task for database administrators to ensure compliance and security. Follow these steps to effectively generate and manage audit trail reports:

1. Access the Audit Trail Data: Oracle 12c stores audit trail records in the `DBA_AUDIT_TRAIL` view, or in `DBA_COMMON_AUDIT_TRAIL` if unified auditing is enabled. This data includes details about user actions, such as the type of action performed, the user who performed it, and the time it occurred.
2. Query the Audit Trail: Construct SQL queries to extract relevant audit information. For example, to generate a report of all audit records for a specific time period, use a query like:
   

   SELECT username, userhost, timestamp, obj_name, action_name, sql_text
   FROM DBA_AUDIT_TRAIL
   WHERE timestamp BETWEEN TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD') AND TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD');
   

   Replace `YYYY-MM-DD` with the desired start and end dates.
3. Customize the Report: Tailor your queries to focus on specific actions, users, or time periods, depending on your auditing requirements. Use SQL clauses and conditions to refine the data selection.
4. Format the Output: For readability and analysis, format the output of your queries. Oracle SQL allows you to use functions and formatting options to structure the output, making it more understandable and report-friendly.
5. Export the Report: For documentation and compliance purposes, export the query results. This can be done using Oracle SQL Developer or other database tools that allow export to formats like CSV, Excel, or PDF.
6. Automate Report Generation: Consider automating the report generation process. This can be achieved by scripting SQL queries and scheduling them to run at regular intervals using Oracle Job Scheduler or external scripting tools like cron jobs in Unix/Linux.
7. Audit Trail Maintenance: Regularly review the size of the audit trail data and archive old records to maintain database performance. Implement procedures for backing up and purging audit trail data as part of regular database maintenance.
8. Review and Update Audit Policies: Periodically review and update your audit policies to ensure they continue to meet the changing needs and compliance requirements of your organization.
9. Ensure Security of Audit Reports: Protect the confidentiality and integrity of audit reports. Store them securely and restrict access to authorized personnel only.

By following these steps, you can efficiently generate, manage, and utilize audit trail reports in Oracle 12c, thereby enhancing the security and compliance posture of your database environment. Always refer to the latest Oracle documentation for detailed instructions and best practices specific to your database version and configuration.
Of course, collecting audit information is just the beginning. You will no doubt want to analyze the audit information you have collected.

As we mentioned earlier, audit records normally go into a table in the database called AUD$ in the SYS schema. This table is fairly complex, so Oracle provides a number of data dictionary views that make it easier for you to view audit records.
Some of these views are as follows:

View
Description
Corresponding view
Additional notes

USER_AUDIT_TRAIL
This view lists all audit records for the current user.
A corresponding view, DBA_AUDIT_TRAIL, lists all audit records for all users.
Some of the most relevant fields in this view are USERNAME, which is the name of the user who performed the audited action; TIMESTAMP, which is the time the action was performed; OWNER and OBJ_NAME, which identify the object the action was performed on; and ACTION_NAME, which is the name of the action performed on the object.
USER_AUDIT_SESSION
This view lists all the audit records for connecting and disconnecting from a database session.
A corresponding view, DBA_AUDIT_SESSION, lists all these statements for all users.
This view is frequently used when you are auditing to monitor security.
USER_AUDIT_STATEMENT
This view lists all audit records for the GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands.
A corresponding view, DBA_AUDIT_STATEMENT, lists all these statements for all users.
USER_AUDIT_OBJECT
This view lists all audit records for all statements that apply to database objects.
A corresponding view, DBA_AUDIT_OBJECT, lists all these statements for all users.
On some systems, these views are not automatically created by Oracle. For these systems, you will have to log on as user SYS and run the SQL script CATAUDIT.SQL.

Once you have collected a set of audit records, you can simply use standard SQL statements against the data dictionary views to see the results of your auditing. In the next lesson, you will learn how to audit specific privileges.

Click the Quiz link to test your knowledge on auditing SQL statements.
Auditing SQL Statements - Quiz

Click the Exercise link below to practice using auditing.
Viewing Audit Records - Exercise