Database Security

1. Database Security CS461/ECE422 Spring 2012

2. Overview • Database model • Relational Databases • Access Control • Inference and Statistical Databases • Database encryption

3. Reading Material • Chapter 5 of the text

4. Motivation • Databases are a common element in today's system architecture • Hold important information • Target of attacks

5. Database Advantages • Years and years of technology improvements • Data integrity and consistency • ACID transactions • Decent performance in face of integrity and consistency requirements • Common well understood model • Shared access • Controlled access

6. Relational Model • Information stored in relations or tables • Each row is a tuple of attributes • Manipulated by standard SQL language

7. Making Queries • Can select rows to create subtables • Select Name, UID, Financial Aid from Students where College = 'Eng'

8. Combining tables • Can use Join to create single set of tuples from multiple tables. selectA.name, B.Dorm, A.Majorfrom A, B where A.UID = B.UID; A B

9. Database Access Control • In most OS access control, subjects are users, and objects are files (and file-like objects) • In databases, what should the objects be?

10. SQL grant Syntax • grantprivilege_liston resource touser_list; • Privileges include select, insert, etc. • Resource may be a table, a database, a function, etc. • User list may be individual users, or may be a user group Griffiths Wade 76

11. Example Application • Alice owns a database table of company employees: name varchar(50), ssn int, salary int, email varchar(50) • Some information (ssn, salary) should be confidential, others can be viewed by any employee.

12. Simple Access Control Rules • Suppose Bob needs access to the whole table (but doesn’t need to make changes): grant select on employee to bob; • Suppose Carol is another employee, who should only access public information: grant select(name,email) on employee to carol; • not implemented in PostgreSQL (see next two slides for how to work around this) • not implemented for select in Oracle • implemented in MySQL

13. Creating Views • Careful with definitions! • A subset of the database to which a user has access, or: • A virtual table created as a “shortcut” query of other tables • View syntax: createview view_name as query_definition; • Querying views is nearly identical to querying regular tables

14. View-Based Access Control • Alternative method to grant Carol access to name and email columns: createview employee_public asselect name,email from employee; grantselecton employee_public to carol;

15. Row-Level Access Control • Suppose we also allow employees to view their own ssn, salary: createviewemployee_Carolasselect* fromemployee wherename='Carol'; grantselectonemployee_Carolto carol; • And we allow them to update their e-mail addresses: grantupdate(email) onemployee_Carolto carol; • (Or create yet another new view…)

16. Delegating Policy Authority • grantprivilege_liston resource touser_listwithgrantoption; • Allows other users to grant privileges, including “with grant option” privileges • Like “Copy right” from Access Control lecture • Can grant subset privileges too • Alice: grantselecton table1 to bob withgrantoption; • Bob: grantselect(column1) on table1 to carol withgrantoption;

17. SQL revoke Syntax • revoke privilege_list on resource from user_list; • What happens when a user is granted access from two different sources, and one is revoked? • What happens when a “with grant option” privilege is revoked?

18. Revoke Example 1 • Alice gives Read, Update, Insert privileges to Bob for table X • Carol gives Read, Update privileges to Bob for table X • Alice revokes Read, Update, Insert privileges from Bob for table X • What privileges should Bob now have on table X?

19. Revoke Example 2 • Alice gives Read, Update, Insert privileges to Bob for table X with Grant option • Bob gives Read, Update privileges to Carol for table X • Alice revokes all privileges from Bob for table X • What privileges should Bob have on table X? • What privileges should Carol have on table X?

20. Revoke Example 3 • Alice gives Read, Update, Insert privileges to Bob for table X with Grant option • Bob gives Read, Update privileges to Carol for table X with Grant option • Carol gives Read, Update privileges to Bob for table X • Alice revokes all privileges from Bob for table X • What privileges do Bob and Carol have now?

21. Disadvantages to SQL Model • Too many views to create • Tedious for many users, each with their own view • View redefinitions that change the view schema require dropping the view, redefining, then reissuing privileges • Fine-grained policies each require their own view— and no obvious way to see that the views come from the same table • Applications often use one user and implement own access control • Other techniques being developed but not yet widely deployed

22. Statistical Access • Access given to aggregate functions only • count, sum, min, max, avg, … • select avg(gpa) from students where student.department = “ECE”; • select count(*) from students where gpa > 3.5; • Inference • Use aggregate values to deduce sensitive information

23. Exercise 1 • How can we use statistical functions to infer sensitive (user-specific values)?

24. Examples • Too-specific query • select avg(gpa) from students where student.name = “Carol”; • Negative result • select count(*) from students where college = ‘Edu’ and gpa > 3.0 => 0;

25. Constraint • Refuse to return results from queries that include too few (<k) rows • Exercise: find out Bob’s GPA if k = 3 • Select avg(financialaid) from students where college = “Eng” or college = “Edu” and (name != “Bob” OR gpa > 2.0);

26. Other possible controls • Return imprecise results • Perturb individual data • Perturb results • Return result on random sample • Hard to get right, see work on Differential Privacy for state of the art

27. Key Points • Database has good integrity features • Fine grained access control implemented in most DB systems • Not always taken advantage of • It is difficult to block inferences