SQL활용(기본SQL작성-DCL)

min seung moon·2021년 2월 8일

Oracle

목록 보기

8/23

DCL (Data Cntrol Language)

업무 단위인 트랜잭션의 완료와 취소를 위한 명령어

1. DCL

-1. DCL 개요

--1. DCL 유형

데이터베이스에서 데이터 이외의 오브젝트에 대해 조작할 필요가 있다
DCL 조작 대상
- 사용자 권한
  - 접근 통제
  - 사용자를 등록하고, 사용자에게 특정 데이터베이스를 사용할 수 있는 권리 부여하는 작업
- 트랜잭션
  - 안전한 거래 보장
  - 동시에 다수의 작업을 독립적으로 안전하게 처리 하기 위한 상호 작용 단위
DCL 명령어
- DCL (사용자 권한)
  - GRANT : 데이터베이스 사용자 권한 부여
  - REVOKE : 데이터베이스 사용자 권한 회수
- TCL (트랜젝션 제어)
  - COMMIT : 트랜잭션 확정
  - ROLLBACK : 트랜잭션 취소
  - CHECKPOINT : 복귀지점 설정

-2. DCL 활용

--1. 사용자 권한 부여 (GRANT)

권한 부여 명령어 문법
- 시스템 권한
  - GRANT 권한1, 권한2 TO 사용자계정
- 객체 권한
  - GRANT 권한1, 권한2 ON 객체명 TO 사용자계정
권한 유형
- 시스템 권한
  - CREATE USER : 계정 생성 권한
  - DROP USER : 계정 삭제 권한
  - DROP ANY TABLE : 테이블 삭제 권한
  - CREATE SESSION : 데이터베이스 접속 권한
  - CREATE TABLE : 테이블 생성 권한
  - CREATE VIEW : 뷰 생성 권한
  - CREATE SEQUENCE : 시퀀스 생성 권한
  - CREATE PROCEDURE : 함수 생성 권한
- 객체 권한
  - ALTER : 테이블 변경 권한
  - INSERT : 데이터 조작 권한
  - DELETE : 데이터 조작 권한
  - SELECT : 데이터 조작 권한
  - UPDATE : 데이터 조작 권한
  - EXECUTE : PROCEDURE 실행 권한

--2. 사용자 권환 회수 (REVOKE)

권한 회수 명령어 문법
- 시스템 권한
  - REVOKE 권한1, 권한2 FROM 사용자계정
- 객체 권한
  - REVOKE 권한1, 권한2 ON 객체명 FROM 사용자계정

-3. DCL 이론적 배경인 접근통제

--1. 접근 통제 개념

데이터베이스의 보안을 구현하는 방법으로 접근 통제 방법을 사용
보안 정책에 따라 접근 객체(시스템 자원, 통신 자우너 등)에 대한
접근 주체(사용자, 프로세스 등)의 접근 권한 확인 및 이를 기반으로 한
접근 제어를 통해 자원에 대한 비인가된 사용을 방지하는 정보 보호 기능

--2. 접근 통제 정책에 따른 유형

임의 접근 통제(DAC : Discretionary Access Control)
- 시스템 객체에 대한 접근을 사용자 개인 또는 그룹의 식별자를 기반으로 제한하는 방법
- 여기서 임의적이라는 말은 어떤 종류의 접근 권한을 갖는 사용자는 다른 사용자에게
  자신의판단에 의해서 권한을 줄 수 있다는 것
- 주체와 객체의 신분 및 임의적 접근 통제 규칙에 기초하여 객체에 대한 주체의 접근을
  통제하는 기능
- 통제 권한이 주체에 있음
- 주체가 임의적으로 접근 통제 권한을 배분하여 제어할 수 있음
강제 접근 통제(MAC : Mandatory Access Control)
- 정보시스템 내에서 어떤 주체가 특정 객체에 접근하여 할 때
  양쪽의 보안 레이블(Security Label)에 기초하여 높은 보안 수준을 요구하는
  정보(객체)가 낮은 보안 수준의 주체에게 노출 되지 않도록 제한하는 통제 방법
- 통제 권한이 제3자에게 있음
- 주제는 접근 통제 권한과 무관함

--3. 접근 통제와 DCL 관계

강제 접근 통제의 경우, 제 3자의 종류에 따라 세분화된 정책이 존재
- DAC(임의 접근 통제) : 데이터베이스관리시스템(DBMS)에서 채택한 접근 통제 정책
- DLC : 데이터베이스관리, 특히 접근 통제 용도로 SQL에서 사용하는 명령어

