119. SQL - DCL
119.1 DCL(Data Contral Language, 데이터 제어어)
데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
- DCL은 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용
- DCL의 종류
| 명령어 |
기능 |
| COMMIT |
명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌 |
| ROLLBACK |
데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구 |
| GRANT |
데이터베이스 사용자에게 사용 권한을 부여 |
| REVOKE |
데이터베이스 사용자의 사용 권한을 취소 |
119.2 GRANT / REVOKE
데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어
- GRANT : 권한 부여를 위한 명령어
- REVOKE : 권한 취소를 위한 명령어
119.2.1 사용자등급 지정 및 해제
GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;
| 등급 |
내용 |
| DBA |
데이터베이스 관리자 |
| RESOURCE |
데이터베이스 및 테이블 생성 가능자 |
| CONNECT |
단순 사용자 |
119.2.1.1 예제 1
- 사용자 ID가 "NABI"인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문은?
GRANT RESOURCE TO NABI;
119.2.1.2 예제 2
- 사용자 ID가 "STAR"인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문은?
GRANT CONNECT TO STAR;
119.2.2 테이블 및 속성에 대한 권한 부여 및 취소
GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
- 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE 등
- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
- CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소
119.2.2.1 예제 3
- 사용자 ID가 "NABI"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문은?
GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;
119.2.2.2 예제 4
- 사용자 ID가 "STAR"인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문은?
REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;
119.3 COMMIT
트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령
- COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고, DML문이 실패하면 자동으로 ROLLBACK이 되도록 Auto COMMIT 기능을 설정할 수 있음
- 트랜잭션(Transaction) : 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로, 작업의 단위가 됨
- 하나의 트랜잭션은 COMMIT 되거나 ROLLBACK 되어야만 함
119.4 ROLLBACK
변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어
- 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency) 상태가 될 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백(Rollback)되어야 함
119.5 SAVEPOINT
트랜잭션 내에 ROLLBACK할 위치인 저장점을 지정하는 명령어
- 저장점을 지정할 때는 이름을 부여
- ROLLBACK 할 때 지정된 저장점까지의 트랜잭션 처리 내용이 모두 취소됨
119.5.1 예제 1
| 사원번호 |
이름 |
부서 |
| 10 |
김기획 |
기획부 |
| 20 |
박인사 |
인사부 |
| 30 |
최재무 |
재무부 |
| 40 |
오영업 |
영업부 |
- <사원> 테이블에서 '사원번호'가 40인 사원의 정보를 삭제한 후 COMMIT을 수행하라
DELETE FROM 사원 WHERE 사원번호=40;
COMMIT;
- DELETE 명령을 수행한 후 COMMIT 명령을 수행했으므로 DELETE 명령으로 삭제된 레코드는 이후 ROLLBACK 명령으로 되돌릴 수 없음
- <사원> 테이블 상태
| 사원번호 |
이름 |
부서 |
| 10 |
김기획 |
기획부 |
| 20 |
박인사 |
인사부 |
| 30 |
최재무 |
재무부 |
119.5.2 예제 2
DELETE FROM 사원 WHERE 사원번호=30;
- DELETE 명령을 수행한 후 COMMIT 명령을 수행하지 않았으므로 DELETE 명령으로 삭제된 레코드는 이후 ROLLBACK 명령으로 되돌릴 수 있음
- <사원> 테이블 상태
| 사원번호 |
이름 |
부서 |
| 10 |
김기획 |
기획부 |
| 20 |
박인사 |
인사부 |
119.5.3 예제 3
- SAVEPOINT 'S1'을 설정하고 '사원번호'가 20인 사원의 정보를 삭제하라
SAVEPOINT S1;
DELETE FROM 사원 WHERE 사원번호=20;
119.5.4 예제 4
- SAVEPOINT 'S2'을 설정하고 '사원번호'가 10인 사원의 정보를 삭제하라
SAVEPOINT S2;
DELETE FROM 사원 WHERE 사원번호=10;
119.5.5 예제 5
- SAVEPOINT 'S2'까지의 ROLLBACK을 수행하라
ROLLBACK TO S2;
- ROLLBACK이 적용되는 시점을 'S2'로 지정하였기 때문에 <사원> 테이블은 예제 4의 작업을 수행하기 전으로 되돌려짐
- <사원> 테이블 상태
119.5.6 예제 6
- SAVEPOINT 'S1'까지의 ROLLBACK을 수행하라
ROLLBACK TO S1;
- ROLLBACK이 적용되는 시점을 'S1'로 지정하였기 때문에 <사원> 테이블은 예제 3의 작업을 수행하기 전으로 되돌려짐
- <사원> 테이블 상태
| 사원번호 |
이름 |
부서 |
| 10 |
김기획 |
기획부 |
| 20 |
박인사 |
인사부 |
119.5.7 예제 7
- SAVEPOINT 없이 ROLLBACK을 수행하라
ROLLBACK;
- '사원번호'가 40인 사원의 정보를 삭제한 후 COMMIT을 수행했으므로 예제 7의 ROLLBACK이 적용되는 시점은 예제 1의 COMMIT 이후 새롭게 작업이 수행되는 예제 2의 시점임
- <사원> 테이블 상태
| 사원번호 |
이름 |
부서 |
| 10 |
김기획 |
기획부 |
| 20 |
박인사 |
인사부 |
| 30 |
최재무 |
재무부 |
