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.
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.