Working with Standard Database Auditing

You have to accept that users have privileges that could be dangerous. Sadly, all you can do is monitor their use of those privileges and track what they actually doing with them.

Auditing SYSDBA Activity

There is an instance parameter called AUDIT_SYS_OPERATIONS .  if set to true (default is false) then every statement issued by user connected as sysdba or sysoper is written out to operating system’s audit trail.

You can find the location of audit trail using:

Select value from v$parameter where name = ‘audit_file_dest’ ;

There are three types of auditing techniques

Database Auditing: it can track use of certain privileges, execution of certain commands, access to certain tables and log-on attempts.

Value based Auditing: it uses database triggers, whenever a row is inserted, updated or deleted a block of PL/SQL code will run that can record complete details of the event.

Fine Grained Auditing (FGA): it allows you to track certain on certain events it is more precise then above two.

Database Auditing

Before setting up database auditing, an instance parameter must be set:  AUDIT_TRAIL (this is a static parameter and instance must be restart for effect)  

This has 4 possible values DB, DB_Extended, XML, XML_Extended.

 If you choose DB as value for Audit_trail then audit records are written to SYS.AUD$  which can be accessed using DBA_AUDIT_TRAIL.

Having set Audit_trail parameter, you can use database auditing.

Database auditing is configured with audit command; use of privileges can be audited with:

Audit create any table;

Audit select any table by session;

After that if any of these privileges are used, a record will be kept for the same.

Note: By default audit will generate one record for every session that violates an audit condition, irrespective of number of time it violates the condition. This is equivalent to appending by session.

By Access will generate one record for every violation

Auditing Towards Object

Audit insert on scott.emp whenever successful; --It will audit on successful insertion on table

audit all on scott.emp; -- it will audit every session that executes DDL commands against tables

audit session whenever not successful; --It will audit logons when not successful

Value based auditing

This is done by database triggers and it will run automatically whenever an insert, update, or delete is executed against a table

Create or replace trigger trigger_name

After update of column_name

On table_name

Referencing new as new old as old

For each row

Begin

If :old.column_name != :new.column_name then

Insert into audit_table values (sys_context('userenv','os_user'), :new.cust_id, :new.cust_name );

End if;

End;

Fine Grained Auditing

It can be configured to generate audit records only when certain rows are accessed. FGA is configured with package called DBMS_FGA. To create FGA audit policy, use the ADD_POLICY procedure.