Oracle Database Auditing in 10g and 9i - Part 1

iDBA , An iphone App for Oracle DBA's

DOWNLOAD

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..