How to Grant All Privileges to Local db2admin with DB2 9.7 on Windows

db2permissionswindows

I have as my developer station a laptop with OS Windows XP Professional Edition, Service Pack 3.

I have downloaded and installed IBM DB2 UDB 9.7 fix pack 4, of the Express-C edition.

I have a local Windows account called db2admin that I am using as my local database administrator for my local install of DB2 (developer purposes only).

I can run the following without issues when running the Command Window:

db2 attach to db2 user db2admin using xxxxxxxxxx

That allows me to attach to my instance called DB2.

I can run my create database commands.

I then attempt to connect to the database to grant all privileges for my db2admin account in DB2.

db2 CONNECT TO MYDB;
SET SCHEMA DB2ADMIN;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;
CONNECT RESET;

However when I run that, it DB2 tells me that my actual windows user account (synprgcma) does not have authority to grant authority to user db2admin.

So if I change the second script to the following:

db2 CONNECT TO MYDB USER db2admin USING xxxxxxxx;
SET SCHEMA DB2ADMIN;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;
CONNECT RESET;

Then DB2 tells me that db2admin cannot revoke or grant authority to itself (actually it tells me that an id cannot revoke or grant authority to itself).

So I am stumped. I did not have this problem with my previous install of DB2 (9.5, and I don't remember which fix pack I was at).

How do I grant the necessary authorities to the local admin account? I believe I need this in order to run a bind command that I need to do next:

db2 CONNECT TO MYDB;
db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 20;
db2 CONNECT RESET;

Any help would be appreciated.

Edit: I've found some of the following links related to this. Still not sure yet how to get it working, but at least I've found some
documentation.

Best Answer

Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.

So I did the following:

  1. Opened the DB2 GUI (was easiest to do this way).
  2. Connected to the desired database as db2admin.
  3. Added my logon to the database as a user.
  4. GRANT all authorities to that id (my id).
  5. Disconnect.
  6. Connect to the desired database as my id.
  7. GRANT all authorities to db2admin.
  8. Disconnect.

Voila! The db2admin logon now as all authorities.

EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the instance owner (in my case the db2admin id) have all the authority needed to interact with the database. I could have actually just commented out those grant lines above in the script. Those were left over from a script which ran against an older version of DB2. I have also found if I need to have the instance owner DBADM after doing a restore to database A from database B, it is easiest just to set the registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available in Fix Pack 2 and above). Then I don't have to try to grant instance owner DBADM. It automatically is granted that to any database restored into the instance. If you are not at Fix Pack 5, you have to bounce the instance for this to take affect.

Related Question