Database Access Control: Current Methods and Research Directions

1. Database Access Control: Current Methods and Research Directions Lars Olson Illinois Security Lab, UIUC

2. Overview • Standard SQL access control syntax • View-Based Access Control • SQL for grant and revoke • Semantics of revocation • Reflective databases • Oracle Virtual Private Database policies • Hippocratic databases

3. 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 • Access control model developed by Selinger and Wade in 1976

4. 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 Selinger Wade 76

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

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

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

8. 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…)

9. Isn’t There an Easier Way? • Couldn’t we parameterize the view? createview employee_view asselect * from employee where name=SESSION_USER; grantselecton employee_view topublic; • Somewhat unsafe • We’ll cover this in the discussion of “Reflective Database Policies”

10. Delegating Policy Authority grant privilege_list on resource to user_list with grant option; • Allows other users to grant privileges, including “with grant option” privileges • Can grant subset privileges too • Alice: grant select on table1 to bob with grant option; • Bob: grant select(column1) on table1 to carol with grant option;

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

12. Selinger-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.

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

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

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

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

17. Alternate Revocation Semantics • 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. • Note: • Not all databases implement it this way! • Not all databases implement it the other way either!

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

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

20. 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)

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

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

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

24. 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 Database Query Table policy function VPD function evaluator User name Other data App-defined context Rewritten query Oracle 05

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

26. Simple Policy • Alice creates the same employee table as before • Alice wants to limit Bob’s access to his own record • Three steps: • Create a policy function • Attach the policy function to the table • Grant Bob access to the table via SQL (for backwards-compatibility): • grant select on employee to bob;

27. Simple Policy create or replace function testFilter (p_schema varchar, p_obj varchar) return varchar as begin if (SYS_CONTEXT('userenv', 'SESSION_USER') = 'BOB') then return'name = ''bob'''; else return''; end if; end; /

