DCL: GRANT·REVOKE

정보처리기사 목차권한과 트랜잭션레슨 101 / 233

DCL(Data Control Language, 데이터 제어어)은 사용자에게 권한을 주고(GRANT) 거두는(REVOKE) 문장입니다. 권한 여러 개를 역할(ROLE)로 묶어 한꺼번에 줄 수도 있습니다.

SQLD를 먼저 공부했다면 이 단원 끝의 모의 문제부터 풀어 보고, 틀린 문제가 나온 레슨만 골라 봐도 됩니다.

학사 데이터베이스 하나를 교수·조교·직원이 함께 씁니다. 수강 표의 점수는 아무나 고치면 안 됩니다. 교수는 점수를 읽고 고칠 수 있어야 하고, 조교는 읽기만 해야 합니다. 표를 만든 사용자가 그 표의 주인입니다. 다른 사용자는 주인이 권한을 줘야 그 표를 쓸 수 있습니다. 아래 그림은 수강 표의 주인이 새 사용자 HONG(교수)·PARK(조교)에게 권한을 주고 거두는 장면입니다.

HONGSELECT COUNT(*) AS CNTFROM SQLD.수강;→ ORA-00942

주인GRANT SELECT ON 수강 TO HONG;

주인REVOKE SELECT ON 수강 FROM HONG;

주인GRANT SELECT ON 수강  TO HONG WITH GRANT OPTION;

HONGGRANT SELECT ON 수강 TO PARK;

주인REVOKE SELECT ON 수강  FROM HONG CASCADE;

주인CREATE ROLE 조교;GRANT SELECT ON 수강 TO 조교;GRANT 조교 TO PARK;

주인CREATE ROLE 조교;GRANT SELECT ON 수강 TO 조교;GRANT 조교 TO PARK;

주인수강 주인

수강 표 읽기✓

수강

조교역할

수강 표 읽기✓

SELECT ON 수강

새 조교

PARK조교

수강 표 읽기✓

조교

SELECT ON 수강

HONGSELECT COUNT(*) AS CNTFROM SQLD.수강;→ ORA-00942

주인GRANT SELECT ON 수강 TO HONG;

주인REVOKE SELECT ON 수강 FROM HONG;

주인GRANT SELECT ON 수강  TO HONG WITH GRANT OPTION;

HONGGRANT SELECT ON 수강 TO PARK;

주인REVOKE SELECT ON 수강  FROM HONG CASCADE;

주인CREATE ROLE 조교;GRANT SELECT ON 수강 TO 조교;GRANT 조교 TO PARK;

HONGSELECT COUNT(*) AS CNTFROM SQLD.수강;→ ORA-00942

주인수강 주인

수강 표 읽기✓

수강

HONG교수

수강 표 읽기✕

PARK조교

수강 표 읽기✕

7단계 중 1단계. 다른 사용자의 표는 주인이 권한을 줘야 읽을 수 있습니다.

1GRANT와 REVOKE의 꼴

그림에서 HONG은 처음에 수강 표를 읽으려다 막힙니다. 주인이 GRANT SELECT ON 수강 TO HONG;⁠을 실행하면 수강 표를 읽는 SELECT 권한이 HONG에게 갑니다. GRANT 다음에 권한, ON 다음에 표, TO 다음에 받는 사람을 씁니다. 그다음 REVOKE SELECT ON 수강 FROM HONG;⁠은 준 권한을 거둡니다. GRANT의 TO 자리에 FROM을 씁니다. HONG은 다시 수강 표를 읽을 수 없습니다.

이렇게 권한을 주고 거두어 누가 어느 데이터에 무엇을 할 수 있는지 정하는 일을 접근 제어(access control)라 합니다. 접근 제어의 정책과 모델은 4과목에서 따로 배웁니다.

시험은 특정 DBMS가 아니라 표준 SQL의 꼴로 냅니다. 대괄호([ ]) 안은 필요할 때만 붙입니다.

GRANT 권한 ON 표
  TO 사용자
  [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR]
  권한 ON 표
  FROM 사용자 [CASCADE];

권한 자리에는 SELECT·INSERT·UPDATE·DELETE를 쉼표로 여럿 쓰거나, 모두를 뜻하는 ALL을 씁니다.
사용자 자리에는 역할을 써도 됩니다. 쉼표로 여럿을 써도 됩니다.

2다시 줄 권리와 거둘 때 붙는 말

그림에서 주인은 같은 권한을 한 번 더 주면서 끝에 WITH GRANT OPTION을 붙입니다. HONG은 권한과 함께, 그 권한을 남에게 다시 줄 권리도 받습니다.

