Field Level Auditing in Oracle 11g R2

Question

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.

Answer 1

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.

• Memon: Perfect. Many many thanks 2 you. I have successfully tested it on 11.2.0.4.