Retrieving multiple grants of the same privilege
You can query the catalog to find information about duplicate grants of the same privilege on objects. If multiple grant records clutter your catalog, consider revoking unnecessary grants, which removes duplicate grant data from the catalog.
Procedure
To retrieve duplicate grants on plans:
SELECT GRANTEE, NAME, COUNT(*)
FROM SYSIBM.SYSPLANAUTH
GROUP BY GRANTEE, NAME
HAVING COUNT(*) > 2
ORDER BY 3 DESC;
This statement orders the duplicate grants by frequency, so that you can easily identify the most duplicated grants. Similar statements for other catalog tables can retrieve information about multiple grants on other types of objects.
If several grantors grant the same privilege to the same grantee, the Db2 catalog can become cluttered with similar data. This similar data is often unnecessary, and it might cause poor performance.
Example
Example 1: Suppose that Judy, Kate, and Patti all grant the SELECT privilege on TABLE1 to Chris. If you care that Chris's ID has the privilege but not who granted the privilege, you might consider two of the SELECT grants to be redundant and unnecessary performance liabilities.
However, you might want to maintain information about authorities that are granted from several different IDs, especially when privileges are revoked.
Example 2: Suppose that the SELECT privilege from the previous example is revoked from Judy. If Chris has the SELECT privilege from only Judy, Chris loses the SELECT privilege. However, Chris retains the SELECT privilege because Kate and Patti also granted the SELECT privilege to Chris. In this case, the similar grants prove not to be redundant.