All steps from this recipe will be performed on the HACKDB database.

For audit trail destination we have the option to store the audit records internally within the database or as external files.

By using the database to contain all the audit records these will be directed to the SYS.AUD$ table. In the EXTENDED mode the statements and bind variables are collected.

Using OS and XML mode, the entire audit files are written to the destination specified by AUDIT_FILE_DEST.

From a security point of view, using an internal destination for audit records is not the best, despite the fact that we will use this mode in majority of recipes in this chapter. There exists the possibility that the audit records can be deleted or tampered with by power users from aud$ and $fga_log$. Typically these tables are not accessible to non-SYS users, unless they have been explicitly granted access. By default, operations such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users are audited and can be checked by querying DBA_COMMON_AUDIT_TRAIL and DBA_AUDIT_TRAIL views.

If you want to use this mode, audit the SYS.AUD$ and SYS.FGA_LOG$ table itself by using fine-grained auditing and implement an alerting mechanism using handlers, or use Oracle Database Vault and build a realm around the SYS.AUD$ and SYS.FGA_LOG$.

Ideally, one of the best solutions would be to store the audit records when they are generated in an external safe location that is not accessible or modifiable by privileged users on either the server or in the database in which the actions that create the audit records are performed. A good but expensive solution could be to collect audit trails in a central location using Oracle Audit Vault. We will cover installation, configuration, and administration in the Appendix. Another option is to integrate Oracle Audit with SYSLOG and its variants. SYSLOG can be configured to transport the audit trails in a central secure location by using encrypted network communication (stunnel, syslog-ng, or rsyslog). We will cover SYSLOG integration in this chapter, in the recipe Integrating standard audit with SYSLOG.

There are specific operations that are always audited, such as SYSDBA and SYSOPER login, database startup, and shutdown. The audit trails for these operations are written in the location specified by audit_file_dest parameter. On Windows systems they are written, by default, to Windows Event Log.