Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to:
1.) Investigate suspicious activity. For example, if an unauthorized user is deleting data from tables,the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
2.) Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
There are two distinct types of auditing
1.) Standard ->> auditing based on statement, privilege and object-level2.) Fine-grained ->> auditing on a finer granule which is based on content i.e. value > 10,000
Standard Auditing is further classified into 3 levels of standards auditing.The following are
i.) Statement ->> audit all actions on any type of object.ii.) Privilege ->> audit actions that stem from system privileges.
iii.) Object-level ->> specific audit actions like select, update, insert or delete.
For all 3 levels of auditing we can choose to audit by access (audit every time we access) or by session (audit only once per access during the session), we can also auditing on if the access was successful (whenever successful) or not (whenever not successful).
How to eanable auditing : Below are some basic methods for auditing :
1.) AUDIT_SYS_OPERATION : This parameter audits the SYS and SYSDBA connections. when we enable the parameter audit_sys_operations the database will write a trace file of the session action to the udump directory. This parameter should be enables on ALL production databases. Here is the method of enabling auditing .
SQL> alter system set audit_sys_operations=true scope=spfile;
SQL> shut immediate
SQL> startup
SQL> sho parameter audit
NAME TYPE VALUE
------------------ ----------- --------------------------------------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN\NOIDA\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB
We can audit sys and sysdba connections from event logfile .(eventvwr.msc)
2.) AUDIT_TRAIL Parameter : This parameter audit SESSION, DCL, DDL, DML, Select statements to table AUD$ or OS files.This is the easiest and most common method of auditing an oracle database, and this parameter should be set on ALL production databases. The audit_trail parameter can take the following values :
os = records the information to a o/s file (uses parameter audit_file_dest for the file location)
db = records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
db, extended = as per the db value but also populates the sqlbind and sqltext clob columns
xml = audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
xml,extended = as per xml option but also populates the sqlbind and sqltext clob columns
A basic list of audit option are as :
==>Turn off auditing :
SQL> alter system set audit_trail=none scope=spfile;
==> Auditing written to o/s :
SQL> alter system set audit_file_dest='c:\oracle\auditing';
SQL> alter system set audit_trail=os scope=spfile;
Note: if the audit_file_dest is not set then the default location is $oracle_home/rdbms/audit/
To start auditing we can use the below (there are many more options than stated below)
==> On Session
SQL> audit session by neer;
==> On Table
SQL> audit table;
==>Table and Specific user
SQL>audit table by neer;
==>Table, Specific User and Access
SQL> audit table by neer by access;
==>Privilege auditing
SQL>audit create any table;
SQL>audit create any table by neer;
==>Object auditing
SQL> audit select on neer.employees by access whenever successful;
SQL> audit select on neer.employees by access whenever not successful;
==>Disabling audit
SQL>noaudit table;
SQL>noaudit all privileges;
SQL>noaudit create any table by neer;
==>Turn off all auditing
SQL> noaudit all; (turn off all statement auditing)
SQL> noaudit all privileges; (turn off all privilege auditing)
SQL> noaudit all on default; (turn off all object auditing)
==>Purge audit table
SQL>delete from sys.aud$;
SQL>truncate from sys.aud$;
Here is demo of the Standard auditting :
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 23 13:58:26 2011
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
SQL> alter system set audit_trail=db scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> create user test identified by test
2 default tablespace users
3 quota unlimited on users;
User created.
SQL> grant connect to test;
Grant succeeded.
SQL> grant create table,create procedure to test;
Grant succeeded.
SQL> audit select table,update table,insert table,delete table by test by access;
Audit succeeded.
SQL> audit all by test by access;
Audit succeeded.
SQL> conn test/test@noida
Connected.
SQL> create table aud_table(id number);
Table created.
SQL> insert into aud_table values (&A);
Enter value for a: 1
old 1: insert into aud_table values (&A)
new 1: insert into aud_table values (1)
1 row created.
SQL> insert into aud_table values (&A);
Enter value for a: 2
old 1: insert into aud_table values (&A)
new 1: insert into aud_table values (2)
1 row created.
SQL> /
Enter value for a: 3
old 1: insert into aud_table values (&A)
new 1: insert into aud_table values (3)
1 row created.
SQL> update aud_table set id=123 where id=2;
1 row updated.
SQL> select * from aud_table;
ID
----------
1
123
3
SQL> delete from aud_table;
3 rows deleted.
SQL> drop table aud_table;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> select username,obj_name,action_name from dba_audit_trail
2 where owner='TEST' order by timestamp;
USERNAME OBJ_NAME ACTION_NAME
--------- --------------- -------------------
TEST AUD_TABLE CREATE TABLE
TEST AUD_TABLE INSERT
TEST AUD_TABLE INSERT
TEST AUD_TABLE INSERT
TEST AUD_TABLE UPDATE
TEST AUD_TABLE SELECT
TEST AUD_TABLE DELETE
TEST AUD_TABLE DROP TABLE
8 rows selected.
FGA (fine-grained auditing) : Fine-grain auditing (FGA) allows us to audit users accessing data of a certain criteria. As per standard auditing we can audit select, insert, update and delete operations. We use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that we can attach handlers (like a trigger) to the policies which can execute procedures.
There are many options that can be applied to the dbms_fga package, here are some simple examples :
==> Creating :
SQL> begin
dbms_fga.add_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test',
audit_column=>'salary,commission_pct',
enable=>false,
statement_types=>'select');
end;
/
==> Removing :
SQL> begin
dbms_fga.drop_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test');
end;
/
==>Enabling :
SQL>begin
dbms_fga.enable_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test');
end;
/
==> Disabling :
SQL> begin
dbms_fga.edisable_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test');
end;
/
Here is Demo of the FGA Auditing :
First,we will create the FGA policy say "FGA_AUD"
SQL> begin
2 dbms_fga.add_policy (
3 object_schema=>'test',
4 object_name=>'hot',
5 policy_name=>'FGA_AUD',
6 audit_column=>'id',
7 enable=>true,
8 statement_types=>'select,update');
9 end;
10 /
PL/SQL procedure successfully completed.
Now we will perform some "select" and "update" statements to audit :
SQL> update hot set id=2222 where id=123;
0 rows updated.
SQL> update hot set id=2222 where id=2;
1 row updated.
SQL> select * from hot;
ID
----------
22
2222
2342
SQL> update hot set id=8888 where id=2342;
1 row updated.
SQL> commit;
Commit complete.
To audit the table and the statements fired by db_user, fire the below query :
SQL> select db_user ,scn, sql_text from dba_fga_audit_trail where db_user='TEST';
db_user SCN SQL_TEXT
-------- ---------- ------------------------------------
TEST 1186412 update hot set id=2222 where id=123
TEST 1186421 update hot set id=2222 where id=2
TEST 1186431 select * from hot
TEST 1186440 update hot set id=8888 where id=2342
SYS.AUD$ table : Make sure that the sys.aud$ table gets purged from time to time as connections and DML activity in the database might come to a stand still if it becomes full.
Purge Audit Table :
SQL> delete from sys.aud$;
SQL> truncate from sys.aud$;