-4. TCL 활용 방법

트랜잭션 개념
- 일 처리 단위
- 트랜잭션은 논리적 연산 단위
- 한 개 이상의 데이터베이스 조작
- 하나 이상의 SQL 문장이 포함
- 트랜잭션 IS '거래' 이다
- 이때 거래 결과가 모두 반영되거나 또는 모두 취소 되어야 한다
- 즉, 데이터베이스의 트랜잭션은 특변한, 엄격한 거래를 의미
- 분할할 수 없는 최소 단위
트랜잭션 제어
- 흐름의 구조를 바꾼다는 것이 아닌 트랜잭션의 결과를 수용하거나 취소한다는 의미
- TCL 명령어
  - COMMIT : 거래 내역을 확정함
  - ROLLBACK : 거래 내역을 취소화
  - CHECKPOINT : 저장점 설정 (ROLLBACK할 위치를 지정함)

2. DCL 활용

-1. 사용자 권한 관련 DCL을 시나리오에 따라 수행

--1. DCL 수행 시나리오를 확인

사용자 계정 추가 및 제거
사용자에게 권한 부여
사용자의 권한 회수

--2. T사용자 계정을 추가

DBMS는 다수의 사용자가 공유하는 조직의 운영 데이터 관리 시스템
다수의 사용자를 지원하기 위하여 사용자 계정 정보를 관리
특정 사용자에게 제한된 권한을 부여하는 방식의 접근 통제를 통하여 데이터베이스 보안 가능

-- 사용자 계정 생성
CREATE USER super@localhost IDENTIFIED BY 'password';
-- super, 사용자 계정
-- password, 사용자 비밀번호

> CREATE USER super@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

-- 사용자 계정 생성 후 정보 확인
SHOW GRANTS FOR super@localhost;
-- USAGE, 접속 권한을 의미
-- 현재 super에게는 접속권한만 주어진 상태

> SHOW GRANTS FOR super@localhost;
+--------------------------------------------+
| Grants for user@localhost                  |
+--------------------------------------------+
| Grants USAGE ON *.* TO 'super'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)

-- 사용자 계정 제거
DROP USER super@localhost;

--3. 사용자에게 권한을 부여

사용자 super에게 GRANT 권한(권한을 부여할 수 있는 권한)을 부여

GRANT ALL ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

SHOW GRANTS를 통한 계정 정보 확인

> SHOW GRANTS FOR super@localhost;
+----------------------------------------------------------------------+
| Grants for user@localhost                                            |
+----------------------------------------------------------------------+
| Grants ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION|
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

권한 부여된 사용자 권한 보기
- ON 절, 사용되는 정보는 DATABASE.TABLE 정보를 의미
- ., 모든 DATABASE의 모든 TABLE에 대해 해당 권한이 부여되였음을 의미

-- super 유저가 biz라는 DATABASE에 대해 SELECT & UPDATE 권한만을 주는 경우
GRANT SELECT, UPDATE ON biz* TO super;
-- 이 경우 super라는 사용자는 biz 데이터베이스의 모든 테이블에 대해 데이터를 삽입하거나 삭제할 수 없다
   다만 조회와 갱신만 가능

--4. 사용자 권한 회수

REVOKE ALL ON *.* FROM super@localhost;

-2. 트랜젝션 제어 관련 TCL을 수행

--1. 트랜잭션을 분석

1. 내 계좌에서 100원을 인출
2. 상대방 계좌에 100원을 입금

- 계좌 이체 과정은 최소 2번의 작업을 필요 (두 개의 작업을 하나로 해서 '인출하고 입금'한다는 작업 단위 정의 가능
  이는 해당 작업을 하나의 문장으로 표현한것이지 결코 하나의 조작이 될 수 없다)
- 두 개의 작업 도중에 문제가 발생했다면, 즉 1번이나 2번 조작이 실패했다면 어떤 결과가 발생할까?
  상대방 계좌에 근거 ㅇ벗는 100원이 나타나거나, 내 계좌에서 100원이 사라지는 현상 발생
- 이러한 문제를 예방하기 위해 트랜잭션을 사용
  즉, 트랜잭션은 연관된 다수의 작업을 하나의 단위로 묶어서 하나의 단위가 바로 '분해할 수 없는 최소단위'가 되는 것

