Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Perfect. Many many thanks 2 you. I have successfully tested it on 11.2.0.4. –  Memon Jan 31 at 6:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.