GRANT SELECT ON 수강
  TO HONG WITH GRANT OPTION;

이어서 HONG이 GRANT SELECT ON 수강 TO PARK;⁠으로 PARK에게 다시 줍니다. 이제 PARK도 수강 표를 읽습니다. DBMS는 누가 누구에게 줬는지(주인 → HONG → PARK) 기록해 둡니다. 주인이 HONG에게서 거두면서 끝에 CASCADE를 붙이면, HONG의 권한이 사라지고 기록을 따라 HONG이 PARK에게 준 권한도 함께 사라집니다.

REVOKE SELECT ON 수강
  FROM HONG CASCADE;

REVOKE에 붙는 말은 셋입니다. 받은 사람의 권한과 그 사람이 다시 준 권한이 어떻게 되는지로 가릅니다.

붙이는 말	붙는 곳	받은 사람의 권한	그 사람이 다시 준 권한
CASCADE	맨 끝	사라진다	함께 사라진다
RESTRICT	맨 끝	다시 준 권한이 있으면 회수가 거부된다	그대로 남는다
GRANT OPTION FOR	REVOKE 바로 뒤	남고, 다시 줄 권리만 사라진다	CASCADE를 붙이면 함께 사라진다

CASCADE와 RESTRICT는 DROP 문의 CASCADE·RESTRICT와 같은 짝입니다. 딸린 것까지 함께 없애거나(CASCADE), 딸린 것이 있으면 하지 않습니다(RESTRICT).
WITH GRANT OPTION과 GRANT OPTION FOR는 이름이 닮아 붙는 곳으로 가립니다. GRANT 끝에 붙으면 다시 줄 권리를 주고, REVOKE 바로 뒤에 붙으면 그 권리만 거둡니다.

3역할(ROLE) — 권한을 상자에 담아 준다

역할(ROLE)은 권한을 담는 상자입니다. 그림의 마지막 장면에서는 CREATE ROLE 조교;로 역할을 만들고, GRANT SELECT ON 수강 TO 조교;⁠로 권한을 담은 뒤, GRANT 조교 TO PARK;로 PARK에게 역할을 줍니다. 조교가 새로 오면 그 사람에게 역할 조교만 주면 됩니다.

예제

DCL 예제는 사용자를 새로 만들어야 해서 관리자(SYS)로 접속해 시작합니다. 블록 하나가 실습 한 번이고, 끝에서 만든 사용자와 역할을 지웁니다.

예제 표의 주인은 SQLD라는 사용자입니다. 다른 사용자의 표는 주인.표로 쓰므로 SQLD.수강입니다. 관리자는 주인 대신 권한을 주고 거둘 수 있습니다.
새 사용자는 접속할 권한도 없습니다. GRANT CONNECT TO HONG;⁠은 Oracle이 미리 만들어 둔 역할 CONNECT(접속 권한이 들어 있음)를 줍니다.
줄 맨 앞의 CONNECT 사용자/암호@FREEPDB1⁠은 그 사용자로 다시 접속하는 SQL*Plus 명령입니다. CONNECT / AS SYSDBA와 그다음 ALTER SESSION 줄은 관리자로 돌아오는 명령이라 시험과는 상관없습니다.
DROP USER HONG CASCADE⁠의 CASCADE는 그 사용자가 가진 객체까지 함께 지우라는 뜻입니다.

여럿을 주고 하나만 거두기

HONG에게 수강 표의 SELECT·UPDATE 권한을 준 뒤 UPDATE만 거둡니다. DBA_TAB_PRIVS는 관리자가 보는 표 권한 목록입니다. GRANTEE는 받은 사람, PRIVILEGE는 권한입니다.

CREATE USER HONG
  IDENTIFIED BY pw1;
GRANT CONNECT TO HONG;
GRANT SELECT, UPDATE
  ON SQLD.수강
  TO HONG;
REVOKE UPDATE ON SQLD.수강
  FROM HONG;
SELECT GRANTEE,
  PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'HONG';
DROP USER HONG CASCADE;

GRANTEE	PRIVILEGE
HONG	SELECT

권한은 쉼표로 여럿을 한 번에 주고, 그중 일부만 거둘 수 있습니다. UPDATE만 거둬 SELECT가 남았습니다.

다시 준 권한까지 거둔다

WITH GRANT OPTION으로 주고 다시 준 권한까지 거두는 그림의 장면을 Oracle에서 돌립니다. PARK은 HONG에게서 받은 권한으로 수강 표 12행을 셉니다. 관리자가 HONG에게서 거둔 뒤 PARK이 다시 세면 ORA-00942 오류가 납니다. HONG이 PARK에게 준 권한까지 함께 사라졌기 때문입니다. Oracle은 권한이 없는 표를 '없는 표'라고 답합니다.

