Document Type | Technical Information
Category | Administration
Document Number | TADTI090
Overview
This explains how to query the privileges (PRIVILEGE) owned by each Role used in the Tibero DBMS.
Method
Privileges Required to Query Privileges Belonging to a Role
General accounts other than the SYS account must be granted the following privileges to check the privileges belonging to a Role.
GRANT SELECT ON DBA_ROLES TO USERNAME; GRANT SELECT ON DBA_SYS_PRIVS TO USERNAME;
Querying Privileges Belonging to a Role
You can query the privileges belonging to a Role using the following SQL.
SELECT A.ROLE, B.PRIVILEGE, B.ADMIN_OPTION FROM DBA_ROLES A, DBA_SYS_PRIVS B WHERE A.ROLE=B.GRANTEE GROUP BY A.ROLE, B.PRIVILEGE, B.ADMIN_OPTION;
Example of Querying Privileges Belonging to a Role
The results from directly querying on a test server are shown below.
SQL> create user test identified by test; User 'TEST' created. SQL> GRANT CONNECT TO TEST; Granted. SQL> GRANT SELECT ON DBA_ROLES TO TEST; Granted. SQL> GRANT SELECT ON DBA_SYS_PRIVS TO TEST; Granted. SQL> conn test/test Connected to Tibero. SQL> SELECT A.ROLE, B.PRIVILEGE, B.ADMIN_OPTION 2 FROM DBA_ROLES A, DBA_SYS_PRIVS B 3 WHERE A.ROLE=B.GRANTEE 4 GROUP BY A.ROLE, B.PRIVILEGE, B.ADMIN_OPTION; ROLE PRIVILEGE ADMIN_OPTION ----------------------- -------------------------------------- ------------ CONNECT CREATE SESSION NO DBA ALTER ANY INDEX YES DBA ALTER ANY MATERIALIZED VIEW YES DBA ALTER ANY PROCEDURE YES DBA ALTER ANY ROLE YES DBA ALTER ANY SEQUENCE YES DBA ALTER ANY TABLE YES DBA ALTER ANY TRIGGER YES DBA ALTER DATABASE YES DBA ALTER PROFILE YES DBA ALTER ROLLBACK SEGMENT YES DBA ALTER SESSION YES DBA ALTER SYSTEM YES DBA ALTER TABLESPACE YES DBA ALTER USER YES DBA ANALYZE ANY YES DBA AUDIT ANY YES DBA AUDIT SYSTEM YES DBA COMMENT ANY TABLE YES DBA CREATE ANY DATABASE LINK YES DBA CREATE ANY DIRECTORY YES DBA CREATE ANY INDEX YES DBA CREATE ANY LIBRARY YES DBA CREATE ANY MATERIALIZED VIEW YES DBA CREATE ANY PROCEDURE YES DBA CREATE ANY SEQUENCE YES DBA CREATE ANY SYNONYM YES DBA CREATE ANY TABLE YES DBA CREATE ANY TRIGGER YES DBA CREATE ANY VIEW YES DBA CREATE DATABASE LINK YES DBA CREATE LIBRARY YES DBA CREATE MATERIALIZED VIEW YES DBA CREATE PROCEDURE YES DBA CREATE PROFILE YES DBA CREATE PUBLIC DATABASE LINK YES DBA CREATE PUBLIC SYNONYM YES DBA CREATE ROLE YES DBA CREATE SEQUENCE YES DBA CREATE SESSION YES DBA CREATE SYNONYM YES DBA CREATE TABLE YES DBA CREATE TABLESPACE YES DBA CREATE TRIGGER YES DBA CREATE USER YES DBA CREATE VIEW YES DBA DELETE ANY TABLE YES DBA DROP ANY DATABASE LINK YES DBA DROP ANY DIRECTORY YES DBA DROP ANY INDEX YES DBA DROP ANY LIBRARY YES DBA DROP ANY MATERIALIZED VIEW YES DBA DROP ANY PROCEDURE YES DBA DROP ANY ROLE YES DBA DROP ANY SEQUENCE YES DBA DROP ANY SYNONYM YES DBA DROP ANY TABLE YES DBA DROP ANY TRIGGER YES DBA DROP ANY VIEW YES DBA DROP PROFILE YES DBA DROP PUBLIC DATABASE LINK YES DBA DROP PUBLIC SYNONYM YES DBA DROP TABLESPACE YES DBA DROP USER YES DBA EXECUTE ANY LIBRARY YES DBA EXECUTE ANY PROCEDURE YES DBA GRANT ANY OBJECT PRIVILEGE YES DBA GRANT ANY PRIVILEGE YES DBA GRANT ANY ROLE YES DBA INSERT ANY TABLE YES DBA LOCK ANY TABLE YES DBA SELECT ANY DICTIONARY YES DBA SELECT ANY SEQUENCE YES DBA SELECT ANY TABLE YES DBA SYSDBA YES DBA UPDATE ANY TABLE YES RESOURCE CREATE PROCEDURE NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TABLE NO RESOURCE CREATE TRIGGER NO 80 rows selected.