Document Type | Technical Information
Category | Administration
Applicable Product Version | 7FS02
Document Number | TADTI165
Overview
This document allows you to query the Privileges granted to Users in Tibero, the Roles assigned to Users, and the Privileges granted to Roles.
Method
1. Query Privileges Granted to a User
SQL> SET LINESIZE 150
SQL> SET PAGESIZE 200
SQL> COL USERNAME FOR A20
SQL> COL NAME FOR A40
SQL> SELECT C.USERNAME, B.NAME FROM _DD_SYSAUTH A, SYSTEM_PRIVILEGES B,DBA_USERS C
WHERE A.PRIV_NO = B.PRIV_NO
AND A.GRANTEE_ID = C.USER_ID
AND C.USERNAME IN ('SYS');
USERNAME NAME
-------------------- ----------------------------------------
SYS ALTER SYSTEM
SYS CREATE SESSION
SYS ALTER SESSION
SYS CREATE TABLESPACE
SYS ALTER TABLESPACE
SYS DROP TABLESPACE
SYS CREATE USER
SYS ALTER USER
SYS DROP USER
SYS ALTER ROLLBACK SEGMENT
SYS CREATE TABLE
SYS CREATE ANY TABLE
SYS ALTER ANY TABLE
SYS DROP ANY TABLE
SYS LOCK ANY TABLE
SYS COMMENT ANY TABLE
SYS SELECT ANY TABLE
SYS INSERT ANY TABLE
SYS UPDATE ANY TABLE
SYS DELETE ANY TABLE
SYS CREATE ANY INDEX
SYS ALTER ANY INDEX
SYS DROP ANY INDEX
SYS CREATE SYNONYM
SYS CREATE ANY SYNONYM
SYS DROP ANY SYNONYM
SYS SYSDBA
SYS CREATE PUBLIC SYNONYM
SYS DROP PUBLIC SYNONYM
SYS CREATE VIEW
SYS CREATE ANY VIEW
SYS DROP ANY VIEW
SYS CREATE MATERIALIZED VIEW
SYS CREATE ANY MATERIALIZED VIEW
SYS ALTER ANY MATERIALIZED VIEW
SYS DROP ANY MATERIALIZED VIEW
SYS CREATE SEQUENCE
SYS CREATE ANY SEQUENCE
SYS ALTER ANY SEQUENCE
SYS DROP ANY SEQUENCE
SYS SELECT ANY SEQUENCE
SYS CREATE ROLE
SYS DROP ANY ROLE
SYS GRANT ANY ROLE
SYS ALTER ANY ROLE
SYS ALTER DATABASE
SYS CREATE PROCEDURE
SYS CREATE ANY PROCEDURE
SYS ALTER ANY PROCEDURE
SYS DROP ANY PROCEDURE
SYS EXECUTE ANY PROCEDURE
SYS CREATE TRIGGER
SYS CREATE ANY TRIGGER
SYS ALTER ANY TRIGGER
SYS DROP ANY TRIGGER
SYS ANALYZE ANY
SYS GRANT ANY PRIVILEGE
SYS GRANT ANY OBJECT PRIVILEGE
SYS CREATE DATABASE LINK
SYS CREATE PUBLIC DATABASE LINK
SYS CREATE ANY DATABASE LINK
SYS DROP PUBLIC DATABASE LINK
SYS DROP ANY DATABASE LINK
SYS CREATE ANY DIRECTORY
SYS DROP ANY DIRECTORY
SYS AUDIT SYSTEM
SYS AUDIT ANY
SYS CREATE LIBRARY
SYS CREATE ANY LIBRARY
SYS DROP ANY LIBRARY
SYS EXECUTE ANY LIBRARY
SYS CREATE PROFILE
SYS ALTER PROFILE
SYS DROP PROFILE
SYS CREATE TYPE
SYS CREATE ANY TYPE
SYS DROP ANY TYPE
SYS ALTER ANY TYPE
SYS ENQUEUE ANY QUEUE
SYS DEQUEUE ANY QUEUE
SYS MANAGE ANY QUEUE
SYS TRUNCATE ANY TABLE
SYS SELECT ANY DICTIONARY
SYS CREATE BTIP
SYS CREATE TIP
SYS CREATE ANY CONTEXT
SYS DROP ANY CONTEXT
SYS FLASHBACK ANY TABLE
SYS CREATE SQL TRANSLATION PROFILE
SYS CREATE ANY SQL TRANSLATION PROFILE
SYS ALTER ANY SQL TRANSLATION PROFILE
SYS DROP ANY SQL TRANSLATION PROFILE
SYS ALTER RMGR
SYS GRANT DEFAULT ROLE
SYS CREATE OPERATOR
SYS CREATE ANY OPERATOR
SYS ALTER ANY OPERATOR
SYS DROP ANY OPERATOR
SYS EXECUTE ANY OPERATOR
SYS CREATE INDEXTYPE
SYS CREATE ANY INDEXTYPE
SYS ALTER ANY INDEXTYPE
SYS DROP ANY INDEXTYPE
SYS EXECUTE ANY INDEXTYPE
SYS ADMINISTER DATABASE TRIGGER
SYS UNLIMITED TABLESPACE
SYS CREATE JOB
SYS CREATE ANY JOB
108 rows selected.2. Query Roles Granted to a User
SQL> COL GRANTEE FOR A20
SQL> COL GRANTED_ROLE FOR A30
SQL> SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN ('SYS');
GRANTEE GRANTED_ROLE
-------------------- ------------------------------
SYS DBA
SYS CONNECT
SYS RESOURCE
SYS SELECT_CATALOG_ROLE
SYS EXP_FULL_DATABASE
SYS IMP_FULL_DATABASE
SYS SCHEDULER_ADMIN
SYS HS_ADMIN_ROLE
SYS LBAC_DBA
9 rows selected.3. Query Privileges Granted to a Role
SQL> COL GRANTEE FOR A20
SQL> COL PRIVILEGE FOR A40
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('DBA') ;
GRANTEE PRIVILEGE
-------------------- ----------------------------------------
DBA ALTER SYSTEM
DBA CREATE SESSION
DBA ALTER SESSION
DBA CREATE TABLESPACE
DBA ALTER TABLESPACE
DBA DROP TABLESPACE
DBA CREATE USER
DBA ALTER USER
DBA DROP USER
DBA ALTER ROLLBACK SEGMENT
DBA CREATE TABLE
DBA CREATE ANY TABLE
DBA ALTER ANY TABLE
DBA DROP ANY TABLE
DBA LOCK ANY TABLE
DBA COMMENT ANY TABLE
DBA SELECT ANY TABLE
DBA INSERT ANY TABLE
DBA UPDATE ANY TABLE
DBA DELETE ANY TABLE
DBA CREATE ANY INDEX
DBA ALTER ANY INDEX
DBA DROP ANY INDEX
DBA CREATE SYNONYM
DBA CREATE ANY SYNONYM
DBA DROP ANY SYNONYM
DBA SYSDBA
DBA CREATE PUBLIC SYNONYM
DBA DROP PUBLIC SYNONYM
DBA CREATE VIEW
DBA CREATE ANY VIEW
DBA DROP ANY VIEW
DBA CREATE MATERIALIZED VIEW
DBA CREATE ANY MATERIALIZED VIEW
DBA ALTER ANY MATERIALIZED VIEW
DBA DROP ANY MATERIALIZED VIEW
DBA CREATE SEQUENCE
DBA CREATE ANY SEQUENCE
DBA ALTER ANY SEQUENCE
DBA DROP ANY SEQUENCE
DBA SELECT ANY SEQUENCE
DBA CREATE ROLE
DBA DROP ANY ROLE
DBA GRANT ANY ROLE
DBA ALTER ANY ROLE
DBA ALTER DATABASE
DBA CREATE PROCEDURE
DBA CREATE ANY PROCEDURE
DBA ALTER ANY PROCEDURE
DBA DROP ANY PROCEDURE
DBA EXECUTE ANY PROCEDURE
DBA CREATE TRIGGER
DBA CREATE ANY TRIGGER
DBA ALTER ANY TRIGGER
DBA DROP ANY TRIGGER
DBA ANALYZE ANY
DBA GRANT ANY PRIVILEGE
DBA GRANT ANY OBJECT PRIVILEGE
DBA CREATE DATABASE LINK
DBA CREATE PUBLIC DATABASE LINK
DBA CREATE ANY DATABASE LINK
DBA DROP PUBLIC DATABASE LINK
DBA DROP ANY DATABASE LINK
DBA CREATE ANY DIRECTORY
DBA DROP ANY DIRECTORY
DBA AUDIT SYSTEM
DBA AUDIT ANY
DBA CREATE LIBRARY
DBA CREATE ANY LIBRARY
DBA DROP ANY LIBRARY
DBA EXECUTE ANY LIBRARY
DBA CREATE PROFILE
DBA ALTER PROFILE
DBA DROP PROFILE
DBA CREATE TYPE
DBA CREATE ANY TYPE
DBA DROP ANY TYPE
DBA ALTER ANY TYPE
DBA ENQUEUE ANY QUEUE
DBA DEQUEUE ANY QUEUE
DBA MANAGE ANY QUEUE
DBA SELECT ANY DICTIONARY
DBA CREATE BTIP
DBA CREATE TIP
DBA CREATE ANY CONTEXT
DBA DROP ANY CONTEXT
DBA FLASHBACK ANY TABLE
DBA CREATE SQL TRANSLATION PROFILE
DBA CREATE ANY SQL TRANSLATION PROFILE
DBA ALTER ANY SQL TRANSLATION PROFILE
DBA DROP ANY SQL TRANSLATION PROFILE
DBA ALTER RMGR
DBA GRANT DEFAULT ROLE
DBA CREATE OPERATOR
DBA CREATE ANY OPERATOR
DBA ALTER ANY OPERATOR
DBA DROP ANY OPERATOR
DBA EXECUTE ANY OPERATOR
DBA CREATE INDEXTYPE
DBA CREATE ANY INDEXTYPE
DBA ALTER ANY INDEXTYPE
DBA DROP ANY INDEXTYPE
DBA EXECUTE ANY INDEXTYPE
DBA ADMINISTER DATABASE TRIGGER
DBA CREATE JOB
DBA CREATE ANY JOB
106 rows selected.4. Generate Script for Privileges of Other Users on User's Objects (Applicable for Tibero/Oracle)
SQL> SELECT 'GRANT '| |PRIVILEGE| |' ON '| |OWNER| |'.'| |TABLE_NAME| |' TO '| |GRANTEE| |';'
2 FROM DBA_TAB_PRIVS
3 WHERE OWNER IN ('SCOTT')
4 ORDER BY OWNER, TABLE_NAME, GRANTEE, PRIVILEGE;
'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||';'
------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT ALTER ON SCOTT.BONUS TO HR;
GRANT ALTER ON SCOTT.BONUS TO PROD;
GRANT DELETE ON SCOTT.DEPT TO HR;
GRANT SELECT ON SCOTT.SALGRADE TO HR;
GRANT SELECT ON SCOTT.SALGRADE TO PROD;
5 rows selected.
5. Generate Script for Object Privileges of Other Users Held by User (Applicable for Tibero/Oracle)
SQL> SELECT 'GRANT '| |PRIVILEGE| |' ON '| |OWNER| |'.'| |TABLE_NAME| |' TO '| |GRANTEE| |';'
2 FROM DBA_TAB_PRIVS
3 WHERE GRANTEE IN ('HR')
4 ORDER BY GRANTEE,OWNER,TABLE_NAME,PRIVILEGE;
'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||';'
------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT ALTER ON SCOTT.BONUS TO HR;
GRANT DELETE ON SCOTT.DEPT TO HR;
GRANT SELECT ON SCOTT.SALGRADE TO HR;
3 rows selected.