Skip to content
Advertisement

How to block a user from executing select queries on a table in Db2?

I have a database ‘SAMPLE’ in which I need to block a user ‘DB2ADMIN’ from executing

SELECT * FROM SYSIBMADM.DBMCFG

I already tried 

REVOKE SELECT ON TABLE SYSIBMADM.DBMCFG FROM USER DB2ADMIN

I get error SQL0556N and message “DB2ADMIN does not hold this privilege”, which means it shouldn’t have worked in first place.

Still I am able to get select query results when I log in with that account and execute above query. Can someone suggest a way out to do this.

Advertisement

Answer

The following query provide you information on why your DB2ADMIN user has SELECT or CONTROL privileges (if any) on the SYSIBMADM.DBMCFG view. 

SELECT  
  P.PRIVILEGE, U.AUTHID, U.AUTHIDTYPE
FROM SYSIBMADM.PRIVILEGES P
CROSS JOIN (VALUES 'DB2ADMIN') A (AUTHID)
JOIN TABLE 
(
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
  UNION ALL
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
  UNION ALL
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.AUTHID AND U.AUTHIDTYPE=P.AUTHIDTYPE
WHERE P.OBJECTSCHEMA = 'SYSIBMADM' AND P.OBJECTNAME = 'DBMCFG' 
AND P.PRIVILEGE IN ('SELECT', 'CONTROL');

The following query provide you information on why your DB2ADMIN user has one of DATAACCESS, DBADM, SQLADM, SECADM or ACCESSCTRL authorities (if any) sufficient to select from this view. Update
Note, that the Authorization list for SYSIBMADM.DBMCFG is not correct. It’s one of the system catalog views, and one having SECADM or ACCESSCTRL database authority has an access to this view. So, we need to check both these user authorities in addition.

SELECT 
  P.GRANTEE, P.GRANTEETYPE
, P.DATAACCESSAUTH, P.DBADMAUTH, P.SQLADMAUTH, P.SECURITYADMAUTH, P.ACCESSCTRLAUTH
FROM SYSCAT.DBAUTH P
CROSS JOIN (VALUES 'DB2ADMIN') A (AUTHID)
JOIN TABLE 
(
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
  UNION ALL
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
  UNION ALL
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.GRANTEE AND U.AUTHIDTYPE=P.GRANTEETYPE
WHERE 'Y' IN (P.DATAACCESSAUTH, P.DBADMAUTH, P.SQLADMAUTH, P.SECURITYADMAUTH, P.ACCESSCTRLAUTH);
Advertisement