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; /
DBA_AUDIT_POLICIES
identify FGA audit policies
DBA_FGA_AUDIT_TRAIL
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.
Five types of auditing in Oracle: