DCL: GRANT·REVOKE·트랜잭션 제어

DCL(Data Control Language, 데이터 제어어)은 누가 어떤 데이터를 다룰 수 있는지 정하는 SQL이다. 주로 DBA(DataBase Administrator, 데이터베이스 관리자)가 보안과 무결성(integrity)을 지키려고 쓴다. 국내 시험은 권한 명령 GRANT·REVOKE와 트랜잭션(transaction) 제어 명령 COMMIT·ROLLBACK을 함께 DCL로 묶어 묻는다.¹ 빈칸 채우기와 "다음 설명의 명령어" 유형이 많다.

네 가지 명령

명령어	하는 일
GRANT	사용자에게 권한을 준다
REVOKE	준 권한을 거둔다
COMMIT	트랜잭션의 변경을 확정해 영구 반영한다
ROLLBACK	확정 전의 변경을 취소하고 이전 상태로 되돌린다

GRANT와 REVOKE

GRANT SELECT, UPDATE ON 학생 TO kim WITH GRANT OPTION;
REVOKE GRANT OPTION FOR UPDATE ON 학생 FROM kim CASCADE;
REVOKE ALL ON 학생 FROM lee;

권한은 SELECT, INSERT, UPDATE, DELETE처럼 명령 단위로 주고, ALL은 전부를 뜻한다. ON 뒤에는 대상 객체, TO·FROM 뒤에는 사용자를 쓴다. 주는 쪽은 TO, 거두는 쪽은 FROM이라는 짝을 헷갈리지 않아야 한다.

WITH GRANT OPTION은 받은 권한을 다른 사용자에게 다시 줄 수 있는 권한까지 함께 준다. 이 옵션이 없으면 kim은 받은 권한을 쓰기만 하고 남에게 넘기지 못한다.

REVOKE에 GRANT OPTION FOR를 붙이면 권한 자체는 두고 다시 줄 수 있는 권한만 거둔다. CASCADE는 kim이 다른 사람에게 넘겨준 권한까지 줄줄이 거둔다. 표준은 CASCADE나 RESTRICT 중 하나를 쓰게 한다. RESTRICT는 넘겨준 권한이 남아 있으면 회수를 거부한다. 위 문법은 표준·PostgreSQL 기준이고, MySQL은 REVOKE GRANT OPTION ON 학생 FROM kim;처럼 쓰며 CASCADE를 받지 않는다.

트랜잭션 제어

트랜잭션은 전부 성공하거나 전부 취소되어야 하는 작업 묶음이다. 계좌 이체에서 출금만 되고 입금이 안 되면 데이터가 어긋나기 때문이다. 그래서 중간에 실패하면 ROLLBACK으로 묶음 전체를 되돌리고, 모두 성공하면 COMMIT으로 확정한다. 확정된 변경은 ROLLBACK으로 되돌릴 수 없다.

SAVEPOINT는 트랜잭션 중간에 이름 붙인 저장점을 만든다. ROLLBACK TO 저장점은 전체가 아니라 그 지점 이후만 취소한다.

BEGIN;
UPDATE 학생 SET 점수 = 100 WHERE 학번 = 1002;
SAVEPOINT sp1;
UPDATE 학생 SET 점수 = 0 WHERE 학번 = 1002;
ROLLBACK TO sp1;
COMMIT;

위를 실행하면 학번 1002 학생의 점수는 100이다. 0으로 바꾼 두 번째 변경만 저장점까지 취소되고, 첫 변경은 COMMIT으로 확정됐기 때문이다. Oracle·MySQL은 CREATE·DROP 같은 DDL(Data Definition Language, 데이터 정의어)을 실행하면 그 전 변경을 자동으로 커밋하므로, DDL 뒤에는 ROLLBACK으로 되돌릴 수 없다.

시험 포인트

• 권한 부여 GRANT … ON … TO, 회수 REVOKE … ON … FROM.
• 받은 권한을 남에게 다시 줄 수 있게 → WITH GRANT OPTION.
• 재부여 권한만 회수 → GRANT OPTION FOR. 넘겨준 권한까지 연쇄 회수 → CASCADE.
• 변경 확정 COMMIT, 취소 ROLLBACK, 중간 저장점 SAVEPOINT.

참고

1. 영어권 자료와 여러 제품 문서는 COMMIT·ROLLBACK·SAVEPOINT를 TCL(Transaction Control Language, 트랜잭션 제어어)로 따로 나눈다. 시험 답안에서는 DCL 분류를 따른다. ↩︎