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;