데이터베이스 - 무결성, 보안, 회복

무결성(integrity)?

데이터의 정확성 또는 유효성을 의미

--> 실제로는 무결성 규칙을 정해놓음

 

무결성 규칙의 종류

 - 도메인 무결성 규칙(domain integrity rules)

 : 주어진 애트리뷰트의 값이 그 애트리뷰트가 정의된 도메인에 속한 값이어야 한다는 것을 규정

 

 - 릴레이션 무결성 규칙(relation integrity rules)

 : 어느 한 튜플이 릴레이션에 삽입 가능한가, 또는 한 릴레이션과 또 다른 릴레이션의 튜플들 간의 관계가 적절한가

 

SQL에서 무결성 규칙 정의 방법(3가지)

1. 애트리뷰트 제약 조건

[CONSTRAINT constraint] CHECK (cond-exp)

constraint : 제약 조건의 이름

cond-exp : 제약 조건을 표현하는 조건식

 

ex : 애트리뷰트 봉급은 정수 6,000,000 보다 작아야 한다

CREATE TABLE 종업원(
	이름 CHAR(10),
    봉급 INTEGER CHECK (봉급 < 6000000));

 

2. 기본테이블 제약조건

CREATE TABLE 수강
	(학번 INTEGER NOT NULL,
	 과목코드 CHAR(4) NOT NULL,
	 성적 INTEGER NOT NULL, 
     PRIMARY KEY (학번, 과목코드), 
     FOREIGN KEY (학번) REFERENCES 학생
	 	ON DELETE CASCADE
	 	ON UPDATE CASCADE,
	 FOREIGN KEY (과목코드) REFERENCES 과목
		ON DELETE CASCADE
		ON UPDATE CASCADE, 
     CHECK (성적 >= 0 AND 성적 <= 100));

 

3. 주장(assertion) : 제약조건을 위반하는 연산이 수행되지 않도록 하는 것

 - 제약 조건 명세 : CREATE ASSERTION 이름 CHECK (조건식);

 - 제약 조건 제거 : DROP ASSERTION 이름;

ex.

CREATE ASSERTION MIN_CREDIT
	CHECK((SELECT MIN(과목.학점) FROM 과목) > 0);
   
CREATE ASSERTION MAX_CREDIT
	CHECK(NOT EXISTS (SELECT * FROM 과목 WHERE NOT (과목.학점 < 5)));

 

트리거?

테이블에 어떤 일이 일어나면 자동으로 실행됨

--> 즉, 테이블에 삽입, 수정, 삭제 등의 작업이 발생할 떄 자동으로 작동

--> Stored Procedure와 비슷한 모양을 갖지만, 직접 실행 시킬수는 없고, 해당 테이블에 이벤트가 발생한 경우에만 실행됨

--> 또한 IN, OUT 매개변수를 사용할 수 없음

--> 트리거 적용 예) 누군가 A라는 테이블에 행을 고의로 삭제한다면

 

트리거란?

데이터를 변경할 때 마다 실행되는 특별한 저장 프로시져

--> 한 테이블과 연관됨

--> 삽입, 삭제, 수정 시 자동적으로 실행됨

--> 저장 프로시저와는 달리 직접 호출될 수는 없음

--> 하나의 트랜잭션이다

 

트리거 사용 이점

--> DB내 관련 테이블 간의 캐스케이드(순차적) 변경

--> CHECK보다 더 복잡한 데이터 무결성을 강화할 수 있따

--> 사용자 정의 에러 메세지 정의 가능

--> 비 정규화 된 DB 환경에서도 사용될 수 있다

--> 변경 시 데이터의 전후 상태 비교 가능

 

트리거 사용 시 고려사항

 - 트리거는 실행 후, 제약조건은 실행 전 수행 

 - 테이블은 어떤 동작에 대해 여러 개의 트리거를 가질 수 있다

 - 테이블 소유자 만이 트리거를 생성, 제거할 수 있다

 - 트리거는 결과 세트를 리턴할 수 없다

 - 트리거는 다중 행 동작을 처리할 수 있다(여러 행에 영향을 미침)

 