Oracle로 돌려 보기

CREATE USER HONG
  IDENTIFIED BY pw1;
CREATE USER PARK
  IDENTIFIED BY pw2;
GRANT CONNECT
  TO HONG, PARK;
GRANT SELECT ON SQLD.수강
  TO HONG
  WITH GRANT OPTION;
-- HONG이 PARK에게 준다
CONNECT HONG/pw1@FREEPDB1
GRANT SELECT ON SQLD.수강
  TO PARK;
-- PARK이 센다
CONNECT PARK/pw2@FREEPDB1
SELECT COUNT(*) AS CNT
FROM SQLD.수강;
-- 관리자가 HONG에게서 거둔다
CONNECT / AS SYSDBA
ALTER SESSION SET
  CONTAINER = FREEPDB1;
REVOKE SELECT ON SQLD.수강
  FROM HONG;
-- PARK이 다시 센다
CONNECT PARK/pw2@FREEPDB1
SELECT COUNT(*) AS CNT
FROM SQLD.수강;
CONNECT / AS SYSDBA
ALTER SESSION SET
  CONTAINER = FREEPDB1;
DROP USER HONG CASCADE;
DROP USER PARK CASCADE;

CNT
12

표는 첫 SELECT의 결과입니다. 둘째 SELECT는 ORA-00942 오류로 막힙니다.

주인이 HONG에게 WITH GRANT OPTION 없이 줬다면, HONG이 PARK에게 주는 GRANT부터 ORA-01031(권한 부족) 오류로 막힙니다.
시험 꼴이라면 REVOKE 끝에 CASCADE를 붙입니다(그림이 보인 꼴). Oracle은 이 CASCADE를 받지 않고 ORA-02000 오류를 냅니다. 대신 붙이지 않아도 표 권한은 언제나 이렇게 연쇄로 거둡니다. RESTRICT와 GRANT OPTION FOR도 Oracle은 받지 않습니다.

역할(ROLE)

역할 조교에 접속 권한과 수강 표의 SELECT 권한을 담고, PARK에게는 역할 하나만 줍니다. PARK은 역할로 받은 권한으로 OS 과목의 점수를 읽습니다.

CREATE USER PARK
  IDENTIFIED BY pw2;
CREATE ROLE 조교;
GRANT CONNECT TO 조교;
GRANT SELECT ON SQLD.수강
  TO 조교;
GRANT 조교 TO PARK;
-- PARK이 읽는다
CONNECT PARK/pw2@FREEPDB1
SELECT 학번,
  점수
FROM SQLD.수강
WHERE 과목코드 = 'OS'
ORDER BY 학번;
CONNECT / AS SYSDBA
ALTER SESSION SET
  CONTAINER = FREEPDB1;
DROP USER PARK CASCADE;
DROP ROLE 조교;

학번	점수
101	75
102	60
104	75

역할에 권한을 더하면, 그 역할을 받은 사용자 모두가 따로 GRANT 없이 그 권한을 씁니다. 사람마다 권한을 주고 거두는 일이 줄어듭니다.
Oracle에는 역할이 미리 만들어져 있습니다. CONNECT는 접속, RESOURCE는 표·뷰 같은 객체 만들기, DBA는 거의 모든 관리 권한을 담고 있습니다.

시험 포인트

필기: 문장의 빈칸이나 옵션의 뜻을 묻습니다. 주는 GRANT는 GRANT 권한 ON 표 TO 사용자, 거두는 REVOKE는 REVOKE 권한 ON 표 FROM 사용자⁠입니다. WITH GRANT OPTION은 다시 줄 권리를 줍니다. CASCADE는 다시 준 권한까지 연쇄로 거둡니다. GRANT OPTION FOR는 권한은 남기고 다시 줄 권리만 거둡니다. 역할(ROLE)은 권한의 묶음입니다.
실기: 권한으로 접근을 제어하는 일의 이름은 접근 제어(Access Control)입니다. GRANT·REVOKE 문을 시험 꼴로 끝까지 씁니다. WITH GRANT OPTION은 GRANT 끝, GRANT OPTION FOR는 REVOKE 바로 뒤, CASCADE는 REVOKE 끝에 둡니다. 권한 묶음의 이름은 역할(ROLE)입니다.

확인 문제

