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
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
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.
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
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.
The insert statement created the audit trail.
Verify the current AUDIT trail settings
Note that, the audit trail is enabled at session level.
