Information Systems Control & Audit(13)

Information Systems Control & Audit(13) Shin, SooJungBased on Ron’s book

Chapter 14 Database Controls

(1) Introduction • The database subsystem provides functions to define, create, modify, delete, and read data in an information system. • Declarative data: static aspects of real-world objects and associations- 예) 급여 파일에는 각 직원의 급여율, 직위, 종업원 등을 저장 • Procedural data: dynamic aspects of real-world objects and associations-예) 주식투자를 위한 룰을 저장 • Knowledge base: Declarative data+ Procedural data • Object-oriented DBMS –design objects, multimedia objects… • Datawarehouse, datamart • Data mining

(2) Access controls (1) Discretionary access controls • Owner • schema, views, relations • (2) Restrictions to users who are not owners. • Name-dependent restrictions(content-independent): users either have access to a named data resources or they do not have access to the resources • Content-dependent restrictions: users are permitted or denied access to a data resource depending on its contents • context -dependent restrictions: users are permitted or denied access to a data resource depending on the context in which they are seeking access. • history -dependent restrictions: users are permitted or denied access to a data resource depending on the time series of accesses to and actions they have on data resources • (3) 기타 • Views: View를 활용하여 4가지의 restriction을 구현할 수 있음.(name-dependent, conditional) • Privilege propagation의 제한: horizontal(사용자의 수 제한), vertical(depth의 제한)

(2) Access controls (2) Mandatory access controls • MAC • resources는 classification으로, users는 clearance level로 할당 • User가 resource에 access하는 rule : BLP, Biba model 등의 policy 활용 • (2) DB에 적용 • Classification levels can be assigned to specific data items/attributes in a resord/relation and to records/relations as a whole. The value of the classification level is then compared against the users’s clearance level to determine whether the data item /attributes or records/relations will be made available to the user • View를 implement하는 방법(1)-하나의 record/relation tuple or instance에 있는 데이터를 filtering 하고 사용자에게 가용한 데이터를 결정하기 위해 conditional rule를 사용 -> single tuple • View를 implement하는 방법(2)-각 clearance level에 따라 보안과 무결성 법칙을 만족하는 multiple tuple을 생성(polyinstantiation) • Database subsystem의 access control rule -> OS와 DBMS의 양쪽에 적용 • DB가 replicated된 경우: 각 site에 동일한 접근제어 규칙이 적용되어야 함. • DB가 partitioned된 경우: 사용자의 request가 정확하고 완전하게 route되어야 함.

(3) Integrity controls • E-R model Integrity constraints • Uniqueness: 한 entity의 각 instance는 unique해야 함 • Maximum cardinality/ Minimum cardinality: entity의 instance들의 최대 및 최소 갯수 • Entity identifier: 각 값이 유일하게 entity의 각 instance를 정의하는 attribute를 지정함. • Value type of identifier: attributes의 allowed value type 지정 • Value set of identifier: attributes의 allowed value set 지정 • (2) Relational data model Integrity constraints • Key: candidate key는 relation의 각 tuple을 유일하게 정의해야 함. • Entity:primary key는 null value가 되면 안됨. • Referential: relation 상의 tuple들 사이에 consistency가 유지되어야 함. • (3) Object data model Integrity constraints • A. Structural properties • Unique identifier(시스템생성):각 object는 유일해야 함 • Unique key(사용자생성) • Value type of attribute: attributes의 allowed value type 지정 • Value set of attribute: attributes의 allowed value set 지정 • Types and inheritance: subtype object는 supertype object와 관련된 integrity constraint를 전부보유 • B. Dynamic properties(procedures) • 사용된 언어의 syntactic & semantic rule를 따라야 함. • C. Relationship integrity • Referential: 한 object가 다른 object를 refer할 경우 존재해야 함 • Composition: composite relationship간의 참여 object의 추가 및 삭제 룰의 정의 • Cardinality: relation에 참여하는 object의 최대 및 최소 갯수 정의

(4) Application software controls • When application programs use the database, they should follow certain update and report protocols to protect the integrity of the database • -update protocols: to ensure that changes to the database reflect changes to the real-world entities and associations between entities that data in the database is supposed to represent. • -report protocols: to provide information to users of the database that will enable them to identify errors or irreguralities • (1) Update protocols • Sequence check the order of transaction & master files during batch update • Ensuring correct end-of-file procedures are followed so that records are not lost • Processing multiple transactions for a single record in the correct order • Posting monetary transactions that mismatch a master file record against a suspense account • (2) Report protocols • printing control data for internal table/standing data to ensure it remains accurate and complete • Printing run-to-run control totals • Printing suspense account entries

(5) Concurrency controls • (1) Nature of the shared data resource problem • 해결책: lock out one process from a data resource while it is being used by another process • -> deadlock의 문제점 발생( 두 프로세스가 서로 필요한 데이터를 release하길 기다림) • The problem of deadlock • Solutions to deadlock • Preventing deadlock( two phase locking) • transaction의 effect를 propagate하기 위해 필요한 모든 데이터를 획득하여 다른 프로세스로 부터 lock을 함. 데이터 items들에 대한 모든 update가 완료될때까지 data item들은 release되지 않음. • growing phase : the transaction acquires locks without releasing locks • Shrinking phase: when the transaction releases a lock -> release all locks • (5) Distributed database concurrency controls • replicated DB: 한 DB에 primary copy를 설정하고, transaction이 데이터 item접근시 primary copy의 lock을 획득함. • Partitioned DB: transaction은 접근하고자 하는 data item에 대한 scheduler를 찾아야 함. Process P Process Q Salesperson 1 Salesperson 2 Time t+1 50 Time t Time t 80 Time t Time t 100 Time t+1 Time t+1 90 Data source 1 Data source 2 PART A 100 PART B 150