1. 학생 표의 주인이 LIM에게 SELECT 권한을 주면서 다시 줄 권리도 줬습니다. LIM은 YOON에게, YOON은 JANG에게 같은 권한과 다시 줄 권리를 차례로 넘겼습니다. 주인은 JANG에게도 같은 권한을 직접 줬습니다. 이제 주인이 LIM에게서 SELECT 권한을 CASCADE로 거두면, 주인 말고 학생 표를 읽을 수 있는 사람은 누구일까요?

① 아무도 없다
② JANG
③ YOON, JANG
④ LIM, YOON, JANG

정답

②입니다. CASCADE는 LIM에게서 시작해 다시 준 권한을 끝까지 따라가 거둡니다. 그래서 LIM과 YOON의 권한, YOON이 JANG에게 준 권한이 사라집니다. JANG에게는 주인이 직접 준 권한이 따로 있어 그것은 남습니다.

① 주인이 직접 준 권한까지 함께 사라진다고 봤습니다. CASCADE는 LIM에게서 이어진 줄만 따라갑니다.
③ LIM의 권한만 사라진다고 봤습니다. CASCADE는 LIM이 다시 준 권한과, 그 사람이 또 다시 준 권한까지 거둡니다.
④ 다시 준 권한이 있어 회수가 거부된다고 봤습니다. 그것은 RESTRICT의 동작입니다.

2. 다음 (가)~(다)에 맞는 말을 쓰세요. (가)는 한글 이름과 영문으로 함께, (나)·(다)는 SQL 키워드로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

(가) 학기마다 조교 여러 명이 새로 옵니다. 관리자는 조교에게 줄 권한 다섯 가지를 이름 하나에 모아 두고, 새 조교에게는 그 이름만 줍니다.
(나) 팀장에게 과목 표를 읽을 권한을 주면서, 팀장이 팀원에게도 같은 권한을 나눠 줄 수 있게 하려고 주는 문장 끝에 붙이는 말입니다.
(다) 팀장은 과목 표를 계속 읽게 두고, 팀원에게 나눠 주는 일만 못 하게 하려고 REVOKE 바로 뒤에 붙이는 말입니다.

정답

받는 답: (가) 역할(ROLE) · (나) WITH GRANT OPTION · (다) GRANT OPTION FOR

(가)의 실마리는 '권한 묶음에 이름을 붙여 사람에게 준다', (나)는 '주는 문장 끝에서 나눠 줄 수 있게 한다', (다)는 '읽기는 남기고 나눠 주는 권리만 거둔다'입니다.

같은 이름의 다른 꼴: (가) 역할이나 ROLE 하나만 쓴 꼴 — 문제가 한글 이름과 영문을 함께 쓰라고 했습니다.
틀리는 꼴 GRANT OPTION FOR — (나)에: REVOKE 바로 뒤에 붙어 나눠 줄 권리를 거두는 말입니다. 그 권리를 주는 말은 WITH GRANT OPTION입니다.
틀리는 꼴 CASCADE — (다)에: 다시 준 권한까지 거두는 말이고, 받은 사람의 권한도 사라집니다. 팀장의 읽기 권한은 남겨야 합니다.

3. 과목 표의 주인이 YOON에게 다음 두 일을 차례로 합니다. 시험 꼴로 한 문장씩 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

(1) 과목 표에 행을 넣는(INSERT) 권한과 지우는(DELETE) 권한을 줍니다. YOON이 두 권한을 남에게 다시 줄 수도 있어야 합니다.
(2) 얼마 뒤 DELETE 권한만 거둡니다. YOON이 그사이 남에게 다시 준 DELETE 권한도 함께 거둡니다.

정답

받는 답:

GRANT INSERT, DELETE
  ON 과목 TO YOON
  WITH GRANT OPTION;
REVOKE DELETE ON 과목
  FROM YOON CASCADE;

(1)은 권한 둘을 쉼표로 잇고, 다시 줄 수 있게 끝에 WITH GRANT OPTION을 붙입니다. (2)는 DELETE만 적어 거두고, 다시 준 권한까지 거두려고 끝에 CASCADE를 붙입니다. 거둔 뒤 YOON에게는 INSERT 권한이 남습니다.

틀리는 꼴 TO와 FROM을 바꿔 쓴 꼴: 주는 GRANT는 TO, 거두는 REVOKE는 FROM입니다.
틀리는 꼴 GRANT OPTION FOR — (2)에: 권한은 남기고 다시 줄 권리만 거둡니다. (2)는 DELETE 권한 자체를 거둬야 합니다.
틀리는 꼴 RESTRICT — (2)에: YOON이 다시 준 권한이 있으면 회수가 거부됩니다.

더 자세히: SQLD의 DCL

읽음으로 표시

끝까지 내려오면 저절로 표시됩니다. 이 기기에만 저장합니다.

정보처리기사 목차로