Extending Query Rewriting Techniques for Fine-Grained Access Control

1. Extending Query Rewriting Techniques for Fine-Grained Access Control Shariq Rizvi, Alberto Mendelzon (Univ. Toronto), S. Sudarshan and Prasan Roy

2. Access Control • Fine-Grained Authorization rules: • You can see your grades • You can see grades of all students in courses you registered for • You can see average grades for all courses • You can see/update/insert/delete grades of courses you taught • Access pattern restrictions • “A teller can see the balance of a customer only by providing her customer-id” • SQL does not support such authorization • SQL authorization at the level of table/column, not particular rows

3. Access Control Tenets • Application-layer access control is bad • Repeated security logic • Complex, redundant code • Malicious/careless programmers • Solution: access control inside database • DBA has a life! • Easy specification of policies • Mechanisms within SQL-framework • Maintenance cost should be low • Especially as policies change infrequently

4. Access Control Tenets (contd.) • Authorization-transparent querying • Queries on base relations • No erroneous query rejections ] • more on this, later… • Avoiding misleading answers • Inherent in query modification approaches • More on this, later… • Minimal time overhead in query processing • Query modification may add redundant joins • More on this, later…

5. Current Approaches • SQL grant/revoke statements • Avoids query modification + • Simple to administer + • Authorization-transparent querying + • Security within the database + • Only Table/column-level authorization- • Even with extensions [DBSec00] • No support for access pattern restrictions -

6. View-Based Security • Avoids query modification + • Security within the database + • Arbitrarily fine-grained policies + • Per-user views – difficult to administer - • Solution: parametrized views create view ShawnGrades as select * from Grades where name = `Shawn’

7. Authorization Views • Shall restrict ourselves to queries for this talk • Model: parameterized view definitions to specify what a user is authorized see • Parameters: user-id, time, … • E.g. create viewmygradesasselect * from grades where student-id = $user-id • E.g. createviewcostudent-gradesasselect student-id, course, gradefrom grades, registeredwhere grades.course=registered.course and registered.student-id = $user-id • Authorization views instantiated at query time

8. View Based Security • Usual Approach: queries are written against the views • Problems: • Applications may need to generate different queries for different users • Programmers need to know database schema and views: more complexity • Authorization Transparent Querying • Idea: write queries against database relations • Idea: query valid if it can be answered using ONLY specified authorization views • Motro [ICDE89,JIIS96] • Rosenthal and Sciore [IFIP89,DMDW00] • More on this in rest of talk

9. Oracle’s Virtual Private Database • “Dynamically modified queries” • Functions encode security logic • Arguments: relation name, user-id, etc. • Return predicates which are added to query’s where clause • Predicates may be nested subqueries • Functions can be in PL/SQL, C, Java • Secure application contexts • Cache application-specific information for predicate-generation • E.g. userid

10. Oracle’s Virtual Private Database select * from Orders select * from Orders where position=`Rep’ and area = `West’

11. Oracle’s Virtual Private Database • Simple to administer + • Authorization-transparent querying + • Security within the database + • Arbitrarily fine-grained authorizations + • No support for access pattern restrictions - • Does query modification-

12. Why is query modification bad? • “Changes execution characteristics of the query” • May add redundant joins • Not a “best-effort” model • May provide misleading information • Inconsistencies between the answer and user’s external information

13. Doesn’t Virtual Private DB do it all? • Consider these authorizations • You can see grades of all students in courses you registered for • Requires complex condition (subquery or very long expression) to be added to where clause • expensive • Added even if your query did the test already • You can see average grades for every course • select avg(grade) from grades where course = ‘CS101’ With VPD, this would return your own grade (if you took CS101) or the empty set

14. The “Truman World” Model • With virtual private databases, each user gets a personal view of the world • All queries are answered w.r.t. this world view • Leads to inconsistencies with user’s external information • May give wrong answer even when user is actually authorized to see correct answer! • E.g. average grade • ~ World in “The Truman Show”

15. The ‘Truman Show’ Movie

16. Non-Truman World Model • Goal: Given set of authorizations and a query Q, is the query allowed under the given set of authorizations? • If query is authorized, allow it to execute • Else tell the user that the query has been rejected • Done by SQL for coarse-grain authorization • Result: No misleading answers Shall we call it “Trueman World” model?

17. Non-Truman Model • Intuition • DBA specifies “what” the user can see (“how” corresponds to access pattern restrictions) • User writes queries in an authorization-transparent manner • DBMS “tests” if the query is “valid” (can it be answered using only what the user can see?)

18. Non-Truman Model: Validity Inference • If the user is allowed to see V, then Q is certainly a valid query • (Parametrized views assumed to have been instantiated) V: select * from Grades where name = `Shawn’ Q: select * from Grades where name = `Shawn’ and grade = `C’

