463.5.1 Database Access Control Tutorial

1. 463.5.1 Database Access Control Tutorial Lars Olson UIUC CS463 Computer Security

2. Overview • Standard SQL access control syntax • Semantics of revocation • Reflective databases • Oracle Virtual Private Database policies • Hippocratic databases

3. Required • P. P. Griffiths and B. W. Wade: “An Authorization Mechanism for a Relational Database System” • Oracle Corporation: “Oracle Virtual Private Database” (white paper) • R. Agrawal et al.: “Hippocratic Databases” • Any database textbook or reference with SQL • H. Garcia-Molina et al.: “Database Systems: The Complete Book” • R. Ramakrishnan and J. Gehrke: “Database Management Systems

4. Access Control • A scheme for mapping users to allowed actions • Identity-Based Access Control • Role-Based Access Control • Attribute-Based Access Control • Discretionary Access Control mechanisms • An individual user can set the policy • e.g. Unix file permissions • Mandatory Access Control mechanisms • The policy is built into the system, individuals cannot modify it • e.g. memory protection mechanisms

5. Access Control for Databases • Challenges: • Multiple operations: select (read), insert/update/delete (write), reference, create trigger, execute stored procedure, create tables, ... • Table-level access control is too coarse-grained, cell-level access control is too tedious (more on that later) • SQL has standardized access control policy definition language • Security model developed by Griffiths and Wade in 1976

6. Quick SQL Review • Creating tables: createtable table_name ( column1 type1, column2 type2, ... ); • Deleting tables: droptable table_name;

7. Quick SQL Review • Types: • int • float • date • char(size) • Always delimited by single quote (apostrophe) • Use two single quotes to represent the apostrophe character • varchar(size) (varchar2 in Oracle) • text (long in Oracle)

8. Quick SQL Review • Querying tables: select column1, column2 from table_name; or select * from table_name; • Conditions: select columns from table_name where condition;

9. Quick SQL Review • Inserting new rows: insertinto table_name values (value1, value2); or insertinto table_name set column1=value1, column2=value2, ...; • Updating rows: update table_name set column1=value1 where condition;

10. Quick SQL Review • Deleting rows: deletefrom table_name where condition; • Set values in conditions: select * from table_name where column in (select_statement); or select * from table_name where column in (value1, value2, ...);

11. Quick SQL Review • Creating functions: create [orreplace] function function_name (parameters) return return_type as [declare_local_variables] begin ... end; /

12. SQL grant Syntax grant privilege_list on resource to user_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

13. 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.

14. 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 slide) • not implemented for select in Oracle • implemented in MySQL

15. 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

16. 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;

17. Row-Level Access Control • Suppose we also allow employees to view their own ssn, salary: createview employee_Carol asselect * from employee where name='Carol'; grantselecton employee_Carol to carol; • And we allow them to update their e-mail addresses: grantupdate(email) on employee_Carol to carol; • (Or create yet another new view…)

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

19. 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?

20. Griffiths-Wade Model • Sequences of grant/ revoke operations • When a privilege is revoked, the ACLs should be indistinguishable from a sequence in which the grant never occurred.

21. Grants from Multiple Sources • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) Bob Alice Carol

22. Not as Easy as it Looks! • grant(Alice,Bob) • grant(Bob,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) • grant(Alice,Bob) • grant(Bob,Carol) • grant(Carol,Bob) • revoke(Alice,Bob) Bob Alice Carol

23. Cascading Revocations • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,David) • grant(Bob,Carol) • revoke(Alice,Carol) • grant(Alice,Bob) • grant(Alice,Carol) • grant(Carol,David) • grant(Bob,Carol) • revoke(Alice,Carol) Alice ? Carol David Bob

24. Meanwhile, in the Real World... • Account privileges get changed all the time • We don’t always want to redo everything • Tedious • Involves other users’ actions • SQL revoke command has two optional arguments: • cascade: undoes all dependent grant commands • restrict: exits with failure if there exist dependent grants Ramakrishnan Gehrke 03

25. Cascading Revocations • How would “revokeselecton table1 from carol cascade” work in the previous example? • Only privileges granted solely through the revoked privileges will also be revoked. • If there exists a path in the graph from the grantor, then do not revoke. • Allows us to specify exceptions preemptively.

26. 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

27. Disadvantages (cont) • Complicated policy logic can be difficult to express and to update • Update anomalies • Updates need to be made in multiple places • If any steps are forgotten, the database is in an inconsistent state • e.g. Suppose we have an employees table, and all managers in this table get special update privileges.

28. Reflective Database Policies • Computational Reflection • Objects contain metadata about their own computation • Modifying metadata changes the actual computation process • Common example: Java’s java.lang.reflect package • Apply to database access control • The policy itself contains a database query • SQL Views may be reflective (in a limited way)

29. Application b Access Control Rules Database User a b Motivation for Reflective DBs • Database applications often need to serve multiple users • Programmers often give their applications elevated privileges

