Oracle Database Auditing

Oracle Database Auditing

There are two distinct type of auditing

Standard: Auditing based on statement, Privileges and object level.

Fine-grained: Auditing on a finer granule which is based on content i.e value > 10,000

The standard auditing having 3 levels of auditing:

Statement: Audit all action at any type of objects.

Privilege: audit action on system level privileges

Object_level: Specific audit action lie select, update, insert or delete.

For all the 3 level of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also audit on if the access was successful (whenever successful) or not (whenever not successful)

DB: Enables database auditing and directs all audit records to the database audit trail, except for records that are always written to the operating system audit trail.

DB, extended: As per the DB value but also populate sqlbind and sqltext clob columns

OS: Enables database auditing and directs all audit records to an operating system file

NONE: Disables auditing (This value is the default.)

Note: You must bounce back the instance to activate audit parameter.

 

Setting Audit Trail:

SQL>alter system set audit_trail = db scope=spfile;

SQL>alter system set audit_file_dest = ‘c:\oracle\auditing’;

SQL>alter system set audit_trail = os scope=spfile;

Note: Note: if the audit_file_dest is not set then the default location is $oracle_home/rdbms/audit/

SQL>alter system set audit_sys_operations = true scope = spfile;

Note: this will audit all sys operations regardless ifaudit_ trail is set.

SQL>alter system set audit_trail = none scope=spfile;

Setup Audit Trail through Initialization Parameter

1. Set "audit_trail = true" in the init.ora file.
2. Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS

Audit Options

BY SESSION/BY ACCESS

BY SESSION causes Oracle 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 Example:

Audit Session By Scott, Lori;

Audit Delete Any Table By Access Whenever Not Successful;
Audit Delete Any Table;
Audit Select Table, Insert Table, Delete Table, Execute Procedure By Access Whenever Not Successful;
Audit Delete On Scott.Emp;
Audit Select, Insert, Delete  On Jward.Dept By Access Whenever Successful;
Audit Select On Default Whenever Not Successful;
Audit Select Table By Appserve On Behalf Of Jackson;
Audit Alter, Index, Rename On Default By Session;
Audit Alter User;
Audit Lock Table By Access Whenever Successful;
Audit Delete On Scott.Emp By Access Whenever Successful;
Audit Delete Table, Update Table By Hr By Access;
Audit All By Hr By Access;
Audit Execute Procedure By Hr By Access;

Disabling Audit

Noaudit Table;

Noaudit All Privileges;

 

Turn Off All Auditing

Noaudit All;

Noaudit All Privileges;

Noaudit All On Default;

 

Purge Auditing

Delete From Sys.Aud$;

Truncate From Sys.Aud$

Delete From Sys.Aud$;

Delete From Sys.Aud$ Where Obj$Name='Emp';

 

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. It's contents can be viewed directly or via the following views:

Dba_Audit_Exists, Dba_Audit_Object, Dba_Audit_Session, Dba_Audit_Statement, Dba_Audit_Trail, Dba_Obj_Audit_Opts, Dba_Priv_Audit_Opts, Dba_Stmt_Audit_Opts 

 

Auditing Via Trigger

It is possible to audit the system by using triggers, there are a number of system-level triggers that can be fired such as database startup, logon, logoff, ddl, server error

 

Example Trigger Auditing:
create or replace trigger audit_insert after insert on vallep.employees for each row
insert into employees_table_audit
values (user, sysdate);
create or replace trigger logon_audit_trig
after logon on database
begin
insert into logon_audit values (user, sys_context('userenv', 'sessionid'), sysdate, null,
sys_context('userenv', 'host'));end;

 

Fine-Grain Auditing

Fine-grain auditing (FGA) allows you to audit users accessing data of a certain criteria. As per standard auditing you can audit select, insert, update and delete operations. You 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 you can attach handlers (like a trigger) to the policies which can execute procedures which could email or page you. There are many options that can be applied to the dbms_fga package, so best to look up the oracle man pages but here are some simple example

Privilege: grant execute on dbms_fga to vallep;

 

Creating Auditing:
dbms_fga.add_policy (object_schema => 'vallep',
object_name => 'employees',policy_name => 'compensation_aud',
audit_columns => 'salary,commission_pct',
enable => false, statement_types => 'select');

 

Creating (handler):
dbms_fga.add_policy (object_schema => 'vallep',
object_name => 'employees',policy_name => 'compensation_aud',
audit_columns => 'salary,commission_pct',
enable => false,statement_types => 'select'
handler_schema => 'vallep'
handler_module => 'log_id');
create procedure vallep.log_id (schema1 varchar2, table1 varchar2, policy1
varchar2) as
beginutil_alert_pager(schema1, table1, policy1);/* send an alert via a pager */ end;

 

Removing auditing:
dbms_fga.drop_policy (object_schema => 'vallep',
object_name => 'employees', policy_name => 'compensation_aud');

 
Enabling auditing:
dbms_fga.enable_policy (object_schema => 'vallep',
object_name => 'employees',
policy_name => 'compensation_aud');

 

Disabling auditing:

dbms_fga.edisable_policy ( object_schema => 'vallep',object_name => 'employees', policy_name => 'compensation_aud');

 

Usful Tables:

DBA_AUDIT_POLICIES, DBA_FGA_AUDIT_TRAIL, DBA_COMMON_AUDIT_TRAIL

 

Maintenance

The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size

 

Security

Only DBAs should have maintenance access to the audit trail. If SELECT access is required by any applications this can be granted to any users, or alternatively a specific user may be created for this. Auditing modifications of the data in the audit trail itself can be achieved as follows:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;