15. 데이타베이스서버의 신뢰성과보안성
이장의주요내용 회복이필요한이유및절차 회복을위한개념들 로그 (log) UNDO 와 REDO 체크포인트 (checkpoint) 회복기법들 즉시갱신회복프로토콜 (UNDO/REDO) 그림자페이지기법 Fundamentals of Database System, 6 th, Elmasri Navathe, 23 장
데이타베이스회복기술이필요한이유 T1 T2 T3 Crash 컴퓨터고장또는시스템붕괴 트랜잭션오류 시스템오류 트랜잭션이탐지한지역적오류또는예외조건 디스크고장 물리적문제와재해 Time 회복이란? 데이타베이스를실패시점에서가장가까운일관적인상태로복원하는것 회복은트랜잭션의원자성과영속성을보장하기위해필요
회복을위한전형적인절차 백업 체크포인트 시스템로그 : 트랜잭션이실행되는동안데이터항목의변경에대한정보를유지 T1 T2 T3 Crash 시간 비재해적실패로부터의회복 로그에의해 Undo/Redo 비재해적실패예 ) 트랜잭션오류, 컴퓨터고장등 재해적실패로부터의회복 백업된데이타베이스의과거사본으로되돌려놓음 실패시점까지완료된트랜잭션들의연산들을로그로부터다시적용
Types of failure Action failure program logic error exception-handling in program Transaction failure abnormal termination undo System failure System crash / malfunction restart media failure disk crash restore DB Local failure Global failure
1. Action failure recovery logic error database operation fails anticipated failure condition exception handling code in application program ( if - then - ) else part
2. Transaction failure recovery(1) failure caused by unplanned, abnormal termination explicit ROLLBACK not failure planned abnormal termination causes arithmetic overflow division by zero storage protection violation recovery : forced rollback undo all changes via log records
2. Transaction failure recovery(2) Execution of UNDO Recovery Manager update subsystem : new --> old value delete subsystem : null --> old value insert subsystem : new value --> null UNDO logic principle in the case of UNDO process failure UNDO (UNDO(UNDO - - - (UNDO(x) - - - ) = UNDO(x) : idempotent
2. Transaction failure recovery(3) log entries ( T : Transaction_id ) [start-transaction, T] [write-item, T, X, old-value, new-value] [read-item, T, X] [commit, T] Write-ahead log(wal) protocol log record must be written on physical log before writing a record to the physical DB to complete COMMIT processing, UNDO / REDO portion of all log records must be written to the physical log
Incremental Log with immediate updates Log : < Tran_id, data-item-name, old-value, newvalue > To : Read ( A, a1 ) < To Starts > a1 := a1-50 write ( A, a1) < To, A, 1000, 950 > A= 950 Read (B, b1) b1 := b1 + 50 log write ( B, b1) < To, B, 2000, 2050 > B= 2050 < To Commit > Undo(Ti) : restore to the old-value Redo(Ti) : set to the new-value Immediate updates DB A = 1000 B = 2000
3. System failure recovery system crash / malfunction system stop restart Contents of main memory(buffer) are lost no physical damage in DB on secondary storage soft crash incomplete transactions must be rolled-back recovery manager should identify victims redo undo
Checkpoint approach Step 1: move log records in log buffer to physical log Step 2: move checkpoint record to physical log list of all transactions in progress address of those transactions most recent log record Step 3: move contents of DB buffer to physical DB(DISK) Step 4: write the address of checkpoint record of log into restart file
Transaction categories (1) time T1 Tc Tf T2 T3 T4 T5 checkpoint system failure T2, T4 : REDO T3, T5 : UNDO T1 : need nothing How to determine? i) Set UNDO list = {transaction listed in checkpoint record} Set REDO list = ii) Search log forward starting from checkpoint record if BEGIN TRANS Keep it in UNDO list if COMMIT TRANS transfer it to REDO list
Transaction categories (2) Execution of REDO by Recovery Manager update subsystem delete subsystem insert subsystem REDO transactions by tracing forward through the log records REDO logic principle : idempotent - in the case of REDO process failure REDO(REDO(REDO..(X).) = REDO(X)
System Startup Warm start restart after a Controlled System SHUTDOWN command make System Vacant : Complete serve for current trans. only no involve redo/undo Emergency restart casual restart : RESTART command after failure need recovery procedures (undo/redo) may need DB reloading from archive Cold start restart from archive version of DB start from installation
4. Media failure(hard crash) recovery disk head crash, disk controller failure partly damaged DB on secondary storage use periodical dump run utility program (dump / restore utility ) i. load DB on to new device from the most recent archive dump ii. redo all transactions completed since that dump, via log Note : log failure? : duplex log
그림자페이지기법 (NO-UNDO/NO-REDO) 갱신된버퍼를디스크의다른위치에기록하므로한데이터항목에대하여여러개의버전이존재 X Y X' Y' Database X 와 Y : 데이터항목의예전 (old) 복사본 X 와 Y : 데이터항목의현재 (new) 복사본
그림자페이징의예
그림자페이징기법의장 / 단점 트랜잭션실행하는동안실패로부터회복하기위해 수정된데이타베이스페이지들을반환 현재디렉토리를폐기 그림자디렉토리가다시현재디렉토리가됨 단점 갱신된데이타베이스페이지들이디스크에서위치를바꿈 이로인해복잡한저장관리방법이없으면연관된데이타베이스페이지들을디스크에서가까운위치에함께관리하기어려워짐 디렉토리가크면트랜잭션이그림자디렉토리를디스크에기록하는오버헤드가심각 트랜잭션이완료되었을때쓰레기수집을어떻게할것인가?
이장의주요내용 관계데이타베이스시스템의보안의목표 DBMS 가지원하는보안 사용자확인을위한계정보안 데이타베이스객체를보호하기위한접근보안 데이타베이스감사
관계데이타베이스시스템의보안 데이타베이스보안의목표 데이타베이스의무결성을보호하고인증되지않은정보의사용을방지하는것 권한을부여받은사용자와프로그램만이데이타베이스의데이터와연산에접근할수있다는것을시스템에서보장해야함 DBMS 가지원하는보안에대한세가지타입 사용자확인을위한계정보안 데이타베이스객체를보호하기위한접근보안 ( 임의접근방법 ) 데이타베이스와화일보호를위한운영체제보안
사용자인증 상용 DBMS 은사용자식별자와암호를데이타베이스의시스템테이블에저장 SQL 은사용자생성, 변경, 삭제하는명령어를제공
사용자계정을처리하는 SQL 예 1 create user Jane identified by crockette; /* 암호를지닌사용자 Jane 생성 */ 2 create user Dick identified by go-man-go default tablespace USERS quota 100 K on USERS; /* 사용자 Dick 에게추가적인특성명시 */ 3 alter user Jane quota unlimited on USERS; /* 사용자 Jane 에게무제한적인세그먼트할당 */ 4 drop user Jane; /* 데이타베이스에서 Jane 삭제 */ 5 alter user Dick account lock; /* 사용자가로그인할수없도록계정에로크를설정 */ 6 alter user Dick identified by stop-please; /* 사용자 Dick 의암호변경 */ 7 alter user Dick password expire; /* 암호의기간이만료되어다음로그인에서새로운암호를물어보게됨 */ 8 create profile LimitedUser limit CONNECT_TIME 10; /* 데이타베이스연결을 10 분으로제한한프로화일생성 */ 9 create user OPS$hannibal profile LimitedUser; /* 사용자의연결시간을제한하는프로화일을사용하여새로운사용자생성, OPS$ : 운영체제계정명시 */ 10 alter user OPS$hannibal identified by use-this-password; /* 운영체제사용자에게 use-this-password 암호로 hannibal 로연결하는것을허용 */
데이타베이스객체의보호 임의접근방법 (discretionary access control) 사용자에게데이타베이스, 스키마, 테이블, 뷰들을읽기, 갱신, 추가, 생성, 삭제로접근할수있는권한을부여하기위해사용 뷰기법 GRANT 와 REVORK 기법
뷰기법 1/2 뷰는정의자체를권한부여기법으로사용 예 1) CUSTOMER 의생성자 U1 이다른사용자 U2 에게 CUSTOMER 릴레이션의일부필드들에대해서만접근할수있도록하기위해 CUSTOMER-VIEW1 을정의하여 U2 에게권한을부여 CREATE VIEW CUSTOMER-VIEW1 AS ( SELECT accountid, lastname, firstname FROM CUSTOMER )
뷰기법 2/2 예 2) 사용자 U3 에 CUSTOMER 의일부투플에대해서만접근을허용하고자할때 CUSTOMER-VIEW2 를정의하여검색권한을부여 CREATE VIEW CUSTOMER-VIEW2 AS ( SELECT * FROM CUSTOMER WHERE balance > 10 ) 보안에민감한데이터를권한이없는사용자로부터은닉시킬수있음 뷰는갱신이나삽입, 삭제와같은연산에제한이있음
GRANT 와 REVORK 기법 1/2 SQL 에서권한부여는 GRANT 명령문에의해명세 GRANT 권한 ON 데이터객체 TO 사용자 / 역할 [WITH GRANT OPTION] 데이터객체 : 릴레이션이름이나뷰이름 사용자 : 사용자나역할리스트 WITH GRANT OPTION : 다른사용자에게자기가부여받은권한을또다시부여할수있음 새로운릴레이션을생성하는사용자는그릴레이션에대한모든권한을자동적으로받음
GRANT 와 REVORK 기법 2/2 권한을취소하기위해 Revork 문사용 REVORK [GRANT OPTION FOR] 권한 ON 데이터객체 FROM 사용자 {cascade, restrict} 데이터객체 : 릴레이션이름이나뷰이름 사용자 : 사용자나역할리스트 GRANT OPTION FOR : 다른사람에게권한을부여할수있게한권한자체를취소 CASCADE : 연쇄취소 ( 기본 ) RESTRICT : 연쇄취소를방지
GRANT 와 REVORK 의 SQL 문예 1. grant insert on Customer to Jane with grant option; /* Customer 객체에새로삽입할수있는권한이사용자 Jane 에게주어짐 */ 2. grant select on Customer to public; /* 모든데이타베이스사용자 (public) 가 Customer 테이블에 select 문을수행할수있도록허용 */ 3. grant all on Employee to Jane; /* Jane에게 Employee 테이블에대한모든권한을부여 */ 4. revoke delete on Employee from Jane; /* 행을삭제할수있는 Jane의권한을취소 */ 5. grant update on Customer(street, city, state, zip) to Jane; /* Jane 이 Customer 의특정컬럼만을갱신할수있도록함 */ 6. create role Clerk not identified; /* Clerk이란 role을생성 */ 7. grant all on Rental, PreviousRental to Clerk; /* rental 테이블에대한모든권한을 Clerk 에게줌 */ 8. grant role Clerk to Dick; /* 어떤특정사용자에게 clerk가될수있도록허용 */ 9. create role FloorManager identified by ImInCharge; /* 암호를가진 FloorManager role 을생성 */ 10. grant role Clerk to FloorManager; /* FloorManager 에게 Clerk role 로수행할수있는모든권한줌 */
데이타베이스감사 - 1/2 추적감사 (audit trail) 데이타베이스에수행된모든연산을추적할수있고각연산을특정사용자및특정로그인별로파악할수있는정보를기록한시스템로그화일 시스템보안을위반하는출처를찾는데중요 어떤정보를추적감사에추가시키는지를제어하는 SQL 문장제공 (Oracle 예 ) audit option; noaudit option; // 로그인과데이타베이스동작시도를기록 // 로그인과데이타베이스동작의기록을하지않음
Oracle 8 에서로그인과데이타베이스동작을추적감사하기위한구성옵션 option All DBA exists Index procedure resource Role rollback segment session Table User View 감사가능한모든명령어들 추적감사에기록되는연산들 grant, revoke, audit, create, 또는 alter tablespace 와같이 DBA 권한을요구하는명령어들 객체가이미존재하기때문에실패한 SQL 문 create, alter, 또는 drop index Create, alter, 또는 drop procedure, function, 또는 package; create package body 테이블, 클러스터, 뷰, 인덱스, 테이블스페이스, 타입과 synonym 의 create 와 drop create, alter, drop, 또는 set role create, alter, 또는 drop rollback segment 로그인시도 create, alter, 또는 drop table create, alter, 또는 drop user create 또는 drop view
데이타베이스감사 - 2/2 데이타조작연산에대해서추적감사지원예 audit select on TimeCard; audit delete on Employee; audit all on PurchaseOrder; noaudit select on Movie; SYS.AUD$ 라불리우는데이타베이스테이블에추적감사정보저장하고데이타베이스뷰를통해이용 select OS_Username, Username, Terminal, Retruncode, Timestamp, Logoff_time from DBA_AUDIT_SESSION; /* 사용자세션에관한정보 */ select OS_Username, Username, Terminal, Owner, Obj_Name, Action_Name, Returncode, Timestamp from DBA_AUDIT_OBJECT; /* 객체를변경하려는시도의결과를검색 */
View 하나또는둘이상의기본테이블 (base table) 로부터유도되어만들어지는가상테이블 (Virtual table) 외부스키마는뷰와기본테이블들의정의로구성됨 기본테이블을들여다보는 ' 유리창 '(window) - 동적임 뷰의정의는시스템카탈로그에파싱된형태로저장됨 33
View 의생성 [1/2] 일반형식 CREATE VIEW 뷰 _ 이름 [( 열 _ 이름리스트 )] AS SELECT 문 [WITH CHECK OPTION]; CREATE VIEW CSTUDENT(SNO, SNAME, YEAR) AS SELECT SNO, SNAME, YEAR FROM STUDENT WHERE DEPT = ' 컴퓨터 ' WITH CHECK OPTION; CREATE VIEW DEPTSIZE(DEPT, STNUM) AS SELECT DEPT, COUNT(*) 34
View 의생성 [2/2] CREATE VIEW HONOR(SNAME, DEPT, GRADE) AS SELECT STUDENT.SNAME, STUDENT.DEPT, ENROL.FINAL FROM STUDENT, ENROL WHERE STUDENT.SNO = ENROL.SNO AND ENROL.FINAL > 95; CREATE VIEW COMHONOR AS SELECT SNAME FROM HONOR WHERE DEPT = ' 컴퓨터 '; 35
View 의제거 일반형식 DROP VIEW 뷰 _ 이름 RESTRICT CASCADE; DROP VIEW DEPTSIZE RESTRICT; Note : "propagated destroys 기본테이블이제거되면그위에만들어진 인덱스나뷰도자동적으로제거됨 36
View 의조작연산 [1/3] 기본테이블에사용가능한어떤검색문도뷰에사용가능 변경 ( 삽입, 삭제, 갱신 ) 연산의제약 열부분집합뷰 (column subset view) CREATE VIEW SVIEW1 AS SELECT SNO, DEPT FROM STUDENT; CREATE VIEW SVIEW2 AS SELECT SNAME, DEPT FROM STUDENT; 행부분집합뷰 (row subset view) CREATE VIEW SVIEW3 AS SELECT SNO, SNAME, YEAR, DEPT FROM STUDENT WHERE YEAR=4; 37
View 의조작연산 [2/3] 죠인뷰 (join view) CREATE VIEW HONOR(SNAME, DEPT, GRADE) AS SELECT STUDENT.SNAME, STUDENT.DEPT, ENROL.FINAL FROM STUDENT, ENROL WHERE STUDENT.SNO = ENROL.SNO AND ENROL.FINAL > 95; 통계적요약뷰 (statistical summary view) CREATE VIEW COSTAT AS SELECT CNO, AVG(MIDTERM) FROM ENROL GROUP BY CNO; 뷰는제한적인갱신만가능함 38
View 의조작연산 [3/3] 뷰이론적으로변경이가능한뷰실제로변경이가능한뷰 39
View 의장단점 뷰의장점 논리적독립성을제공 ( 확장, 구조변경 ) 데이타의접근을제어 ( 보안 ) 사용자의데이타관리를간단히함 여러사용자에다양한뷰를제공 뷰의단점 독자적인인덱스를가질수없음 정의를변경할수없음 삽입, 삭제, 갱신연산에제한이많음 40