Retrieving all IDs or roles with access to the same table

To retrieve all IDs or roles (GRANTEETYPE="L") that are explicitly authorized to access the sample employee table (DSN8D10.EMP in database DSN8D13A), issue the following statement:

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSTABAUTH
  WHERE TTNAME='EMP' AND TCREATOR='DSN8910' AND
        GRANTEETYPE IN (' ','L');

To retrieve all IDs or roles (GRANTEETYPE="L") that can change the sample employee table (IDs with administrative authorities and IDs to which authority is explicitly granted), issue the following statement:

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSTABAUTH
  WHERE TTNAME='EMP' AND
        TCREATOR='DSN8910' AND
        GRANTEETYPE IN (' ','L') AND
        (ALTERAUTH  <> ' ' OR
         DELETEAUTH <> ' ' OR
         INSERTAUTH <> ' ' OR
         UPDATEAUTH <> ' ')
UNION
SELECT GRANTEE FROM SYSIBM.SYSUSERAUTH   
  WHERE SYSADMAUTH <> ' '
UNION
SELECT GRANTEE FROM SYSIBM.SYSDBAUTH     
  WHERE DBADMAUTH <> ' ' AND NAME='DSN8D91A';

To retrieve the columns of DSN8D10.EMP for which update privileges have been granted on a specific set of columns, issue the following statement:

SELECT DISTINCT COLNAME, GRANTEE, GRANTEETYPE FROM SYSIBM.SYSCOLAUTH
  WHERE CREATOR='DSN8D10' AND TNAME='EMP'
  ORDER BY COLNAME;

The character in the GRANTEETYPE column shows whether the privileges have been granted to a primary or secondary authorization ID (blank), a role (L), or are used by an application plan or package (P).

To retrieve the IDs that have been granted the privilege of updating one or more columns of DSN8D10.EMP, issue the following statement:

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSTABAUTH
  WHERE TTNAME='EMP' AND
        TCREATOR='DSN8910' AND
        GRANTEETYPE IN (' ','L') AND
        UPDATEAUTH <> ' ';

The query returns only the IDs or roles (GRANTEETYPE="L") to which update privileges have been specifically granted. It does not return IDs or roles that have the privilege because of SYSADM or DBADM authority. You could include them by forming a union with additional queries, as shown in the following example:

SELECT DISTINCT GRANTEE GRANTEETYPE FROM SYSIBM.SYSTABAUTH
  WHERE TTNAME='EMP' AND
        TCREATOR='DSN8910' AND
        GRANTEETYPE IN (' ','L') AND
        UPDATEAUTH <> ' '
UNION
SELECT GRANTEE FROM SYSIBM.SYSUSERAUTH
  WHERE SYSADMAUTH <> ' '
UNION
SELECT GRANTEE FROM SYSIBM.SYSDBAUTH
  WHERE DBADMAUTH <> ' ' AND NAME='DSN8D91A';