트랜잭션의 특성
- 원자성(Atomicty)
  - 트랜잭션 안에 정의된 연산은 모두 실행되거나 모두 실행되지 않아야 함
  - ALL or NOTHING
- 일관성(Consistency)
  - 트랜잭션 실행 전과 후 동일하게 오류가 없어야 함
  - 무결성
- 고립성(Isolation)
  - 트랜잭션 실행 중 다른 트랜잭션에 영향을 받지 않아야 함
  - 독립성
- 지속성(Durabillity)
  - 트랜젝션 결과는 항상 보존 됨
  - 장애 대응성

- 데이터베이스에서 '일관성'은 상태가 항상 같아야 함을 의미
  상태라는 것은 이전에 오류가 있었다면 이후에도 오류가 있어야 하며,
  이전에 오류가 없었다면 이후에도 오류가 없어야 함을 의미

- 오류가 없는 상태를 '무결성'이라 함
  '무결성'은 '정확성', '일관성', '유일성', '신뢰성' 았는 상태를 한 번에 표현 하는 것
  
- 트랜잭션
  언제 어떠한 형태의 실패에도 안전한 거래를 보장하는 수단

--2. 메모리 관점에서 트랜잭션 조작을 확인

DBMS의 모든 정보는 하드디스크에 저장
DBMS에서 이루어진 모든 조작 또는 연산은 메모리에서 이루어진다
하드디스크에 있는 정보를 메모리로 옮겨서 연산을 수행
적당한 시점에서 메모리 정보를 하드디스크로 옮긴다
TCL 명령어의 메모리 관점 동작
- COMMIT
  - 메모리의 내용을 하드디스크에 저장
  - 영구히 저장
- ROLLBACK
  - 메모리의 내용을 하드스크에 저장하지 않고 버린다
  - 메모리 내용 무효회
- CHECKPOINT
  - ROLLBACK 범위 설정을 위한 메모리상에 경계를 설정
  - 상태 기억

--3. 트랜잭션 조작을 확인

-----------------------------------------------------------------------
순서	명령문			     비고
-----------------------------------------------------------------------
1	트랜잭션 시작			
-----------------------------------------------------------------------
2	INSERT
-----------------------------------------------------------------------
3	SAVEPOINT A		     A라는 이름으로 복구 지점 설정
-----------------------------------------------------------------------
4	UPDATE
-----------------------------------------------------------------------
5	SAVEPOINT B		     B라는 이름으로 복구 지점 설정
-----------------------------------------------------------------------
6	DELETE
-----------------------------------------------------------------------
7	(현재 위치에서  ROLLBACK 예정)  복구 지점 사용에 따라 결과 달라짐
-----------------------------------------------------------------------

트랜잭션 ROLLBACK 경우
- ROLLBACK
  - 1번째 명령까지 취소 됨
  - 즉, 현재 위치 이전 트랜잭션 처리 내용이 모두 취소
- ROLLBACK TO A
  - 1~3까지의 명령은 유효하게 남고, 4~6까지 내용이 취소 됨
- ROLLBACK TO B
  - 1~5까지의 명령은 유효하게 남고, 6 내용이 취소

--4. Auto Commit을 이용

트랜잭션 명령문에 COMMIT이 없어도 DML 문이 성공적으로 수행하면 자동으로 COMMIT이 되며
또한 DML이 실패하면 자동으로 ROLLBACK이 되는 기능

-- MY-SQL에서 AUTO COMMIT 설정 여부 확인 명령어
SELECT @@AUTOCOMMIT

-- AUTOCOMMIT 설정 명령어, 1이 나오면 정상적으로 설정
SET AUTOCOMMIT = TRUE; -- AUTO COMMIT 설정
SET AUTOCOMMIT = FALSE; -- AUTO COMMIT 해제

--5. 트랜잭션을 제어(MY-SQL 기준)

트랜잭션 제어 SQL 문

----------------------------------------------------------------------------------------------------
위치		트랜잭션 제어 SQL문
----------------------------------------------------------------------------------------------------
1	|	START TRANSACTION;
2	|	savepoint a;
3	|	INSERT INTO 'salaries'(emp_no, salary, from_date, to_date) VALUES (1001, 900, ...);
4	|	savepoint b;
5	|	UPDATE salaryes set salary = 1000 where emp_no = 1001;
6	|	rollback to b;
7	|	rollback to a;