Use of the system tableDOMAIN.ROLEPRIVILEGES
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 and use roles. Proceed as described in SQL Tutorial, Roles.
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 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 of a database user that is automatically activated when a database session is opened: see ROLES
All active roles in the current database session: see SESSION_ROLES