How many types of auditing in Oracle?

Sapna Malik

13y

6.8k

Database Auditing

There are two distinct types of auditing

standard - auditing based on statement, privilege and object-level
fine-grained - auditing on a finer granule which is based on content i.e. value > 10,000

Standard

There are 3 levels of standard auditing

statement - audit all actions on any type of object
privilege - audit actions that stem from system privileges
object-level - specific audit actions like select, update, insert or delete

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

none - disables auditing
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

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

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;

## 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$;

Dinkar Chavhan

13y