트리거 명세 방법

에: 조건(학생 테이블에 INSERT 혹은 DELETE가 일어나면)이 만족되면 UPDATE 명령 자동 실행

CREATE TRIGGER STUDENT_INS
	AFTER INSERT ON 학생
BEGIN UPDATE 학과테이블
	  SET 학생수 = 학생수 + 1
      WHERE 학과코드 = NEW_STUDENT.학과코드;
END;

CREATE TRIGGER STUDENT_DEL
	AFTER DELETE ON 학생
BEGIN UPDATE 학과테이블
	  SET 학생수 = 학생수 - 1
      WHERE 학과코드 = OLD_STUDENT.학과코드;
END;

 

보안?

 - 권한이 없는 데이터 엑세스, 악의적인 데이터 파괴 및 유출로부터 DB를 보호하는 것

 - DB의 접근 제어는 여러 수준으로 적용 됨

 --> ex. 어떤 사용자는 읽기만 할 수 있고, 어떤 사용자는 테이블을 갱신할 수 있음

 - 사용자마다 계정과 비밀번호를 부여하고 DB의 특정 부분에 특정 작업만을 허용

 

DBMS 보안 기능

 - DB에 대한 접근을 통제할 수 있는 기능

 --> 사용자 계정과 암호를 관리함

 --> 이를 접근제어라 함

 - 특정 사용자에게 지정된 영역만 접근통제하는 기능

 --> DBMS는 이를 위한 권한 관리 모듈을 가지고 있음

 

데이터베이스 관리자(DBA)

--> DB 시스템 전체에 대한 보안을 관리함

--> 사용자들에게 새로운 계정과 암호를 만들어 줌

--> 어떤 사용자에게 권한을 부여/회수하는 일

--> 보안 사고시 DB에 대한 감사 실시

 

데이터베이스 보안 기법(4가지)

 

1) 권한 부여 테이블 사용

 - 사용자 프로파일(user profile)

 --> 사용자가 접근할 수 있도록 권한이 부여된 데이터 객체와 이 데이터 객체에 대해 수행시킬 수 있는 연산에 관한 정보

 - 권한부여 테이블

 --> 모든 사용자에 대한 프로파일을 하나의 테이블로 종합 관리

 --> T[i,j] : 데이터 객체 j에 대해 사용자 i가 수행할 수 있는 연산 권한

 2) 뷰 기법

 - 뷰의 정의 그 자체가 권한 부여 기법

 - 민감한 데이터를 권한이 없는 사용자로부터 은닉

CREATE VIEW 학생_뷰
	AS	SELECT 학번, 성명, 학과	
    	FROM 학생
        WHERE 학과 = '컴퓨터';

약점 : 갱신이나 삽입, 삭제와 같은 연산에 제약이 있다

 

3) GRANT/REVOKE 기법

--> 뷰와는 달리 허용된 데이터에 대해 연산 제한이 가능

--> 특정 데이터와 연산을 특정 사용자만 수행할 수 있게 권한 부여를 명세할 수 있다

 

 - 테이블의 생성자? 테이블에 대한 모든 권한(테이블 제거 연산 포함) 가짐

 - 권한 일부를 다른 사용자에게 부여 가능

 

- DBA? DBMS에 있는 모든 자원 접근 권한을 가진 사람

 - 따라서, 생성자나 DBA는 GRANT 문을 사용하여 권한 일부를 다른 사용자에게 다시 부여 가능

 

SQL GRANT 명령문

 - 권한부여 형식

GRANT 권한_리스트 ON 객체 TO 사용자 [WITH GRANT OPTION]

WITH GRANT OPTION : 다른 사용자에게 자기가 부여받은 권한 또 다시 부여

 

 - 권한 회수 형식

REVOKE [GRNAT OPTION FOR] 권한_리스트 ON 객체 FROM 사용자 {CASCADE, RESTRICT};

GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있게 한 권한 자체를 취소

 

ex

생성자 : GRANT SELECT, INSERT ON 학생 TO A;
생성자 : GRANT SELECT ON 학생 TO B WITH GRANT OPTION;
사용자B : GRANT SELECT ON 학생 TO C;
생성자 : REVOKE SELECT ON 학생 FROM B CASCADE;

