How many types of auditing in Oracle?

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.

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

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));

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

purge audit table

delete from sys.aud$;
truncate from sys.aud$;