[Oracle Database] TCL, DCL (2023.03.27)

🔎 TCL (Transaction Control Language) 

트랜잭션을 제어한다

 

트랜잭션 : 어떤 업무를 수행하기 위한 일련의 단계

 

트랜잭션 특징 

1. 원자성(atomicity) : 트랜잭션이 모두 완료되거나 하나라도 실패시 모두 원상복구 되어야 함 (all or nothing) 

2. 일관성(consistency) : 항상 일관적인 형태를 가져야 함. 트랜잭션 실행 전이 정상이라면 실행 후에도 정상이어야 함

3. 고립성(isolation) : 트랜잭션은 서로 간섭하면 안됨

4. 영속성/지속성 (durability) : 영구 저장을 했으면 반드시 반영되어야 함. commit을 했으면 데이터는 반드시 영구 저장 되어 있어야 함

 

📖 종류

COMMIT

ROLLBACK

SAVEPOINT 

 

📖 COMMIT

트랜잭션이 모두 정상 처리되면 변경된 데이터를 영구 반영하기 위해 COMMIT 

마지막 COMMIT 시점 이후 실행한 트랜잭션 결과를 데이터베이스에 영구 저장

 

📖 ROLLBACK

트랜잭션 도중에 에러가 발생하면?

COMMIT 불가

트랜잭션 도중에 하나라도 오류가 생기면  이전에 했던 모든 활동이 ROLLBACK → 원자성

이전에 실행했던 트랜잭션도 모두 ROLLBACK

ROLLBACK은 특정 지점까지만 원상 복구를 하도록 조절해주는 문법

마지막으로 COMMIT한 시점까지만 ROLLBACK 가능

 

-- 오라클에서는
DML(데이터조작어) : INSERT, UPDATE, DELETE
→ 직접 COMMIT을 해줘야 영구 반영이 됨
DDL(데이터정의어) : CREATE, ALTER, DROP, TRUCATE
→ 실행할 때마다 자동으로 알아서 COMMIT이 됨

1. 
INSERT ...
CREATE ...
하게 되면 자동으로 COMMIT이 됨

2. 
INSERT INTO...
COMMIT ; -- 마지막 커밋 시점

INSERT INTO ...
DELETE ...
UPDATE .....

ROLLBACK ; 롤백은 마지막 커밋 시점까지 되돌아감 

 

📖 SAVEPOINT 

특정 지점까지만 원상 복구를 하도록 조절해주는 문법

 

 

📖 LOCK 

둘 이상의 세션이 똑같은 행을 조작하려고 할 때 충돌하는 현상

트랙잭션의 특징인 고립성과 연관됨

 

 

 

🔎 DCL(Data Control Language)

객체에 권한을 부여한다

 

📖 종류

GRANT 

REVOKE

ROLE

 

데이터베이스는 권한을 부여/회수 하면서 객체를 보호 → DBA가 사용 (일반 개발자는 잘 사용하지 않음)

 

SQL COMMAND LINE에서 계정 생성 (SYSTEM 관리자 계정으로 접속)

 

사용자는 만들었는데 데이터베이스에 접속할 권한은 주지 않음 → 테스트 실패

 

📖 GRANT

권한을 부여할 때 사용하는 문법

 

GRANT 부여할권한 [ ON 대상객체 ] TO 부여받을계정 [ WITH GRANT OPTION | WITH ADMIN OPTION ]

 

권한 부여 후 데이터베이스 접속 성공

 

📖 ROLL 

여러 권한을 한번에 부여할 수 있음

CONNECT, RESOURCE

 

 

CONNECT	RESOURCE
ALTER SESSION	CREATE CLUSTER
CREATE SESSION	CREATE PROCEDURE
CREATE SYNONYM	CREATE TABLE
CREATE TABLE	CREATE TREIGGER
CREATE VIEW	CREATE ...

 

DCL TEST 계정에게 SERVICE 계정이 가지고 있는 직원 테이블에 조회할 권한을 부여

 

 

수정 권한 부여 안했을 때

 

📖 REVOKE 

권한 회수 

 

REVOKE 회수할권한 [ ON 회수할객체 ] FROM 회수당할계정

 

 

 

조회 
SELECT -- DML에 포함시키기도 함

DML(Data Manupulation Language)
INSERT 
UPDATE
DELETE 
MERGE

DDL(Data Definition Language)
CREATE
ALTER
DROP
TRUNCATE
RENAME

TCL(Transaction Control Language) -- DCL에 포함시키기도 함
COMMIT
ROLLBACK
SAVEPOINT

DCL(Data Control Language)
GRANT
REVOKE
ROLE

 

⚙ 권한부여 실습

1. SQL COMMAND LINE을 열어 SYSTEM 관리자 계정으로 접속 

SYSTEM 계정 : 오라클 데이터베이스를 설치할 때 생성한 계정

(DISC :  연결 해제)

 

CONN SYSTEM/12345;

 

2. HACKER 계정 생성

 

CREATE USER HACKER IDENTIFIED BY 12345 ;

 

3. 권한부여

(1) 데이터베이스 접속이 가능하도록 CREATE SESSION 부여

※ 오라클 데이터베이스 세션은 데이터베이스 접속을 시작으로, 여러 작업을 수행한 후 접속 종료까지의 전체 기간을 의미함. 세션 내부에는 하나 이상의 트랜잭션이 존재함. 일반적으로 데이터베이스는 여러 곳에서 동시에 접근하여 수많은 세션이 동시에 연결되어 있음

 

GRANT CREATE SESSION TO HACKER ;

 

(2) 여러 권한을 한번에 처리할 수 있게 CONNECT와 RESOURCE ROLE 부여

 

GRANT CONNECT, RESOURCE TO HACKER ;

 

(3) SERVICE 계정의 직원 테이블을 조회, 수정할 수 있게 SELECT, UPDATE 권한 부여

 

GRANT SELECT, UPDATE ON SERVICE.직원 TO HACKER ; 

 

(4) HACKER 계정으로부터 SERVICE 계정의 직원 테이블에 대한 UPDATE, SELECT 권한 제거

 

REVOKE SELECT, UPDATE ON SERVICE.직원 FROM HACKER ;