Modern Database Management Thirteenth Edition Global Edition Databases

Modern Database Management Thirteenth Edition, Global Edition 資料庫應用補充 Databases in Applications 授課老師：楊立偉教授，台灣大學管系 (13版於Chapter

Transaction Integrity: ACID Rules • Atomic – Transaction cannot be subdivided • Consistent – Constraints don’t change from before transaction to after transaction • Isolated – Database changes not revealed to users until after transaction has completed • Durable – Database changes are permanent Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Figure 7 -19 A SQL Transaction Sequence (Pseudocode) Environment Copyright © 2020 Pearson Education

Controlling Concurrent Access • Problem – In a multi-user environment, simultaneous access to data can result in interference and data loss (lost update problem) • Solution – Concurrency Control – Managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multiuser environment Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Figure 7 -20 Lost Update (No Concurrency Control in Effect) Simultaneous access causes updates to cancel each other. A similar problem is the inconsistent read problem. Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Concurrency Control Techniques • Serializability 循序排隊 – Finish one transaction before starting another • Locking Mechanisms 鎖定 – The most common way of achieving serialization – Data that is retrieved for the purpose of updating is locked for the updater – No other user can perform update until unlocked Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Figure 7 -21 Updates with Locking (Concurrency Control) Locking solves the lost update problem

Locking Mechanisms • Locking level: 鎖定層級越大則影響範圍越大、同時操作性越低 – Database – used during database updates – Table – used for bulk updates – Block or page – very commonly used – Record – only requested row; fairly commonly used – Field – requires significant overhead; impractical • Types of locks: – Shared lock – Read but no update permitted. Used when just reading to prevent another user from placing an exclusive lock on the record 又稱讀取鎖定read lock – Exclusive lock – No access permitted. Used when preparing to update 又稱寫入鎖定write lock Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Deadlock • An impasse that results when two or more transactions have locked common resources, and each waits for the other to unlock their resources 彼此等待對方所有的 • Figure 7 -22 shows the problem of deadlock • John and Marsha will wait forever for each other to release their locked resources! Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Managing Deadlock • Deadlock prevention: – Lock all records required at the beginning of a transaction – Two-phase locking protocol ▪ Growing phase 逐步增加鎖定 ▪ Shrinking phase 逐步釋放鎖定 – May be difficult to determine all needed resources in advance • Deadlock Resolution: – Allow deadlocks to occur – Mechanisms for detecting and breaking deadlock ▪ Resource usage matrix 自動偵測是否發生死鎖 ▪ Back out one deadlock at a time 一次解除一個死鎖 ▪ Re-run transaction 重新執行 Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Versioning • Optimistic approach to concurrency control • Instead of locking • Assumption is that simultaneous updates will be infrequent • Each transaction can attempt an update as it wishes 想寫就寫 • The system will create a new version of a record instead of replacing the old one • When a conflict occurs, accept one user’s update and inform the other user that its update needs to be tried again. 有版本衝突時，保留一方結果，通知另一方需要重新寫入 • Use of rollback and commit for this Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Figure 7 -24 The Use of Versioning Better performance than locking Copyright © 2020

Data Security • Database Security: Protection of the data against accidental or intentional loss, destruction, or misuse • Increased difficulty due to Internet access and client/server technologies 網路增加了資料安全的困難 Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Threats to Data Security • Accidental losses attributable to: 意外 – Human error 人為錯誤 – Software failure 軟體錯誤 – Hardware failure 硬體錯誤 • Theft and fraud 竊取或詐欺 • Loss of privacy or confidentiality 喪失隱私或機密性 – Loss of privacy (personal data) – Loss of confidentiality (corporate data) • Loss of data integrity 喪失資料完整性 • Loss of availability (e. g. , through sabotage) 喪失可用性 Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Figure 7 -25 Possible Locations of Data Security Threats 可能的資料安全威脅來源 Threats come from many sources and vulnerabilities exist in multiple places within an information system. Copyright © 2020 Pearson Education Ltd. All Rights Reserved

Figure 7 -26 Establishing Internet Security Copyright © 2020 Pearson Education Ltd. All Rights

Client–Server Application Security • Static HTML files are easy to secure – Standard database access controls – Place Web files in protected directories on server • Dynamic pages are harder – User authentication 使用者認證 – Session security 連線安全 – SSL for encryption 傳輸加密 – Restrict number of users and open ports 人數及連線限制 – Remove unnecessary programs 移除不用的程式 Copyright © 2020 Pearson Education Ltd. All Rights Reserved

SQL Injection Attacks - example Source : portswigger. net/web-security/sql-injection Copyright © 2020 Pearson Education

Data Privacy • W 3 C Web Privacy Standard – Platform for Privacy Protection (P 3 P) • Addresses the following: – Who collects data 誰收集資料 – What data is collected and for what purpose 收集什麼及用途 – Who is data shared with 誰可使用 – Can users control access to their data 使用者可控制嗎 – How are disputes resolved 爭議時如何處理 – Policies for retaining data 如何保存資料 – Where are policies kept and how can they be accessed Copyright © 2020 Pearson Education Ltd. All Rights Reserved