SQL 롤 권한 제어

롤 : 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어 놓은 것

(수정이나 비활성화를 할때도 한번에 가능)

 

 

사전 정의된 롤

 

CONNECT 롤 : 사용자가 데이터베이스에 접속 가능하도록 해주는 권한

ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK,
 CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, 
CREATE TABLE, CREATE VIEW

 

RESOURCE 롤 : 사용자가 객체(테이블, 뷰, 인덱스)를 생성할 수 있도록 하게 해주는 권한

CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, 
CREATE TABLE, CREATE TRIGGER

 

DBA 롤 : 사용자들이 소유한 데이터베이스객체를 관리하고 사용자들을 작성하고 변경하고 제거할 수 있도록 하는 모든 권한(강력한 권한을 보유한 롤)

 

롤 부여하기

--1.데이터베이스 관리자로 접속
CONN system/manager
--2.사용자 생성
CREATE USER USER04 IDENTIFIED BY TIGER;
--3.로그인 (CREATE SESSION권한이 없어서 실패)
CONN USER04/TIGER
--4.DB관리자로 접속
CONN system/manager
--5.CONNECT롤과 RESOURCE롤 부여
GRANT CONNECT, RESOURCE TO USER04;
--6.로그인 성공
CONN USER04/TIGER

 

롤 관련 딕셔너리 확인

SELECT *
FROM DICT
WHERE TABLE_NAME LIKE '%ROLE%';

현재 사용자에게 부여된 롤 확인

SELECT * FROM USER_ROLE_PRIVS;

 

사용자가 정의한 롤

롤 생성

CREATE ROLE role_name;
GRANT privilege_name TO role_name;

 

시스템 권한 할당

--1.DB관리자 권한을 가진(롤을 생성할 수 있는) 사용자로 접속
CONN system/manager
CREATE ROLE MROLE;
--2.생성된 롤에게 권한을 부여
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO MROLE;
--3.사용자를 생성하여 롤을 부여
CREATE USER USER05 IDENTIFIED BY TIGER;
GRANT MROLE TO USER05;

 

객체 권한 할당

--1.CREATE ROLE 명령문을 사용하여 MROLE02 를 생성
CONN system/manager
CREATE ROLE MROLE02;
--2.MRLOE02 에게 EMP 테이블 객체를 조회할 수 있도록 SELECT 권한을 준다.
CONN scott/tiger   --EMP테이블을 소유한 SCOTT에 연결해줬음
GRANT SELECT ON EMP TO MROLE02;
--3.EMP 테이블 객체를 조회할 수 있도록 SELECT 권한을 준다.
CONN system/manager  --롤을 사용자에게 부여하기 위해 다시 DB관리자에 연결
GRANT MROLE02 TO USER05;

 

롤 회수, 삭제 

REVOKE role_name FROM user_name;
DROP ROLE role_name FROM user_name;

CONN system/manager
REVOKE MROLE FROM USER05;

DROP ROLE MROLE02;