권한과 롤

오라클은 유저에게 권한을 부여하여 다른 유저가 소유하고 있는 특정 객체(Object)를 액세스하여 조회하거나 조작(DML) 할수 있게 해줍니다.

권한은 크게 객체 권한(Object Privileges)와 시스템 권한(System Privileges)으로 나누어 집니다.

 

객체 권한(Object Privileges)란?

객체의 소유자는 해당 객체에 대한 모든 권한을 가지고 있으며, 이 권한은 취소될 수 없습니다.

테이블이나 뷰,시퀀스,프로시저,함수 또는 패키지 중 지정된 한 객체에 특별한 작업을 수행할 수 있게 합니다.

객체 소유자는 다른 사용자에게 특정 객체권한을 부여 할 수 있습니다.

ADMIN권한이 있는 사용자는 권한이 부여 된 객체를 소유하지 않은 사용자로부터 권한을 부여 및 취소할 수 있습니다.

PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 합니다.

WITH GRANT OPTION옵션은 ROLE에 권한을 부여할 때는 사용할 수 없습니다.

 

사용 방법

--권한 부여 예제
Scott유저에게emp 테이블을 Select,Insert할수 있는 권한을 부여
Scott유저는 다른 유저에게 그 권한을 부여할 수 있음

SQL> grant SELECT,INSERT
	on EMP
    TO SCOTT
    WITH GRANT OPTION;

--권한 회수 예제
SCOTT 유저에게 부여한 emp 테이블에 대한 SELECT,INSERT 권한을 회수
만약 SCOTT유저가 다른 유저에게 SELECT,INSERT권한을 부여하였으면 그 권한들도 회수 됌

SQL> revoke SELECT,INSERT
	on EMP
    from SCOTT;

 

권한	오브젝트 타입	설명
DELETE	Table	테이블 삭제를 가능하게 함
EXECUTE	PL/SQL package, procedure or function	PL/SQL 패키지,프로시저,함수를 직접 실행할 수 있게 함
FLUSH	Cache group	사용자가 캐시 그룹을 비울 수 있도록 함
INDEX	Table or materialized view	사용자가 테이블 또는 M View에 인덱스를 생성할 수 있음
INSERT	Table or synonym	테이블 또는 시노님을 통한 테이블에 삽입을 할 수 있게 함
LOAD	Cache group	사용자가 캐시 그룹을 로드할 수 있게 함
REFERENCES	Table or materialized view	사용자가 테이블 또는 M View에 외래키 종속성을 생성할 수 있게 함 이 권한은 부모 테이블에 암묵적으로 SELECT 권한을 부여 함
REFRESH	Cache group	사용자가 캐시 그룹을 새로 고칠수 있게함
SELECT	Table, sequence, view, materialized view, or synonym	사용자가 테이블,시퀀스,뷰,M View 또는 시노님에서 조회할 수 있게 함 SELECT 권한은 사용자가 시퀀스에 대한 모든 작업을 수행 할 수 있도록 함 사용자는 원래 테이블에 대한 SELECT 권한을 명시적으로 부여하지 않고도 시노님 또는 뷰에 대한 SELECT 권한을 부여 받을 수 있음
UNLOAD	Cache group	사용자가 캐시 그룹을 언로드 할 수 있게함
UPDATE	Table	테이블을 업데이트 할 수 있게 함

 

시스템 권한 (System Privileges)

시스템 권한은 데이터베이스에서 특정 작업을 수행할 수 있도록 합니다.

권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미합니다.

객체의 소유자는 해당 객체에 대한 모든 객체 권한을 가지며 이러한 권한은 취소 할 수 없습니다.

인스턴스 관리자 또는 ADMIN 권한이 있는 사용자만 시스템 권한을 부여하거나 취소할 수 있습니다.

 

사용방법

--SYS 권한으로 접속합니다
SQL> conn / as sysdba

--SCOTT 사용자에게 사용자를 생성, 수정, 삭제할 수 있는 권한을 부여함
--SCOTT 사용자는 다른 사용자에게 그 권한을 부여할 수 있도록 함

SQL> grant CREATE USER,ALTER USER, DROP USER 
	to SCOTT
    with admin option;
    

--SCOTT 사용자에게 부여했던 권한들을 회수함
--SCOTT 사용자가 다른 사용자에게 이 권한을 부여하였더라도 회수 시 같이 회수 되지 않음
SQL> revoke CREATE USER,ALTER USER, DROP USER
	from SCOTT;
   

 

