DB2 WSE 9.7 - General Errors

The below would apply only when a Database is created with RESTRICTIVE option. This is one of the NEW Feature in DB2 WSE/LINUX 9.7.

I notice difference in the procedure creating users in Db2 9.7 (WSE) with RESTRICTIVE option, which is not documented clearly. At least, I didn't find in DB2 Manuals or info center.

Database created in Db2 9.7 with Restrictive option, requires administrator to grant privileges granular level and it does make sense.

when you create database in previous versions of DB2 or in Db2 9.7 without restrictive option, a DB user do get some default privileges (public).

When create an normal user (applications, developers) with permissions to application schema/tablespace, After login user get errors like below.

ERROR [42501] [IBM][DB2/LINUX] SQL0551N "<>" does not have the privilege to perform operation "EXECUTE" on object "NULLID.SYSSH200". SQLSTATE=42501


if your DB is in earlier versions, re-binding packages might solve the problem. But if you are in Db2 9.7 with restrictive option, you might want to jump to the LABEL errors in db2 9.7

Windows/Linux
Command Prompt
type > db2

I assume, you have db2 path set or you can use clp (Command Line Processor)

run the below sequence of commands
login as instance user and make sure you change the path to your database path below. I tested this on Db2 9.7

> db2
> connect to mydatabase
> bind '/opt/ibm/db2/V9.7/bnd/@db2ubind.lst' GRANT PUBLIC
> bind '/opt/ibm/db2/V9.7/bnd/@db2cli.lst' GRANT PUBLIC
> bind '/opt/ibm/db2/V9.7/bnd/@ddcs400.lst' BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE
> terminate
Reference
http://www-01.ibm.com/support/docview.wss?uid=swg21163994



ERROR IN DB2 9.7 - With restrictive option

You can also grant to PUBLIC instead of specfic username. If you grant to public, you might want to re think on RESTRICTIVE option.
db2 GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO DBUSER;
db2 GRANT SELECT ON SYSCAT.SCHEMATA to DBUSER
db2 GRANT SELECT ON SYSIBM.SYSDUMMY1 to DBUSER
db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user DBUSER
db2 GRANT SELECT ON SYSCAT.TABLES to DBUSER
db2 GRANT SELECT ON SYSCAT.VIEWS to DBUSER
db2 GRANT SELECT ON SYSCAT.INDEXES to DBUSER
db2 GRANT SELECT ON SYSCAT.TRIGGERS to DBUSER
GRANT SELECT ON SYSCAT.COLUMNS to DBUSER;

--FROM HERE KIND OF ADMIN PRIVILEGES START
db2 GRANT SELECT ON SYSCAT.DBAUTH to DBUSER
db2 GRANT SELECT ON SYSCAT.SERVERS to DBUSER

--ACCESS TO LIST USER from USER TABLE
db2 GRANT SELECT ON SYSCAT.ROUTINEAUTH to DBUSER
db2 GRANT SELECT ON SYSCAT.TBSPACEAUTH to DBUSER
db2 GRANT SELECT ON SYSCAT.SCHEMAAUTH to DBUSER
db2 GRANT SELECT ON SYSCAT.INDEXAUTH to DBUSER
db2 GRANT SELECT ON SYSCAT.TABAUTH to DBUSER
----

db2 GRANT SELECT ON SYSCAT.TABOPTIONS to DBUSER
db2 GRANT SELECT ON SYSCAT.EVENTMONITORS to DBUSER
db2 GRANT SELECT ON SYSCAT.ROUTINEPARMS to DBUSER
db2 GRANT SELECT ON SYSCAT.ROUTINES to DBUSER
db2 GRANT SELECT ON SYSCAT.PACKAGES to DBUSER
db2 GRANT SELECT ON SYSCAT.PACKAGEAUTH to DBUSER
db2 GRANT SELECT ON SYSCAT.EVENTS to DBUSER
db2 GRANT SELECT ON SYSCAT.SEQUENCES to DBUSER
db2 GRANT SELECT ON SYSCAT.DATATYPES to DBUSER
db2 GRANT SELECT ON SYSCAT.BUFFERPOOLS to DBUSER
db2 GRANT SELECT ON SYSCAT.TABLESPACES to DBUSER
db2 GRANT SELECT ON SYSCAT.TABCONST to DBUSER
db2 GRANT SELECT ON SYSCAT.WRAPPERS to DBUSER
db2 GRANT SELECT ON SYSCAT.DBPARTITIONGROUPS to DBUSER

________________________STEP BY STEP ON ERROR


ERROR [42501] [IBM][DB2/LINUX] SQL0551N "DBUSER" does not have the privilege to perform operation "EXECUTE" on object "NULLID.SYSSH200". SQLSTATE=42501

db2 connect to aswini;
db2 GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO DBUSER;
______________________________________

ERROR [42501] [IBM][DB2/LINUX] SQL0551N "DBUSER" does not have the privilege to perform operation "SELECT" on object "SYSCAT.SCHEMATA". SQLSTATE=42501

db2 GRANT SELECT ON SYSCAT.SCHEMATA to DBUSER
DB20000I The SQL command completed successfully.
______________________________________________________

ERROR [42501] [IBM][DB2/LINUX] SQL0551N "DBUSER" does not have the privilege to perform operation "SELECT" on object "SYSIBM.SYSDUMMY1". SQLSTATE=42501

db2 GRANT SELECT ON SYSIBM.SYSDUMMY1 to DBUSER
DB20000I The SQL command completed successfully.
__________________________________________________________

ERROR [5U020] [IBM][DB2/LINUX] SQL0969N There is no message text corresponding to SQL error "-4707" in the message file on this workstation. The error was returned from module "SQLRW00C" with original tokens "SYSDEFAULTUSERWORKLOAD". SQLSTATE=5U020


db2 grant usage on workload SYSDEFAULTUSERWORKLOAD to user DBUSER
DB20000I The SQL command completed successfully.