2과목 · SQL 기본 및 활용 / 핵심

DCL

GRANT·REVOKE로 시스템 권한과 객체 권한을 주고 회수하며, ROLE로 권한을 묶어 관리합니다. WITH GRANT OPTION의 연쇄 회수와 ROLE 중첩을 기출 중심으로 익힙니다.

출제기출 11회분 중 12문항 · 많이 나온 주제: GRANT/REVOKE(11)

학습 목표

DCL 한눈에 보기

DCL(Data Control Language)은 사용자에게 권한을 주거나 회수하는 명령입니다. 사무실 출입카드에 비유하면 신입에게 카드를 발급(GRANT)하고, 퇴사자에게서 회수(REVOKE)하며, 부서별로 권한을 묶어(ROLE) 관리합니다.

SQL 명령은 네 가지로 나뉩니다. DDL(구조: CREATE·ALTER·DROP·TRUNCATE·RENAME), DML(데이터: SELECT·INSERT·UPDATE·DELETE·MERGE), TCL(트랜잭션: COMMIT·ROLLBACK·SAVEPOINT), DCL(권한: GRANT·REVOKE)입니다. COMMIT을 DCL로 묶는 함정이 자주 나옵니다.

개념 정리DCL의 세 가지 도구

GRANT
권한 부여
GRANT 권한 ON 객체 TO 사용자 · ROLE 부여도 GRANT
REVOKE
권한 회수
REVOKE 권한 ON 객체 FROM 사용자 · 지정한 권한만 회수
ROLE
권한 묶음
계정이 아니라 권한의 집합 · 한 번에 부여·회수

SQL 명령 분류
분류	다루는 것	명령
DDL	구조(객체)	CREATE · ALTER · DROP · TRUNCATE · RENAME
DML	데이터(행)	SELECT · INSERT · UPDATE · DELETE · MERGE
TCL	트랜잭션	COMMIT · ROLLBACK · SAVEPOINT
DCL	권한	GRANT · REVOKE

시스템 권한과 객체 권한

시스템 권한은 DB 자체에 대한 권한입니다. 접속(CREATE SESSION), 테이블 생성(CREATE TABLE), 사용자 생성(CREATE USER) 등이 있고, 다시 줄 수 있게 하는 옵션은 WITH ADMIN OPTION입니다. CREATE USER로 사용자를 만들어도 CREATE SESSION이 없으면 접속조차 할 수 없습니다.

객체 권한은 특정 테이블·뷰 같은 객체에 대한 권한입니다. SELECT·INSERT·UPDATE·DELETE·ALTER·EXECUTE·REFERENCES가 있고, 다시 줄 수 있게 하는 옵션은 WITH GRANT OPTION입니다. 다른 사용자의 테이블은 소유자(또는 권한을 받은 사람)가 GRANT해 줘야 조회할 수 있습니다. 모든 사용자에게 한 번에 주려면 PUBLIC에 부여합니다.

두 종류의 권한
구분	대상	예	재부여 옵션
시스템 권한	DB 전체	CREATE SESSION · CREATE TABLE · CREATE USER	WITH ADMIN OPTION
객체 권한	특정 객체	SELECT · INSERT · UPDATE · DELETE · REFERENCES	WITH GRANT OPTION

-- 시스템 권한: ON 없음
GRANT CREATE SESSION, CREATE TABLE TO USER1;

-- 객체 권한: ON 객체
GRANT SELECT, INSERT ON EMP TO USER1;
GRANT SELECT ON EMP TO PUBLIC;          -- 모든 사용자

-- R2가 R1의 테이블을 조회하려면 소유자 R1이 부여
GRANT SELECT ON 주문 TO R2;               -- R1이 실행

GRANT와 REVOKE

GRANT 권한 ON 객체 TO 사용자로 주고, REVOKE 권한 ON 객체 FROM 사용자로 회수합니다(TO와 FROM을 구분). REVOKE는 지정한 권한만 회수하므로, SELECT·INSERT·DELETE를 받은 사용자에게서 DELETE만 회수하면 SELECT와 INSERT는 그대로 남습니다.

