TABLEPRIVILEGES

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the demo database instance DEMODB as user MONA.

Define privileges. Proceed as described in SQL Tutorial, Database Users and Their Privileges.

Examples

You can use the system table TABLEPRIVILEGES to determine the following database information, among other things:

?     All tables for which the current user has been granted a privilege. Own tables are not output.

SELECT schemaname, tablename, privilege
  FROM DOMAIN.TABLEPRIVILEGES
    WHERE grantee = 'MONA'

?     All tables for which the current user has been granted the SELECT privilege and is allowed to pass this on. Own tables are not output.

SELECT schemaname, owner, tablename
  FROM DOMAIN.TABLEPRIVILEGES
    WHERE grantee = 'MONA'
      AND privilege LIKE '%SEL%'
      AND is_grantable = 'YES'

?     All privileges that the current user has granted to the user DAVID

SELECT schemaname, tablename, privilege
  FROM DOMAIN.TABLEPRIVILEGES
    WHERE grantor = 'MONA'
      AND grantee = 'DAVID'

All tables: see TABLES

Existence of a table: see TABLES

Table definition: see COLUMNS

Synonym for a table: see SYNONYMS

Table privilege: see TABLES

Table in a view definition: see VIEWCOLUMNS