How to Enable Auditing in 10g Database

Check the values of audit parameters of your 10g Database, as it is a default disabled feature.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

The AUDIT_TRAIL parameter can be used to enable auditing by setting one of the following values:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with
all audit records stored in the database audit trial (SYS.AUD$).
db,extended
- As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml-
Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also
populated.
os- Auditing is enabled, with all audit records directed to the
operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.

The Database needs to be bounced for the change to take affect.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
System altered.

The SQL_BIND and SQL_TEXT columns are populated, when extended option in AUDIT_TRAIL is enabled.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB

The AUDIT_SYS_OPERATIONS parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB, EXTENDED

Modifications of the data in the audit trail can be audited using the following statement:

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Audit succeeded.

To enable auditing on a schema/user we can use the following syntaxes

For auditing DDL (CREATE, ALTER & DROP of objects) statements

AUDIT ALL BY ACCESS;

For auditing DML (INSERT UPDATE, DELETE, SELECT, EXECUTE) statements

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY BY ACCESS;

For auditing SYSTEM EVENTS (LOGON, LOGOFF etc.) statements

AUDIT EXECUTE PROCEDURE BY BY ACCESS;

To monitor the audit trail we can use the DBA_AUDIT_TRAIL view.