ROLEPRIVILEGES 

Prerequisites

You have generated the demo data for the SQL Tutorial.

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

Define and use roles. Proceed as described in SQL Tutorial, Roles.

Examples

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

●     All privileges granted directly to the role ROLE_1 for the CITY table. Privileges granted indirectly, that is, granted for the CITY table via a different role, are not displayed.

SELECT privileges
  FROM DOMAIN.ROLEPRIVILEGES
    WHERE grantee = 'ROLE_1'
      AND schemaname = 'HOTEL'
      AND tablename = 'CITY'

●     All roles that were granted to ROLE_4. Specification of who granted the role.

SELECT role, grantor
  FROM DOMAIN.ROLEPRIVILEGES
    WHERE grantee = 'ROLE_4'
      AND role IS NOT NULL

All roles: see ROLES

Role for a user that is automatically activated when a database session is opened: see ROLES

All roles that are active in the current database session: see SESSION_ROLES