09_DCL(GRANT, REVOKE)

DCL

* DCL : DATA CONTROL LANGUAGE
* 데이터 제어 언어
* 계정에게 시스템 권한 또는 객체접근권한을 부여(GRANT)하거나 회수(REVOKE)하는 언어

GRANT

* 권한을 부여할 때 사용하는 명령어
* 구체적으로는 시스템권한과 객체접근권한을 부여할 수 있음.
*
* [표현법]
* GRANT 권한, 권한, .... TO 계정명

시스템 권한

* 시스템 권한 :  특정 DB에 접근하는 권한, 객체들을 생성할 수 있는 권한
* [종류]
* CREATE SESSION : 계정에 접속할 수 있는 권한
* CREATE TABLE : 테이블을 생성할 수 있는 권한
* CREATE VIEW : 뷰(조회용 임시테이블)를 생성할 수 있는 권한
* CREATE SEQUENCE : 시퀀스를 생성할 수 있는 권한
* CREATE USER : 계정을 생성할 수 있는 권한

-- 1. SAMPLE 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;
-- 계정 생성에 대한 권한은 주었으나, 접속에 대한 권한은 주지 않았음.

-- 2. SAMPLE 계정에 접속하기 위한 CREATE SESSION 권한을 부여
GRANT CREATE SESSION TO SAMPLE;

-- 3.1 CREATE TABLE 권한을 부여받기 전
CREATE TABLE TEST(
    TEST_ID NUMBER
);
-- [오류] insufficient privileges
-- 테이블을 생성할 수 있는 권한이 없기 때문에 오류가 발생함.

-- 3.1 SAMPLE계정에 테이블을 생성하기 위한 CREATE TABLE 권한 부여
GRANT CREATE TABLE TO SAMPLE;

-- 3.1 CREATE TABLE 권한을 부여한 후
CREATE TABLE TEST(
    TEST_ID NUMBER
);
-- [오류] no privileges on tablespace 'SYSTEM'
-- TABLE SPACE(테이블들이 모여있는 공간)가 할당되지 않아서 생기는 오류임.

-- 3.2 SAMPLE계정에 테이블스페이스를 할당해주기(SAMPLE계정 구조 변경)
ALTER USER SAMPLE QUOTA 2M ON SYSTEM;
-- QUOTA : 몫, 할당
-- 2M : 2 MEGA BYTE

-- 3.2 TABLE SPACE를 할당 받은 후
CREATE TABLE TEST(
    TEST_ID NUMBER
);
-- 테이블 생성 완료

-- 위의 테이블 생성 권한(CREATE TABLE)을 부여받게 되면
-- 해당 계정이 가지고 있는 테이블들을 조작(DML)하는 것도 가능해짐.
SELECT * FROM TEST;
INSERT INTO TEST VALUES(1);

-- 4. 뷰 만들어보기
CREATE VIEW V_TEST
AS (SELECT * FROM TEST);
-- [오류] "insufficient privileges"
-- 뷰 객체를 생성할 수 있는 CREATE VIEW 권한이 없기 때문에 오류가 발생함.

-- 4. AMPLE계정에 뷰를 생성할 수 있는 CREATE VIEW 권한 부여
GRANT CREATE VIEW TO SAMPLE;

-- 4. CREATE VIEW 권한을 부여받은 후
CREATE VIEW V_TEST
AS (SELECT * FROM TEST);
-- 뷰 생성완료

객체접근 권한

* 특정 객체들을 조작(DML - SELECT, INSERT, UPDATE, DELETE)할 수 있는 권한
*
* [표현법]
* GRANT 권한종류 ON 특정객체 TO 계정명;
* =====================================
* 권한종류         특정 객체
* =====================================
* SELECT          TABLE, VIEW, SEQUENCE
* INSERT          TABLE, VIEW
* UPDATE          TABLE, VIEW
* DELETE          TABLE, VIEW 

-- 5. SAMPLE 계정에서 KH계정에 있는 테이블에 접근
SELECT * 
FROM KH.EMPLOYEE;
-- [오류] table or view does not exist
-- KH계정의 테이블에 접근해서 조회할 수 있는 권한이 없기 때문에 오류 발생
-- SAMPLE계정 입장에서 "KH.EMPLOYEE"라는 이름의 테이블이 존재하지 않음.

-- 5. SAMPLE 계정에 KH.EMPLOYEE테이블을 조회할 수 있는 권한 부여
GRANT SELECT ON KH.EMPLOYEE TO SAMPLE;

-- 5. SELECT ON 권한 부여 후
SELECT * 
FROM KH.EMPLOYEE;
-- EMPLOYEE 테이블 조회 성공

/*
SELECT *
FROM KH.DEPARTMENT;
*/
-- KH.EMPLOYEE테이블에만 접근권한을 주어서 DEPARTMENT에는 적용이 안됨. 


-- 6. SAMPLE 계정에서 KH계정의 테이블에 접근해서 행 삽입해보기
INSERT INTO KH.DEPARTMENT VALUES('D0','회계부','L2');
-- [오류] table or view does not exist
-- KH계정의 테이블에 접근해서 삽입할 수 있는 권한이 없기 때문에 오류 발생

-- 6. SAMPLE 계정에 KH.DEPARTMENT테이블에 행을 삽입할 수 있는 권한 부여
GRANT INSERT ON KH.DEPARTMENT TO SAMPLE;
-- KH.DEPARTMENT테이블에 행 INSERT 성공

-- 6. INSERT ON 권한 부여 후
INSERT INTO KH.DEPARTMENT VALUES('D0','회계부','L2');
COMMIT;
-- 커밋까지 해줘야 새로운 행이 삽입됨.

ROLE

* 특정 권한들을 하나의 집합으로 모아놓은 것 
* CONNECT : 접속할 수 있는 권한들을 묶어놓은 ROLE (CREATE SESSION)
* RESOURCE : 특정 객체들을 생성 및 관리할 수 있는 권한들을 묶어놓은 ROLE
* (CREATE TABLE, CREATE SEQUENCE,...)

-- 롤을 확인할 수 있는 데이터 딕셔너리
SELECT * 
FROM ROLE_SYS_PRIVS
WHERE ROLE IN ('CONNECT','RESOURCE');
-- 2개의 ROLE로 9개의 권한을 부여하는 효과

-- 사용자에게 부여할 권한 : CONNECT, RESOURCE
-- 권한을 부여받은 사용자의 이름 : MYTEST
CREATE USER MYTEST IDENTIFIED BY MYTEST; -- 1단계 : 계정생성
GRANT CONNECT, RESOURCE TO MYTEST; -- 2단계 : 최소한의 권한 부여

-- MYTEST 계정으로 접속해서 작업

-- 데이터 관리 권한만 회수하고 싶다면?
REVOKE RESOURCE FROM MYTEST; -- 롤단위의 회수도 가능함.

-- MYTEST계정이 더이상 쓸모없다면?
DROP USER MYTEST; -- 접속중이 아니어야 삭제 가능.

REVOKE

* 권한을 회수할 때 사용하는 명령어
* 
* [표현법]
* REVOKE 권한, 권한, 권한,... FROM 계정명

-- 7. SAMPLE 계정에서 테이블을 생성할 수 없도록 권한을 회수
REVOKE CREATE TABLE FROM SAMPLE;

-- 7. 테이블 만들어보기
CREATE TABLE TEST2(
    TEST_ID NUMBER
);
-- [오류] insufficient privileges
-- 테이블 생성 권한을 회수해서 테이블을 생성할 수 없음.