Select Trigger in Oracle Database – introducing Fine Grained Auditing

Does Oracle provide a SELECT trigger in the same way that we have INSERT, UPDATE and DELETE triggers? No, it does not. However. It does offer something that comes amazingly close to a create trigger emp_on_select_trigger before select on EMP statement. It does that through the Fine Grained Auditing framework, implemented in the DBMS_FGA package. This package allows us to define a PL/SQL procedure, stand-alone or inside a package, that is called whenever a SELECT statement is executed against a certain table.
Fine Grained Auditing is primarily intended for auditing special events in the database. In Oracle 9i the only event that was supported by dbms_fga was the query event – execution of a SELECT statement. In 10g, dbms_fga has been extended to also capture DML events – INSERT, UPDATE and DELETE statements. For the purpose of our SELECT trigger, we will focus on the SELECT event. Hence, this is available in both 9i and 10g. Note: this trigger is a STATEMENT level trigger. The select-trigger will not be called for every record that is either evaluated or returned by the query. Fortunately!

We create our SELECT trigger in two steps. First we create the Handler – a PL/SQL procedure – that will process the Select Events that we want to be triggered on, then we can define a Fine Grained Auditing Policy that is associated with our Handler.

Creating the Handler

The Handler is just a stand alone or packaged PL/SQL Procedure. The only requirement is that its specification is defined as follows:

PROCEDURE NAME_OF_SELECT_EVENT_HANDLER
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);

A Handler can be used for Select Events on multiple Tables or Views. When called, it will be informed of the specific object – schema and name – and the specific policy that was defined for the event. Inside the handler, you can do anything you like, including defining an Autonomous Transaction for saving and committing an audit-record.

Here is an example of what a Select Event Handler could look like:

create or replace
package AUDIT_HANDLER
is
PROCEDURE HANDLE_EMP_SAL_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
end;
/

create or replace
package body AUDIT_HANDLER
is
PROCEDURE HANDLE_EMP_SAL_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
) is
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into emp_audit
  ( whodidit, whenwasit, sql_executed)
  values
  ( user, systimestamp, sys_context('userenv','current_sql'))
  ;
  commit;
end HANDLE_EMP_SAL_ACCESS;
end;
/

The code sys_context('userenv','current_sql') will return the (first 4Kb) of the current SQL statement. If the SQL statement is longer than 4Kb, we can use sys_context(‘userenv’,’current_sql1′) to sys_context(‘userenv’,’current_sql7′) for retrieving the remainder. Note that sys_context(‘userenv’,’current_sql’) can only be used inside the Handler called from a Fine Grained Auditing “trigger”.

The handler inserts custom audit-records into a table EMP_AUDIT, set up like this:

create table emp_audit
( whodidit varchar2(40)
, whenwasit timestamp
, sql_executed varchar2(4000)
)
/

Note: while your Handler can slow down the Select operation, it cannot abort it nor can it change the query being executed. Even when your Handler raises an exception, the query will proceed unhamperedly and return its results.

Defining the Select Trigger or Fine Grained Auditing Policy

The Select Trigger as said before does not exist as such. What we can set up is a Fine Grained Auditing Policy that is associated with a custom handler that is invoked for relevant Select Events.

Here is the PL/SQL statement to set up an Audit Policy:

begin
  dbms_fga.add_policy
  ( object_schema=>'SCOTT'
  , object_name=>'EMP'
  , policy_name=>'SAL_ACCESS_HANDLED'
  , audit_column => 'SAL'
  , audit_condition => 'SAL>= 1500'
  , handler_schema => 'SCOTT'
  , handler_module => 'AUDIT_HANDLER.HANDLE_EMP_SAL_ACCESS'
  );
end;
/

Here we specify an Audit Policy on table EMP in our SCOTT schema. The policy is labeled SAL_ACCESS_HANDLED.

We indicate through the parameter audit_column that we are only interested in SELECT events that involve column SAL. The default, NULL, for this parameter causes audit if any column is accessed or affected. You can specify multiple columns in this parameter. Through the parameter audit_column_opts – not shown here – can you indicate whether auditing (fire select event) must take place when the query references any column specified in the audit_column parameter or only when all such columns are referenced.

