0

In Db2 v11.5.8.0 on Linux x86-64 I see there are duplicate rows in sysibmadm.privileges for tables. Investigating in detail I see duplicates are from syscat.tabauth table, because the same privilege was granted by two grantors:

SELECT
    grantor, grantortype, grantee, granteetype, tabschema, tabname, deleteauth
FROM
    syscat.tabauth
WHERE
    tabschema = 'MYSCHEMA' and 
    tabname = 'MYTABLE'

Outputs:

GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE TABSCHEMA TABNAME DELETEAUTH
------- ----------- ------- ----------- --------- ------- ----------
SYSIBM  S           USER1   U           MYSCHEMA  MYTABLE G
MXEP    U           USER1   U           MYSCHEMA  MYTABLE G

I don't want to remove DELETE privilege from user. I just don't like to see two rows in this table. How to remove second row and leave first row intact?

After second row remove, I want to have:

GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE TABSCHEMA TABNAME DELETEAUTH
------- ----------- ------- ----------- --------- ------- ----------
SYSIBM  S           USER1   U           MYSCHEMA  MYTABLE G

Regards

Improve this question
2
  • Have you tried revoking the unwanted privilege? Commented Jan 30 at 13:06
  • Yes I tested: ` REVOKE DELETE ON TABLE MYSCHEMA.MYTABLE FROM USER USER1` and both of lines rows from syscat.tabauth get removed. I looked at the revoke command syntax and it looks it can't be set something like "REVOKE DELETE ON TABLE MYSCHEMA.MYTABLE FROM USER USER1 GRANTED BY MXEP". Commented Jan 31 at 11:23

1 Answer 1

Reset to default
1

Db2 tracks a privilege grantor. So, when some user grants a privilege (even the same one which another user has already granted this grantee), an additional row appears in the syscat.tabauth table. The row disappears, if all privileges specified in this row are revoked only.
This is why you can't remove such a "duplicate" row sometimes, and the presence of such rows may be unavoidable.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.