Retrieving names authorized to access a table

You can use the PRIVILEGES and other administrative views to retrieve information about the authorization names that have been granted privileges in a database.

About this task

The following statement retrieves all authorization names (and their types) that are directly authorized to access the table EMPLOYEE with the qualifier JAMES:

SELECT DISTINCT AUTHID, AUTHIDTYPE FROM SYSIBMADM.PRIVILEGES 
    WHERE OBJECTNAME = 'EMPLOYEE' AND OBJECTSCHEMA = 'JAMES'

For releases earlier than Version 9.1, the following query retrieves the same information:

    SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH
       WHERE TABNAME = 'EMPLOYEE'
         AND TABSCHEMA = 'JAMES'
    UNION
    SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH
       WHERE TABNAME = 'EMPLOYEE'
         AND TABSCHEMA = 'JAMES'

To find out who can update the table EMPLOYEE with the qualifier JAMES, issue the following statement:

     SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH
       WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' AND
          (CONTROLAUTH  = 'Y' OR
           UPDATEAUTH IN ('G','Y'))
    UNION
    SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.DBAUTH
       WHERE DBADMAUTH = 'Y'
    UNION
    SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH
       WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' AND
       PRIVTYPE = 'U'

This retrieves any authorization names with DBADM authority, as well as those names to which CONTROL or UPDATE privileges have been directly granted.

Remember that some of the authorization names may be groups, not just individual users.