How to Restrict INSERT operations on table for DB2?

Question

I have a table OAUTH20_TOKEN_CACHE for which I have restricted INSERT operations by running below command. db2 REVOKE INSERT ON HVD121.OAUTH20_TOKEN_CACHE FROM USER HVD121

This could be seen INSERTAUTH = 'N' for particular grantee 'HVD121' in the 'SYSTABAUTH' table,but still I could see INSERT operations succeeding for the particular table wherein the connection is established using user 'HVD121.

Is this correct approach??

Answer 1

User in DB2 can have privileges granted via user's groups and roles as well. DB2 stores permissions on objects, not restrictions. You can't revoke a privilege on an object from a user, if this user hasn't been granted this permission explicitly (you get SQL0556N on such a revoke statement). The reason you see INSERTAUTH = 'N' in SYSCAT.TABAUTH (SYSIBM.SYSTABAUTH) is that this user has some other *AUTH permission(s) on this table, but not INSERT.

So, you can't revoke a privilege from a user explicitly, if this user has this privilege via its groups or roles (we don't consider LBAC here).

2 checks should be done to understand why some particular user has some privilege on some object. Change the constants 'MYUSER', 'MYSCHEMA', 'MYTABLE' below accordingly.

1) If the user has DATAACCESS database authority:

SELECT P.GRANTEE, P.GRANTEETYPE
FROM SYSCAT.DBAUTH P
CROSS JOIN TABLE(VALUES 'MYUSER') 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 P.DATAACCESSAUTH='Y';

2) If the user has the privilege on the table:

SELECT  
  P.PRIVILEGE, P.OBJECTTYPE, P.OBJECTSCHEMA, P.OBJECTNAME
, U.AUTHID, U.AUTHIDTYPE
FROM SYSIBMADM.PRIVILEGES P
CROSS JOIN TABLE(VALUES 'MYUSER') 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 = 'MYSCHEMA' AND P.OBJECTNAME='MYTABLE' AND P.OBJECTTYPE='TABLE'
AND P.PRIVILEGE IN ('INSERT', 'CONTROL')
ORDER BY 2, 3, 4;

@Redhat: Show the results of the queries above for MYSCHEMA=HVD121, MYTABLE=OAUTH20_TOKEN_CACHE, MYUSER=HVD121.