Select Trigger in Oracle Database – introducing Fine Grained Auditing

13

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&lt; 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&lt; 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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

13 Comments

  1. David Wendelken on

    Hi Lucas!  It’s been awhile since I last saw you at ODTUG.

    Re Kalid’s question, he could embed his application’s queries in packaged procedures and have the packaged procedure record the record level info before returning the data to the calling app.

    Unless, of course, they’ve given users the ability to query the data directly via sql*plus or some other tool…

    Good article, by the way. 

  2. Dear Kalid,

     

    The only way to learn about the actual primary key values accessed in the query would be to execute the query itself (with the same context variables set and the same VPD policies and View where conditions applied) from within the audit handler procedure. And that is not necessarily very easy.

    One way to record every primary key for every record queried is by having all data accessed by views that consist of a query from a table (or view) joined to a table function based on PL/SQL function that takes the primary key as its input and a collection with a single row as its output. The PL/SQL function can record the primary key somewhere. Sounds somewhat farfetched… There may be an easier way – but I cannot think of it rightaway.

    Lucas

     

  3. Hi,
    Excellent Article. Question, is it possible to capture pk of the record as in this example empid and insert that into emp_audit table? HIPPA regulation requires to store info about the record and not just the sql statement. Wondering if in 11gr2 is it possible? I know you made it clear that it is statement level trigger and not on each record returned by query but the regulation requires each record audit. That’s our requirement. Is this possible?
    Thanks

  4. I’m researcher ,
    Can useful from this to  fight SQL injection?I think its food idea to create something  triger special to audit sql statement this is useful to detectt sql Injection .
     

  5. Hi
    Is it possible to track if someone executes a packaged procedure or function too?
    Thanks
    Christian

  6. Dear Lucas, i ‘m new member of AMIS blog.
    Is is usefull to use this package / trigger when insert a record in EMP for a non-existing DEP, also insert a record in DEP for this EMP. (“cascade insert”) or should another solution be better?

  7. Pingback: Oracle: Loggen von Selects - PG-Forum.de

  8. Pingback: Oracle: Loggen von Selects - PG-Forum.de

  9. Pingback: links for 2008-11-01 « My place

  10. Pingback: oracle find current statement from trigger

  11. Pingback: AMIS Technology blog » Blog Archive » Calling Stored Procedures using plain SQL - For when SQL is allowed but calls to Stored Procedures are not

  12. I suppose the VPD Policy Function can also be used as a select trigger. It is invoked prior to any query – at least if the policy is defined as dynamic. I do not think we can find out about the SQL statement – as it is still being composed, partly based on the contributon of the VPD policy function. However, we can log the fact that the query takes place – and who it is performed by. We are also able to influence the query. At least by applying the predicate of course, but presumably also by changing the session settings (alter session set nls_date_language for example). I have not actually tested this – it is an assumption.