문서유형ㅣ기술정보
분야ㅣ관리/환경설정
문서번호ㅣTADTI090
개요
티베로 DBMS 에 사용되는 각 Role이 소유한 권한(PRIVILEGE)을 조회하는 방법을 설명합니다.
방법
Role에 속한 권한 조회 권한
SYS 계정을 제외한 일반 계정은 다음과 같은 권한이 부여되어야 Role에 속한 권한을 확인할 수 있습니다.
GRANT SELECT ON DBA_ROLES TO USERNAME; GRANT SELECT ON DBA_SYS_PRIVS TO USERNAME;
Role에 속한 권한 조회
아래와 같은 SQL을 통해 Role에 속한 권한들을 조회 할 수 있습니다.
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;
Role에 속한 권한 조회 예시
테스트서버를 통해 직접 조회해 본 결과값은 아래와 같습니다.
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.