Home > General, Security, Technology > Enabling Audit to a table

Enabling Audit to a table

Audit Trail helps you in assessing who did what and when and from where information. In enterprise products Audit becomes mandatory and most of the time a compliance requirement. Audit as such can be performed at application level or database or both.

For audit at application level, you need to rely on a custom framework or manual way of secure insertion of audit events to a separate table.

In this post we will discuss steps or policies that need to be enforced over a database table to keep a track of who accessed the database table with what query and when and from where! Through this policy we basically address 4 w’s – who, what, where and when.

Here are simple steps to enforce audit policy on the table Employee of HR schema that gets loaded as a default database schema in oracle.

Note: The policy we are going to enforce is specific to Oracle database

Policy to enforce Audit on Employee Table of HR schema to Audit ‘Select, Insert, Update Delete’ operations.

     object_schema => 'HR',
     object_name => 'EMPLOYEE',
     policy_name => 'Employee_Policy_1',
     audit_condition => NULL,
     audit_column => NULL,
     handler_schema => NULL,
     handler_module => NULL,
     enable => TRUE,
     statement_types => 'SELECT, INSERT, UPDATE, DELETE',
     audit_trail => DBMS_FGA.DB_EXTENDED,
     audit_column_opts => DBMS_FGA.ANY_COLUMNS


Some times the logged in database user may not have permissions to execute over DBMS_FGA. In this case you need to login as sysdba and grant permissions to HR to execute over DBMS_FGA.

grant execute on dbms_fga to hr;

Some times executing the above policy may result in the following error:

In this case trying to check for the version of the oracle database whether auditing is enabled or not.

select * from v$version;

Fine grained Auditing comes with enterprise database not with stand alone databases. If you are using Enterprise endition d/b then look for what options enabled for the installed database

select * from v$option;

You should have Fine-grained Auditing = TRUE, if so continue to next step other wise you can’t enable audit for the table.

Activate the above policy now

     object_schema => 'HR',
     object_name => 'EMPLOYEE',
     policy_name => 'Employee_Policy_1'

Run sample query to check and see if audit is working fine or not

Connect as any user who has access to query over hr, say sysdba

SQL> select * from HR.Employee;

Run query over audit trail table to see if any records inserted or not.

SQL> SELECT * FROM DBA_FGA_AUDIT_TRAIL where policy_name = 'EMPLOYEES_Policy_1';

From above query result, you should find answers to 4 W’s – What was the query, from Where it’s been fired, When was it fired and Who fired the query.


  1. February 5, 2011 at 5:24 AM

    Cartier, Celine, Chanel, Chloe, Christian Dior, Christian Louboutin, Coach, Dior Homme, Dolce & Gabbana, Fendi, Giuseppe Zanotti, Givenchy, Gucci, Hermes, Jimmy Choo, Lancel, Lanvin, Loewe, Louis Vuitton, Manolo Blahnik, Marc Jacobs, Marni, Miu Miu, Mulberry, Prada, Salvatore Ferragamo, Thomas Wylde, Tory Burch, Valentino, Versace, Vivienne Westwood, Yves Saint Laurent, 3.1 Phillip Lim, Alexander McQueen, Alexander Wang, Anya Hindmarch, Balenciaga, Bally, Bottega Veneta, Burberry


  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: