[ SQLD ] SQL - TCL / DCL 240817

TCL ( Transaction Control Language ) 은 트랜잭션을 제어하는 명령어다.

- 트랜잭션은 데이터베이스의 ( 하나의 ) 논리적 연산 단위다.

1) 의미적으로 분할할 수 없는 최소의 단위 → A통장에서 B통장으로 100원을 송금할 때 A통장에서는 100원이 나가고,

B통장에는 100이 들어온다. 이 모든 ( 연산 ) 과정은 '이체' 라는 하나의 작업 단위이며,

이체를 하나의 트랜잭션 ( 여러 개의 물리적 작업이 하나의 논리적 단위를 이룬다 ) 이라 할 수 있다.

트랜잭션은 하나의 논리적 작업 단위이며 여러 개의 물리적 작업으로 구성된다.

여러 개의 물리적 작업중 하나라도 수행되지 않는 물리적 작업이 있다면 하나의 논리적 단위를 이루지 못함으로

하나의 트랜잭션의 수행 결과는 실패로 끝난다. 트랜잭션 수행이 실패하게 되면 결과적으로 ( 모든 ) 연산을 반영하지 못한다.

2) 일반적으로 하나의 트랜잭션은 여러 SQL 문장을 포함한다.

3) 성공시 모든 연산을 반영하고 취소시 모든 연산을 취소한다 → ALL or NOTHING ( 트랜잭션의 특징중 하나 )

 

 

- 원자성 ( Atomicity ) : 하나의 트랜잭션은 여러 개의 물리적 작업 단위로 구성되며, 여러 개의 물리적 작업이 모두 진행되거나 ( 여러 개의 물리적 작업중 하나라도 수행되지 않은 게 있다면 ) 전혀 실행되지 않은 상태로 남게 된다.

- 지속성에서 영구적으로 저장된다는 의미는 다른 트랜잭션이 성공적으로 수행되지 않는 한 기존에 저장되어 있던 내용은 변하지 않는다는 것이다.

 

트랜잭션의 원자성, 일관성, 독립성, 지속성 ( ACID 특성 ) 을 보장하기 위해 DBMS 는 트랜잭션 관리자 시스템 모듈을 두게 되는데 이 트랜잭션 관리자가 동시성 제어를 수행한다. 여러 개의 트랜잭션이 수행되지만 각 트랜잭션이 고립되어서 수행될 수 있게 처리하는 알고리즘이 동시성 제어 ( Concurrency Control ) 기법이고, 동시성 제어에는 Lock ( 잠금 / 로크 ) 기반과 Timestamp 기반이 있다.

 

데이터베이스 관리 시스템에서 저장하고 있는 데이터가 변경되려면 COMMIT이 되어야한다.

COMMIT은 메모리에서 일어났던 연산의 결과를 데이터베이스에 반영하는 연산이고,

COMMIT을 수행하고 나면 지속성이 생겨서 반영된 연산의 결과는 다른 트랜잭션이 변경하지 않는 한 영구적으로 저장된다.

- COMMIT은 ( 메모리에서 ) 변경된 내용을 데이터베이스에 영구적으로 반영한다.

- ROLLBACK은 기본적으로 ( 메모리에서 ) 변경된 내용을 모두 버리고

변경 전 상태 ( 가장 최근에 발생했던 마지막 COMMIT ) 로 복귀한다.

단, SAVEPOINT 를 지정한 경우 지정한 저장점까지만 복귀한다.

→ SAVEPOINT는 부분 복귀를 위해 지정한 저장(지)점이다.

 

트랜잭션은 SQL문 실행시 자동으로 시작되고, COMMIT 또는 ROLLBACK 실행시 종료된다.

자동 커밋이나 자동 롤백을 설정할 수 있다. 설정할 경우

- DDL ( CREATE / ALTER / DROP / RENAME 등 ) 문장을 수행할 때 DDL 수행 전에 자동으로 커밋이 된다 ( auto commit )

- DB를 정상적으로 접속 종료하면 자동으로 커밋된다.

- 어플리케이션의 이상 종료로 DB와의 접속이 단절되었을 때 자동으로 롤백된다.

 

 

DCL ( Data Control Language ) 은 데이터 ( 권한 ) 제어어로

사용자 계정 생성 ( 아이디와 비밀번호 ) / 삭제, 권한 부여 / 회수에 대한 명령어다. 

CREATE USER → 아이디를 생성하는 문장

IDENTIFIED BY → 생성한 아이디와 짝을 이루는 비밀번호 입력한다.

CREATE USER mis1 IDENTIFIED BY pw1; mis1 이라는 아이디는 pw1 의 패스워드를 갖는다 → 계정 생성

ALTER USER mis1 IDENTIFIED BY new_pw; → mis1 아이디의 패스워드를 new_pw로 바꾼다 → 계정 정보 ( 비밀번호 ) 변경

 

만들어진 계정 ( id: mis1, pw: new_pw ) 에 권한을 주지 않는다면 계정 생성, 데이터베이스 접속, 데이터베이스 변경 등 아무것도 할 수 없다. 데이터베이스를 관리하는 최고 관리자인 system 계정에서 계정을 생성할 수 있는 권한을 다음과 같이 부여한다면 mis1 계정에서도 또다른 계정을 생성할 수 있다.

GRANT CREATE USER TO mis1; →  ( system 계정이 ) mis1 계정에게 계정을 생성할 수 있는 권한을 부여한다.

REVOKE CREATE USER TO mis1; →  ( system 계정이 ) mis1 계정에게 계정을 생성할 수 있는 권한을 회수한다.

DROP USER mis1 CASCADE; → mis1 계정을 삭제할 때, 만약 mis1이 계정을 생성했다면 CASCADE 옵션으로 인해mis1 계정으로 파생된 ( 만들어진 ) 계정 또한 mis1 계정이 삭제될 때 같이 삭제된다.

 

SESSION 생성 권한

CREATE SESSION 은 생성한 계정이 로그인할 수 있게 하는 명령어로 사용자 계정을 만들었을 때 반드시 부여해야 하는 권한이다.

GRANT CREATE SESSION TO kmumis; → kmumis 계정이 DB에 접속할 수 있도록 로그인할 수 있는 권한을 부여한다는 SQL문

 

Object 권한

- 테이블 생성을 위해 CREATE TABLE 권한 필요 → GRANT CREATE TABLE TO kmumis; 테이블을 생성할 수 있는 권한을 kmumis 계정에 부여한다.

- 다른 Object 권한 → A계정이 만든 테이블은 A계정이 접근할 수 있고, B계정이 만든 테이블은 B계정이 접근할 수 있다. A계정이 만든 든 테이블에 B계정이 접근할 수 없고, A계정 또는 관리자가 다른 계정에게 A계정이 만든 테이블에 접근할 수 있는 권한을 부여할 수 있다. 어떤 계정의 테이블에 접근할 수 있는 권한을 부여하는 SQL문은 GRANT 권한 ON 소유계정.테이블명 TO 계정명; 이다.

 

객체 ( Object ) 의 종류 ( TABLE, VIEW, SEQUENCE, PROCEDURE ) 는 여러 개가 있고, 객체마다 부여할 수 있는 권한은 다르다.

 

권한들의 묶음 ( Package )을 만들어 놓은 것을 ROLE 이라고 한다.

권한을 부여할 때 하나하나 부여하면 작성해야하는 SQL문이 많아져 ROLE을 이용해 권한을 부여한다.

 

RESOURCE 라는 ROLE 에는 8개의 권한이 포함되어 있다.

myid 계정에 RESOURCE 라는 ROLE ( 권한 묶음 ) 을 부여한다는 것은 8개의 권한을 모두 부여한다는 것을 의미한다.