DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

best practicesdb2Security

Based on a previous question I have asked, I am attempting to work on a solution as recommended by mustaccio's answer, whereby I have another ID to create my DDL objects.

I have been trying to follow an IBM developer works article called "DB2 security, Part 8: Twelve DB2 security best practices" in my attempt to do it. I have noticed it appears the article was written for DB2 8.2 at the time. I am currently working on DB2 9.7 fix pack 4 ESE for LUW. Specifically I am on AIX. I am currently testing out how to do this on my local system, which is Windowx XP SP3 and using DB2 9.7 fix pack 4 Express-C for LUW.

I have my local user account (which is the instance owner) called db2admin in this case (not so on our AIX systems). This ID belongs to the DB2ADMNS group. Since it is different from my Windows account I make sure I attach to the instance first and then create my database to assure that db2admin is granted DBADM,SECADM,DATAACCESS,ACCESSCTRL on my database.

db2 attach to DB2 user db2admin using ********
db2 create database SECTEST automatic storage yes on 'C:\' dbpath on 'C:\'
  using codeset UTF-8 territory US collate using system

After that I connect to the system as db2admin

db2 connect to sectest user db2admin using ********

Then to test things out, I made sure I granted CONNECT and SECADM to my Windows account

db2 grant connect,secadm on database to user myid

And then I wished to set up my bogus ID to create objects under. I intentionally create this ID as nine characters to help with preventing connection to DB2 from the command line, but still allow it via SET SESSION_USER. For the sake of simplicity I have named my ID bobabob97.

So I have done the following:

db2 grant CREATETAB,IMPLICIT_SCHEMA,BINDADD,CREATE_NOT_FENCED_ROUTINE,
  CREATE_EXTERNAL_ROUTINE on database to user bobabob97
db2 grant use of tablespace userspace1 to user bobabob97

At this point I think I have everything set up correctly. I have explicitly not granted bobabob97 CONNECT on the database as I do not want anyone to be able to connect to the database with this ID.

So now I try to switch over to that ID to create test creating my objects and owning them under a generic ID.

db2 set session_user=bobabob97

But then I get the following error:

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0552N "DB2ADMIN" does not have the privilege to perform
operation "SET SESSION AUTHORIZATION". SQLSTATE=28000

So, now I am puzzled. The ID I am currently connected to the database as (db2admin) has DBADM,SECADM,DATAACCESS,and ACCESSCTRL on the database (and of course it implicitly holds CONNECT,CREATETAB,BINDADD,QUIESCE_CONNECT,IMPLICIT_SCHEMA,LOAD,CREATE_NOT_FENCED_ROUTINE, and CREATE_EXTERNAL_ROUTINE). I am essentially "god" on the database. Why can I not switch to this ID?

I tried playing with GRANT SETSESSIONUSER, but I know SECADM can't grant to itself. What do I do here?

Best Answer

Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.

db2 grant setsessionuser on public to group DB2ADMNS

Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:

db2 grant setsessionuser on user bobabob97 to group DB2ADMNS

So now I can perform

db2 set session_user=bobabob97

and it works.

To switch back I only need to run

db2 set session_user=system_user
Related Question