[Oracle] DCL(Data Control Language) , TCL(Transaction Control Language)

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

DCL은 테이블에 데이터를 조작할때 필요한 권한을 조작하는 행위입니다.

Oracle에서는 기본적으로 테스트용 샘플 유저인 SCOTT, DBA ROLE을 부여받은 SYS,

그리고 데이터베이스의 모든 시스템 권한을 가진 SYSTEM, 세 가지 유저가 존재합니다.

데이터제어어를 사용하여 이러한 세 가지 유저 이외에도 추가적으로 유저를 생성하고 각각에게 권한을 부여,

회수하는 것이 가능합니다.

DCL에는 GRANT와 REVOKE가 있습니다.

 

GRANT

GRANT는 유저에게 권한을 부여할 때 사용하는 명령어 입니다.

1) 객체권한 > Grant select , insert , delete , update on <테이블명> to <유저명>;
2) 시스템권한 > Grant create table to <유저명>;

※ SELECT, INSERT, DELETE, UPDATE를 모두 사용할 때, ALL을 사용합니다.

 

REVOKE

REVOKE는 유저에게 권한을 회수할 때 사용하는 명령어 입니다.

REVOKE SELECT, INSERT, DELETE, UPDATE ON <테이블 명> FROM <유저명>;

 

 

 

 

TCL(Transaction Control Language, 트랜젝션 제어어)

트랜잭션(Transaction)이란?

 데이터베이스에서 트랜잭션은 데이터 처리의 한 단위입니다.

오라클에서 발생하는 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는데 이틀 트랜잭션이라고 합니다.

여러 개의 명령어의 집합이 정상적으로 처리되면 정상 종료하도록 하고,

여러 개의 명령어 중에서 하나의 명령어라도 잘못되었다면 전체를 취소합니다.

 

트랜잭션의 특성

원자성(atomicity)

 - 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전행 실행되지 않은 상태로 남아 있어야 합니다.

일관성(consistency)

 - 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 됩니다.

독립성(isolation)

 - 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 됩니다.

영속성(durability)

 - 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장됩니다.

 

트랜잭션 제어를 위한 명령어

COMMIT

 - 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하라는 명령입니다.  

SAVEPOINT

 - 현재까지의 트랜잭션을 특정 이름으로 지정하라는 명령입니다.

ROLLBACK

 - 저장되지 않은 모든 데이터 변경 사항을 취소하고 현재의 트랜잭션을 종료하라는 명령입니다.

 

COMMIT, ROLLBACK 문

-- 1.현재 트랜잭션을 종료하기 위해서 COOMMIT 문을 실행합니다.
COMMIT;
 
-- 2.TEMP01_EMP 테이블의 내용을 조회합니다. 
SELECT *
  FROM TEMP01_EMP;
 
-- 3.DELETE 문을 실행하여 TEMP01_EMP에 모든 데이터를 삭제합니다. 
DELETE
  FROM TEMP01_EMP;
 
-- 4.특정 데이터만 삭제하려고 했는데 위에 같은 명령을 수행했다면 모든 데이터가 삭제되어 조회 시에 원하는 데이터를 찾을 수 없습니다.
SELECT *
  FROM TEMP01_EMP;
  
-- 5.DELETE 문을 실행하기 전 상태로 복구하기 위해서 ROLLBACK 문을 실행합니다.
ROLLBACK;
 
-- 6.ROLLBACK 문을 실행하면 DELETE 문을 실행하기 전 상태로 복구됩니다.
SELECT *
  FROM TEMP01_EMP;
  
-- 7.특정 데이터만 삭제한 후, 확인합니다.  
DELETE 
  FROM TEMP01_EMP
 WHERE EMPNO = 7369; 
 
SELECT *
  FROM TEMP01_EMP; 
  
-- 8.삭제한 결과를 영구적으로 반영하기 위해서는 COMMIT 문을 실행합니다.
COMMIT;  
Colored by Color Scripter

※ DBeaver에서는 auto commit이 가능합니다.

COMMIT 이후의 데이터 상태

 - 데이터에 대한 변경 사항이 데이터베이스에 반영됩니다.

 - 이전 데이터는 영구적으로 잃어버리게 됩니다.

 - 모든 사용자는 결과를 확인 할 수 있습니다.

 - 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있습니다.

 

ROLLBACK 이후의 데이터 상태 

 - 데이터에 대한 변경 사항은 취소됩니다.

 - 이전 데이터는 다시 복구됩니다.

 - 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있습니다.

 

COMMIT이나 ROLLBACK 효과

 - 데이터 무결성을 보장할 수 있습니다.

 - 영구적이 변경을 하기 전에 데이터의 변경 사항을 확인 할 수 있습니다.

 - 논리적으로 연관된 작업을 그룹핑하여 처리가 가능합니다.

  

SAVEPOINT 문

-- 1.EMPNO가 7369인 데이터를 삭제하기 전에 SAVEPOINT A를 설정합니다.
SAVEPOINT A;
 
DELETE 
  FROM TEMP01_EMP
 WHERE EMPNO = 7369; 
 
-- 2.EMPNO가 7499인 데이터를 삭제하기 전에 SAVEPOINT B를 설정합니다.
SAVEPOINT B;
 
DELETE 
  FROM TEMP01_EMP
 WHERE EMPNO = 7499;  
 
-- 3.EMPNO가 7499를 삭제하기 전 상태로 ROLLBACK 후, 확인합니다.
ROLLBACK TO B;
 
SELECT *
  FROM TEMP01_EMP;
  
-- 4.EMPNO가 7369인 삭제하기 전 상태로 ROLLBACK 후, 확인합니다.
ROLLBACK TO A;
 
SELECT *
  FROM TEMP01_EMP;
Colored by Color Scripter

.