Enable Basic Auditing in Oracle 10.0.2

I have a test Oracle instance (10.0.2) running in NOARCHIVELOG mode without any auditing.

In order to enable AUDITing, following are the steps.

Verify the current AUDIT trail settings

First, we will login to the database check the current audit trail settings.

MYSERVER@ORA102>select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Show parameter audit displays the current settings of audit trail in the database.

MYSERVER@ORA102>show parameter audit;

NAME TYPE VALUE

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

audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN

\ORA102\ADUMP

audit_sys_operations boolean FALSE

audit_trail string NONE

Note that, the audit_trail parameter is set to NONE and audit_sys_operations is set to FALSE. In this database, both normal auditing and audit_sys_operations are disabled.

Although Oracle will allow us to enable auditing for specific database objects, no audit information will be captured.

Let us take a quick look.

The DBA_AUDIT_TRAIL view shows the audit trail records in the database. This view is one of the several Oracle views to access AUDIT information.

DBA_AUDIT_TRAIL

DBA_AUDIT_SESSION

DBA_AUDIT_STATEMENT

DBA_AUDIT_OBJECT

DBA_AUDIT_EXISTS

DBA_AUDIT_POLICIES

DBA_AUDIT_POLICY_COLUMNS

And more…

Also, we can check what audit options are enabled in our database using one of the following views.

DBA_OBJ_AUDIT_OPTS

ALL_DEF_AUDIT_OPTS

USER_OBJ_AUDIT_OPTS

DBA_STMT_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS
MYSERVER@ORA102>select * from dba_obj_audit_opts;
no rows selected

There are no objects being audited, or more accurately, have auditing options in effect.

The AUDIT data is stored in SYS.AUD$ table. It is recommended that this table be not access directly.

MYSERVER@ORA102>select * from dba_audit_trail;

no rows selected

There are currently no audit trail records in our database.

Enable AUDIT trail settings

Now we will create a table, enable auditing for DML on the table, perform some DML and check audit trail.

MYSERVER@ORA102>create table t (c int);

Table created.

MYSERVER@ORA102>audit insert, update, delete on t;

Audit succeeded.

Let us check the DBA_OBJ_AUDIT_OPTS view…

MYSERVER@ORA102>exec print_table('select * from dba_obj_audit_opts');

OWNER : SUDHAKAR

OBJECT_NAME : T

OBJECT_TYPE : TABLE

ALT : -/-

AUD : -/-

COM : -/-

DEL : S/S

GRA : -/-

IND : -/-

INS : S/S

LOC : -/-

REN : -/-

SEL : -/-

UPD : S/S

REF : -/-

EXE : -/-

CRE : -/-

REA : -/-

WRI : -/-

FBK : -/-

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

PL/SQL procedure successfully completed.

Note: print_table is a procedure, developed and made available by Tom Kyte.

This shows that, AUDITING options are in effect for INS, UPD, DEL on table T.

The format of the values (S/S or -/-) indicate the level of auditing if effect.

First position indicates the auditing option for SUCCESSFUL operation and third position for UNSUCCESSFUL operation

-/- indicates no auditing in effect

S/S indicates auditing is by SESSION.

A/A indicates auditing is by ACCESS.

Click here for various options and their effect in
DBA_OBJ_AUDIT_OPTS settings.

MYSERVER@ORA102>insert into t values (1);

1 row created.

MYSERVER@ORA102>commit;

Commit complete.

MYSERVER@ORA102>select * from t;

C

----------

1

MYSERVER@ORA102>select * from dba_audit_trail;

no rows selected

MYSERVER@ORA102>

Note: Although the auditing is in effect for the table T, No audit trail is generated.

AUDIT trail enabling requires DB Restart

Now we will turn on the audit_trail setting system wide.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> alter system set audit_trail=db,extended;

alter system set audit_trail=db,extended

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

Note that in 10g this parameter is not modifiable without DB restart.

SQL> alter system set audit_trail=db,extended scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248600 bytes

Variable Size 92275368 bytes

Database Buffers 192937984 bytes

Redo Buffers 2945024 bytes

Database mounted.

Database opened.

SQL>

DML to validate AUDIT trail settings

Now we will perform some DML on table T and observe the AUDIT trail creation.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 26 18:10:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

MYSERVER@ORA102>select * from dba_audit_trail;

no rows selected

MYSERVER@ORA102>insert into t values (2);

1 row created.

MYSERVER@ORA102>commit;

Commit complete.

MYSERVER@ORA102>select * from t;

C

----------

2

1

MYSERVER@ORA102>exec print_table('select * from dba_audit_trail');

OS_USERNAME : sudhab_c

USERNAME : SUDHAKAR

USERHOST : DIV16\SUDHABC1

TERMINAL : SUDHABC1

TIMESTAMP : 26-dec-2008 18:12:01

OWNER : SUDHAKAR

OBJ_NAME : T

ACTION : 103

ACTION_NAME : SESSION REC

NEW_OWNER :

NEW_NAME :

OBJ_PRIVILEGE :

SYS_PRIVILEGE :

ADMIN_OPTION :

GRANTEE :

AUDIT_OPTION :

SES_ACTIONS : ------S---------

LOGOFF_TIME :

LOGOFF_LREAD :

LOGOFF_PREAD :

LOGOFF_LWRITE :

LOGOFF_DLOCK :

COMMENT_TEXT :

SESSIONID : 46163

ENTRYID : 1

STATEMENTID : 13

RETURNCODE : 0

PRIV_USED :

CLIENT_ID :

ECONTEXT_ID :

SESSION_CPU :

EXTENDED_TIMESTAMP : 26-DEC-08 06.12.01.243000 PM -05:00

PROXY_SESSIONID :

GLOBAL_UID :

INSTANCE_NUMBER : 0

OS_PROCESS : 2900:1612

TRANSACTIONID : 0900050089130000

SCN : 0

SQL_BIND :

SQL_TEXT : insert into t values (2)

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

PL/SQL procedure successfully completed.

MYSERVER@ORA102>

The insert statement created the audit trail.

Verify the current AUDIT trail settings

Note that, the audit trail is enabled at session level.