DCL

상위 문서: SQL

Data Control Language; 데이터 제어어

데이터베이스에 접근하거나 객체에 권한을 주는등의 역할을 하는 언어

데이터를 제어하는 언어 입니다.
데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용합니다.

종류	역할
GRANT	특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한 부여 합니다.
REVOKE	특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 박탈, 회수 합니다.
COMMIT	트랜잭션의 작업을 성공적으로 종료하는 역할을 합니다.
ROLLBACK	트랜잭션의 작업을 취소 및 원래대로 복구하는 역할을 합니다.

SQLD 출제기준과 Oracle 분류에서는 COMMIT·ROLLBACK·SAVEPOINT를 TCL로 따로 다룬다. 시험에서 DCL은 GRANT와 REVOKE를 가리킨다.

사용자 생성

CREATE USER KIM IDENTIFIED BY pw1234;   -- 사용자 생성(관리자 계정에서 실행)
GRANT CREATE SESSION TO KIM;            -- 접속 권한 부여
ALTER USER KIM IDENTIFIED BY newpw;     -- 비밀번호 변경
DROP USER KIM CASCADE;                  -- 사용자와 그 스키마의 객체까지 삭제

사용자를 만들기만 하면 아무 권한이 없어 접속도 할 수 없다. 실습에서 CREATE SESSION 권한이 없는 사용자로 접속하면 ORA-01045 오류가 났다.
테이블을 만들려면 CREATE TABLE 권한과 함께 테이블스페이스 사용 한도(QUOTA)도 있어야 한다.

시스템 권한과 객체 권한

구분	시스템 권한	객체 권한
의미	데이터베이스 전체에서 특정 작업을 할 수 있는 권한	특정 객체(테이블, 뷰, 프로시저 등)에 대한 권한
예	CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE USER, CREATE ANY TABLE, DROP ANY TABLE	SELECT, INSERT, UPDATE, DELETE, ALTER, REFERENCES, INDEX(테이블), EXECUTE(프로시저·함수)
부여 문법	`GRANT CREATE TABLE TO 사용자`	`GRANT SELECT ON 테이블 TO 사용자`
재부여 옵션	WITH ADMIN OPTION	WITH GRANT OPTION
회수 시 연쇄 회수	없다	있다

객체의 소유자는 자기 객체에 대한 모든 권한을 갖는다. 다른 사용자의 테이블을 쓰려면 소유자.테이블로 쓰고 객체 권한을 받아야 한다.
INSERT, UPDATE, REFERENCES는 칼럼 단위로도 줄 수 있다. 예: GRANT UPDATE (가격) ON 상품 TO LEE
시스템 권한에 WITH GRANT OPTION을 붙이면 ORA-01939 오류가 난다. 두 옵션은 대상이 정해져 있다.

GRANT와 REVOKE

GRANT 시스템권한 [, ...] TO 사용자|롤|PUBLIC [WITH ADMIN OPTION];
GRANT 객체권한 [, ...] ON 객체 TO 사용자|롤|PUBLIC [WITH GRANT OPTION];

REVOKE 시스템권한 FROM 사용자|롤|PUBLIC;
REVOKE 객체권한 ON 객체 FROM 사용자|롤|PUBLIC;

WITH GRANT OPTION과 WITH ADMIN OPTION의 차이

KIM이 상품 테이블의 소유자이고, KIM → LEE → PARK 순서로 권한을 넘겨준 뒤 중간 사용자 LEE의 권한을 회수했다.

순서	객체 권한(WITH GRANT OPTION)	시스템 권한(WITH ADMIN OPTION)
1	KIM: `GRANT SELECT ON 상품 TO LEE WITH GRANT OPTION`	관리자: `GRANT CREATE TABLE TO LEE WITH ADMIN OPTION`
2	LEE: `GRANT SELECT ON KIM.상품 TO PARK`	LEE: `GRANT CREATE TABLE TO PARK`
3	KIM: `REVOKE SELECT ON 상품 FROM LEE`	관리자: `REVOKE CREATE TABLE FROM LEE`
PARK의 권한	함께 회수됨. PARK의 `SELECT ... FROM KIM.상품`은 ORA-00942 오류	그대로 남음. DBA_SYS_PRIVS에 PARK의 CREATE TABLE이 남아 있음