권한	설명
ADMIN	사용자가 체크포인트,백업,마이그레이션, 사용자 생성 및 삭제를 포함한 관리 작업을 수행할 수 있음
ALTER ANY CACHE GROUP	데이터에비스의 캐시 그룹을 변경할 수 있음
ALTER ANY INDEX	데이터베이스의 모든 인덱스를 변경할 수 있음 Note: ALTER INDEX문은 없습니다.
ALTER ANY MATERIALIZED VIEW	데이터베이스의 모든 M View를 변경할 수 있음 Note: ALTER MATERIALIZED VIEW문은 없습니다.
ALTER ANY PROCEDURE	데이터베이스의 PL/SQL 프로시저,함수,패키지를 변경할 수 있음
ALTER ANY SEQUENCE	데이터베이스의 모든 시퀀스를 변경할 수 있음 Note: ALTER SEQUENCE문은 없습니다.
ALTER ANY TABLE	데이터베이스의 모든 테이블을 변경할 수 있음
ALTER ANY VIEW	데이터베이스의 모든 뷰를 변경할 수 있음 Note: ALTER VIEW문은 없습니다.
CACHE_MANAGER	사용자가 캐시 그룹과 관련된 작업을 수행할 수 있음
CREATE ANY CACHE GROUP	데이터베이스의 모든 사용자가 소유한 캐시 그룹을 만들 수 있음
CREATE ANY INDEX	데이터베이스의 모든 테이블 또는 M View에 인덱스를 생성할 수 있음
CREATE ANY MATERIALIZED VIEW	데이터베이스의 모든 사용자가 소유한 M View를 생성할 수 있음
CREATE ANY PROCEDURE	데이터베이스의 모든 사용자가 소유한 PL/SQL 프로시저,함수 또는 패키지를 생성할 수 있음
CREATE ANY SEQUENCE	데이터베이스의 모든 사용자가 소유한 시퀀스를 만들 수 있음
CREATE ANY SYNONYM	데이터베이스의 모든 사용자가 소유한 private 시노님을 생성 할 수 있음
CREATE ANY TABLE	데이터베이스의 모든 사용자가 소유한 테이블을 만들 수 있음
CREATE ANY VIEW	데이터베이스의 모든 사용자가 소유한 뷰를 만들 수 있음
CREATE CACHE GROUP	사용자가 해당 사용자가 소유한 캐시 그룹을 만들 수 있음
CREATE MATERIALIZED VIEW	사용자가 해당 사용자가 소유한 M View를 만들 수있음
CREATE PROCEDURE	사용자가 해당 사용자가 소유한 PL/SQL 프로시저,함수 또는 패키지를 생성할 수 있음
CREATE PUBLIC SYNONYM	Public 시노님을 생성할 수 있음
CREATE SEQUENCE	사용자가 해당 사용자가 소유한 시퀀스를 만들 수있음
CREATE SESSION	데이터베이스에 대한 유저 접속을 할 수 있게 함
CREATE SYNONYM	private 시노님을 생성할 수 있음
CREATE TABLE	사용자가 해당 사용자가 소유한 테이블을 만들 수 있음
CREATE VIEW	사용자가 해당 사용자가 소유한 뷰를 만들 수 있음
DELETE ANY TABLE	데이터베이스의 모든 테이블에서 삭제(delete)할 수 있음
DROP ANY CACHE GROUP	데이터베이스에서 캐시 그룹을 삭제할 수 있음
DROP ANY INDEX	데이터베이스의 모든 인덱스를 삭제할 수 있음
DROP ANY MATERIALIZED VIEW	데이터베이스에서 M View를 삭제할 수 있음
DROP ANY PROCEDURE	데이터베이스에서 PL/SQL 프로시저,함수 또는 패키지를 삭제할 수 있음
DROP ANY SEQUENCE	데이터베이스에서 시퀀스를 삭제할 수 있음
DROP ANY SYNONYM	데이터베이스에서 모든사용자가 소유한 시노님을 삭제할 수 있음
DROP ANY TABLE	데이터베이스에서 모든사용자가 소유한 테이블을 삭제(Drop)할 수있음
DROP ANY VIEW	데이터베이스에서 뷰를 삭제할 수 있음
DROP PUBLIC SYNONYM	Public 시노님을 삭제할 수 있음
EXECUTE ANY PROCEDURE	데이터베이스에서 PL/SQL 프로시저,함수 또는 패키지를 실행할 수 있음
FLUSH ANY CACHE GROUP	데이터베이스의 캐시 그룹을 플러시 할 수 있음
INSERT ANY TABLE	데이터베이스의 모든 테이블에 삽입할 수 있음. 또한 사용자는 Public 또는 Private 시노님을 사용하여 해당 테이블에 삽입할 수 있음
LOAD ANY CACHE GROUP	데이터베이스의 모든 캐시 그룹을 로드할 수 있음
REFRESH ANY CACHE GROUP	데이터베이스의 캐시 그룹을 refresh할 수 있음
SELECT ANY SEQUENCE	데이터베이스에 있는 시퀀스
SELECT ANY TABLE	데이터베이스의 모든 테이블,뷰,M view, 시노님에서 조회할 수 있음
UNLOAD ANY CACHE GROUP	사용자가 데이터베이스에서 캐시 그룹을 언로드 할 수 있음
UPDATE ANY TABLE	데이터베이스의 모든 테이블 또는 시노님을 업데이트 할 수 있음
XLA	사용자가 XLA판독기로 데이터베이스에 연결할 수 있음

 

