Tibero에서 User에게 부여된 Privileges 와 Role, Role에 부여된 Privileges를 조회할 수 있습니다.
User에게 부여된 Privileges 조회
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
User에게 부여된 Role 조회
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 HS_ADMIN_ROLE
SYS PLUSTRACE
Role에 부여된 Privileges 조회
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