위 과정을 설명하자면,

줄 1 : A가 SELECT, INSERT 권한을 부여받음

줄 2 : A가 B에 SELECT 권한을 부여, 또 그 권한을 다른 곳에 다시 부여할 수 있는 옵션을 줌 

줄 3 : B는 WITH GRANT OPTION으로 권한부여 가능, 그래서 C에 SELECT 권한 부여

줄 4 : 생성자가 학생 B의 SELECT를 회수, 그리고 CASCADE로 인해 C의 SELECT 권한도 사라짐

남은 권한은? A의 SELECT, INSERT 권한만 남음

 

4) 데이터 암호화

 --> 허가 받지 않은 사람들이 내용을 쉽게 이해할 수 없도록 은폐시키기 위해 데이터를 암호로 바꾸는 것

 - 암호화 시스템

 --> 암호화 알고리즘(E : encryption algorithm)

 --> 암호화 키(K : encryption key)

 --> 해독 알고리즘(D : decryption algorithm)

 --> 평문 P(plaintext) : 전송이나 저장해야 될 원시 데이터

 --> 암호문 C(ciphertext) : 암호화된 결과

 then 

 

암호화 기법

1. 전치암호화 기법

--> 문자들을 일정한 간격으로 나누고 위치를 변경시키는 것

ex. 

평문 :    ENCRYPTION ALGORITHM

암호문 : NERCRYITNOA b GLROTIMH

--> 데이터를 두 문자씩 나누고 다시 그 문자를 서로 교환하는 방법

--> 공백도 하나의 문자로 취급하므로 b로 표시

즉, EN|CR|YP|TI|ON|bA|LG|OR|IT|HM

으로 나눠서 두 글자의 자리를 변경

 

2. 대체암호화 기법

--> 평문의 문자를 다른 문자 정보로 대체

ex.

평문          : THIS TEXT IS TOP SECRET

암호화 키  : TODAY

--> 1단계 : 평문을 암호화키와 같은 길이의 블록으로 나눔(5개씩)

--> 진행 :  THISb|TEXTb|ISbTO|PbSEC|RETbb|

--> 2단계 : 대체할 문자 코드를 b=00, A=01, B=02, ,,, Z=26으로 정하고 해당 정수코드로 대체

--> 진행 : 2008091900 2005242000 0919002015 1600190503 1805200000

--> 3단계 : 암호화키에 대해서도 2단계 적용

--> 진행 : 암호화 키? 2015040125

--> 4단계 : 문자별로 합산(블록 + 암호화키) 한 후 mod 27 적용

--> 5단계 : 정수코드에 해당하는 문자로 바꾸어 암호문을 얻음

--> 진행 : 암호문? MWMTY MTAUY BGDUM IOWFA KTXAY

 

그외 암호화 기법

DES 기법

--> 미국 정부가 일반 대중이 사용할 수 있도록 개발한 암호화 기법

--> 대체 암호화 기법과 순열을 사용함

 

공개키 암호화 기법

--> 공개키 암호화

--> 두 개의 키를 사용

--> 암호화 키와 해독 키

 

MySQL 암호화 기법

--> MySQL에서 지원되는 암호화 알고리즘은 AES기법을 사용, 방법은 일반적 테이블 구문과 동일하며, 마지막에 

ENCRYPTION='Y' 옵션만 추가로 넣으면 됨

CREATE TABLE encryption_test(
	id int,
    txt varchar(100)
) ENCRYPTION='Y';

 

회복

 - 데이터 저장장치

 --> 1. 휘발성 저장장치(volatile storage)

 --> 메인 메모리 / 시스템 고장 시 저장된 정보 유실

 --> 2. 비휘발성 저장장치(nonvolatile storage)

 --> 디스크나 자기 테이프 / 시스템 고장시에도 저장된 정보는 손실되지 않음, DB에서 디스크가 많이 사용하고 자기 테이프는 백업용으로

 --> 3. 안정 저장장치(stable storage)

 --> RAID 시스템 : 서로 다른 디스크에 블록 사본을 저장

 