(6)Cryptographic controls • (1) The primary means of encrypting data: block encryption • (2) Portable storage media: encryption이 controller에 있는 cryptographic device에 의해 수행됨. 미디어가 도난시 데이터의 privacy를 보장하지만 사용자 사이에는 보호하지 못함 • (3) DB가 replicated된 경우: • 각 replication마다 동일한 키를 사용할 경우, 복사본을 만들고 복구하기가 쉽고, 사용자의 transaction에 대해 load balancing하기가 용이하지만 • 키가 안전하게 분산되어야 하고, 한 키에 대한 변경은 모든 사이트의 키의 변경을 가져와야 함 • 각 사이트마다 각 키가 존재하면 조금 더 안전하지만 백업이나 작업 분산이 어려움. • (4) DB가 partitioned된 경우 • 사용자에 의해 소유된 데이터가 분산됨 • 사용자에 따라 동일한 키가 사용될 경우 사용자 데이터에 대한 접근이 명확하나 키가 여러 사이트에서 보관되어야 하므로 위의 문제들이 발생함. • 여러 사이트가 각기 다른 키를 가지고 있는 경우 각 사이트의 데이터는 조금 더 안전하나 transaction이 여러곳의 데이타들을 접근할 때 높은 overhead발생

(7) File handling controls • (1) File handling controls are used to prevent accidental destruction of data contained on a storage media • (2) 방법 • internal label • Generation numbers • Retention dates • Control totals • Magnetic tape file protection rings • Read-only switches • External labels

(8) Audit trail controls • (1) Accounting audit trail • Accounting audit trail을 유지하기 위해서 database subsystem은 다음과 같은 기능을 수행함. • It must attach a unique time stamp to all transactions applied against the database definition or the database(implosion, explosion) • The database subsystem must attach beforeimages and afterimages of the data item against which a transaction is applied to the audit trail entry for the transaction • The database subsystem must provide facilities to define, create, modify, delete, and retrieve data in the audit trail • (2) Operations audit trail • Maintains the chronology of resource consumption events that affect the database definition or the database • Check Response time, amount of resource consumed •  DB reorganization 고려 •  transaction 적용 process의 재구성 고려

(9) Existence controls • (1) 5 types of failure • application program error • System software error • HW failure • Procedural error • Environmental failure • (2) Existence control • loss 발생시 복구수행 • Backup strategy • Recovery strategy • a. DB의 current state가 복구되어야 할 경우(rollforward operation)- using prior version or dump of DB and a log of transaction or changes • b. DB의 prior state가 복구되어야 할 경우(rollback operation)- using a log of changes to the database

(9) Existence controls (1) Grandfather, father, son strategy • Maintaining the previous two version of a master file and the previous version of the transaction file • If the current(son) version of the master file is lost, it can be recovered by processing the current transaction file against the previous version of the master file(father). • If the previous version of the master file is lost during recovery, it too can be recovered by using the grandfather version of the master file and the previous version of the transaction file. Father-kept for Further 2 cycles Grandfather-kept for Further 2 cycles Input master file Transaction file Update program Son-kept for Further 3 cycles Output master file Update reports

(9) Existence controls (2) Dual recording/mirroring strategy • Maintaining two completely separate copies of the database and updating both simultaneously • Assists a recovery from environmental failure, processor failure, or storage medium failure, but it does not protect the database against software error or procedural error. Remotely located Primary database Primary processor Frontend processor Duplicate database Duplicate processor Remotely located

(9) Existence controls (3) Dumping • Copying the whole or a portion of the database to some backup medium. • Recovery involves rewriting the dump back to the primary storage medium and reprocessing transactions that have occurred since the time of the dump • Physical dumping involves reading and copying the database in the serial order of the records on the storage medium(track by track). – global recovery of the database • Logical dumping involves reading and copying the database in the serial order of the logical records in a file – selective recovery of the database

(9) Existence controls (4) Logging • Recording a transaction that changes the database or an image of the record changed by an update action • 3 types of logging • transaction log to allow reprocessing of transactions during recovery • Beforeimage logs to allow rollback of the database: record가 update되는 각 시간마다 update 되기전의 image가 logged됨 • Afterimage logsto allow rollforward of the database:record가 transaction에 의해 update된 후에 그 image가 log에 copy되어야 함 Unsuccessful input transaction Transaction input successful input transaction DBMS DB

(9) Existence controls (5) Residual dumping • Full dump의 overhead를 줄이려는 목적 • Logging records that have not been changed since the last database dump • The database is recovered by going back to but not including the second last residual dump and rolling forward the database using the residual dump log. • Residual dumping reduces the overheads associated with dumping because records that have been changed and recorded on the log are not then dumped

(9) Existence controls (6) Differential files/shadow paging • Differential file is a file of changes made to the database • Keeping the database intact and writing changes to the database to a separate file • In due course these changes are written to the database • If failure occurs before changes are applied, the intact database constitutes a prior dump of the database • Providing a log of transactions have been kept, these transactions can then be reprocesses against the database. shadow Page table Current Page table shadow Page table Separate channels Differential file Page 3 (old) 1 1 2 2 Page 2 DBMS 3 3 Page 3 (new) Primary file 4 4 Page 4 Points to New page 3 Points to old page 3 Page 1 (a) Differential file strategy (b) Shadow page strategy

Information Systems Control & Audit(13)