Configure and Administer Database Auditing

In my previous topic I have given an idea about basics of oracle database auditing. Before entering in this topic I assume that my previous topic “Basics of Database Auditing” has already been read.

Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file. This fact is called mandatory auditing, and it includes the following operations:

• Connections to the instance with administrator privileges (connecting to Oracle Database as SYSOPER or SYSDBA.)

• Database startup

• Database shutdown

Enabling and Disabling Standard Auditing

(A)Setting the AUDIT_TRAIL Initialization Parameter

ALTER SYSTEM SET AUDIT_TRAIL =value scope=spfile;

The AUDIT_TRAIL initialization parameter must be set to enable standard auditing. It may take any of the six values.

1. DB: Enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail.
2. XML: All elements of the AuditRecord node except Sql_Text and Sql_Bind will be printed to the operating system XML audit file.
3. DB,EXTENDED: Does all actions of AUDIT_TRAIL=DB and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These two columns are populated only when this parameter is specified.)
4. XML,EXTENDED: Does all actions of AUDIT_TRAIL=XML and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These columns are populated only when this parameter is specified.)
5. OS: Enables database auditing and directs all audit records to an operating system file
6. NONE: Disables standard auditing (This value is the default.)

(B)Specifying a Directory for the Operating System Auditing Trail

The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when either AUDIT_TRAIL=OS or AUDIT_TRAIL=XML is specified.

Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified.

AUDIT_FILE_DEST can be changed with ALTER SYSTEM SET AUDIT_FILE_DEST = DEFERRED, meaning the new destination will be effective for all subsequent sessions.

If the AUDIT_FILE_DEST parameter is not specified, then the default location on Solaris is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump.

(C)Enabling Standard Auditing Options

To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege.

To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.

Audit statements that set statement and privilege audit options can include a BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:

• BY SESSION/BY ACCESS

BY SESSION causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.

• WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

Auditing Connections and Disconnections

AUDIT SESSION;

AUDIT SESSION BY PROD7, PROD72;
Enabling Privilege Auditing

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

Enabling Object Auditing

AUDIT SELECT, INSERT, DELETE ON PROD7.USER_ACTIVITY BY ACCESS WHENEVER SUCCESSFUL;

(D)Turning Off Statement and Privilege Auditing

The following statement turns off all statement audit options:

NOAUDIT ALL;
The following statement turns off all privilege audit options:

NOAUDIT ALL PRIVILEGES;

Turning Off Object Auditing

NOAUDIT ALL ON PROD7;

Turning Off Network Auditing

NOAUDIT NETWORK;

Purging Audit Records from the Audit Trail

For example, to delete all audit records from the audit trail, enter the following statement:

DELETE FROM SYS.AUD$;
Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:

DELETE FROM SYS.AUD$ WHERE obj$name=’EMP’;

(E)Viewing Database Audit Trail Information DBA_STMT_AUDIT_OPTS

DBA_STMT_AUDIT_OPTS

Describes current system auditing options across the system and by user.

DBA_PRIV_AUDIT_OPTS

Describes current system privileges being audited across the system and by user.

DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS

Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user.

DBA_AUDIT_TRAIL USER_AUDIT_TRAIL

Lists all audit trail entries. The USER view shows audit trail entries relating to current user.

DBA_AUDIT_OBJECT USER_AUDIT_OBJECT

Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user.

DBA_AUDIT_SESSION USER_AUDIT_SESSION

Lists all audit trail records concerning CONNECT and DISCONNECT. The USER view lists all audit trail records concerning connections and disconnections for the current user.

DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT

Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user.

DBA_AUDIT_EXISTS

Lists audit trail entries produced BY AUDIT NOT EXISTS.

Read Full Post »

Audit Trigger Activity in Oracle

Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.

We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.

Here is a test. Inside test schema I have made an example.

Connect as test user and create three tables.

SQL> conn test/test
Connected.

SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.

SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.

SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.

2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.

SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.

3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string NONE

SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.

4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,’A’,’B’);
1 row created.

SQL> commit;
Commit complete.

5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.

SQL> conn / as sysdba
Connected.

SQL> startup force
ORACLE instance started.

Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.

SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string DB, EXTENDED

6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.

SQL> insert into test values(1,’Tes’,’T2′);
1 row created.

SQL> commit;
Commit complete.

7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected

SQL> select * from test1;
A B C
———- — —
1 Tes T2

SQL> select * from test2;
A B C
———- — —
1 Tes B

8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.

SQL> insert into test values(2,’Test’,’Test2′);
insert into test values(2,’Test’,’Test2′)
*
ERROR at line 1:
ORA-12899: value too large for column “TEST”.”TEST1″.”B” (actual: 4, maximum:3)
ORA-06512: at “TEST.TEST_T”, line 2
ORA-04088: error during execution of trigger ‘TEST.TEST_T’

SQL> select username,sql_text from dba_audit_trail;
USERNAME                             SQL_TEXT
————–                       ————————–
TEST                                 insert into test values(2,’Test’,’Test2′)

Read Full Post »

How to move audit table out of SYSTEM tablespace

Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile ‘/oracle/TST/datafile/aud01.dbf’ size 500M;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

SQL> select count(*) from aud$; 

  COUNT(*)
———-
         32706

Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

SQL> select count(*) from aud$$; 

  COUNT(*)
———-
         32706
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

SQL> select count(*) from aud$; 

  COUNT(*)
———-
         0
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Read Full Post »