Introduction to Oracle 10g initialization parameters Audit_trail

In 9i, initialization parameters Audit_trail only the None, DB, and OS three optional values, while in 10g Oracle added several new options.

This article describes the XML options.

Database version and default audit_trail parameter settings:

Sql> select * from V$version;

BANNER

----------------------------------------------------------------

Oracle database10genterpriseedition Release10.2.0.3.0-64bi

Pl/sql Release 10.2.0.3.0-production

CORE 10.2.0.3.0 Production

TNS for Linux:version 10.2.0.3.0-production

Nlsrtl Version 10.2.0.3.0-production

Sql> Show Parameter Audit_trail

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Audit_trail string NONE

The following sets the parameter to XML and restarts the database:

Sql> alter system SET Audit_trail = XML scope = SPFile;

System altered.

Sql> Conn/as SYSDBA

Connected.

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2074112 bytes

Variable Size 486541824 bytes

Database buffers 1644167168 bytes

Redo buffers 14700544 bytes

Database mounted.

Database opened.

Sql> Show Parameter Audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Audit_file_dest String/opt/ora10g/admin/primary/adump

Audit_sys_operations Boolean FALSE

Audit_syslog_level string

Audit_trail string XML

Open auditing in the database and trigger:

sql> Audit CREATE table;

Audit succeeded.

Sql> Conn Test/test

Connected.

Sql> CREATE TABLE T_audit (ID number);

Table created.

Although the XML parameter setting causes audit information to be written to the operating system, Oracle still provides the view with access to all audit records:

Sql> Select Audit_type, Db_user, object_name, Statement_type, Sql_text, Os_privilege

2 from Dba_common_audit_trail;

Audit_type db_user object_name statement_type sql_text Os_priv

---------------------- -------- ------------ --------------- -------------------- -------

Mandatory XML Audit/connect SYSDBA

Mandatory XML Audit/connect SYSDBA