[SQLD] 14. DCL (데이터 제어어)

DCL (데이터 제어어)

DCL(Data Control Language)은 데이터베이스 사용자에게 특정 작업을 수행할 수 있는 권한을 부여(GRANT)하거나 회수(REVOKE)하는 명령어임. 데이터베이스의 보안과 접근 통제를 담당함. DCL 명령어는 실행 즉시 자동 커밋(Auto Commit)됨.

---

1. 권한 (Privilege)의 종류

DCL로 제어하는 권한은 크게 시스템 권한과 객체 권한으로 나뉨.

종류	설명	주요 권한 예시
시스템 권한	데이터베이스 시스템 자체에 대한 접근 및 생성 권한.	CREATE SESSION: 데이터베이스 접속 권한 CREATE TABLE: 테이블 생성 권한 CREATE USER: 사용자 생성 권한
객체 권한	특정 테이블, 뷰 등 특정 객체에 대한 조작 권한.	SELECT, INSERT, UPDATE, DELETE on a table EXECUTE on a procedure

---

2. GRANT - 권한 부여

사용자 또는 Role에게 시스템/객체 권한을 부여함.

2.1 시스템 권한 부여

/*
 * -- 사용자 생성 및 접속 권한 부여 --
 * 1. MARKETER 사용자를 생성함 (DDL).
 * 2. MARKETER 사용자에게 데이터베이스 접속 권한(CREATE SESSION)을 부여함.
 */

-- 1. 사용자 생성 (DDL)
CREATE USER MARKETER IDENTIFIED BY marketing123;

-- 2. 시스템 권한 부여 (DCL)
GRANT CREATE SESSION TO MARKETER;

2.2 객체 권한 부여

• 특정 테이블에 대한 SELECT, INSERT 등의 권한을 부여함.

/*
 * -- 객체 권한 부여 --
 * MARKETER 사용자에게 Course 테이블을 조회(SELECT)할 수 있는 권한을 부여함.
 */
GRANT SELECT ON Course TO MARKETER;

2.3 WITH GRANT OPTION

• 권한을 부여하면서, 그 권한을 다른 사용자에게 재부여할 수 있는 권한까지 함께 부여함.

-- MARKETER에게 Course 테이블 SELECT 권한과, 이 권한을 남에게 줄 수 있는 권한을 함께 부여
GRANT SELECT ON Course TO MARKETER WITH GRANT OPTION;

-- 이제 MARKETER 사용자는 MKT_INTERN에게 SELECT 권한을 직접 부여할 수 있음.
-- (MARKETER 사용자로 접속하여 아래 쿼리 실행 가능)
-- GRANT SELECT ON Course TO MKT_INTERN;

---

3. REVOKE - 권한 회수

사용자 또는 Role로부터 부여했던 권한을 회수함.

/*
 * -- 권한 회수 예시 --
 * MARKETER 사용자로부터 Course 테이블에 대한 SELECT 권한을 회수함.
 */
REVOKE SELECT ON Course FROM MARKETER;

/*
 * -- 시스템 권한 회수 예시 --
 * MARKETER 사용자로부터 데이터베이스 접속 권한을 회수함.
 */
REVOKE CREATE SESSION FROM MARKETER;

※ 실전 팁: WITH GRANT OPTION으로 부여된 권한을 회수하면, 그 사용자가 다른 사용자에게 재부여했던 권한까지 연쇄적으로(CASCADE) 모두 회수됨. 예를 들어, MARKETER의 권한을 회수하면 MKT_INTERN이 부여받았던 권한도 함께 사라짐.

---

4. ROLE - 권한의 집합

ROLE은 여러 시스템/객체 권한들을 하나의 '꾸러미'로 묶어 관리의 효율성을 높이는 객체임. 사용자에게 개별 권한을 부여하는 대신, 직책에 맞는 ROLE을 부여하여 권한을 일괄적으로 관리할 수 있음.

4.1 ROLE을 이용한 권한 관리 3단계

1. CREATE ROLE: 권한을 담을 ROLE을 생성함.
2. GRANT ... TO ROLE: 생성된 ROLE에 필요한 시스템/객체 권한들을 부여함.
3. GRANT ROLE TO USER: 사용자에게 해당 ROLE을 부여함.

-- Step 1: '개발자' 역할을 할 DEV_ROLE 생성
CREATE ROLE DEV_ROLE;

-- Step 2: DEV_ROLE에 필요한 권한들 부여
GRANT CREATE SESSION, CREATE TABLE TO DEV_ROLE; -- 시스템 권한 부여
GRANT SELECT, INSERT, UPDATE ON Course TO DEV_ROLE; -- 객체 권한 부여
GRANT SELECT, INSERT, UPDATE ON Student TO DEV_ROLE;

-- Step 3: DEV01 사용자에게 DEV_ROLE 부여
CREATE USER DEV01 IDENTIFIED BY developer1;
GRANT DEV_ROLE TO DEV01;

-- 이제 DEV01 사용자는 DEV_ROLE에 포함된 모든 권한을 갖게 됨.

권한 회수 시에도 REVOKE DEV_ROLE FROM DEV01; 한 줄로 ROLE에 포함된 모든 권한을 한 번에 회수할 수 있어 관리가 매우 용이함.

---

5. 시험 문제 유형 및 함정 포인트

• 권한의 종류: CREATE SESSION은 시스템 권한, SELECT ON table은 객체 권한임을 구분하는 문제가 출제됨.
• GRANT / REVOKE 문법: ON 키워드는 객체 권한에만 사용됨. GRANT CREATE SESSION ON User ... 와 같은 잘못된 문법을 찾는 문제가 나올 수 있음.
• WITH GRANT OPTION의 연쇄 회수(CASCADE): 권한을 부여한 사용자의 권한이 회수될 때, 그로부터 권한을 위임받은 사용자의 권한도 함께 회수된다는 점은 단골 출제 포인트임.
• ROLE의 장점: ROLE을 사용하는 이유(권한 관리의 편의성, 효율성, 보안성)를 묻는 문제가 출제될 수 있음.
• DCL과 자동 커밋: GRANT, REVOKE는 DCL로, 실행 즉시 자동 커밋을 유발함. TCL 파트의 자동 커밋 개념과 연계하여 출제될 수 있음.

6. DBMS별 문법 차이

• 사용자 생성 및 권한 부여: 제시된 예제는 Oracle 기준임.
  • MySQL: CREATE USER 'user'@'host' IDENTIFIED BY 'password';, GRANT privilege ON database.table TO 'user'@'host'; 와 같이 사용자 식별 방식과 권한 부여 문법에 차이가 있음.
  • Oracle 12c 이후 CDB/PDB 환경: 예제 코드에서처럼 일반 사용자를 생성할 때 C## 접두사를 붙이거나, ALTER SESSION SET "_ORACLE_SCRIPT"=true; 와 같은 세션 설정이 필요할 수 있음. SQLD 시험은 전통적인 문법을 기준으로 하므로 이 차이를 깊게 알 필요는 없으나, 실습 환경 구축 시 참고해야 함.
• SQLD 시험은 ANSI 표준과 Oracle 문법을 기준으로 하므로, 본문의 GRANT/REVOKE 문법을 숙지하는 것이 중요함.