Capture bind variables in SQL with Oracle FGA

In Oracle 10g with fine-grained auditing (FGA) it is possible to track the bind variables of your application's SQL statements. I know, Oracle 11g is out but how amazing, I am still discovering new things in Oracle 10g!

Oracle FGA is good for bind variables, if that is what you want after all. It is lightweight and more easy to use than its alternatives, for example the full SQL TRACE with the option '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'.

Another benefit of FGA is that you can choose which object you want to audit and what kind of statements, SELECT, INSERT...etc you want to audit. Whereas with the SQL TRACE you just have to accept the trace dump and the performance implications.

More good news is that in Oracle 10g you don't have to bounce the database anymore to set the initialization parameter AUDIT_TRAIL and enable auditing for your system. FGA does not require a database shutdown/restart.

Object or user auditing can be done ad-hoc and on the spot, thanks to FGA. You can audit INSERT, DELETE or just SELECT statements. Moreover, you can be object specific or user specific. For example if you wanted to audit the DML happening on a table all you have to do is to create an Audit Policy for that table. You can use the DBMS_FGA.ADD_POLICY procedure to create the policy. The modifications on the table, together with the bind variables, are captured and logged in a SYS owned log table called FGA_LOG$. How amazing!

Here is an example:

We set up an object auditing policy, which will monitor all INSERT, UPDATE and DELETE operations on the table EMP on Scott's schema.


SQL> begin
dbms_fga.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_DETECTIVES',
audit_column => 'ENAME',
statement_types => 'INSERT, UPDATE, DELETE',
audit_trail => DBMS_FGA.DB_EXTENDED
);
end;

PL/SQL procedure successfully completed.


Then connect as SCOTT the owner of the object you are auditing and do some changes on the object.


SQL> conn scott/tiger

Connected.

SQL> variable myname varchar2(50);
SQL> exec :myname :='ROBIN';


PL/SQL procedure successfully completed.

SQL> insert into emp values (9999, :myname, null, null, null, null, null,null);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> delete emp where ename = :myname;

1 row deleted.

SQL> commit;


Now look at the FGA_LOG$ to see the audit entries.

SQL> conn sys/***** as sysdba

Connected.

SQL> column ntimestamp# format a30
SQL> column lsqltext format a15
SQL> column dbuid format a15
SQL> column obj$name format a10
SQL> column lsqlbind format a15
SQL> column lsqltext format a20

SQL> select ntimestamp#, dbuid, obj$name, lsqlbind, lsqltext  from sys.fga_log$;


NTIMESTAMP#                    DBUID           OBJ$NAME   LSQLBIND        LSQLTEXT
------------------------------ --------------- ---------- --------------- --------------------
14-AUG-08 19.39.48.427696 PM   SCOTT           EMP         #1(5):ROBIN    insert into emp valu
                                                                          es (9999, :myname, n
                                                                          ull, null, null, nul
                                                                          l, null,null)

14-AUG-08 19.39.56.577619 PM   SCOTT           EMP         #1(5):ROBIN    delete emp where ena
                                                                          me = :myname

WARNING : Don't forget to drop the policy once you are done, cause the auditing will go for infinite time on the object. The FGA_LOG$ table will fill up and you will waste space. You can drop the policy once you have done with it like this:


SQL> conn sys/***** as sysdba
SQL>
begin
dbms_fga.drop_policy ( 'SCOTT', 'EMP','EMP_DETECTIVES');
end;