0 
0 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< 1500If 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
Related posts:
Oracle Quiz on SQL and PL/SQL – See Water Burning – paper for ODTUG 2005 Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases Oracle 10gR2 – Conditional PL/SQL Compilation 
Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table
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.Â
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
Â
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
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 .
Â
Hi
Is it possible to track if someone executes a packaged procedure or function too?
Thanks
Christian
For that particular situation, you can just use an Insert trigger – no need to make things complicated with this ‘select trigger’ trick.
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?
[…] select trigger gleich der erste treffer – von jemandem der sich mit dem thema beschaeftigt hat AMIS Technology blog » Blog Archive » Select Trigger in Oracle Database – introducing Fine Grained A… weiter unten findest du dann noch quellen, deren titel VERMUTEN lassen, dass sie sich mit ganz […]
[…] google – oracle select trigger gleich der erste treffer – von jemandem der sich mit dem thema beschaeftigt hat AMIS Technology blog » Blog Archive » Select Trigger in Oracle Database – introducing Fine Grained A… […]
[…] AMIS Technology blog » Blog Archive » Select Trigger in Oracle Database – introducing Fine Grained… 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. (tags: oracle audit programming pl/sql) […]
[…] in the same way that we have INSERT, … will return the first 4Kb of the current SQL statement.http://technology.amis.nl/blog/?p=810tafe nsw code of conduct pdfhow many uk firefighters replacing jeep steering stabilizer warren […]
[…] While investigating the relatively new EJB 3.0 Persistence API, I came under the impression that the specification does not cater for Stored Procedures. In general, there seem to be more instances when plain SQL – ordinary SELECT statements against tables and views – are supported but calls to Stored Procedures, written for example in PL/SQL for Oracle Databases, are not. All Object Relational Mapping frameworks for Java/RDBMS bridging for example support plain SQL, but are limited for use of PL/SQL or other stored languages. At any rate, being able to invoke a stored procedure through ordinary select statements is usually much easier to implement.In this article, I will look at creating wrappers for PL/SQL procedures that allow them to be invoked from external environments, for example Java Applications using EJB 3.0 Persistence or other ORM frameworks that do not readily cater for CallableStatements to stored procedures. It turns out to be relatively simple to implement – do not expect anything spectacular. Yet the apporach discussed can be useful in certain situations. Note: I am still trying to find out whether the EJB 3.0 Persistence API does indeed exclude CallableStatements and Stored Procedures; for the time being – after having carefully read the specs – that has to be my assumption. The EntityManager interface does also not include a getConnection() method that would allow us to create a JDBC CallableStatement directly on the JDBC connection that the EntityManager itself is also using.We will work with a very, somewhat unnecessary, PL/SQL procedure abs_date_diff. It takes two dates as input parameters and returns the difference in years, months and days between the two dates in three numeric out parameters:create or replace procedure abs_date_diff( p_date1 in date, p_date2 in date, p_years out number, p_months out number, p_days out number) is l_months number; l_date date;begin l_months:= months_between( p_date1, p_date2); p_years := trunc(abs(l_months)/12); p_days:= (abs(l_months) – trunc(abs(l_months)))*31; p_months := trunc(abs(l_months) – (12*p_years));end abs_date_diff;/ If we want to call this procedure, clearly we cannot do so using a simple SELECT statement: we can only call PL/SQL functions in select statements, and we can not deal with out-parameters if it was a function we were dealing with. Wrapper FunctionI create a wrapper function, called FUN_ABS_DATE_DIFF. This function takes the same input parameters as the procedure it wraps. It returns a numeric value – the result, typically 0. It also stores the values of the out-parameters in a package called proc_results:create or replacefunction fun_abs_date_diff( p_date1 in date, p_date2 in date) return numberis l_years number; l_months number; l_days number;begin abs_date_diff ( p_date1 , p_date2 , l_years , l_months , l_days ); proc_results.set_years(l_years); proc_results.set_months(l_months); proc_results.set_days(l_days); return 0;end fun_abs_date_diff;/ This package looks somewhat like a JavaBean with three properties and corresponding getters and setters. The package is called from the wrapper function to set the three properties. Later on, it can be called to retrieve the results of the original procedure call. This query would look like:select proc_results.get_years, proc_results.get_months, proc_results.get_daysfrom dualwhere fun_abs_date_diff( to_date(’30-10-2000′,’DD-MM-YYYY’), sysdate ) = 0/ GET_YEARS GET_MONTHS GET_DAYS———- ———- ———- 5 2 24 Now we have created a situation where executing a simple SQL SELECT statement returns the result of a PL/SQL Procedure call! This same select statement can of course be used inside an Oracle ADF BC ViewObject definition – using one or two bind-parameters for the values that we want to pass to the PL/SQL procedure. We can also use this Select Statement as NativeQuery in EJB 3.0 Persistence: String fromDate = "30-10-2000"; Query q = myEntityManager.createNativeQuery("select proc_results.get_yearsn" + ", proc_results.get_monthsn" + ", proc_results.get_daysn" + "from dualn" + "where fun_abs_date_diff( to_date(’"+ fromDate +"’,’DD-MM-YYYY’), sysdate ) = 0n" ); Vector theResult = (Vector)q.getSingleResult(); System.out.println("Tobias is now exactly "+theResult.get(0)+" years, "+theResult.get(1)+ " months and "+ theResult.get(2)+" days old.");…Tobias is now exactly 5 years, 2 months and 24 days old. Note that Named Parameters are not supported for Native Queries in the specifications. With regard to positional parameters, the specification states: "The use of named parameters is not defined for native queries. Only positional parameter binding for SQL queries may be used by portable applications." In my current build of the GlassFish Reference Implementation, I could not get the positional parameters to work either, hence the hard-coded values.Of course if we were to define an Entity with properties Years, Months, Days, then we could even call the PL/SQL procedure through a normal EJB QL query. That seems like abuse though.Alternative approach using Table FunctionsThere is another way of wrapping the PL/SQL procedure. It takes about the same effort, so I cannot really recommend one approach over the other. Using the Table Functions is perhaps a bit more elegant, but I am not yet sure of that.With Table Functions, we are moving the function call from the Where Clause – which is not particularly the appropriate spot – to the From Clause:select abs_date_diff.*from table ( tblfun_abs_date_diff ( to_date(’30-10-2000′,’DD-MM-YYYY’) , sysdate ) ) abs_date_diff/ YEARS MONTHS DAYS———- ———- ———- 5 2 24 In order to get this to work, we need to set up the Table Function itself and two Type definitions in the Oracle Database. We do not need the package proc_results, which is good thing. The table function tblfun_abs_date_diff looks as follows:create or replacefunction tblfun_abs_date_diff( p_date1 in date, p_date2 in date) return abs_date_diff_tblis l_years number; l_months number; l_days number; l_abs_date_diff_tbl abs_date_diff_tbl := abs_date_diff_tbl();begin abs_date_diff ( p_date1 , p_date2 , l_years , l_months , l_days ); l_abs_date_diff_tbl.extend(); l_abs_date_diff_tbl(l_abs_date_diff_tbl.last):= abs_date_diff_type(l_years, l_months, l_days); return l_abs_date_diff_tbl; end tblfun_abs_date_diff;/ The two type definitions are pretty straightforward:< >create or replace type abs_date_diff_typeis object( years number(4), months number(2), days number(2))/create or replacetype abs_date_diff_tblistable of abs_date_diff_type/ >We can invoke the Table Function in the same way from Java as we called the Wrapper FUN_ABS_DATE_DIFF function:String fromDate = "30-10-2000"; Query q = libraryClient.getEntityManager().createNativeQuery("select abs_date_diff.*n" + "from table( tblfun_abs_date_diff( to_date(’"+fromDate+"’,’DD-MM-YYYY’), sysdate )) abs_date_diffn" ); Vector theResult = (Vector)q.getSingleResult(); System.out.println("Tobias is now exactly "+theResult.get(0)+" years, "+theResult.get(1)+ " months and "+ theResult.get(2)+" days old."); Yet another approach… Instead Of triggers with INSERT or UPDATE statementsAt the risk of getting too exotic on this issue… The INSTEAD-OF triggers that Oracle allows us to define for Views are another vehicle for translating SQL statement – DML operations like INSERT and UPDATE in the case of IOTs – into PL/SQL calls. The values we set for the columns in the insert or update statement – something like update iot_abs_date_diff set date1=to_date(’30-10-2000′,’DD-MM-YYYY’), date2=sysdate – can be read inside the IOT and used to make any PL/SQL call. However, we would then find a way to r
etu
rn the values to the caller. The route via the PROC_RESULTS package seems to be the only available one: we cannot set :new.years to a certain value inside the IOT and read that value using the returning YEARS into :years. The SQL engine does not allow that construction. So that UPDATE statement that triggers the IOT into calling a PL/SQL stored procedure needs to be followed by a separate SELECT statement that returns the values set in the package.In code this would look something like the following (note: we can make this code far more generic):First we create a dummy table as Oracle does not allow the update or insert of virtual columns (create view x as select ‘’ in1 from dual followed by update x set in1 = value results in ORA-01733: virtual column not allowed here):create table dul( procedure_name varchar2(100), in1 varchar2(100), in2 varchar2(100), in3 varchar2(100))/insert into dulvalues( ‘X’,’X’,’X’,’X’)/Next we create a view on top of this table. Note that we make use of the proc_results package that we created earlier in this article. Again, we could make this more generic, using functions like get_out(1) or get_number_result(1) instead of get_years. We also create the Instead of Trigger for this view:create or replace view procedure_callasselect procedure_name, in1, in2, in3, proc_results.get_years, proc_results.get_months, proc_results.get_daysfrom dul/create or replaceTRIGGER ioT_procedure_callINSTEAD OF UPDATE OR INSERTON procedure_callFOR EACH ROWdeclare l_years number; l_months number; l_days number;begin abs_date_diff ( to_date(:new.in1) , to_date(:new.in2) , l_years , l_months , l_days ); proc_results.set_years(l_years); proc_results.set_months(l_months); proc_results.set_days(l_days);END ioT_procedure_call;/Now we can make the procedure call by either inserting into or updating view PROCEDURE_CALL:update procedure_callset procedure_name = ‘ABS_DATE_DIFF’, in1 = to_date(’30-10-2000′,’DD-MM-YYYY’), in2 = sysdate/insert into procedure_call(procedure_name, in1, in2)values(’ABS_DATE_DIFF’, to_date(’30-10-2000′,’DD-MM-YYYY’), sysdate)/The results are the same in both instances:select *from procedure_call/ PROCEDURE_NAME—————————————-IN1—————————————-IN2—————————————-IN3—————————————- GET_YEARS GET_MONTHS GET_DAYS———- ———- ———-XXX 5 2 24And another… with Fine Grained Auditing< >> For some hopeful moments I considered another scenario: a select-statement triggers a Fine Grained Auditing trigger (also see Select Trigger in Oracle Database – introducing Fine Grained Auditing); this trigger reads the input values from the where clause, makes the PL/SQL call and sets the return results in package variables. In code that would be something like:select audit_handler.get_out1, audit_handler_get_out2from dualwhere ‘in1=first_value’and ‘in2=second_value’/We would define an audit policy on DUAL that invokes a audit policy function in the audit_handler package. Something like:create or replace package AUDIT_HANDLERisfunction get_call_timereturn timestamp;PROCEDURE HANDLE_PROCEDURE_CALL( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 );end;/create or replace package body AUDIT_HANDLERisg_timestamp timestamp;function get_call_timereturn timestampisbegin return g_timestamp;end;PROCEDURE HANDLE_PROCEDURE_CALL( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) is PRAGMA AUTONOMOUS_TRANSACTION;begin — find current SQL in sys_context(’userenv’,’current_sql’) g_timestamp:= systimestamp;end HANDLE_PROCEDURE_CALL;end;/In this example we have not yet bothered with the in# en out# values; I just wanted to see whether anything the select policy function does can actually influence the result of the query. I activated the audit policy:begin dbms_fga.add_policy ( object_schema=>’SCOTT’ , object_name=>’PROCEDURE_CALL’ , policy_name=>’CALL_BROKER’ , handler_schema => ‘SCOTT’ , handler_module => ‘AUDIT_HANDLER.HANDLE_PROCEDURE_CALL’ );end;/instead of DUAL we have used another object: VIEW PROCEDURE_CALL:create or replace view procedure_callasselect ‘’ procedure_name, ‘’ in1, ‘’ in2, ‘’ in3, ‘’ out1, ‘’ out2, ‘’ out3, AUDIT_HANDLER.get_call_timefrom dual/and attempted a query:select * from procedure_call/P I I I O O O- – – – – – -GET_CALL_TIME—————————————————————————1 row selected.The result was disappointing: no value was returned. So it seems that the Audit Policy Function executes very much on its own. No connection, not even through session settings such as global package variables, with the query execution environment. We can discard this idea.SummaryIf for some reason you want to call a PL/SQL procedure, that takes multiple input parameters and returns one or more out-parameters, and you have to make this call through an interface or framework that does not (easily) allow calls to stored procedures, you can easily create wrappers that make it possible to execute a simple SELECT statement that indirectly makes the PL/SQL call. ORM Frameworks and technologies such as EJB 3.0 Persistence and Oracle ADF Business Components (ViewObjects) are examples where executing queryies is (much) easier that calling PL/SQL procedures […]
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.