오라클에서 권한을 부여할때 롤을 사용할때도 있고 사용하지 않는 경우도 있다.
쉽게 말해서 롤은 권한을 담고있는 폴더라고 생각하면된다. 즉 권한을 담을수있는 그릇, 쟁반과도 같은 의미이다.
리눅스 -> 그룹과 비슷하다 (단순, 반복적인 권한은 롤을 만들어 사용한다.) 편리하게 권한을 부여하기 위해 만들어진것이다.
사용 목적으로는 롤을 이용하여 한꺼번에 많은 권한을 편리하게 부여하기 위해서 만든 것이다.
형식_
ex) grant create table, create sequence,,,,to 롤이름
dba_roles -> 권한 -> 시스템 계정으로만 사용가능
dba_XXX, sys_XXX, v$_XXXX로 시작하는 테이블은 관리자만 볼수있는 테이블종류이다.
문제_
시스템에서 현재 사용중인 롤을 조회하는 SQL을 작성해 보자. 반드시 시스템 계정에서만 조회가 가능하다.
SQL> show user
USER은 "SYS"입니다
SQL> select * from dba_roles;
ROLE (롤이름) PASSWORD AUTHENTICAT
------------------------------ -------- -----------
CONNECT -> create session (접속권한) NO NONE
RESOURCE -> create table 권한외~ NO NONE
DBA ->관리자롤 NO NONE
SELECT_CATALOG_ROLE NO NONE
EXECUTE_CATALOG_ROLE NO NONE
DELETE_CATALOG_ROLE NO NONE
EXP_FULL_DATABASE NO NONE
IMP_FULL_DATABASE NO NONE
LOGSTDBY_ADMINISTRATOR NO NONE
DBFS_ROLE NO NONE
AQ_ADMINISTRATOR_ROLE NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
AQ_USER_ROLE NO NONE
DATAPUMP_EXP_FULL_DATABASE NO NONE
DATAPUMP_IMP_FULL_DATABASE NO NONE
ADM_PARALLEL_EXECUTE_TASK NO NONE
GATHER_SYSTEM_STATISTICS NO NONE
JAVA_DEPLOY NO NONE
RECOVERY_CATALOG_OWNER NO NONE
SCHEDULER_ADMIN NO NONE
HS_ADMIN_SELECT_ROLE NO NONE
HS_ADMIN_EXECUTE_ROLE NO NONE
HS_ADMIN_ROLE NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
GLOBAL_AQ_USER_ROLE GLOBAL GLOBAL
OEM_ADVISOR NO NONE
OEM_MONITOR NO NONE
WM_ADMIN_ROLE NO NONE
JAVAUSERPRIV NO NONE
JAVAIDPRIV NO NONE
JAVASYSPRIV NO NONE
JAVADEBUGPRIV NO NONE
EJBCLIENT NO NONE
JMXSERVER NO NONE
JAVA_ADMIN NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
CTXAPP NO NONE
XDBADMIN NO NONE
XDB_SET_INVOKER NO NONE
AUTHENTICATEDUSER NO NONE
XDB_WEBSERVICES NO NONE
XDB_WEBSERVICES_WITH_PUBLIC NO NONE
XDB_WEBSERVICES_OVER_HTTP NO NONE
ORDADMIN NO NONE
OLAPI_TRACE_USER NO NONE
OLAP_XS_ADMIN NO NONE
OWB_USER NO NONE
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
OLAP_DBA NO NONE
CWM_USER NO NONE
OLAP_USER NO NONE
SPATIAL_WFS_ADMIN NO NONE
WFS_USR_ROLE YES PASSWORD
SPATIAL_CSW_ADMIN YES PASSWORD
CSW_USR_ROLE YES PASSWORD
MGMT_USER NO NONE
APEX_ADMINISTRATOR_ROLE NO NONE
OWB$CLIENT YES PASSWORD
OWB_DESIGNCENTER_VIEW NO NONE
55 개의 행이 선택되었습니다.
[있다가 하나 추가를 한다면=>+1=56]
확인한 결과 롤의 종류는 모두 55개 이다.
그리고 우리는 조회한 롤 내부에 어떤 권한들이 포함되어있는지 모두 보기는 어렵지만, 특정롤에 포함된 권한이 무엇이있는지 확인해 보도록 해보자.
형식_
save를 이용(뷰) => dba_sys_privs
SQL> select * from dba_sys_privs
2 where grantee='CONNECT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
CONNECT롤을 조회해본결과 한가지의 권한만 부여가 되어있다.
다른 롤을 조회할때 보다 편리하게 조회를 할수있도록 동적인 코드로 만들어 보자
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 select * from dba_sys_privs
2* where grantee=upper('&rolename')
이렇게 만들었고, 해당 구문을 이용 resource 롤을 보다 편리하게 조회를 해본 결과는 아래와 같다.
SQL> /
rolename의 값을 입력하십시오: resource
구 2: where grantee=upper('&rolename')
신 2: where grantee=upper('resource')
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 개의 행이 선택되었습니다.
DBA 롤을 조회해보자면
SQL> /
rolename의 값을 입력하십시오: dba
구 2: where grantee=upper('&rolename')
신 2: where grantee=upper('dba')
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY CUBE BUILD PROCESS YES
DBA CREATE CUBE YES
DBA ALTER ANY CUBE DIMENSION YES
DBA ALTER ANY MINING MODEL YES
DBA DROP ANY MINING MODEL YES
DBA DROP ANY EDITION YES
DBA CHANGE NOTIFICATION YES
DBA ADMINISTER ANY SQL TUNING SET YES
DBA ALTER ANY SQL PROFILE YES
DBA CREATE RULE YES
DBA EXPORT FULL DATABASE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA EXECUTE ANY EVALUATION CONTEXT YES
DBA DEQUEUE ANY QUEUE YES
DBA DROP ANY INDEXTYPE YES
DBA ALTER ANY INDEXTYPE YES
DBA EXECUTE ANY LIBRARY YES
DBA CREATE ANY LIBRARY YES
DBA CREATE ANY DIRECTORY YES
DBA ALTER PROFILE YES
DBA EXECUTE ANY PROCEDURE YES
DBA CREATE ROLE YES
DBA SELECT ANY SEQUENCE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY INDEX YES
DBA UPDATE ANY TABLE YES
DBA INSERT ANY TABLE YES
DBA SELECT ANY TABLE YES
DBA DROP ROLLBACK SEGMENT YES
DBA BECOME USER YES
DBA DROP TABLESPACE YES
DBA ALTER SESSION YES
DBA CREATE SESSION YES
DBA DROP ANY MEASURE FOLDER YES
DBA SELECT ANY CUBE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER ANY CUBE YES
DBA CREATE ANY ASSEMBLY YES
DBA ALTER ANY EDITION YES
DBA ANALYZE ANY DICTIONARY YES
DBA ALTER ANY RULE SET YES
DBA CREATE RULE SET YES
DBA DEBUG ANY PROCEDURE YES
DBA CREATE DIMENSION YES
DBA ALTER ANY LIBRARY YES
DBA UNDER ANY TYPE YES
DBA DROP ANY MATERIALIZED VIEW YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY TRIGGER YES
DBA ALTER ANY PROCEDURE YES
DBA FORCE ANY TRANSACTION YES
DBA ALTER DATABASE YES
DBA DELETE ANY TABLE YES
DBA ALTER ROLLBACK SEGMENT YES
DBA UPDATE ANY CUBE DIMENSION YES
DBA CREATE ANY CUBE BUILD PROCESS YES
DBA CREATE CUBE DIMENSION YES
DBA ALTER ANY ASSEMBLY YES
DBA CREATE ASSEMBLY YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY EDITION YES
DBA EXECUTE ANY PROGRAM YES
DBA EXECUTE ANY RULE YES
DBA IMPORT FULL DATABASE YES
DBA EXECUTE ANY RULE SET YES
DBA CREATE ANY RULE SET YES
DBA FLASHBACK ANY TABLE YES
DBA RESUMABLE YES
DBA ADMINISTER DATABASE TRIGGER YES
DBA CREATE ANY OUTLINE YES
DBA ALTER ANY DIMENSION YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY DIMENSION YES
DBA EXECUTE ANY OPERATOR YES
DBA CREATE TYPE YES
DBA CREATE TRIGGER YES
DBA GRANT ANY ROLE YES
DBA DROP ANY VIEW YES
DBA CREATE VIEW YES
DBA LOCK ANY TABLE YES
DBA ALTER USER YES
DBA CREATE USER YES
DBA ALTER TABLESPACE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE TABLESPACE YES
DBA RESTRICTED SESSION YES
DBA UPDATE ANY CUBE BUILD PROCESS YES
DBA DROP ANY CUBE YES
DBA INSERT ANY CUBE DIMENSION YES
DBA CREATE MINING MODEL YES
DBA CREATE ANY JOB YES
DBA CREATE JOB YES
DBA CREATE ANY RULE YES
DBA DROP ANY EVALUATION CONTEXT YES
DBA CREATE ANY EVALUATION CONTEXT YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE EVALUATION CONTEXT YES
DBA GRANT ANY OBJECT PRIVILEGE YES
DBA SELECT ANY DICTIONARY YES
DBA DROP ANY DIMENSION YES
DBA UNDER ANY TABLE YES
DBA CREATE INDEXTYPE YES
DBA CREATE ANY OPERATOR YES
DBA DROP ANY LIBRARY YES
DBA ANALYZE ANY YES
DBA ALTER ANY ROLE YES
DBA CREATE ANY SEQUENCE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY INDEX YES
DBA CREATE ANY TABLE YES
DBA DELETE ANY MEASURE FOLDER YES
DBA CREATE ANY MEASURE FOLDER YES
DBA SELECT ANY MINING MODEL YES
DBA CREATE ANY MINING MODEL YES
DBA MANAGE FILE GROUP YES
DBA MANAGE SCHEDULER YES
DBA ADMINISTER RESOURCE MANAGER YES
DBA ALTER ANY OUTLINE YES
DBA DROP ANY CONTEXT YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA EXECUTE ANY INDEXTYPE YES
DBA UNDER ANY VIEW YES
DBA DROP ANY TYPE YES
DBA ALTER ANY TYPE YES
DBA ALTER ANY MATERIALIZED VIEW YES
DBA CREATE PROFILE YES
DBA DROP PUBLIC DATABASE LINK YES
DBA ALTER ANY INDEX YES
DBA CREATE CLUSTER YES
DBA COMMENT ANY TABLE YES
DBA DROP ANY TABLE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ROLLBACK SEGMENT YES
DBA UNLIMITED TABLESPACE YES
DBA AUDIT SYSTEM YES
DBA ALTER SYSTEM YES
DBA SELECT ANY CUBE DIMENSION YES
DBA DELETE ANY CUBE DIMENSION YES
DBA CREATE ANY CUBE DIMENSION YES
DBA COMMENT ANY MINING MODEL YES
DBA EXECUTE ASSEMBLY YES
DBA EXECUTE ANY ASSEMBLY YES
DBA MANAGE ANY FILE GROUP YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA EXECUTE ANY CLASS YES
DBA DROP ANY RULE SET YES
DBA DEBUG CONNECT SESSION YES
DBA ON COMMIT REFRESH YES
DBA ENQUEUE ANY QUEUE YES
DBA CREATE ANY INDEXTYPE YES
DBA ALTER ANY OPERATOR YES
DBA CREATE ANY TYPE YES
DBA DROP ANY DIRECTORY YES
DBA ALTER RESOURCE COST YES
DBA CREATE ANY PROCEDURE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE PROCEDURE YES
DBA FORCE TRANSACTION YES
DBA ALTER ANY SEQUENCE YES
DBA CREATE SEQUENCE YES
DBA CREATE ANY VIEW YES
DBA DROP PUBLIC SYNONYM YES
DBA DROP ANY SYNONYM YES
DBA CREATE ANY CLUSTER YES
DBA BACKUP ANY TABLE YES
DBA CREATE TABLE YES
DBA ADMINISTER SQL MANAGEMENT OBJECT YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA INSERT ANY MEASURE FOLDER YES
DBA UPDATE ANY CUBE YES
DBA ADMINISTER SQL TUNING SET YES
DBA MERGE ANY VIEW YES
DBA DROP ANY OUTLINE YES
DBA CREATE OPERATOR YES
DBA CREATE LIBRARY YES
DBA GRANT ANY PRIVILEGE YES
DBA DROP PROFILE YES
DBA ALTER ANY TRIGGER YES
DBA CREATE ANY TRIGGER YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY PROCEDURE YES
DBA AUDIT ANY YES
DBA DROP ANY ROLE YES
DBA DROP ANY SEQUENCE YES
DBA CREATE PUBLIC SYNONYM YES
DBA CREATE SYNONYM YES
DBA DROP ANY CLUSTER YES
DBA ALTER ANY TABLE YES
DBA FLASHBACK ARCHIVE ADMINISTER YES
DBA CREATE CUBE BUILD PROCESS YES
DBA CREATE MEASURE FOLDER YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY CUBE YES
DBA DROP ANY CUBE DIMENSION YES
DBA DROP ANY ASSEMBLY YES
DBA CREATE EXTERNAL JOB YES
DBA READ ANY FILE GROUP YES
DBA CREATE ANY SQL PROFILE YES
DBA DROP ANY SQL PROFILE YES
DBA SELECT ANY TRANSACTION YES
DBA ADVISOR YES
DBA DROP ANY RULE YES
DBA ALTER ANY RULE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER ANY EVALUATION CONTEXT YES
DBA CREATE ANY CONTEXT YES
DBA MANAGE ANY QUEUE YES
DBA GLOBAL QUERY REWRITE YES
DBA QUERY REWRITE YES
DBA DROP ANY OPERATOR YES
DBA EXECUTE ANY TYPE YES
DBA CREATE ANY MATERIALIZED VIEW YES
DBA CREATE MATERIALIZED VIEW YES
DBA CREATE PUBLIC DATABASE LINK YES
DBA CREATE DATABASE LINK YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY SYNONYM YES
DBA ALTER ANY CLUSTER YES
DBA DROP USER YES
DBA MANAGE TABLESPACE YES
202 개의 행이 선택되었습니다.
'Oracle > Role' 카테고리의 다른 글
| 데이터 베이스_ 오라클 [Role] 삭제 및 권한위임 (0) | 2019.07.04 |
|---|---|
| 데이터 베이스_ 오라클 [Role] 생성 및 권한 부여 (0) | 2019.07.04 |
댓글