Logon triggers in DB2 (kind of)
Let's for example assume you are implementing the INSTRB function.
INSTRB is just a synonym for INSTR(VARCHAR, VARCHAR) so perhaps you write the following:
So now an application can simply use INSTRB, right?CREATE OR REPLACE FUNCTION ORAL
In order to find this function the ORALIB schema which was chosen to host the function need to be added to the PATH:VALUES INSTRB('hello', 'll);
Good. But now we are facing the next problem: Where to put this SET PATH statement?SET PATH = CURRENT PATH, ORALIB;
There are some options to specify PATH on connect strings or a cli.ini file etc.
But to do that you need to change the clients and perhaps application code.
A much nicer way is to find a central place in the database to update the path automatically whenever a new connect is done from any client.
DB2 9.7.3 provides such a handy place.
A new database configuration parameter CONNECT_PROC has been added that serves as a "user-exit".
When CONNECT_PROC is given a qualified procedure name: schema.name
then on every connect DB2 will execute that procedure.
Now we can connect and execute INSTRB without worries:--#SET TERMINATOR @
That's pretty cool.CONNECT TO TEST;
Now what if you want to, for example, record when a user connects in some audit table?
No problem. While DB2 does not allow the CONNECT_PROC to modify the database we can circumvent it by using an AUTONOMOUS procedure. Essentially we escape into our own little world.
In order to set both environment variables and do the update we do this using a nested procedure call:
Time to test:CREATE TABLE SECU
This is working.db2 => CONNECT TO TEST;
But what about security? Providing a hook to execute any procedure on CONNECT provides a lot of power.
What if someone injects some procedure here, or perhaps drop the procedure to avoid getting audited?
To avoid this from happening DB2 enforces a few rule:
DB2 9.7.3 provides an easy and safe way to implement a user exit for connect (which is often also called a logon trigger).
The user exit can be used to set environment variables such as PATH or do auditing.
Other uses that come to mind are overloading of built-in functions (by prefixing PATH with a path of your own) or setting of global variables on connect rather than on first reference (for example a CONNECT_TIME variable).