SQL) DCL (Data Control Language)

[ DCL ]

DCL은 특정 사용자의 권한을

제어할 수 있는 명령어이다.

(Data Control Language)

 

데이터의 보호와 보안을 위해서

DCL을 통해 유저의 권한을 관리하는데

권한을 줄 때는 GRANT문을 사용하고

권한을 회수 할 때는 REVOKE문을 사용한다.

 

DCL은 DDL문의 일종으로서 커밋 혹은

롤백이 필요없이 실행하는 순간 적용된다.

---

[ 오라클에서 제공하는 계정 ]

오라클 DBMS를 설치하면 기본적으로

생성되어 있는 계정은 아래 3개가 대표적이다.

사용자 계정	설명
SCOTT	테스트용 샘플 유저
SYS	DBA 권한을 부여받은 최상위 유저 (DB 생성과 제거 가능)
SYSTEM	DBA 권한을 부여받은 유저 (DB 생성과 제거는 불가능)

 

SQL*Plus 기준 예시

SYSTEM 계정으로 접속해서

새 사용자 계정 생성

→ 권한 부여

→ 접속 테스트

→ 접속 종료

-- 1) SQL*Plus 접속 (OS 명령 예시)
Windows: cmd / PowerShell
sqlplus system/비밀번호@서비스명

-- 예: 로컬 XE 많이 쓰는 케이스
sqlplus system/비밀번호@localhost:1521/XEPDB1
-- 또는
sqlplus system/비밀번호@XE


-- 2) SYSTEM으로 접속된 상태에서 새 사용자 생성
-- (사용자명/비번은 예시)
CREATE USER app_user IDENTIFIED BY app1234;

-- 3) 테이블 같은 객체 만들 공간(기본 테이블스페이스) 부여
-- 실습용으로 USERS를 자주 사용
ALTER USER app_user DEFAULT TABLESPACE USERS;

-- 4) USERS 테이블스페이스에 쓸 수 있는 용량(쿼터) 부여
ALTER USER app_user QUOTA UNLIMITED ON USERS;

-- 5) 접속 권한 부여
GRANT CREATE SESSION TO app_user;

-- 6) 객체 생성 권한 부여
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE SEQUENCE TO app_user;
GRANT CREATE PROCEDURE TO app_user;

-- (주의) 아래처럼 DBA 롤을 주면 실습은 편하지만 권한이 너무 큼
-- GRANT DBA TO app_user;

-- 7) SYSTEM 접속 종료
EXIT;

 

새로 만든 사용자로 '접속 테스트 → 종료' 예시

-- 8) 새 사용자로 SQL*Plus 접속
sqlplus app_user/app1234@localhost:1521/XEPDB1

-- 9) 접속 확인 (현재 사용자)
SHOW USER;

-- 10) 접속 종료
EXIT;

---

[ 테이블 권한 주기와 권한 회수]

계정이 소유한 테이블 권한을 줄 수 있는 GRANT와

권한을 회수하는 REVOKE는 아래와 같이 사용할 수 있다. 

 

SYSTEM 계정으로 접속 후 권한 부여 (GRANT)

-- SYSTEM으로 접속했다고 가정
sqlplus system/비밀번호@서비스명

-- 예시 상황
-- 테이블 소유자: APP_OWNER
-- 권한 받을 사용자: APP_USER
-- 대상 테이블: APP_OWNER.BOARD

-- 테이블 DML 권한 부여(조회/입력/수정/삭제)
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_owner.board
TO app_user;

-- 컬럼 단위 UPDATE 권한 부여
GRANT UPDATE(title, content)
ON app_owner.board
TO app_user;

 

권한 회수(REVOKE)

-- 테이블 DML 권한 회수
REVOKE SELECT, INSERT, UPDATE, DELETE
ON app_owner.board
FROM app_user;

-- 컬럼 단위 UPDATE 권한 회수
REVOKE UPDATE(title, content)
ON app_owner.board
FROM app_user;

---

[ ROLE을 이용한 권한 부여 ]

DBA는 여러 권한들의 집합인 ROLE을

생성하고 ROLE에 각종 권한을 부여한 후,

해당 ROLE을 다른 유저에게 부여할 수 있다.

 

DBA는 ROLE을 사용자 계정에게 부여함으로써

ROLE이 가지고 있는 권한들을 사용자들에게

신속하고 정확하게 부여할 수 있게 된다.

 

ROLE '생성/부여' 와 '회수/삭제' 예시

-- (예시)
-- ROLE 이름: app_dml_role
-- 테이블 소유자: APP_OWNER
-- 권한 받을 사용자: APP_USER
-- 대상 테이블: APP_OWNER.BOARD

-- 1) ROLE 생성 (DBA/SYSTEM에서 실행)
CREATE ROLE app_dml_role;

-- 2) ROLE에 권한 부여 (테이블 DML 권한 예시)
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_owner.board
TO app_dml_role;

-- 3) 사용자에게 ROLE 부여
GRANT app_dml_role TO app_user;

-- 사용자에게서 ROLE 회수
REVOKE app_dml_role FROM app_user;

-- ROLE 자체 삭제 (더 이상 안 쓰면)
DROP ROLE app_dml_role;

---

[ 오라클 DBMS에서 일반적으로 부여하는 ROLE ]

오라클 DBMS는 기본적으로

다양한 ROLE을 가지고 있다.

 

이러한 ROLE 중에서 일반 사용자에게

권한을 주는 대표적인 ROLE에는

CONNECT와 RESOURCE가 있다.

 

대표적인 ROLE

ROLE명	부여 권한
CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER CREATE PROCEDURE CREATE TYPE CREATE SEQUENCE CREATE TRIGGER CREATE OPERATOR CREATE TABLE CREATE INDEXTYPE