Field Level Auditing in Oracle 11g R2

auditoracleoracle-11g-r2

I want to enable auditing on some column in a table. I have tested the SYS, Schema and Table level auditing. Guide me or refer me some document to enable auditing on column level.

Best Answer

Check that auditing is enabled:

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/phil12c1/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576

If audit_trail is not set (NONE), turn auditing on (as SYSDBA):

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

... then restart the database.

I'll create a test table to use:

create table fgatest
(
  id number primary key,
  value number
);

insert into fgatest values ( 1, 2 );

insert into fgatest values ( 2, 200 );

commit;

Now, I'll create an example of an FGA auditing policy that uses the above table. This will audit any SQL that contains a row with a value greater that 100 in its resultset. The parameters speak for themselves.

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'C##PHIL',
    object_name     => 'FGATEST',
    policy_name     => 'FGA_TEST_CHECK_VALUE_GT_100',
    audit_condition => 'VALUE > 100',
    audit_column    => 'VALUE');
END;
/

Test:

[oracle@ora12c1 ~]$ sqlplus c##phil/phil

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 30 09:55:57 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 30 2014 09:37:34 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> select * from fgatest where value>150;

        ID      VALUE
---------- ----------
         2        200
SQL>

SQL> conn / as sysdba
Connected.
SQL> select session_id,timestamp,db_user,sql_text
     from dba_fga_audit_trail;


SESSION_ID TIMESTAMP DB_USER    SQL_TEXT
---------- --------- ---------- -------------------------
    280018 30-JAN-14 C##PHIL    select * from fgatest where value>150

SQL>

Note I've done my test on 12c, but this test will work perfectly on 11.2 too.

Related Question