Return
-
Title
"USER DOES NOT HAVE PRIVILEGE TO PERFORM OPERATION SELECT ON THIS OBJECT SYSIBM.SYSDUMMY1" in DB2 -
Description
DB2 permissions script fails on accessing SYSIBM.SYSDUMMY1
ERROR [DB2Installer-1600410587359-1600411908883-1600411838969] com.quest.qsi.fason.db2.installer.callback.DB2DatabaseValidator - DB2 database validation failure for instance [gtkinst1] and database [DBNAME]
com.quest.qsi.fason.framework.installer.exceptions.InstallerServiceException: Failed to validate database permissions
Caused by: com.quest.qsi.fason.framework.connections.common.exceptions.FrameworkQueryException: [Foglight][DB2 JDBC Driver][DB2]USERID DOES NOT HAVE PRIVILEGE TO PERFORM OPERATION SELECT ON THIS OBJECT SYSIBM.SYSDUMMY1- Profile:DB2Profile{host='HOSTNAME', port='50000', databaseName='DBNAME', username='USERNAME', passw d='xxxxxxx', socketTimeout (sec)='120000, authenticationType='SERVER', codePage='null', ssl='false', validateServerCertificate='false', cryptoProtocolVersion='null', trustStore='null', trustStoreP d=XXXX} Query: "--/****************************************************************************************************************************************************/ --/* Apply To: DB2 */ --/* Function: The following code check if given user has administrative privilidges on the database connected to */ --/* ...t has --/* Date Created: 16/01/2014. */ --/* Last Modified: */ --/****************************************************************************************************************************************************/ select SUM (A.authorized) from sysibm.sysdummy1, (SE...LIC = 'Y' or ROLE_USER = 'Y' or ROLE_GROUP = 'Y' or ROLE_PUBLIC = 'Y' then 1 else 0 end as AUTHORIZED FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID (cast (upper('questmon') as VARCHAR(128)) , 'U') ) AS T WHERE AUTHORITY in ('SYSADM', 'DATAACCESS','DBADM') ORDER BY AUTHORITY) -
Cause
The SYSDUMMY1 catalog table contains one row. It is used for SQL statements in which a table reference is required, but the contents of the table are not important. It's like using DUAL in Oracle.A SELECT privilege is granted to PUBLIC on the SYSIBM.SYSDUMMYx tables when they are created during the DB2 installation. It's possible that this select privilege was removed from PUBLIC the object, or that the customer using some external product for authentication. -
Resolution
WORKAROUNDGrant the SELECT privilege to the following objects or have the external product grant access to the objects.- SYSIBM.SYSDUMMY1
- SYSCAT.COLUMNS
- SYSIBM.SYSPLAN
STATUSFAIO-384 has been logged to include the permissions for objects granted to the PUBLIC role to the DB2 permissions requirements in the deployment guide. -
Defect ID
FAIO-384