19. Unconditional Validity DEFINITION (Unconditionally Valid Query) For given parameter values, a query q is said to be unconditionally valid if there is a query q0 that is written using only the instantiated authorization views, and is equivalent to q, that is, q0 produces the same result as q on all database states. (For SQL: multiset equivalence)

20. Example V: select * from Grades where name = `Shawn’ Q: select * from Grades where name = `Shawn’ and grade = `C’ Q: select * from V where grade = `C’ So, Q is unconditionally valid

21. Why Multiset Equivalence? • Because SQL has multiset semantics • Cheap example Q1: select 1 from Grades where name = `Shawn’ Q2: select distinct(1) from Grades where name = `Shawn’ Q1 gives more “information” than Q2! So, in some scenarios, Q1 may be invalid but Q2 may be valid (assumption: both give a non-empty result)

22. Beyond Unconditional Validity create authorization view CoStudentGrades as select Grades.* from Grades, Registered where Registered.sid = $user-id and Registered.course-id = Grades.course-id • Is Q valid? • Looks like “yes” if the user is registered for CS186 • Looks like “no” if the user is not registered for CS186 • But Q can not be written using CoStudentGrades! • Applying selection ‘CS186’ on CoStudentGrades gives same result only if user is registered for CS186 • Will this reveal any extra information? • Not if user knows that he/she is registered for CS101 Q: select Grades.* from Grades where course-id = `CS186’

23. Beyond Unconditional Validity • Unconditional validity is too strong a requirement since it requires equivalence on all database states • But user knows some information about current database states, so why care about equivalence on other states? • However: equivalence on just the current database state is too weak • May reveal information to user

24. PA Equivalent Database States PA-Equivalent to D All database instances that produce the same result for the user’s instantiated authorization views as the instance D (Intuition: User cannot distinguish instance D from any of its PA-equivalent instances) D P U

25. Conditional Validity DEFINITION (Conditionally Valid Query) “For given parameter values, a query q is said to be conditionally valid in database state D if there is a query q’ that is written using only the instantiated authorization views, and is equivalent to q over all database instances that are PA-equivalent to database state D”

26. Testing Unconditional Validity • Example: User query select * from grades where student-id = ‘2937’ and course = ‘CS101’ • Can be rewritten using view mygrades, provided user-id = 2937, as: select * from mygrades where course = ‘CS101’ • Rewriting/equivalence-checking straightforward in this simple case • Exact test for “conjunctive” (select-project-join) queries – chase algorithm • Sufficient conditions for the general case

27. Unconditional Validity (Cont.) • Authorization view • Create view avggrades asselect course, avg(grade)from grades group by course • Query Q: • select avg(grade) from grades where course=CS101 • Q is valid: it can be rewritten using avggrades • Contrast with Truman World model where user query would have to be written using avggrades to get correct answer • Queries do not have to deal with authorization views

28. Query Rewriting Using Views • Extensive Work by Database Community • Query optimization, view maintenance, data integration systems, … • “complete rewriting” vs. “maximal rewriting” • Testing complete rewriting with conjunctive query and conjunctive views is NP-hard [VLDBJ01] • Queries with arithmetic comparisons [ICDE95] • Multiset semantics [PODS94] • Queries/views with aggregation/grouping [VLDB96] • We extend with inference rules for rewriting under integrity constraints

29. Inference Rules • Inference Rule U1: If v is an authorization view, then the query v is unconditionally valid. • Inference Rule U2: If q1, q2, …, qn are unconditionally valid queries, and E is an expression combining q1, q2, …, qn, with no other relation appearing in it, then E is an unconditionally valid query.

30. Inf. Rules & Integrity Constraints • create authorization view RegStudents as select Registered.course-id, Students.name, Students.typefrom Registered, Studentswhere Students.student-id =Registered.student-id • integrity constraint • each student has to register for at least one course. • q: select distinct name, type from Students • Is valid since it can be rewritten as selection + projection on auth view

31. Inf. Rules using Integ. Constr. • Inference rules U3a, U3b and U3c • notion of view core and view remainder • Integrity constraint: remainder will exist for view core • Infer: view core is valid

32. Testing for Conditional Validity • Rules C1, C2: Symmetric to U1, U2 • Rule C3a, C3b: Basic idea: generate “test queries” to lookup the database while testing for conditional validity

33. Uncond. Validity Checking • Rule C3a (approx): Query Q is valid if there is an authorization view definition of the form select v1.* from v1, v2 where c1, c2 Such that Q can be rewritten using select v1.* from v1where c1 AND C1 (resp. C2) contains no attributes from V2 (resp. V1) AND exists (select * from v2 where c2) evaluates to true on the database AND (select 1 from v2 where c2) is a valid query • Extension • The remainder query (select 1 from v2 where c2) can contain attributes from V1, provided query Q has selections that make these constants

34. Uncond. Validity: An example • E.g. given query Q • select * from gradeswhere course = ‘CS101’ Split • create view costudent-grades as select student-id, course, gradefrom grades, registeredwhere grades.course=registered.course and registered.student-id = $user-id Into query on grades below + a remainder query • Select student-id, course, grade from grades Q can be rewritten using above part Remainder must satisfy some conditions:

35. Uncond. Validity Example Cont. • Conditions to be satisfied by remainder query: exists (select * from registered where registered.course=‘CS101’ and registered.student-id=$user-id) • Note: course = CS101 becomes a constant • Verify that the remainder query evaluates to true on the database • And verify that the remainder query is valid

36. Etc. • Read the paper [SIGMOD04] for • The inference rules • Ideas for implementation over the Volcano query optimizer [ICDE93] • Piggy-back on the rewriting capability of the query optimizer • Roles and delegations under this model • Update queries under this model • Access pattern restrictions • Handling false negatives under this model

37. Other Models • Oracle’s Virtual Private Database and Label Security [Ora99] • Flexible authorization frameworks • Jajodia et al [DBSec01, TODS01] • Multilevel Relations • Jajodia and Sandhu [SIGMOD91], Denning [SSP85]

38. Summary • Parametrized view definitions are a neat way of specifying fine-grained authorization • New and very powerful technique for deciding on validity of queries • Avoids problems of Truman World • Can be implemented in query optimizer, or in a middleware system • Currently being implemented

39. Detecting Unauthorized Updates using DBTripWire (with Kanti Kiran)

40. Motivation • Today’s DB security scenario • Lots of critical data • Sales, financial data, course grades, … • Many omnipotent gods! • DB Administrators • DB Application programmers • Anyone who breaks in as one of above • Makes people in charge extremely uneasy!

41. Approaches to Increasing Security • 2-Factor security • More than one person needs to cooperate to do something • Standard in manual Indian govt/bank systems • Not fully supported in databases/applications • Ensuring 2-factor security • Encryption based • Multiple copy based

42. Approaches to Increase Security • Use encryption, store encrypted data in database • Expensive • Simple approach: store encryption key in application code • Guards against DB admin, but • Doesn’t guard against • application programmers • break-ins to application • Deletion of data • Need encryption at end user • E.g. encryption in smart card

43. Proposed Approach • Current industry solution: keep audit trail • Who is to monitor audit trail? • Mine for unusual actions? • Dealing (partially) with application insecurity • User login should be done through separate system • Application should not get user password • Application can only perform actions for a user while the user is logged in • Audit trails of actions performed by application • Fine grain authorizations on audit trail to enable users to check their own information • When all barriers fail: DBTripWire • detect unauthorized updates post fact

44. DBTripwire • Inspired by Unix Tripwire system • Idea: • keep copy of data (or checksum thereof) and • compare with master copy periodically • Note: we tackle unauthorized updates only, not reads • Issues: • Have to deal with inserts/updates • Have to undo any detected damage • Must keep a copy of data at a separate place, under separate DB Admin

45. DBTripWire • Idea: when user makes update, application program sends copy to separate DBTripWire system • DBTripWire needs to independently authenticate that user performed the update • Can ensure user is logged in • user authentication via centralized authentication systems like pubcookie • User password not available to application server! • Ensuring the update was done by the user is hard • DBTripWire currently accepts updates via application server, without independent authentication • Update can be verified later and undone if required

46. Recording Updates • Problem: how to tell DBTripWire about updates • Our current solution: application code flags groups of tuples as “frozen” • I.e. will not change henceforth • E.g. when grades for a course are finalized • Record this information in the master database • Compute a checksum • and give it to user for independent verification • Future work: modify application-DB interface to • submit updates to master and DBTripWire site • dealing with transient site failures, etc.

47. Detecting Unauthorized Updates • Checking frozen groups • E.g. All grades for CS101 in Spring 2003 • Groups are defined by (parametrized) queries whose results are expected to not change • Select * from grades where course = ‘CS101’ and semester = ‘spring’ and year = ‘2003’ • DBTripWire stores (parametrized) queries and their original results • Executes a single query to get/check results for a particular query type • across all parameter values

48. Detecting Unauth. Updates (Cont.) • DBTripWire frequently does the following • Get information about new frozen groups from master database • I.e. queries defining the groups • Execute the queries and store query result locally • Window of vulnerability .. • And periodically does the following • Compare results for each frozen group with local copy of data • Checksums computed using user-defined functions can be used to minimize this overhead • Future work: Extend to handle general updates

49. Handling Unauthorized Updates • On detecting differences, DBTripWire • informs humans and • helps to restore data using its copy • Human intervention needed to ensure that unauthorized updates done to DBTripWire copy don’t get automatically propagated to master copy! • Current Status: under implementation

50. Thank You!