Post Login Script in DB2

by | Oct 23, 2013

One of less known but very useful feature of DB2 is the post login script after an application makes a connection.

DB2 database has a configuration parameter known as CONNECT_PROC which is name of the database procedure which will be executed everytime someone connects to the database. This is equivalent to a login script but generalized at the database level. Purist may doubt this but this is a very useful feature.

For example:

set current schema = XYZ @

drop variable ALLOW_RESTRICTED @

create variable ALLOW_RESTRICTED smallint default 1 @
grant read,write on variable ALLOW_RESTRICTED to public @

drop procedure test_gv @

create procedure test_gv
language sql
begin
declare vcount smallint;
set vcount = 0;
set vcount  = (select count(*) from (select rolename FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID(session_user,'U')) AS t  ) x where x.rolename='ALLOW_RESTRICTED') ;

if (vcount > 0) then SET XYZ.ALLOW_RESTRICTED = 1;
else SET XYZ.ALLOW_RESTRICTED = 0;
end if
;
end
@

grant execute on procedure XYZ.test_gv to public @

After above procedure was created, you can use :

db2 update db cfg for testdb using connect_proc xyz.test_gv

There are several test cases that can be used here and especially is the use of GLOBAL variables that you define using based upon the group ID of the user connecting to the database.

create variable addr smallint default 1;
create variable bank smallint default 1;
create variable routing smallint default 1;
create variable id smallint default 1;

create procedure setgroup
language sql
begin
SET addr =  
  coalesce((select 1 from db2inst2.group_user where  userid = session_user and groupid = 'addr'), 0);
SET shop  =  
  coalesce((select 1 from db2inst2.group_user where  userid = session_user and groupid = 'shop'), 0);         
SET routing  =  
  coalesce((select 1 from db2inst2.group_user where  userid = session_user and groupid = 'routing'), 0);
SET id  =  
  coalesce((select 1 from db2inst2.group_user where  userid = session_user and groupid = 'id'), 0);         
end
@

The value of variables addr, bank, routing etc are then used in other stored procedure to do a specific tasks or enforce securities.