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?