The parameter audit_condition can be used to focus on special select events. Only when the query returns records satisfying the audit_condition will the select event be fired. NULL is allowed and acts as TRUE meaning the select event is fired when any row is returned at all . Note: it [parameter audit_condition] cannot contain the following elements: Subqueries or sequences, Any direct use of SYSDATE, UID, USER or USERENV functions (However, a user-defined function and other SQL functions can use these functions to return the desired information), the pseudo columns LEVEL, PRIOR, or ROWNUM.

If no audit_condition is specified, then the SELECT event is triggered even when no records are returned at all. So even a statement like select * from emp where 1=2 will cause a select event to be fired. When there is an audit_condition, then the event is only fired when after applying the audit_condition, the result set would still contain any records. That means that with the above audit_condition of “sal > 6500”, no select event is fired for queries like select * from emp where 1=2 that do not in fact return any rows.

So when we query only for records that do not satisfy the audit condition, the audit handler is not fired:

SQL> select ename, sal from emp where sal<1500
  2  /
ENAME             SAL
---------- ----------
SMITH             800
WARD             1250
MARTIN           1250
ADAMS            1100
JAMES             950
MILLER           1300
6 rows selected.
SQL> select * from emp_audit
  2  /
no rows selected

However, if we do select records that satisfy the audit condition, then our SELECT-trigger is fired:

SQL> select ename, sal from emp where rownum<3
  2  /
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
SQL> select * from emp_audit
  2  /
WHODIDIT                                 WHENWASIT
---------------------------------------- -----------------------------
SQL_EXECUTED
----------------------------------------------------------------------
SCOTT                                    24-SEP-05 09.06.13.449000 PM
select ename, sal from emp where rownum<3

Even if we do not directly see any records within the Audit condition, but they were used all the same, for example to calculate an aggregate, the audit handler is fired:

SQL> select min(sal) from emp
  2  /
  MIN(SAL)
----------
       800
SQL> select * from emp_audit
  2  /
WHODIDIT                                 WHENWASIT
---------------------------------------- -----------------------------
SQL_EXECUTED
----------------------------------------------------------------------
SCOTT                                    24-SEP-05 09.09.33.256000 PM
select min(sal) from emp

Note: if the query contains a subquery or in-line view that returns records satisfying the audit_condition, then the Select Event is fired, even if the overall query does not return records satisfying the search condition. For example, this statement will fire the Select Event:

select *
from   emp
where  sal < ( select max(sal)
               from emp
             )
and    sal< 1500
/

even though no records satisfying the audit_condition are returned:

SQL> select * from emp_audit
  2  /
WHODIDIT                                 WHENWASIT
---------------------------------------- ------------------------------
SQL_EXECUTED
-----------------------------------------------------------------------
SCOTT                                    24-SEP-05 09.11.14.782000 PM
 select *
 from   emp
 where  sal < ( select max(sal)
                from emp
              )
 and    sal< 1500

If you do not retrieve the audit-column - SAL in our example - but you still refer to it in the query, the Where clause or Order By condition, the query will fire the select event trigger:

SQL>  select ename from emp where sal > 4000
  2  /
ENAME
----------
KING
SQL> select * from emp_audit
  2  /
WHODIDIT                                 WHENWASIT
---------------------------------------- -----------------------------
SQL_EXECUTED
----------------------------------------------------------------------
SCOTT                                    24-SEP-05 09.13.57.165000 PM
 select ename from emp where sal > 4000

However, selecting all Ename values from all records, including those with salaries over 1500, will not fire the audit handler - if there is no reference to the AUDIT-column in the query:

SQL> select ename from emp
  2  /
ENAME
----------
SMITH
ALLEN
WARD
...
MILLER
14 rows selected.
SQL> select * from emp_audit
  2  /
no rows selected

Oh, by the way, getting rid of a once established auditing policy is quite simple:

begin
  dbms_fga.drop_policy
  ( object_schema=>'SCOTT'
  , object_name=>'EMP'
  , policy_name=>'SAL_ACCESS_HANDLED'
  );
end;
/

Resources

Do You Know Who Is Accessing Your Data? by Eric Yen - interesting, introductory article on DBAzine.com
Fine-Grained Auditing for Real-World Problems, Part 3 By Arup Nanda, series of three articles in Oracle Magazine

Oracle 10gR2 Documentation Library PL/SQL Packages and Types Reference - DBMS_FGA
Oracle® Database Security Guide 10g Release 2 (10.2) - 12 Configuring and Administering Auditing