Database Auditing

There are two distinct types of auditing

Standard

There are 3 levels of standard auditing

For all 3 levels 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 auditing on if the access was successful (whenever successful) or not (whenever not successful).

When auditing you need somewhere to log the audit information, this is controlled by the audit_trail parameter which can take the following values

Remember to restart the instance when changing the audit_trail parameter.

turn off auditing

alter system set audit_trail=none scope=spfile;

auditing written to db alter system set audit_trail=db scope=spfile;
auditing written to o/s

alter system set audit_file_dest='c:\oracle\auditing';
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/

audit all sys operations (default false)

alter system set audit_sys_operations=true scope=spfile;

Note: this will audit all sys operations regardless if audit_trail is set.

To start auditing you can use the below (there are many more options than stated below)

session audit session by vallep;
table audit table;
table and specific user audit table by vallep;
table, specific user and access audit table by vallep by access;
privilege auditing audit create any table;
audit create any table by vallep;
object auditing audit select on vallep.employees by access whenever successful;
audit select on vallep.employees by access whenever not successful;
disabling audit noaudit table;
noaudit all privileges;
noaudit create any table by vallep;
turn off all auditing noaudit all;         (turn off all statement auditing)
noaudit all privileges;     (turn off all privilege auditing)
noaudit all on default;    (turn off all object auditing)
purge audit table delete from sys.aud$;
truncate from sys.aud$;
Useful Views
DBA_STMT_AUDIT_OPTS display any statement auditing
DBA_PRIV_AUDIT_OPTS display any privilege auditing
DBA_OBJ_AUDIT_OPTS display any object auditing
DBA_AUDIT_TRAIL display the captured audit information
DBA_COMMON_AUDIT_TRAIL displays the captured audit information for both standard and FGA

Auditing via Triggers

It is possible to audit the system by using triggers, there are a number of system-level triggers that can be fired

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;

## You would need to create table as below, you would also create a logoff trigger that populated the below table

create table logon_audit (
user_id varchar2(30),
sess_id number(10),
logon_time date,
logoff_time date,
host varchar2(20));

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 examples

Privilege grant execute on dbms_fga to vallep;
Creating

begin
dbms_fga.add_policy (
object_schema=>'vallep',
object_name=>'employees',
policy_name=>'compensation_aud',
audit_column=>'salary,commission_pct',
enable=>false,
statement_types=>'select');
end;
/

Creating (handler)

dbms_fga.add_policy (
object_schema=>'vallep',
object_name=>'employees',
policy_name=>'compensation_aud',
audit_column=>'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
begin
   util_alert_pager(schema1, table1, policy1);  /* send an alert via a pager */
end;

Removing begin
dbms_fga.drop_policy (
object_schema=>'vallep',
object_name=>'employees',
policy_name=>'compensation_aud');
end;
/
Enabling begin
dbms_fga.enable_policy (
object_schema=>'vallep',
object_name=>'employees',
policy_name=>'compensation_aud');
end;
/
Disabling

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

Useful Views
DBA_AUDIT_POLICIES identify FGA audit policies
DBA_FGA_AUDIT_TRAIL display the captured audit information
DBA_COMMON_AUDIT_TRAIL displays the captured audit information for both standard and FGA

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 delete from sys.aud$;
truncate from sys.aud$;