본 게시글은 Oracle을 기준으로 작성되었습니다.
SQL 문장의 종류는 크게 네 가지로 나눠볼 수 있다.
DDL : Data Definition Language
DML : Data Manipulation Langauge
DCL : Data Control Language
TCL : Transaction Control Language
본 게시물에서는 위 네 가지 중에서 DDL, DDL에 대해 설명할 것이다.
TCL(Transaction Control Language)
트랜잭션은 데이터베이스에서 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합을 의미한다. 그리고 트랜잭션 제어어는 이를 제어하는 언어로, COMMIT, ROLLBACK, SAVEPOINT가 이에 속한다.
1. COMMIT
COMMIT은 문제가 없다고 판단한 데이터를 데이터베이스에 반영시키는 것을 의미한다.
TRUNCATE TABLE UNIV;
COMMIT;
Oracle에서는 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며, DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해주어야 트랜잭션이 종료된다. 따라서 DML에 속하는 TRUNCATE 명령어의 경우 COMMIT을 하기 전까지는 번복하는 것이 가능하다. DELETE의 경우 TRUNCATE와 유사한 효과를 가지고 있지만 DML이 아닌 DCL에 속하기 때문에 따로 트랜잭션 종료를 선언하지 않더라도 자동으로 커밋이 된다.
2. ROLLBACK
ROLLBACK은 말 그대로 이전 상태로 복원하는 것을 의미한다.
TRUNCATE TABLE UNIV;
ROLLBACK;
SELECT * FROM UNIV;
TRUNCATE 명령어를 수행하였지만 이어서 ROLLBACK을 수행하였으므로 UNIV 테이블의 데이터를 조회하는 경우 기존에 있던 데이터를 온전하게 확인할 수 있다.
3. SAVEPOINT
SAVEPOINT는 ROLLBACK과 엮어서 사용할 수 있는 기능으로, ROLLBACK이 가능한 저장점을 직접 지정할 때 사용하는 명령어이다.
CREATE TABLE UNIV;
SAVEPOINT SVPT1;
TRUNCATE TABLE UNIV;
ROLLBACK TO SVPT1;
이러한 경우 SAVEPOINT가 테이블을 생성한 이후, 테이블의 데이터를 제거하기 이전의 지점에 설정되어있기 때문에 ROLLBACK 명령어를 수행할 시 테이블의 데이터 제거는 번복되지만 테이블을 생성한 것은 커밋이 되는 효과를 얻게 된다.
DCL(Data Control Language)
데이터제어어는 유저를 생성하고 권한을 제어할 수 있는 언어이다. Oracle에서는 기본적으로 테스트용 샘플 유저인 SCOTT, DBA ROLE을 부여받은 SYS, 그리고 데이터베이스의 모든 시스템 권한을 가진 SYSTEM, 세 가지 유저가 존재한다. 데이터제어어를 사용하여 이러한 세 가지 유저 이외에도 추가적으로 유저를 생성하고 각각에게 권한을 부여, 회수하는 것이 가능하다. DCL에는 GRANT와 REVOKE가 포함된다.
1. GRANT
GRANT는 유저에게 권한을 부여할 때 사용하는 명령어이다.
GRANT CREATE USER TO SCOTT;
CONN SCOTT/TIGER
CREATE USER PJS IDENTIFIED BY YONSEI;
GRANT CREATE SESSION TO PJS;
CONN PJS/YONSEI
SCOTT 유저는 원래 유저를 생성할 수 있는 권한을 가지고 있지 않다. 하지만 SYSTEM 계정으로 접속하면 SCOTT 유저에게 유저 생성 권한을 부여할 수 있다.
또한 갓 생성된 PJS 유저는 아직 아무런 권한이 없기 때문에 로그인을 시도할 경우 CREATE SESSION 권한이 없다는 오류가 발생하게 된다. 따라서 다시 SYSTEM 계정을 이용하여 PJS 유저에게 세션 생성 권한을 부여해야 한다.
2. REVOKE
REVOKE는 GRANT와 반대로 유저에게 부여된 권한을 회수할 때 사용하는 명령어이다.
REVOKE CREATE USER FROM SCOTT;
위 명령어를 사용하여 SCOTT 유저로부터 유저 생성 권한을 회수하면 SCOTT 유저는 더 이상 새로운 유저를 생성할 수 없게 된다.