0

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?

Improve this question

1 Answer 1

Reset to default
1

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
Improve this answer
6
  • Interestingly enough, I can run a SET SCHEMA due to having bobabob97 been granted IMPLICIT_SCHEMA, but I cannot do a CREATE_SCHEMA. I would like to find out how to do the CREATE_SCHEMA (even using the AUTHORIZATION clause doesn't help) as any schemas created implicitly are technically owned by PUBLIC and I'd rather not have things owned by PUBLIC.
    – Chris Aldrich
    Sep 17, 2013 at 15:25
  • 1
    CREATE SCHEMA requires DBADM, as evident from the manual. I'm assuming you're getting this error while connected with your "DDL owner" user, as the other user has DBADM.
    – mustaccio
    Sep 17, 2013 at 17:50
  • @mustaccio - I was wondering about that. So I probably need to grant DBADM WITHOUT DATAACCESS WITHOUT ACCESSCTRL? I realize I will implicitly have SELECT via having CONTROL on the objects. I suppose I would then have to revoke QUIESCE_CONNECT, CONNECT, and LOAD?
    – Chris Aldrich
    Sep 17, 2013 at 18:05
  • @mustaccio - or is it better to have implicit schemas even if those are owned by PUBLIC? Also curious, if you have a RESTRICTIVE database, are implicit schemas still owned by PUBLIC?
    – Chris Aldrich
    Sep 17, 2013 at 18:12
  • 1
    I'm afraid I don't have all the answers, so you'll just have to test by yourself. You seem to have a more thorough approach than most organizations I've worked with: in most cases the intended object owner/schema creator is just granted DBADM, or rather whoever has DBADM creates all objects. And I think SYSIBM, not PUBLIC is the owner of implicit schemas. PUBLIC is basically a group, so it can't be the owner of anything.
    – mustaccio
    Sep 17, 2013 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.