IBM DB2 "Administrator" does not have the privilege to perform "Select"

Question

I've been testing a software I helped develop, which resides on a 32-bit application server. It is to connect to a 64-bit database server, which uses IBM DB2 v10.1.

I was the one to setup the DB2, but I'm pretty sure it has since been modified; I am no longer able to connect using the Username/Password: db2admin/db2admin.

Instead, I have to use Administrator/p@ssw0rd. I do not recall creating that user myself - it is a local account on the computer itself - but from my ODBC tests, it can connect to the database.

However, it appears it doesn't have any privileges. My attempts to see where this 'user' is using Data Studio 3.2.0 have failed, though given my experience (lack thereof) with DB2, this is not surprising.

My concerns are two-fold:

• To find where this 'Administrator' resides.
• And to modify its privileges to replicate that of db2admin, which, iirc, is a Database Administrator.

My attempts to research the problem on the net was met with failure - either the so called solution doesn't work, or it is too complex for me to understand if it did work (it didn't).

I have tried the following:

• Modifying the Database directly via Data Studio 3.2.0; Right clicking on the database, selecting Manage Privileges, and checking everything I could find - note that I did not find any 'Administrator', just a 'PUBLIC', 'DB2ADMIN', 'SYSDEBUG'. Also, it doesn't seem to save.
• 'Select * from SYSCAT.DBAUTH where GRANTEE = 'Administrator'; This produces a long list of tables, I guess. Don't know what to do with them, but if I replace 'Administrator' with 'db2admin', I get exactly the same result.
• Creating a new user called 'Administrator' using Data Studio;

Please, I'd like some light shed on this; DB2 is an extremely frustrating database. I'm using DB2 v10.1, Data Studio 3.2.0, and Windows Server 2008.

Answer 1

DB2 authentication relies on an external mechanism, such as OS security or ldap. If your case, it seems it is Windows security.

DB2 authorisation is internal, so any grant is inside the database, with some exceptions.

There are several authorities in DB2, some at instance level and other at database level. Those at database level, you can find them inside the database, by querying the catalog, and they can be assigned to a user or to a group. The other authorities, at instance level, are associated to a OS group (external mechanism)

The highest authority in a database is DBADM, and the highest authority at an instance level is SYSADM. Every user in the associated group to SYSAMD becomes automatically DBADM in all database inside the instance.

Well, this is just a short explanation of how DB2 security is. It means that you 'Administrator' user has the 'connect' privilege (sometime 'connect' privilege is public, it means, any user can connect), but it does not have any other privilege, nor authority.

Finally, in Windows environment, there is another security layer, that associates users in two groups DB2ADMNS and DB2USERS. For more information check this link http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0023391.html

Answer 2

you have to GRANT a SELECT on the TABLE to the USER.

GRANT SELECT, INSERT ON mytable TO USER peter

"db2 is not frustrating!"

Answer 3

Well, I solved it, but it remains to be seen why this occurred in the first place;

After creating the user 'Administrator', I modified the privilege by checking everything. It seemed to work.