CODEWITHSUNDEEP
sqllinuxdb2ibm-clouddb2-luw
Divik Shrivastava
Divik Shrivastava

Reputation: 154

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.

Upvotes: 0

Views: 730

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

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);

Upvotes: 2

data_henrik
data_henrik

Reputation: 17118

Take a look at the documentation for the administrative view (not table) SYIDBMADM.DBMCFG. It states the following:

One of the following authorizations is required:

  • SELECT privilege on the DBMCFG administrative view
  • CONTROL privilege on the DBMCFG administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

So that user in question might not have SELECT privilege, but may have DBADM authority. Given the user name, I might guess correctly that is the case. Revoke DBADM authority from that user and test again.

If that does not help, try to find out more about that user(id). Use the procedures AUTH_LIST_GROUPS_FOR_AUTHID, AUTH_LIST_AUTHORITIES_FOR_AUTHID and PRIVILEGES to obtain security-related information.

Upvotes: 3

Related Questions