[DB] 데이터 제어어 (DCL) 이란?

서론

이번 글에서는 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용되는 데이터 제어어(DCL)에 대한 개념과 유형을 알아보고자 한다.

 

데이터 정의어(DDL), 데이터 조작어(DML)에 대한 개념 이해가 필요한 분들은 아래 글을 참고하면 좋을 것 같다.

 

• [DB] 데이터 정의어 (DDL) 이란?
• [DB] 데이터 조작어 (DML) 이란? 

DCL(Data Control Language, 데이터 제어어)

데이터 조작어는 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용하는 명령어로 서론에서 언급한 데이터 보안 및 제어에 대한 역할을 담당한다.

 

데이터 제어어에 속한 유형은 다음과 같다.

명령어	기능
COMMIT	명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려준다.
ROLLBACK	데이터베이스 조작 작업이 비정상 종료 시 원래의 상태로 복구한다.
GRANT	데이터베이스 사용자에게 사용 권한을 부여한다.
REVOKE	데이터베이스 사용자의 사용 권한을 취소한다.

1. GRANT / REVOKE

데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여/취소 하기 위한 명령어이다.

 

• GRANT : 권한 부여를 위한 명령어
• REVOKE : 권한 취소를 위한 명령어

사용자 등급을 지정/해제 하기 위한 GRANT, REVOKE 형식은 다음과 같다.

• 사용자 등급은 DBA(데이터베이스 관리자), RESOURCE(DB 및 테이블 생성 가능자), CONNET(단순 사용자)로 이루어져 있다.

- GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
- REVOKE 사용자등급 FROM 사용자_ID_리스트;

 

예를 들어, 사용자 ID가 "NABI"인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문을 작성하려면

GRANT RESOURCE TO NABI;

 

이와 같이 SQL 질의어를 작성할 수 있다.

 

이번에는 테이블 및 속성에 대한 권한 부여/취소를 위한 GRANT, REVOKE 형식을 알아보자.

- GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
- REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

 

• 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
• CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

[예시 1] 사용자 ID가 "NABI"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문을 작성하시오.

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

 

[예시 2] 사용자 ID가 "STAR"인 사람에게 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문을 작성하시오.

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

 

2. COMMIT

트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영하는 명령어이다.

대부분의 데이터베이스는 Auto Commit 기능을 제공하고 있어서 COMMIT 명령을 실행하지 않아도 DML 명령어가 성공적으로 완료되면 자동으로 COMMIT이 되고, 실패하면 자동으로 ROLLBACK하는 기능을 제공하고 있다.

 

3. ROLLBACK

아직 COMMIT되지 않은 변경된 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어이다.

트랜잭션은 일관성(Consistency)를 충족해야 하기 때문에 일부 변경된 내용이 데이터베이스에 반영되면 안된다. 따라서 일부분만 완료된 트랜잭션은 ROLLBACK을 통해 원래 상태로 되돌려야 한다.

 

4. SAVEPOINT

SAVEPOINT는 트랜잭션 내에 ROLLBACK할 위치인 저장점을 지정하는 명령어이다.

 

예시를 통해서 SAVEPOINT를 이해해보자.

 

<사원> 테이블에 다음과 같은 데이터가 있다고 가정해보자.

사원번호	이름	부서
10	김기획	기획부
20	박인사	인사부
30	최재무	재무부
40	오영업	영업부

 

[예시 1] <사원> 테이블에서 '사원번호'가 40인 사원 정보를 삭제한 후 COMMIT을 수행해라.

DELETE FROM 사원 WHERE 사원번호 = 40;
COMMIT;

 

SQL 질의어를 수행하고 난 뒤 테이블 결과는 다음과 같다.

사원번호	이름	부서
10	김기획	기획부
20	박인사	인사부
30	최재무	재무부

해당 예시에서는 COMMIT을 수행했으므로 DELETE 명령으로 삭제된 레코드(튜플)는 이후에 ROLLBACK을 통해 되돌릴 수 없다.

 

[예시 2] '사원번호'가 30인 사원의 정보를 삭제해라.

DELETE FROM 사원 WHERE 사원번호 = 30;

 

SQL 질의어를 수행하고 난 뒤 테이블 결과는 다음과 같다.

사원번호	이름	부서
10	김기획	기획부
20	박인사	인사부

해당 SQL 질의어에서는 COMMIT을 수행하지 않았으므로 DELETE 명령으로 삭제된 레코드는 ROLLBACK을 통해 되돌릴 수 있다.

 

[예시 3] SAVEPOINT 'S1'을 설정하고 '사원번호'가 20인 사원의 정보를 삭제해라.

SAVEPOINT S1;
DELETE FROM 사원 WHERE 사원번호 = 20;

 

SQL 질의어를 수행하고 난 뒤 테이블 결과는 다음과 같다.

사원번호	이름	부서
10	김기획	기획부

SAVEPOINT S1을 지정했으므로 이후 명령어로 ROLLBACK TO S1;을 수행하면 사원번호 = 20인 레코드가 지워지기 전 상태로 ROLLBACK을 한다.

 

[예시 4] SAVEPOINT 'S2'을 설정하고 '사원번호'가 10인 사원의 정보를 삭제해라.

SAVEPOINT S2;
DELETE FROM 사원 WHERE 사원번호 = 10;

 

SQL 질의어를 수행하고 난 뒤 테이블 결과는 다음과 같다.

사원번호

이름

부서

SAVEPOINT S2를 지정했으므로 이후 명령어로 ROLLBACK TO S2;을 수행하면 사원번호 = 10인 레코드가 지워지기 전 상태로 ROLLBACK을 한다.

 

이제 ROLLBACK 명령어를 통해서 삭제된 레코드를 다시 가져와보자.

 

1. ROLLBACK TO S2;

사원번호	이름	부서
10	김기획	기획부

SAVEPOINT S2까지 ROLLBACK을 수행하면 위와 같이 [예시 4]를 수행하기 전 상태로 되돌아간다.

 

2. ROLLBACK TO S1;

사원번호	이름	부서
10	김기획	기획부
20	박인사	인사부

SAVEPOINT S1까지 ROLLBACK을 수행하면 위와 같이 [예시 3]를 수행하기 전 상태로 되돌아간다.

 

3. ROLLBACK;

SAVEPOINT가 없는 경우 ROLLBACK을 하면 COMMIT 이후 새롭게 작업이 수행되는 작업 시점으로 돌아간다.

즉, [예시 1] 에서 COMMIT 명령어를 수행했으므로 [예시 1]이 끝난 직후의 상태로 ROLLBACK이 된다.

 

사원번호 = 40인 레코드는 COMMIT 전에 먼저 수행된 명령어 이므로 ROLLBACK을 통해 불러올 수 없다.

사원번호	이름	부서
10	김기획	기획부
20	박인사	인사부
30	최재무	재무부