1. Database Integrity and Security HAP 709 – Healthcare Databases George Mason University Janusz Wojtusiak, PhD Fall, 2010

2. Goal • This lecture covers two important topics in databases • How to ensure correctness of a database? • How to ensure security of a database?

3. Part 1: Integrity

4. Database Integrity • Constraints require database to have specific properties • Database is in consistent state if it satisfies all constraints • Consistent database is a database in consistent state.

5. Example Patient table: Visit table:

6. Example Patient table (as of 2010):

7. Example Order table: Item table:

8. Reasons of Lost Integrity • Computer failure • Hardware error • Software error • Network error • Computation failure • Overflow, logical error, division by zero, etc. • User error • Concurrency control failure • Multiple users updating the same records at the same time.

9. Recovery from Failure • Catastrophic failure • Data is lost due to storage error • Recover from backup • Non-catastrophic failure • Reverse/fix only changes that made database inconsistent • Redo operations using log (very slow) • Sometimes requires checking what is the correct state, checkpoints • Adding redundancy to database.

10. Preventing Inconsistency • Constraints supported by RDBMS • Most RDBMS allow database designers to create constraints that prevent inserting incorrect data. • Transactions • Transactions are used to make sure that a set of operations preserves integrity.

11. Preventing Inconsistency • Consider the following table • This type of error can be very easily detected by checking reasonable range of temperature!

12. Constraints in SQL • NOT NULL • A field must contain a value • UNIQUE • Two or more records cannot have the same values for specified fields • PRIMARY KEY • Combination of the above • FOREIGN KEY • Value of a field must match value in other table.

13. Constraints in SQL • CHECK • Enter specified conditions for validity of values • E.g. discounted price < price, Age > 0, … • It is possible to create a DOMAIN • Define a set of possible values • Use when there are several fields with the same domain • Constraints may not capture full correctness of database.

14. Transactions • Transaction is a set of operations that preserve consistency of database … • Transactions provide • Coherent sets of operations that can be used for recovery • Isolation between concurrent accesses to database. Begin Operation 1 Operation n Commit Rollback

15. Part 2: Security

16. Security in Databases • Users should be able to access or modify data they are allowed to • Users should not be able to access or modify data they are not allowed to.

17. Security in Databases • Security policy specifies what users are allowed to do • Security mechanism enforces the policy • Mandatory Access Control – based on system-wide policies that cannot be changed by individual users • Discretionary access control – creator of an object gets full rights to it, and can assign rights to others.

18. Security in SQL – Giving Permissions • GRANT command is used to provide others with permissions GRANT <privileges> ON <object> TO <users> [WITH <options>] • Users can grant rights to select, update, insert, delete, and so on • Users can also grant all privileges.

19. Security in SQL – Removing Permissions • REVOKE command is used remove permissions to database REVOKE <permission> ON <object> FROM <users>

20. Security in SQL - Views • Views are an easy way to provide users with access to aggregated or some data, not entire table(s) • Views are “stored select queries” that can have their own permissions CREATE VIEW <name> AS <query> • Example: CREATE VIEW patients AS SELECT * PERSON WHERE p_type = ‘Patient’;

21. Examples in PostgreSQL