DBMS_FGA - Oracle Fine Grained Auditing

dwaipayan1986's picture

Suppose your boss calls you one day and tells you that there has been some unexpected changes in the employee database.Employee's designation, their salary are being manipulated illegally.Such things have been continuing from a past few days and he asks if you could help getting hold of the culprit.
Don't worry Oracle's DBMS_FGA package will save your day and earn you a raise in your job.

The Oracle DBMS_FGA package provides fine grained auditing on objects.

To have an overview of the summary of dbms_fga subprograms visit :


In this article I am going to add a policy on a table FGA_TEST in the SCOTT schema.
The policy will report on any dml actions on this table affecting its 2 columns 'esal' and 'designation'.
Another user HACKER will execute dml queries on this table and we will try and investigate whether the actions of the HACKER are reported.
The corresponding event handler of this policy will be in a 3rd schema FGA_HANDLER .we will also find out if the audit event was handled properly.

Note: Here it is worthwhile mentioning that the DBMS_FGA package can be used not only to audit records in case of data manipulation(DML) but also in cases where data might have been simply viewed depending upon the policy we define. eg:- in case of selecting particular records from a database table.

SQL> show user;USER is "SCOTT"

First of all let us create a new schema FGA_HANDLER which will contain the event handler .

SQL> create user fga_handler identified by fga_handler;

User created.

SQL> grant resource,connect to fga_handler;

Grant succeeded.

Now, let us create a new table ,FGA_TEST in SCOTT schema, on which we will enforce the audit conditions(policy) with the help of the DBMS_FGA package.

SQL> create table fga_test2 (empno number,3 empname varchar2(30),4 esal number,5 designation varchar2(20)6 );

Table created.

Let us insert some dummy rows in it now.

SQL> insert into FGA_TEST2 SELECT 1,'dwaipayan',20000,'programmer' from dual3 union all4 select 2,'dhruva',30000,'analyst' from dual5 union all6 select 3,'shiba',40000,'manager' from dual7 ;

3 rows created.

SQL> select * from fga_test;

EMPNO EMPNAME ESAL DESIGNATION ---------- ------------------------------ ---------- -------------------- 1 dwaipayan 20000 programmer 2 dhruva 30000 analyst 3 shiba 40000 manager

Given below are the parameters of the ADD_POLICY Procedure:
Now, let us add a policy on our FGA_TEST table such that whenever any user tries to insert, update or delete the ‘esal’ or the ‘designation’ columns of any row of FGA_TEST table ,the action will be recorded.

BEGIN DBMS_FGA.ADD_POLICY ( object_schema => 'SCOTT', object_name => 'FGA_TEST', policy_name => 'FGA_TEST_POLICY', audit_condition => NULL, audit_column => 'ESAL,DESIGNATION', handler_schema => 'FGA_HANDLER', handler_module => 'sp_audit', enable => true,statement_types => ‘INSERT,UPDATE,DELETE’ );end;

Note: the default 'statement_types' is 'SELECT'

Now, sp_audit is the audit procedure, which will act as the alerting mechanism for the administrator.

The required interface for such a procedure is as follows:
PROCEDURE ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS .... Now, let us connect to the FGA_HANDLER schema and design the
the sp_audit procedure.

First let us create the table where the sp_audit procedure will dump the data into.

SQL> conn fga_handler/fga_handler;Connected.

SQL> create table audit_event 2 (audit_event_no number);

Table created.

We create the sp_audit procedure as follows:

SQL> create or replace procedure sp_audit(object_schema in varchar2,object_name in varchar2,policy_name in varchar2)ascount number;begin

select nvl(max(audit_event_no),0) into count from audit_event;

insert into audit_event values (count+1); commit;


The procedure simply adds a record to the audit_event table each time it is executed and the column audit_event_no acts as counter which displays the number of times the proc has been executed.