객체 권한은 회수하면 그 사용자가 다른 사용자에게 넘겨준 권한까지 연쇄적으로 회수된다(cascade).
시스템 권한은 WITH ADMIN OPTION으로 받아 넘겨주었더라도 연쇄 회수되지 않는다. Oracle 문서는 DDL 관련 시스템 권한 회수에는 연쇄 효과가 없으며, ADMIN 옵션 여부와 관계없다고 설명한다.
롤에는 객체 권한을 WITH GRANT OPTION으로 줄 수 없다(ORA-01926). 롤에 시스템 권한을 WITH ADMIN OPTION으로 주는 것은 된다.

롤(ROLE)

여러 권한을 묶어 이름을 붙인 것이다. 롤에 권한을 주고 사용자에게 롤을 주면, 사용자가 많아도 권한 관리가 쉬워진다.

CREATE ROLE 조회롤;
GRANT SELECT ON KIM.상품 TO 조회롤;   -- 롤에 권한 부여
GRANT 조회롤 TO CHOI;                -- 사용자에게 롤 부여
REVOKE 조회롤 FROM CHOI;             -- 롤 회수
DROP ROLE 조회롤;

실습에서 CHOI는 조회롤을 받은 뒤 KIM.상품을 조회할 수 있었고, SESSION_ROLES에 조회롤이 나타났다.
롤 안에 다른 롤을 넣을 수도 있다.

Oracle이 미리 만들어 둔 대표 롤은 다음과 같다. Oracle 문서는 이 롤들이 이전 버전과의 호환을 위해 제공되며, 직접 롤을 설계해 쓰라고 권한다.

롤	19c 문서에 나온 권한
CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
DBA	DELETE ANY TABLE, GRANT ANY PRIVILEGE 같은 ANY 권한을 포함한 많은 시스템 권한(관리자용)

19c 기준 RESOURCE에는 CREATE VIEW와 CREATE SYNONYM이 없다. 또 RESOURCE는 더 이상 UNLIMITED TABLESPACE 권한을 주지 않는다.
롤의 실제 구성은 버전에 따라 다르므로 DBA_SYS_PRIVS로 확인한다. 실습한 23ai(26ai Free)에서는 CONNECT에 SET CONTAINER가, RESOURCE에 CREATE VIEW, CREATE SYNONYM, CREATE MATERIALIZED VIEW 등이 더 들어 있었다.

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('CONNECT', 'RESOURCE') ORDER BY 1, 2;

PUBLIC

PUBLIC은 모든 사용자를 뜻한다. GRANT SELECT ON 상품 TO PUBLIC을 실행하면 현재와 앞으로 만들어질 모든 사용자가 그 권한을 갖는다. 실습에서 KIM이 PUBLIC에 SELECT를 주자 따로 권한을 받지 않은 PARK도 조회할 수 있었다. 권한이 너무 넓게 퍼지므로 데이터베이스 보안 측면에서 꼭 필요할 때만 쓴다.

SQL Server와 비교

항목	Oracle	SQL Server
권한 부여·회수	GRANT, REVOKE	GRANT, REVOKE
거부	없음	DENY: 권한을 명시적으로 거부한다. 그룹이나 롤을 통해 받은 권한보다 우선한다(객체 소유자와 sysadmin 구성원에게는 적용되지 않음).
연쇄 처리	객체 권한 회수 시 자동 연쇄 회수	WITH GRANT OPTION으로 받은 권한을 DENY하거나 REVOKE할 때 CASCADE를 지정한다.

시험 포인트

시스템 권한은 WITH ADMIN OPTION, 객체 권한은 WITH GRANT OPTION으로 재부여 권한을 준다. 회수할 때 객체 권한만 연쇄 회수된다.
객체 권한 문법은 GRANT 권한 ON 객체 TO 사용자, 회수는 REVOKE 권한 ON 객체 FROM 사용자이다. TO와 FROM을 바꿔 쓴 보기를 고르게 하는 문제가 나온다.
사용자는 CREATE SESSION 권한이 있어야 접속할 수 있다.
롤을 쓰면 여러 권한을 묶어 한 번에 주고받을 수 있다. 롤에는 WITH GRANT OPTION으로 객체 권한을 줄 수 없다.
COMMIT과 ROLLBACK은 SQLD에서 DCL이 아니라 TCL로 분류한다.

같이 보기

참고 문헌