WITH GRANT OPTION으로 받은 객체 권한을 다른 사람에게 다시 준 뒤, 중간 사용자의 권한을 회수하면 다시 준 권한까지 연쇄적으로 회수됩니다. 반대로 WITH ADMIN OPTION으로 받은 시스템 권한은 회수해도 다시 준 권한이 함께 회수되지 않습니다.

WITH GRANT OPTION은 연쇄 회수된다

DBA → USER1

USER	SELECT ON EMP
USER1	O (재부여 가능)
USER2	X

GRANTUSER1이 USER2에게

USER1 → USER2

USER	SELECT ON EMP
USER1	O
USER2	O

REVOKEUSER1에서 회수

DBA가 USER1에서 REVOKE

USER	SELECT ON EMP
USER1	X
USER2	X (연쇄)

USER2는 USER1을 통해 받았기 때문에, USER1의 권한이 회수되면 함께 사라집니다. 시스템 권한의 WITH ADMIN OPTION은 이렇게 연쇄되지 않습니다.

지정한 권한만 회수된다 — DBA가 U1에서 UPDATE만 회수
사용자	받은 권한	REVOKE UPDATE 후
U1	SELECT, INSERT, UPDATE	SELECT, INSERT 유지
U2	SELECT (WITH GRANT OPTION)	SELECT 유지
U3	SELECT (U2에게서)	SELECT 유지 (U2는 회수되지 않음)

ROLE

ROLE은 여러 권한을 하나로 묶은 권한의 집합입니다(사용자 계정이 아닙니다). CREATE ROLE로 만들고, ROLE에 권한을 GRANT한 뒤, 사용자에게 ROLE을 GRANT합니다. ROLE에서 권한을 회수하면 그 ROLE을 가진 모든 사용자에게 바로 적용되고, ROLE에 준 권한도 REVOKE로 회수할 수 있습니다.

ROLE은 다른 ROLE에 부여할 수도 있습니다. STAFF를 MGR에 주고 MGR을 철수에게 주면, 철수는 MGR을 거쳐 STAFF의 권한까지 씁니다. 이때 DROP ROLE STAFF를 하면 그 경로로 받던 권한도 사라집니다. Oracle의 기본 ROLE로 CONNECT(접속), RESOURCE(객체 생성), DBA(모든 권한)가 있습니다.

CREATE ROLE DEV_ROLE;
GRANT SELECT, INSERT, UPDATE ON EMP TO DEV_ROLE;
GRANT SELECT ON DEPT TO DEV_ROLE;

GRANT DEV_ROLE TO USER1, USER2;   -- 한 번에 부여
REVOKE DEV_ROLE FROM USER1;       -- USER1만 회수

ROLE 중첩 — 권한이 사용자에게 닿는 경로

철수

SELECT ON T
STAFF
MGR
철수

영희

SELECT ON T
STAFF
영희

MGR에만 있는 DELETE는 영희에게 없습니다. DROP ROLE STAFF를 하면 철수·영희 모두 STAFF 경로의 SELECT를 잃습니다.

보충 실습여기부터는 테이블을 직접 만들어 보는 긴 SQL 실습입니다. 처음 공부할 때는 건너뛰고, 위 내용을 이해한 뒤 돌아와도 됩니다.

SQL 실전: 사용자 생성부터 권한 회수까지

보충 실습

DBA로 접속해 사용자를 만들고, 접속 권한과 조회 권한을 차례로 준 뒤 조회 권한을 회수합니다. CREATE USER·ALTER USER·DROP USER는 DDL이지만 권한 관리와 함께 씁니다. Oracle에서는 사용자 = 스키마(그 사용자가 소유한 객체 모음)입니다.

-- DBA로 접속
CREATE USER STAFF IDENTIFIED BY pw1234;
GRANT CREATE SESSION TO STAFF;      -- 접속 권한
GRANT SELECT ON HR.EMP TO STAFF;    -- 조회 권한

