The place and how the audit information is stored can be crucial to determine the operations performed on the database. In this recipe, we will discuss how and where this information can be collected and we will cover the possible destinations of audit trails and what options we may have.
For audit trail destination we have the option to store the audit records internally within the database or as external files.
AUDIT_TRAIL
parameter is DB
. By using this option, minimal audit information will be generated. If we want to extend the audit information to include issued statements and the bind variables that were used, we must utilize the EXTENDED
option. Connect as system
and issue the following statement:SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile; System altered. SQL>
AUDIT_TRAIL
value to OS
. In this case the audit files will be generated in the location specified by the AUDIT_FILE_DEST
parameter. If this parameter is not specified, the default location is ORACLE_BASE/admin/DB_UNIQUE_NAME/adump
or $ORACLE_HOME/rdbms/audit
:SQL> alter system set audit_trail='OS' scope=spfile; System altered.
AUDIT_TRAIL
to XML
as follows:SQL> alter system set audit_trail='XML' scope=spfile; System altered. SQL>
EXTENDED
mode is not allowed with normal text files, nor is it used in the OS mode. Similarly with DB EXTENDED
mode, by using XML format we can collect extended audit information also. To switch to XML with the EXTENDED
option execute the following statement:SQL> alter system set audit_trail='XML','EXTENDED' scope=spfile; System altered. SQL>
AUDIT_TRAIL
to NONE
as follows:SQL> alter system set audit_trail=none scope=spfile; System altered. SQL>
DB
with the EXTENDED
mode option. Set AUDIT_TRAIL
to this mode and bounce the database.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.
3.142.250.203