Logon triggers in DB2 (kind of)

Posted by: Serge Rielau

DB2 for LUW is a very extensible database engine and one of things I do love to recommend to customers and IBMers alike is to use these capabilities whenever they encounter mismatches between e.g. Oracle and DB2 which need to be overcome. or, alternatively, perhaps you want to add some "libraries of function" on your own.

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:

CREATE OR REPLACE FUNCTION ORALIB.INSTRB(VARCHAR(), VARCHAR())
  SPECIFIC INSTRB
  RETURNS INTEGER SOURCE SYSIBM.INSTR(VARCHAR(), VARCHAR());

So now an application can simply use INSTRB, right?

VALUES INSTRB('hello', 'll);
SQL0440N  No authorized routine named "INSTRB" of type "FUNCTION" having
compatible arguments was found.  SQLSTATE=42884

In order to find this function the ORALIB schema which was chosen to host the function need to be added to the PATH:

SET PATH = CURRENT PATH, ORALIB;
VALUES INSTRB('hello', 'll';
1
-----------
          3
 
  1 record(s) selected.

Good. But now we are facing the next problem: Where to put this SET PATH statement?
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.

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE ENVIRONMENT.CONNECTION_SETUP()
BEGIN
  SET PATH = CURRENT PATH, ORALIB;
END
@
--#SET TERMINATOR ;
 
UPDATE DB CFG USING CONNECT_PROC ENVIRONMENT.CONNECTION_SETUP;
 
CONNECT RESET;

Now we can connect and execute INSTRB without worries:

CONNECT TO TEST;
VALUES INSTRB('hello', 'll');
1
-----------
          3
 
  1 record(s) selected.

That's pretty cool.
 
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:

CREATE TABLE SECURITY.AUDIT(userid VARCHAR(20), event VARCHAR(20), tstamp TIMESTAMP);
 
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE ENVIRONMENT.CONNECTION_SETUP_DBMOD()
BEGIN
  INSERT INTO SECURITY.AUDIT VALUES(USER, 'CONNECT', CURRENT TIMESTAMP);
END
@
--#SET TERMINATOR ;
 
UPDATE DB CFG USING CONNECT_PROC NULL;
 
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE ENVIRONMENT.CONNECTION_SETUP()
BEGIN
  SET PATH = CURRENT PATH, ORALIB;
  CALL ENVIRONMENT.CONNECTION_SETUP_DBMOD();
END
@
--#SET TERMINATOR ;
 
UPDATE DB CFG USING CONNECT_PROC ENVIRONMENT.CONNECTION_SETUP;
CONNECT RESET;

Time to test:

db2 => CONNECT TO TEST;
 
   Database Connection Information
 
 Database server        = DB2/NT 9.7.3
 SQL authorization ID   = SERGE
 Local database alias   = TEST
 
db2 => SELECT * FROM SECURITY.AUDIT; 
USERID               EVENT                TSTAMP
-------------------- -------------------- --------------------------
SERGE                CONNECT              2010-10-27-17.25.38.812000

  1 record(s) selected.

This is working.
 
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:

1. The procedure specified in CONNECT_PROC must exist before the values can be set.
   This prevents writing a "blank check" for a procedure whose content is not fully understood
2. The value in CONNECT_PROC must be of the form schemaname.procedurename
3. Only procedures with no parameters are considered.
   Thus there is only exactly one procedure that can be found : schemaname.procedurename()
4. Once tapped as the connect procedure the procedure cannot be replaced or otherwise altered.
   (that's why above I had to reset the CONNECT_PROC first before extending it).

 
To summarize:
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).
#Db2