-- STAFF로 접속
SELECT * FROM HR.EMP;               -- 가능

-- 다시 DBA로
REVOKE SELECT ON HR.EMP FROM STAFF;
DROP USER STAFF CASCADE;            -- 소유 객체까지 삭제

시점별 STAFF가 할 수 있는 일
시점	접속	HR.EMP 조회
CREATE USER 직후	불가 (CREATE SESSION 없음)	불가
GRANT CREATE SESSION	가능	불가 (객체 권한 없음)
GRANT SELECT ON HR.EMP	가능	가능
REVOKE SELECT	가능	불가 — ORA-00942 (테이블 또는 뷰가 없음)

권한이 없는 테이블을 조회하면 Oracle은 '권한 부족'이 아니라 ORA-00942: 테이블 또는 뷰가 존재하지 않습니다를 돌려줍니다. 권한이 없는 객체는 보이지 않는 것처럼 처리하기 때문입니다. GRANT CONNECT, RESOURCE TO USER1;처럼 기본 ROLE로 한 번에 줄 수도 있습니다.

개념을 불러오는 중…

SQL 명령 분류

분류

다루는 것

명령

DDL

구조(객체)

CREATE · ALTER · DROP · TRUNCATE · RENAME

DML

데이터(행)

SELECT · INSERT · UPDATE · DELETE · MERGE

TCL

트랜잭션

COMMIT · ROLLBACK · SAVEPOINT

DCL

권한

GRANT · REVOKE

두 종류의 권한

구분

대상

예

재부여 옵션

시스템 권한

DB 전체

CREATE SESSION · CREATE TABLE · CREATE USER

WITH ADMIN OPTION

객체 권한

특정 객체

SELECT · INSERT · UPDATE · DELETE · REFERENCES

WITH GRANT OPTION

-- 시스템 권한: ON 없음 GRANT CREATE SESSION, CREATE TABLE TO USER1; -- 객체 권한: ON 객체 GRANT SELECT, INSERT ON EMP TO USER1; GRANT SELECT ON EMP TO PUBLIC; -- 모든 사용자 -- R2가 R1의 테이블을 조회하려면 소유자 R1이 부여 GRANT SELECT ON 주문 TO R2; -- R1이 실행

USER

SELECT ON EMP

USER1

O (재부여 가능)

USER2

USER

SELECT ON EMP

USER1

USER2

USER

SELECT ON EMP

USER1

USER2

X (연쇄)

지정한 권한만 회수된다 — DBA가 U1에서 UPDATE만 회수

사용자

받은 권한

REVOKE UPDATE 후

SELECT, INSERT, UPDATE

SELECT, INSERT 유지

SELECT (WITH GRANT OPTION)

SELECT 유지

SELECT (U2에게서)

SELECT 유지 (U2는 회수되지 않음)

CREATE ROLE DEV_ROLE; GRANT SELECT, INSERT, UPDATE ON EMP TO DEV_ROLE; GRANT SELECT ON DEPT TO DEV_ROLE; GRANT DEV_ROLE TO USER1, USER2; -- 한 번에 부여 REVOKE DEV_ROLE FROM USER1; -- USER1만 회수

-- DBA로 접속 CREATE USER STAFF IDENTIFIED BY pw1234; GRANT CREATE SESSION TO STAFF; -- 접속 권한 GRANT SELECT ON HR.EMP TO STAFF; -- 조회 권한 -- STAFF로 접속 SELECT * FROM HR.EMP; -- 가능 -- 다시 DBA로 REVOKE SELECT ON HR.EMP FROM STAFF; DROP USER STAFF CASCADE; -- 소유 객체까지 삭제

시점별 STAFF가 할 수 있는 일

시점

접속

HR.EMP 조회

CREATE USER 직후

불가 (CREATE SESSION 없음)

불가

GRANT CREATE SESSION

가능

불가 (객체 권한 없음)

GRANT SELECT ON HR.EMP

가능

REVOKE SELECT

가능

불가 — ORA-00942 (테이블 또는 뷰가 없음)