30. Motivation (cont) • Violates principle of least-privilege • Programming errors • Malicious attacks (e.g. SQL injection attacks) • Separates access control from the database • New policy may require updates on database and on application • Database may have other entry points • So why do programmers still do this? • Too many users to give database accounts • Complicated access policy logic

31. Enhanced Security Layer Application b (Optional access control) Database Access Control Rules User a a Ideal Model • Database enforces its own security • Can be implemented as wrapper on database or as part of the database

32. Database Query Table policy function VPD function evaluator User name Other data App-defined context Rewritten query Virtual Private Databases • Security model for Oracle • Policies are user-defined functions that return a condition for an SQL where clause • Applications can also define a “context,” e.g. for role-based access control Oracle 05

33. Features • Functions are executed each time the table is accessed. • Multiple functions can be attached to a table. • Different functions can be defined depending on: • Operation (read vs. write) • Columns being accessed

34. Simple Policy • Two users, Alice and Bob • Alice creates a table: createtable test (a intprimarykey, b varchar2(50)); insertinto test values(1, 'hello'); insertinto test values(2, 'world'); commit; • Alice wants to limit Bob’s access to the row where a=1 • Three steps: • Grant Bob access to the table: grantselecton test to bob; • Create a policy function • Attach the policy function to the table

35. Simple Policy createorreplacefunction testFilter (p_schema varchar2, p_obj varchar2) returnvarchar2as begin if (SYS_CONTEXT('userenv', 'SESSION_USER') = 'BOB') then return'a = 1'; else return''; endif; end; /

36. Simple Policy execute dbms_rls.add_policy( object_schema => 'alice', object_name => 'test', policy_name => 'FilterForBob', function_schema => 'alice', policy_function => 'testFilter', statement_types => 'select, update, insert',update_check => true); • Query the table, once as Alice and again as Bob: select * from alice.test;

37. Logging Policy • Alice wants to log all accesses to her test table • Create a log table: createtable logtable ( when date, entry long);

38. Logging Policy createorreplacefunction testLogging(p_schema varchar2, p_obj varchar2) returnvarchar2as begin insertinto alice.logtable values( sysdate, SYS_CONTEXT('userenv', 'SESSION_USER') || ',' || SYS_CONTEXT('userenv', 'CURRENT_SQL')); commit; return''; end; /

39. Logging Policy execute dbms_rls.add_policy( object_schema => 'alice', object_name => 'test', policy_name => 'LogAccesses', function_schema => 'alice', policy_function => 'testLogging', statement_types => 'select, update, insert',update_check => true);

40. Logging Policy • Query the test table again, once as Alice and again as Bob: select * from alice.test; • Query the logtable table: select * from logtable; • Note that the original query is recorded, not the rewritten query. • Note also that Bob cannot query logtable.

41. Reflective Policy • Alice wants the permissions on each row in her test table to be defined in another table, userperms. • Create the table: createtable userperms (username varchar2(50), a intreferences test); • Note the foreign key reference • Not required, but it can help optimization

42. Reflective Policy • Populate the table: insertinto userperms values('BOB', 1); insertinto userperms values('ALICE', 1); insertinto userperms values('ALICE', 2); commit; • Note the capital letters in BOB and ALICE • SQL commands are case-insensitive • Table values are case-sensitive

43. Reflective Policy createorreplacefunction testFilter(p_schema varchar2, p_obj varchar2) returnvarchar2as begin return'a in (select a from alice.userperms ' || 'where username = ''' || SYS_CONTEXT('userenv', 'SESSION_USER') || ''')'; end; /

44. Reflective Policy • Query the test table, both as Alice and as Bob: select * from alice.test; • Have Alice allow additional access to Bob, and then have Bob query test again: insertinto userperms values('BOB', 2); commit;

45. Reflective Policy • Notes: • Currently, Alice cannot insert new rows into test (since userperms only gives Alice access to a=1 or a=2) • Alice cannot insert new rows into userperms (since the foreign key constraint requires the a value to exist in test) • Solutions: • Alice can exempt herself by writing the logic in the function • Alice can be exempted after adding the policy: grantexemptaccesspolicyto alice; • Must be granted from system administrator • Exempts from all access policies, cannot specify table-by-table basis

46. Hippocratic Databases • Hippocrates: Greek physician, ~400 B.C. • Hippocratic Oath: statement of ethical conduct in medicine, includes respecting privacy of patients • Hippocratic Databases: • database with built-in privacy controls • requires merging security policies from database owner and from data owner(s) Agrawal et al. 02

47. Hippocratic DB Techniques • Query rewriting • Boolean opt-in/opt-out columns • VPD-like policies for each data cell

48. Key Points • Access control for databases requires scalability • SQL standard: • grant, revoke • with grant option • view-based access control • Reflective databases: • Oracle VPD policy functions • Hippocratic databases (data ownership and privacy policies)

49. Software Versions • PostgreSQL • testbed version 8.2 • MySQL • testbed version 5.0 • Oracle • testbed version 10.2 • Only Enterprise Edition has VPD • Other standard SQL databases • Microsoft SQL Server, IBM DB2, Sybase • should all work, but we don’t have testbeds