Skip to main content

Search

Welcome to Tibero GTS!

Viewing Privileges Granted to Tibero Roles

Document Type | Technical Information

Category | Administration

Document Number | TADTI082

 

Overview

This explains how to check the privileges granted to a Role in Tibero.
 
 

Method

Querying 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

 

Generating Scripts for Other Users' Privileges on a User's Objects

(Can be created in Tibero/Oracle)
SQL> SELECT 'GRANT '| |PRIVILEGE| |' ON '| |OWNER| |'.'| |TABLE_NAME| |' TO '| |GRANTEE| |';' 
			FROM DBA_TAB_PRIVS
				WHERE OWNER IN ('SCOTT')
				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;

 

Generating Scripts for a User's Privileges on Other Users' Objects

(Can be created in Tibero/Oracle)
SQL> SELECT 'GRANT '| |PRIVILEGE| |' ON '| |OWNER| |'.'| |TABLE_NAME| |' TO '| |GRANTEE| |';' 
			FROM DBA_TAB_PRIVS
			WHERE GRANTEE IN ('HR')
			ORDER BY GRANTEE,OWNER,TABLE_NAME,PRIVILEGE;
			
'GRANT'| |PRIVILEGE| |'ON'| |OWNER| |'.'| |TABLE_NAME| |'TO'| |GRANTEE| |';'

GRANT SELECT ONORDDATA.ORDDCM_ANON_ATTRS TO HR;
GRANT ALTER ON SCOTT.BONUS TO HR;
GRANT DELETE ON SCOTT.DEPT TO HR;
GRANT SELECT ON SCOTT.SALGRADE TO HR;
GRANT INSERT ON SYSMAN.MGMT_COLLECTION_TEMPLATE_CREDS TO HR; 
GRANT DELETE ON SYSMAN.MGMT_HOST_CREDENTIALS TO HR;
GRANT SELECT ON SYSMAN.MGMT_USER_CAS TO HR;

 

 

 

Related to