데이터베이스 입출력 연산

 - 디스크와 메인메모리 사이의 블록 이동

 --> Input(B) --> 데이터 B가 포함된 디스크 블록을 메인 메모리로 이동 - 요청에 의해(On demand)

 --> Output(C) -->  데이터 C가 포함되어 있는 버퍼 블록을 디스크 블록에 이동시켜 기록 - 버퍼 관리자에 의해(by Buffer Manager)

 

회복이란?

장애가 일어났을 때 데이터베이스를 장애 이전의 일관된 상태(consistent state)로 복원시키는 것

 

일관된 상태?

데이터베이스에 오류나 모순이 없는 상태

 

장애?

시스템이 정해진 명세대로 작동하지 않는 상태를 말함

--> 장애의 원인 : 내부적 문제(프로그램 오류, 하드웨어 결함, 소프트웨어의 결함), 외부적 문제(화재, 정전)

 

회복의 개념

회복의 기본원리 : 데이터의 중복(data redundancy) 저장

 - 덤프(dump)

 : 주기적으로 DB 전체를 다른 저장장치로 복제

 - 로그(log)

 : DB가 변경될 때마다 변경되는 데이터 값의 이전 값(old value)과 이후 값(new value)을 별도의 로그 파일에 저장해 두는 것

 --> 일지(journal)라고 함

 

장애 발생 시 회복을 위해 취할 수 있는 조치

--> REDO와 UNDO 두가지 유형이 있다

 

REDO(재수행)

--> DB 내용 자체가 손상된 경우에 사용

--> 복사본과 로그 파일을 이용하여 복구하는 것

--> 가장 최근에 덤프받은 복사본을 적재시킨 뒤 이 복사본 이후에 일어난 변경만을 로그를 이용하여 재수행

REDO 수행 절차

 

UNDO(취소)

--> DB 내용 자체는 손상되지 않았지만, 변경된 내용에 대한 신뢰성을 잃어버린 경우에 사용

--> 로그 파일을 이용하여 모든 변경된 사항들을 취소시킴

--> DB를 원래의 상태로 복원시킴

UNDO 수행 절차

데이터베이스 로그를 이용한 회복

데이터베이스 로그

--> 데이터베이스 변경에 대한 기록으로 가장 많이 사용

--> 로그를 구성하는 레코드

 --> 트랜잭션 ID, 데이터 항목, 이전 값(old value), 이후 값(new value)

 

로그 레코드의 유형

- <T, Start>         : 트랜잭션 T가 시작될 때 기록되는 로그 데이터

- <T, X, V1, V2>  : 트랜잭션 T의 데이터 항목 X를 이전 값 V1에서 이후 값 V2로 변경되었음을 나타내는 로그 데이터

- <T, Commit>    : 트랜잭션 T가 모든 갱신을 성공적으로 완료

- <T, Abort>        : 트랜잭션 T가 철회되었음을 나타내는 로그 데이터, 모든 변경 사항을 취소함

 

즉시갱신 회복기법(immediate update)

데이터 변경 연산이 발생할 때마다 데이터 변경 결과를 즉시 데이터베이스에 반영하는 것

--> 데이터베이스 뿐만 아니라 로그 파일에도 변경 내용을 함께 저장하게 됨

 

트랜잭션 수행 중에 실패 발생해 트랜잭션을 철회할 때

--> 로그 파일을 참조하여 UNDO 작업을 수행

 --> 트랜잭션이 실행되기 전 상태의 데이터값으로 복원

 --> 로그 레코드 값 중에서 이전 값(old value)이 사용됨

 --> 로그 레코드 형식

 <트랜잭션 ID, 데이터 항목, 이전 값, 이후 값>

 

수행과정?

--> 트랜잭션 T가 실행 시작 --> 로그에 <T, Start> 레코드 기록 --> 트랜잭션이 Write를 실행할 경우 --> 로그에 적절한 로그 레코드 기록 후 Output 연산 실행 --> T가 부분 완료할 경우 --> 로그에 <T, Commit> 레코드가 기록 --> 로그가 데이터베이스 회복에 사용될 수 있기 위해서 로그 레코드가 기록되기 전 데이터베이스가 갱신되선 안됨! --> 로그 레코드를 안정 저장소에 기록 후 Output(B) 실행

 