28. Simple Policy execute dbms_rls.add_policy( object_schema => 'alice', object_name => 'employee', 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.employee;

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

30. Logging Policy create or replace function testLogging(p_schema varchar, p_obj varchar) return varchar as begin insert into alice.logtable values( sysdate, SYS_CONTEXT('userenv', 'SESSION_USER') || ',' || SYS_CONTEXT('userenv', 'CURRENT_SQL')); commit; return''; end; /

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

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

33. Reflective Policy • Alice wants the permissions on each row in her employee table to be defined in another table, userperms. • Create the table: create table userperms (username varchar(50), name varchar(50) references employee); • Note the foreign key reference • Not required, but it can help optimization

34. Reflective Policy • Populate the table: insert into userperms values('BOB', 'bob'); insert into userperms values('ALICE', 'alice'); insert into userperms values('ALICE', 'bob'); insert into userperms values('ALICE', 'carol'); insert into userperms values('CAROL', 'carol'); commit; • Note the capital letters in BOB, ALICE, and CAROL • SQL commands are case-insensitive • Table values are case-sensitive

35. Reflective Policy create or replace function testFilter(p_schema varchar, p_obj varchar) return varchar as begin return'name in ' || '(select name from alice.userperms ' || 'where username = ''' || SYS_CONTEXT('userenv', 'SESSION_USER') || ''')'; end; /

36. Reflective Policy • Query the employee table, both as Alice and as Bob: select * from alice.employee; • Have Alice allow additional access to Bob, and then have Bob query employee again: insert into userperms values('BOB', 'carol'); commit;

37. Subtleties with VPD • Currently, Alice cannot insert new rows into employee (since userperms only gives Alice access to ‘alice’, ‘bob’, and ‘carol’) insertinto employee values ('david', 876543210, 60000, 'david@example.com'); • Alice cannot insert new rows into userperms (since the foreign key constraint requires the name value to exist in employee) insert into userperms values('ALICE', 'david');

38. Subtleties with VPD • Solutions: • Alice can exempt herself by writing the logic in the function • Alice can be exempted after adding the policy: • grant exempt access policy to alice; • Must be granted from system administrator • Exempts from all access policies, cannot specify table-by-table basis

39. Subtleties with VPD • Policy cycles • Need table self-reference for policy like “allow managers to view data for employees in their department” • If detected, execution will halt • If not detected, infinite loop will occur

40. Subtleties with VPD • Under whose permissions does the policy execute? • Can it be tricked? • Suppose Bob is put in charge of the company picnic: create table picnic(name varchar(20), assignment varchar(20)); insert into picnic values('alice', 'chips'); insert into picnic values('bob', 'drinks'); insert into picnic values('carol', 'salad'); commit; grant select on picnic to public; create table employee_leak(name varchar(50) primary key, ssn int, salary int, email varchar(50));

41. Subtleties with VPD createorreplacefunction leakInfo(p_schema varchar, p_obj varchar) returnvarcharas begin for allowedVal in (select * from alice.employee) loop begin insertinto employee_leak values ( allowedVal.name, allowedVal.ssn, allowedVal.salary, allowedVal.email); exception when DUP_VAL_ON_INDEX then update employee_leak set ssn=allowedVal.ssn, salary=allowedVal.salary, email=allowedVal.email where name=allowedVal.name; end; endloop; commit; return''; end; /

42. Subtleties with VPD execute dbms_rls.add_policy( object_schema => 'bob', object_name => 'picnic', policy_name => 'LeakPolicy', function_schema => 'bob', policy_function => 'leakInfo', statement_types => 'select,update,insert', update_check => true); • Query the table as Alice: select * from bob.picnic; • Examine the leaked info as Bob: select * from employee_leak;

43. Subtleties with VPD • Note that similar information flows can occur with parameterized views using SESSION_USER in other databases • Solutions: • Don’t trust anyone! • For Oracle, Alice should have used SYS_CONTEXT('userenv', 'POLICY_INVOKER') instead • Use a formal reflective policy model (my thesis project)

44. Database Logic (Datalog) • Similar to predicate logic employee_Carol(Name, SSN, Salary, Email) :- employee(Name, SSN, Salary, Email), Name='Carol'. means the same thing as employee(Name, SSN, Salary, Email)  Name=‘Carol’→ employee_Carol(Name, SSN, Salary, Email) • Commonly-used mathematical model for databases • Efficient query evaluation • Negations • Recursive queries

45. Reflective Policy Model • Transaction Datalog: syntax and semantics for insertions and deletions p(X, Y) :- q(X), ins.r(Y). • Database views are always defined with a particular permission picnic(User, A, B) :- employee(User, Name, SSN, ...), ins.employee_leak('bob', Name, SSN, ...), picnic('bob', A, B). picnic(User, A, B) :- employee('bob', Name, SSN, ...), ins.employee_leak('bob', Name, SSN, ...), picnic('bob', A, B). Bonner 98

46. Application Contexts • Motivation: secure way of storing security data from application • Implementation: attribute  value mapping within a namespace • Attributes can only be set within the context of the namespace • Requires “create any context” system privilege

47. Application Contexts • As Alice: create or replace package HR_package is procedure set_context_for_payroll; end; / create or replace package body HR_package is contextns constant varchar(18) := 'HR_namespace'; procedure set_context_for_payroll is begin dbms_session.set_context (contextns, 'employee_name', SYS_CONTEXT('userenv', 'SESSION_USER')); dbms_session.set_context (contextns, 'purpose', 'payroll_update'); end; end; / grant execute on HR_package to public; create context HR_namespace using HR_package;

48. Application Contexts • As any other user: execute alice.HR_package.set_context_for_payroll; select * from session_context;

49. Application Contexts • How to enforce context creation? • Create trigger that executes on login • Requires “administer database trigger” privilege • Use a policy function on each protected table • Check if context has been created • Deny access if not

50. Application Contexts • How secure is it? • Cannot set attributes for ‘HR_namespace’ except in procedures within package ‘HR_package’ • Adding “drop any context” privilege allows the user to override any context information • No way to specify which namespaces are allowed to be defined / rewritten by which users • Probably not designed for multiple policy writers