006. SQL - DCL 데이터 조작어 COMMIT, ROLLBACK, GRANT, REVOKE + SAVEPOINT, ROLE

DDL (Data Define Language)

DB구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정하는데 사용하는 언어 

DML (Data Manipulation Language)

저장된 데이터를 실직적으로 관리하는데 사용하는 언어

DCL (Data Control Language)

데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어

 

 

> DCL 유형

COMMIT	현재 트랜잭션을 종료하고 트랜잭션의 갱신된 내용을 실제 물리적 디스크에 저장
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
GRANT	데이터베이스 사용자에게 사용 권한을 부여
REVOKE	데이터베이스 사용자의 사용 권한을 취소

 

 

 

 

 

 

 

COMMIT 문

 

- COMMIT 문을 사용하여 보류중인 모든 변경 내용(INSERT, UPDATE, DELETE)을 영구적으로 만든다.
- COMMIT 후 데이터 변경 사항이 데이터베이스 파일에 기록된다.
- 영향을 받은 행은 LOCK이 해제되고, 다른 사용자가 행을 변경할 수 있다.

 

 

mysql은 commit 기본 값이 1이여서 실습을 위해 잠시 0으로 해제 

SET AUTOCOMMIT = 0;

 

 

INSERT INTO S_DEPT (deptno, dname, loc)
VALUES (60, 'LAW', 'LA');

COMMIT;

 

이렇게 데이터 변경 후 COMMIT 명령어로 디스크에 저장이 된다. 

 

 

 

 

 

 

ROLLBACK 문 

 

 

- 데이터의 변경이 취소되고, 데이터의 이전 상태가 복원
- n 영향을 받은 행은 LOCK이 해제되고, 다른 사용자가 행을 변경할 수 있다.

 

 

SMITH 삭제 후 없어진 것 확인 -> ROLLBACK -> 복구 !

 

 

 

 

SAVEPOINT

 

INSERT INTO S_EMP(empno, ename, hiredate, sal)
VALUES (3790, 'GOODMAN', SYSDATE(), 2000);
SAVEPOINT a;
INSERT INTO S_EMP(empno, ename, hiredate, sal)
VALUES (3791, 'BADMAN', SYSDATE(), 1000);
SAVEPOINT b;
INSERT INTO S_EMP(empno, ename, hiredate, sal)
VALUES (3792, 'YESMAN', SYSDATE(), 3000);

 

 

ROLLBACK TO SAVEPOINT a;
COMMIT;

 

 

 

⚠ 암묵적으로 COMMIT 및 ROLLBACK을 수행하는 상황이 있기 때문에 위와 같은 방법을 쓸 때는 주의해야한다. 

 

- DDL 명령어 실행 ▶ 자동 COMMIT

- 데이터베이스 정상 종료 ▶ 자동 COMMIT

- 비정상 종료 및 시스템 오류 ▶ 자동 ROLLBACK

 

 

 

 

 

 

 

GRANT 와 REVOKE

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 : 권한 취소시 다른 사용자에게 부여한 권한도 연쇄적으로 취소

 

 

 

"GRANT"

 

▶ 현재 yewon 사용자에게 있는 권한 리스트 

 

▶ root 로그인 상태에서 GRANT로 권한을 부여

GRANT CREATE, INSERT ON testdb.* TO 'yewon'@'localhost';

 

▶ 다시 yewon 사용자의 권한 리스트 확인

 

 

 

"REVOKE"

▶ root 로그인 상태에서 REVOKE로 권한 부여 취소

REVOKE INSERT ON testdb.* FROM 'yewon'@'localhost';

 

▶ 다시 yewon 사용자의 권한 리스트 확인

 

 

 

 

 

ROLE

- 역할 사용을 통해 권한 관리를 단순화 시킴
- 시스템 권한과 객체 권한으로 구성 가능
- 역할은 사용자가 소유하지 않고, 스키마에도 존재하지 않음.

 

CREATE ROLE role_name;

-> 이렇게 선언 후 GRANT로 역할을 부여하면 된다. 

 

CREATE ROLE master;   //역할 생성 
GRANT ALL ON testdb.* TO master;  //역할에 권한 부여
GRANT master TO 'yewon'@'localhost';  //사용자에게 역할 부여

 

 

▶ yewon 사용자의 권한 리스트 확인 :: master 역할 생김!