Auditing process in oracle 11g

Oracle 11g Fine-Grained Audit Trail (FGA)

Existing FGA Policy View:


select * from DBA_AUDIT_POLICIES
select * from USER_AUDIT_POLICIES

Adding policy for FGA:


BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'AUDIT_TEST',
   object_name        => 'COUNTRY',
   policy_name        => 'CHECK_ID',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE',
   audit_condition    => 'REGION_ID = 1',
   audit_trail        =>  DBMS_FGA.DB);
END;
/

Example:
DBMS_FGA.ADD_POLICY (
   object_schema      =>  'scott',
   object_name        =>  'emp',
   policy_name        =>  'mypolicy1',
   audit_condition    =>  'sal   'comm,sal',
   handler_schema     =>   NULL,
   handler_module     =>   NULL,
   enable             =>   TRUE,
   statement_types    =>  'INSERT, UPDATE',
   audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED,
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);

Disable policy for FGA:


BEGIN
DBMS_FGA.DISABLE_POLICY (
object_schema   =>  'AUDIT_TEST',
object_name     =>  'COUNTRY',
policy_name     =>  'CHECK_ID');
END;

Drop policy for FGA:


BEGIN
  DBMS_FGA.drop_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'COUNTRY',
    policy_name     => 'CHECK_ID');
END;

Viewing FGA:


SELECT sql_text FROM dba_fga_audit_trail;

Oracle 11g Audit Trial

Audit Trail Parameters:

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      G:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB, EXTENDED

Set AUDIT_TRIAL parameter

SQL> alter system set audit_trail=none scope=spfile;
SQL> alter system set audit_trail=db scope=spfile;
SQL> alter system set audit_trail=db, extended scope=spfile;
SQL> alter system set audit_trail=xml, extended scope=spfile;

Check Audit Enable/Disable option:

SELECT OWNER, OBJECT_NAME FROM DBA_OBJ_AUDIT_OPTS WHERE UPPER(OWNER) = 'AUDIT_TEST';

Enabling AUDIT Option on Specific Table:

AUDIT INSERT, DELETE, UPDATE ON AUDIT_TEST.EMP BY ACCESS  WHENEVER SUCCESSFUL;
AUDIT INSERT, DELETE, UPDATE ON AUDIT_TEST.EMP BY ACCESS  WHENEVER NOT SUCCESSFUL; 

Disabling AUDIT Option on Specific Table

NOAUDIT INSERT, DELETE, UPDATE ON AUDIT_TEST.EMP;

Script for Enable/Disable AUDIT option on Table:

SELECT 'AUDIT INSERT, DELETE, UPDATE ON ' || OWNER || '.' || OBJECT_NAME  || ' BY ACCESS 
WHENEVER SUCCESSFUL;' 
FROM DBA_OBJ_AUDIT_OPTS 
WHERE OWNER = 'AUDIT_TEST';

SELECT 'NOAUDIT INSERT, DELETE, UPDATE ON ' || OWNER || '.' || OBJECT_NAME 
FROM DBA_OBJ_AUDIT_OPTS 
WHERE OWNER = 'AUDIT_TEST';

View AUDITING:

db views:
---------
SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail
WHERE  owner = 'AUDIT_TEST'
ORDER BY timestamp;

SELECT USERNAME, SQL_TEXT, TIMESTAMP 
FROM DBA_AUDIT_TRAIL 
WHERE SQL_TEXT LIKE 'INSERT %';

XML Views:
-----------
SELECT db_user,
       extended_timestamp,
       object_schema,
       object_name,
       action
FROM   v$xml_audit_trail
WHERE  object_schema = 'AUDIT_TEST'
ORDER BY extended_timestamp;

AUDIT Views:

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.

The three main views are shown below.

DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.