4. SQL 기본 문법(DCL, TCL)

DCL은 주로 DB관리자가 사용하는 언어입니다. 일반 어플리케이션 개발자라면 크게 사용할 일이 없습니다.
하지만 TCL은 일련의 작업을 처리하는 단위를 지정하며, 지켜지지 않을 시 큰 피해를 발생시킬 수 있습니다.

 

 

DCL

Data Control Language, 데이터 제어어.

데이터베이스의 권한과 접근 제어를 관리하는 데 사용됩니다. DB에 권한이 없는 자가 접속하는 것을 방지하고 사용자에게 필요한 권한만을 부여할 수 있습니다. GRANT문으로 권한을 부여하고, REVOKE문으로 부여된 권한을 회수합니다.

 

 

1. GRANT

GRANT [SELECT | INSERT | ...] ON 테이블명 TO 사용자명

GRANT문 기본 구조입니다.

 

특정 사용자에게 특정 작업에 대한 권한을 부여합니다. 

 

 

GRANT SELECT, UPDATE ON 유저테이블 TO 사용자1;
GRANT SELECT, UPDATE ON 유저테이블 TO 사용자2;
GRANT SELECT, UPDATE ON 유저테이블 TO 사용자3;
GRANT SELECT, UPDATE ON 유저테이블 TO 사용자4;

 

모든 사용자에게 위와 같이 권한을 부여하는 것은 비효율적이고 관리도 어렵습니다. 따라서 아래와 같이 Role이라는 그룹을 생성한 후 그룹 단위로 권한을 부여하는 방식으로 사용합니다.

 

--롤 생성
CREATE ROLE 롤1;

-- 롤에 권한 부여
GRANT SELECT, UPDATE ON 유저테이블 TO 롤1;

--사용자에게 롤 부여
GRANT 롤1 TO 사용자1;

 

사용자가 추가되거나 삭제되는 경우 Role에서 사용자만 지워주면 됩니다.

 

 

SELECT * FROM DBA_ROLES;

 

해당 명령어로 현재 정의된 Role들을 조회할 수 있습니다.

 

 

 

2. REVOKE

REVOKE [SELECT | INSERT | ...] ON 테이블명 FROM 사용자명

REVOKE문 기본 구조입니다.

 

사용자나 Role에게 부여했던 권한을 회수합니다. TO가 아닌 FROM을 사용하는 점을 주의해야 합니다. 사용법은 GRANT와 유사합니다.

 

 

 

TCL

Transaction Control Language, 트랜잭션 제어 언어.

트랜잭션을 제어하기 위해 사용합니다. 트랜잭션이란 '더이상 분할이 불가능한 업무처리의 단위'를 뜻합니다. 트랜젹션의 특징은 다음 4가지와 같고 알파벳 앞자리를 따서 ACID라고 부릅니다

Atomicity 원자성	트랜잭션 내에서 일어나는 작업은 모두 성공적으로 처리되어야 합니다. 일부는 성공하고 일부는 실패한다면 모든 작업을 원래대로 돌려야 합니다.
Consistency 일관성	트랜잭션이 실행되는 동안 데이터베이스의 상태가 일관성을 유지해야 합니다. 트랜잭션 실행 전후로 데이터베이스는 특정 규칙이나 제약 조건을 충족해야 합니다.
Isolation 독립성	여러 다양한 트랜잭션이 동시에 실행되더라도 각각의 트랜잭션이 서로에게 영향을 미쳐서는 안됩니다.
Durability 영구성	트랜잭션이 완료된 후 변경된 데이터는 영구적으로 유지되어야 합니다.

 

은행에서 송금 시스템을 예시로 들어보겠습니다. A의 계좌에서 B의 계좌로 1,000원을 이체하려고 합니다.

1. UPDATE 계좌테이블 SET 잔액 = 잔액 - 1000 WHERE 계좌번호 = A계좌번호
2. UPDATE 계좌테이블 SET 잔액 = 잔액 + 1000 WHERE 계좌번호 = B계좌번호 [에러발생]

A 계좌에서 1,000원을 빼고 B의 계좌에 1,000원을 입금해야 하지만 2번 작업 중간에 에러가 발생했습니다.
이 때 트랜잭션에 대한 고려가 없다면, A계좌에서는 1,000원이 빠졌지만 B계좌는 잔액 변동이 없게되는 문제가 발생합니다.

따라서 2번 작업에 문제가 생겼을 시 1번 작업도 취소 처리를 할 수 있도록 작업의 단위를 묶는 것이 트랜잭션입니다.

 

1. COMMIT

COMMIT

 

명령어 실행 시, 데이터 변경 사항을 모두 영구 저장 처리합니다.

UPDATE, INSERT 등의 명령어로 데이터를 수정한 뒤 COMMIT 명령어가 실행되기 전까지는 데이터는 메모리 내의 Buffer Pool이라는 장소에 임시저장됩니다.

COMMIT 처리가 된 데이터들은 더 이상 복구할 수 없으므로 주의해야 합니다.

 

 

 

2. ROLLBACK

ROLLBACK

 

마지막 COMMIT 된 상태로 복구시키는 명령어입니다.

 

ROLLBACK TO 세이브포인트명

 

위 명령어로 특정 세이브포인트 위치로 ROLLBACK 할 수 있습니다.

 

 

 

3. SAVEPOINT

SAVEPOINT 포인트명

 

용어 그대로 트랜잭션 내에서 특정 작업 시점을 저장하는 명령어입니다.

 

-- 트랜잭션 시작

UPDATE 계좌테이블 SET 잔액 = 잔액 - 1000 WHERE 계좌번호 = A계좌번호;

SAVEPOINT p1;

UPDATE 계좌테이블 SET 잔액 = 잔액 + 1000 WHERE 계좌번호 = B계좌번호;

SAVEPOINT p2;

UPDATE 계좌테이블 SET 잔액 = 잔액 + 1000 WHERE 계좌번호 = C계좌번호;

ROLLBACK TO p1;

 

ROLLBACK TO p1으로 A계좌 변경 직전 상태까지 이동합니다.

 

위와 같이 여러 개의 세이브포인트를 지정한 경우, p1 포인트로 이동하게 된다면 p2 세이브포인트는 사라지게 됩니다.