권한 계층(Privilege hierarchy)

 

일부 권한은 다른 권한을 포함하고 있습니다. ADMIN권한은 다른 모든 권한을 포함하고 있습니다.

 CREATE ANY TABLE 시스템 권한은 CREATE TABLE 객체 권한을 포함하고 있습니다.

PrivilegeConfers these privileges

권한	포함하고 있는 권한들
ADMIN	All other privileges including CACHE_MANAGER
CREATE ANY INDEX	INDEX (any table or materialized view)
CREATE ANY MATERIALIZED VIEW	CREATE MATERIALIZED VIEW
CREATE ANY PROCEDURE	CREATE PROCEDURE
CREATE ANY SEQUENCE	CREATE SEQUENCE
CREATE ANY SYNONYM	CREATE SYNONYM
CREATE ANY TABLE	CREATE TABLE
CREATE ANY VIEW	CREATE VIEW
DELETE ANY TABLE	DELETE (any table)
EXECUTE ANY PROCEDURE	EXECUTE (any procedure)
INSERT ANY TABLE	INSERT (any table)
SELECT ANY SEQUENCE	SELECT (any sequence)
SELECT ANY TABLE	SELECT (any table, view or materialized view)
UPDATE ANY TABLE	UPDATE (any table)

 

롤(ROLE)

롤이란 사용자가 허가할 수 있는 권한들의 집합입니다.

롤을 이용하면 권한부여와 회수를 쉽게 할 수 있습니다.

롤은 CREATE ROLE권한을 가진 유저에 의해 생성됩니다.

한 유저가 여러개의 ROLE을 ACCESS할 수 있고, 여러 사용자에게 같은 ROLE을 부여할 수 있습니다.

시스템 권한을 부여하고 취소할때와 동일한 명령을 사용하여 사용자에게 부여하고, 취소할 수 있습니다.

롤에 롤을 부여할 수 있습니다.

오라클 데이터베이스를 설치하면 기본적으로 CONNECT,RESOURCE,DBA 롤이 제공됩니다.

 

 

롤 생성 예제

-- ROLE을 생성 합니다.
SQL>CREATE ROLE manager;

-- ROLE에 권한을 부여 합니다.
SQL>GRANT create session, create table TO manager;

-- 권한이 부여된ROLE을 USER나 ROLE에 부여 합니다.
SQL>GRANT manager TO scott, test;

 

권한&롤 관련 딕셔너리 뷰

 

DBA_TAB_PRIVS : 객체 권한관련 정보를 볼 수 있는 뷰

DBA_SYS_PRIVS : 시스템권한관련 정보를 볼 수 있는 뷰

DBA_ROLE_PRIVS : 데이터베이스 내의 모든 사용자와 ROLE에 부여된 ROLE을 볼 수 있는 뷰

DBA_ROLES : 데이터베이스에 존재하는 롤을 볼 수 있는 뷰

ROLE_SYS_PRIVS : 사용자와 ROLE에 부여된 시스템 권한을 볼 수 있는 뷰

ROLE_TAB_PRIVS : 사용자와 ROLE에 부여된 객체 권한을 볼 수 있는 뷰

USER_TAB_PRIVS_MADE : 현재 사용자의 객체에 부여한 객체 권한

USER_TAB_PRIVS_RECD : 현재 사용자의 객체에 부여한 객체 권한

USER_COL_PRIVS_MADE : 현재 사용자 객체의 특정 컬럼에 부여한 객체 권한

USER_COL_PRIVS_RECD : 현재 사용자 객체의 특정 컬럼에 부여한 객체 권한

 

출처 : docs.oracle.com/database/121/TTSQL/privileges.htm#TTSQL345

출처 : www.gurubee.net/lecture/1009

출처 : www.gurubee.net/lecture/1008

출처 : www.gurubee.net/lecture/1010