24.5.2 Acquiring table access privileges information

Search the SQL_TABLE_PRIVILEGES dictionary table to acquire access privileges information for a table. See the following example.

Example

SELECT TABLE_SCHEMA, TABLE_NAME, GRANTOR, GRANTEE, SELECT_PRIVILEGE,
   INSERT_PRIVILEGE,DELETE_PRIVILEGE, UPDATE_PRIVILEGE, GRANTEE_TYPE
 FROM MASTER.SQL_TABLE_PRIVILEGES
      WHERE TABLE_SCHEMA <> 'HiRDB'
        AND ((GRANTEE_TYPE IS NULL AND GRANTEE IN (USER,'PUBLIC'))
        OR (GRANTEE_TYPE='G' AND
            IS_USER_CONTAINED_IN_HDS_GROUP(GRANTEE) IS TRUE))

Note: IS_USER_CONTAINED_IN_HDS_GROUP is a scalar function.
Reference note
  • If a role name is the same as a user ID and table access privileges were granted to both names, it may not be possible to distinguish between the names when acquiring table access privileges information. To distinguish between the two names, either execute the SQL such as in the example above or use DABroker (version 02-06 or later) to acquire the table access privileges information.
  • When you use DABroker (a version earlier than 02-06), table access privileges information granted to user roles cannot be acquired.