Now, finally we create another schema ‘HACKER’ which tries to manipulate the values of the ‘esal’ or ‘designation’ columns of the FGA_TEST table.

SQL> create user hacker identified by hacker;User created.

SQL> grant resource,connect to hacker; Grant succeeded.

SQL> grant all on fga_test to hacker; Grant succeeded. Now we log onto HACKER and try to change the ‘designation’ of ‘dwaipayan’ from ‘Programmer’ to ‘manager’

SQL> conn hacker/hacker; Connected.

SQL> update scott.fga_test set designation='manager' where empname='dwaipayan';

1 row updated.

Now, we connect with SCOTT to see the dba_fga_audit_trail view to find if the event was recorded.


HACKER HOME-6C286D743C\Dwaipayan FGA_TEST_POLICY update scott.fga_test set designation = 'manager' where empname='dwaipayan' 5-Jun-11

Now we connect to the FGA_HANDLER schema to see if the event handler(sp_audit) was called:

SQL> conn fga_HANDLER/fga_handler;Connected.SQL> select * from audit_event;


We execute the following from HACKER schema:

SQL>update SCOTT.FGA_TEST set designation='HR' where name='shiba';
SQL> conn fga_HANDLER/fga_handler;
SQL> select * from audit_event;


Note: I have just created the sp_audit procedure to add rows to the audit_event table in this testing environment but in a ideal production scenario, we are likely to send emails or Page instead.


LINKEDIN PROFILE : http://www.linkedin.com/pub/dwaipayan-de/21/20/29b

BLOG : http://databasefundas.blogspot.com

CONTACT : 9903063253


Nice example of FGA, but I think one should always emphasize how dangerous FGA is.
The handler is basically a trigger. Normal DML triggers are bad enough for SQL injections: a user does something, and without his knowledge something else (totally unrelated) happens. This is why the CREATE ANY TRIGGER privilege is so dangerous, and should always be audited. FGA is even worse, because you can apply a policy to SELECT statements. That means you have a trigger on SELECT. If you think about the privileges with which the trigger and any procedure it calls might execute, you can see that this is a very easy way to construct a SQL injection that will run with escalated privileges.
So yes, FGA can be useful - but before you grant anyone permission to use it, think about how you are going to audit its use.

dwaipayan1986's picture

John, I agree...FGA should only be used by privileged users as it is used for administering security issues.
But as you can see from the example above,the handler is a procedure and not a trigger.When a security violation takes place that needs to be audited(according to the policy defined), the handler is executed (which is essentially the proc
sp_audit in my example).Now, i think what you are referring to as a trigger, is the Oracle internal trigger that executes the proc.That cannot be of any risk as you already know what its going to do..it is going to execute the handler proc.
Now if you have granted the FGA privilege to someone who shouldn't be having it, then what you can do is, go to DBA_AUDIT_POLICIES view and get all information regarding the policies defined and their corresponding handlers.
There you can get details of the schema where handler is present and the name of the handler procs. You can go though the procs and make sure it doesn't do anything fishy.As far as sql injection is concerned, there are plenty of ways to prevent that. You can always ensure the usage of the DBMS_ASSERT package to ensure the inputs are valid object names,etc.
But as I said ..only privileged users should granted FGA , the same is true for any useful powerful feature of Oracle.

I have done everything according to your procedure but I found below error when update designation value in FGA_TEST table. I need your help to fix this issue.

SQL> conn hacker/HACKER@pdborcl;
SQL> update SCOTT.FGA_TEST set designation = 'MAN' WHERE empno = 1;
update SCOTT.FGA_TEST set designation = 'MAN' WHERE empno = 1
ERROR at line 1:
ORA-28144: Failed to execute fine-grained audit handler
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "FGA_HANDLER.SP_AUDIT", line 5
ORA-06512: at line 1

Your problem is because you haven't granted your FGA_HANDLER user any quota on the USERS tablespace. The RESOURCE role no longer includes an implicit grant of unlimited tablespace.