Oracle Database Auditing in 10g and 9i - Part 1
Auditing required features installed default when you install a database . You need to enable it when you want to make use of it . The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command.
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /vivekapp1p/admin/vivek1/adump
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL>
By Default Auditing is disabled, but can be enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
The following list provides a description of each setting:
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.
AUDIT_SYS_OPERATIONS: static 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.
AUDIT_FILE_DEST: parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
To enable auditing and direct audit records to the database audit trail, we would do the following.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN
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 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>
Demonstration of setting Audit option for an Oracle User
OK, Our auditing has been set so straight we can proceed to enable it for particular user , which should be monitored under that .
SQL> sho parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/acme/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
SQL>
SQL>
SQL>
Now create a new for Auditing
SQL> create user audi identified by "xxxxxxx"
2 ;
User created.
SQL> grant connect ,resource to audi;
Grant succeeded.
SQL> grant dba to audi;
Grant succeeded.
Next we audit all operations by the AUDIT_TEST user.
SQL> AUDIT ALL BY audi BY ACCESS;
Audit succeeded.
SQL>
______________________________________________________________________________
Now login from different session to perform some activity as AUDI user .
poracle@acmord3p> sqlplus audi
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Mar 14 23:29:37 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table b (name int);
Table created.
SQL> insert into a values (1234);
1 row created.
SQL> select * from a;
NAME
----------
1234
1234
SQL> insert into b values (1234);
1 row created.
SQL> select * from b;
NAME
----------
1234
SQL> exit
_______________________________________________________________________________
Login as SYS and check if above activity have been audited for user AUDI .
SQL> COLUMN username FORMAT A10
SQL> COLUMN owner FORMAT A10
SQL> COLUMN obj_name FORMAT A10
SQL> COLUMN extended_timestamp FORMAT A35
SQL>
SQL> SELECT username,
2 extended_timestamp,
3 owner,
4 obj_name,
5 action_name
FROM dba_audit_trail
6 WHERE owner = 'AUDI'
7 8 ORDER BY timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME
---------- ----------------------------------- ---------- ----------
ACTION_NAME
----------------------------
AUDI 14-MAR-10 11.27.00.967650 PM +08:00 AUDI A
CREATE TABLE
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY AUDI BY ACCESS;
Audit succeeded.
SQL> COLUMN username FORMAT A10
SQL> COLUMN owner FORMAT A10
SQL> COLUMN obj_name FORMAT A10
SQL> COLUMN extended_timestamp FORMAT A35
SQL>
SQL> SELECT username,
2 extended_timestamp,
3 owner,
4 obj_name,
5 action_name
6 FROM dba_audit_trail
7 WHERE owner = 'AUDI'
8 ORDER BY timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDI 14-MAR-10 11.27.00.967650 PM +08:00 AUDI A CREATE TABLE
AUDI 14-MAR-10 11.29.49.480341 PM +08:00 AUDI B CREATE TABLE
AUDI 14-MAR-10 11.29.52.804367 PM +08:00 AUDI A INSERT
AUDI 14-MAR-10 11.29.57.708012 PM +08:00 AUDI A SELECT
AUDI 14-MAR-10 11.30.15.240634 PM +08:00 AUDI B INSERT
AUDI 14-MAR-10 11.30.21.371140 PM +08:00 AUDI B SELECT
6 rows selected.
To check about enabled audit options in database :
SELECT * FROM DBA_PRIV_AUDIT_OPTS where USER_NAME='AUDI';
USER_NAME AUDIT_OPTION SUCCESS FAILURE
------------------- ---------------------------------------- ---------- ----------
AUDI SELECT TABLE BY ACCESS BY ACCESS
AUDI ALTER SYSTEM BY ACCESS BY ACCESS
AUDI SYSTEM AUDIT BY ACCESS BY ACCESS
AUDI CREATE SESSION BY ACCESS BY ACCESS
AUDI TABLE BY ACCESS BY ACCESS
AUDI CLUSTER BY ACCESS BY ACCESS
AUDI TABLESPACE BY ACCESS BY ACCESS
AUDI USER BY ACCESS BY ACCESS
AUDI ROLLBACK SEGMENT BY ACCESS BY ACCESS
AUDI TYPE BY ACCESS BY ACCESS
AUDI INDEX BY ACCESS BY ACCESS
USER_NAME AUDIT_OPTION SUCCESS FAILURE
------------------- ---------------------------------------- ---------- ----------
AUDI SYNONYM BY ACCESS BY ACCESS
AUDI PUBLIC SYNONYM BY ACCESS BY ACCESS
AUDI VIEW BY ACCESS BY ACCESS
AUDI SEQUENCE BY ACCESS BY ACCESS
AUDI DATABASE LINK BY ACCESS BY ACCESS
AUDI PUBLIC DATABASE LINK BY ACCESS BY ACCESS
AUDI ROLE BY ACCESS BY ACCESS
AUDI DIMENSION BY ACCESS BY ACCESS
AUDI PROCEDURE BY ACCESS BY ACCESS
AUDI TRIGGER BY ACCESS BY ACCESS
AUDI PROFILE BY ACCESS BY ACCESS
USER_NAME AUDIT_OPTION SUCCESS FAILURE
------------------- ---------------------------------------- ---------- ----------
AUDI DIRECTORY BY ACCESS BY ACCESS
AUDI MATERIALIZED VIEW BY ACCESS BY ACCESS
AUDI NOT EXISTS BY ACCESS BY ACCESS
AUDI SYSTEM GRANT BY ACCESS BY ACCESS
AUDI CONTEXT BY ACCESS BY ACCESS
AUDI INSERT TABLE BY ACCESS BY ACCESS
AUDI UPDATE TABLE BY ACCESS BY ACCESS
AUDI DELETE TABLE BY ACCESS BY ACCESS
SGGUVI CREATE SESSION BY ACCESS BY ACCESS
CAUTION when AUDIT is set to DB :
As the table AUD$ which contains all the auditing data is created in the system tablespace,
so as the auditing information grows the size of the system tablespace also increases,
so it is advisable to move this particular table AUD$ to some another tablespace.
THE COMMAND TO MOVE THE TABLE IS:-
SQL>create table AUDX tablespace as select * from AUD$;
SQL>rename AUD$ to AUD$$;
SQL>rename AUDX to AUD$;
TO check whether AUD$ table has shifted to the new tablespace write the following query in the SQL prompt
SQL>select table_name,tablespace_name from dba_tables where table_name=’AUD$’;
to be continued..