즉시 갱신 회복 예)

트랜잭선 T1 : 계좌 A에서 50원을 계좌 B로 송금

트랜잭션 T2 : 계좌 C로 부터 100원을 인출

실제로 로그와 DB에 출력되는 순서

만약 장애가 일어나면 회복 기법은 로그를 참조하여

--> 만일 로그에 <T, Start>레코드만 있고 <T, Commit> 레코드가 없으면 T는 UNDO 되어야 함

--> 만일 로그에 <T, Start>레코드와 <T, Commit> 레코드가 모두 다 있으면 T는 REDO 되어야 함

 

회복기법 적용의 예

 - 만약 T1이 Commit하기 직전에 시스템 붕괴 : UNDO(T1) 실행 : A = 1000, B = 2000

 - 만약 T2가 Commit하기 직전에 시스템 붕괴 : UNDO(T2) 실행 후 REDO(T1) 실행 : A = 950, B = 2050, C=700

 - 만약 T2가 <T2, Commit> 로그 레코드 출력 직후 시스템 붕괴 : REDO(T1), REDO(T2) 실행 : A = 950, B = 2050, C = 600

 

지연갱신 회복

--> 트랜잭션이 부분 완료될 때 까지 모든 변경 내용을 로그 파일에만 저장하고, 데이터베이스에 저장하는 것은 지연한다

--> 부분 완료 상태에 이르면 로그 파일에 저장된 변경 내용을 데이터 베이스에 반영 

--> 만일 트랜잭션 실행 완료 전 장애 발생 시 로그에 있는 정보는 그냥 버리고 무시,

--> 따라서 UNDO 연산이 불필요

--> REDO 연산만 수행되므로 로그 레코드에는 이전 값 필드가 없어도 됨, 즉

--> 로그 레코드는 <트랜잭션 ID, 데이터 항목, 변경된 값> 으로 구성

예시

만일 로그에 <T, Start> 레코드만 있고 <T, Commit> 레코드 없으면 DB에 저장된 내용 없기에 로그 레코드를 폐기

--> 만일 로그에 <T, Start> 레코드와 <T, Commit> 레코드 모두 있으면 T는 REDO 연산을 실행해 로그 값들로 DB를 갱신

실제 출력순서?

지연 회복 기법 적용의 예

 - T1이 Commit 하기 직전에 시스템 붕괴 : 아무런 조치 필요 없음

 - T2가 Commit 하기 직전에 시스템 붕괴 : REDO(T1) : A = 950, B = 2050, C = 700

 - T2가 <T2, Commit> 레코드 출력 직후 시스템 붕괴 : REDO(T1), REDO(T2) 실행 : A = 950, B = 2050, C = 600

 

검사 시점 회복

--> 로그를 이용하는 기법에서는 REDO와 UNDO 되어야 할 트랜잭션을 결정해야 하는데, 이를 위해선 원칙적으로 로그 전체를 분석해야

--> 시간이 너무 많이 걸리고 할 필요없는 REDO 연산 반복해야하는 문제

--> 이러한 문제 해결위한 방법이 검사시점(checkpoint) 방법이다

--> 이 방법은 트랜잭션을 수행하는 동안 일정 간격으로 검사시점을 만들어 놓는다

어우 많아

 

미디어 회복

--> 지금까진 휘발성 저장장치만 고려

--> 비 휘발성 저장장치의 내용이 손상되는 경우?

미디어(디스크) 회복의 기본 개념

--> DB내용 전체를 주기적으로 다른 안전 저장장치에 덤프(dump)

--> 미디어 장애가 발생되었을 때? 가장 최근의 덤프를 이용해 장애 발생 이전에 어떤 일관된 데이터베이스 상태로 복구한 후 , 

       로그를 이용해 가장 최근의 일관된 상태로 데이터베이스를 복원시킴

 

최근엔 디스크가 이중와 및 삼중화 과정을 거치는 추세로