DB2 : Grant all privileges to db2admin on the localhost database

Question

I have installed DB2 Express-C on my local machine and sample database is created for my username. Now, I want db2admin to access this sample database. I have tried below command,

connect to DATABASENAME user USER_NAME using USER_PASSWORD

GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;

But then too, I am unable to access the database tables using db2admin as shown below,

connect to DATABASENAME user db2admin using USER_PASSWORD

list tables

It is displaying 0 record found. That means it is not able to access the database.

Any help would be highly appreciated.

Solution 1

That means it is not able to access the database.

Wrong. DB2 would tell you that already when you connected. Your example merely shows that user db2admin has no tables in his default schema.

Try this:

list tables for schema user_name

where user_name is the name of the db creator.

Note that

list tables

lists the tables for the current user, and will return 0 rows until you create some.

Solution 2

I think you can try the following steps: (Here I'll use SAMPLE as an example)

1. connect to SAMPLE using your windows domain ID:

   db2 connect to SAMPLE

   Here, the connection will use your windows domain ID by default. And you check this by return "SQL authorization ID = "

2. db2 grant DATAACCESS on DATABASE to user db2admin

   This will grant DATAACCESS authority to db2admin

3. Terminate connection: db2 terminate

4. Connect to SAMPLE with db2admin: db2 connect to sample user db2admin using 'password'

5. You should be able to access db with your db2admin

You can read more about windows domain ID and db2admin here:

1. IBM Data Studio can't browse data on SAMPLE (DB2 Express-C)
2. How do I grant all privileges to local db2admin with DB2 9.7 on Windows?

Please let me know how it works.