Show TOC

TABLEPRIVILEGESLocate this document in the navigation structure

Use

Use of the system table DOMAIN. TABLEPRIVILEGES

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

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

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Note the General Instructions for formulating SQL statements.

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

  • All tables for which the current database 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 database 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 database user has granted to the database user DAVID

    SELECT schemaname, tablename, privilege

    FROM DOMAIN.TABLEPRIVILEGES

    WHERE grantor = 'MONA'

    AND grantee = 'DAVID'

More Information

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