Charity begins at home.

1. Charity begins at home. So does security.

2. Redundancy & Information Leakagein Fine-grained Access Control Based on paper & slides by Govind Kabra (Univ of Illinois, Urbana-Champaign) Ravi Ramamurthy (Microsoft Research) S. Sudarshan (IIT Bombay) Seminar presented for CS 632 by: Aditya Joshi Subhajit Datta 08305908 09305051 Course Instructor: Prof. S Sudarshan

3. Contents Introduction Redundancy Removal Information leakage Integrating RR & Safe plans Conclusion & Future Work

4. Contents Introduction Introduction Redundancy Removal Information leakage Integrating RR & Safe plans Conclusion & Future Work

5. Redundancy & Information Leakage in Fine grained access control Redundancy & Information Leakage in Fine grained access control • Conventional Access control: Table/column level • Fine grained access control: Access control at a lower level of granularity • grant select on employee(name) to public • Views

6. Example Professor teaching 101 Student Roll number 1 select * from grades;

7. σ σ σ σ L authL L O So what is query rewriting? select * from grades; ‘grades that I am allowed to see’ • Replace relation R in the query by RA • RA is an authorization view • Query Rewriting User Query: select * from lineitem where shipmode=‘express’

8. Existing systems • Oracle’s Virtual Private Database (VPD) • LeFevre et al Functions associated with each relation which return strings of predicates. Replacing unauthorized values with null

9. Types of models • Truman Models • Uses query rewriting • Non-Truman Models • Valid if it can be rewritten with authorized views. Invalid queries rejected

10. Two Semantics • The Truman Model = filter semantics • The non-Truman model = deny semantics T M Q Q’ nT M Q accepted Based on: www.cs.washington.edu/homes/suciu/current-trends.ppt

11. Query Rewriting • Authorized Views CREATE VIEW auth_Ri AS SELECT Li FROM Ri WHERE Pi Li contains expressions implementing cell level access-control Pi has the authorization predicates (may have sub-queries) • Query using such views Ri Ai where Ai is an expression containing the sub-queries in Pi R1 R2 …. Rn (R1 A1) (R2 A2) … θi θ1 θ2

12. Query Rewriting • Authorized Views CREATE VIEW authGrades AS SELECT * FROM GRADES g1 WHERE EXISTS (SELECT * FROM FACULTYCOURSES f1 WHERE FACID=getFacID() and g1.courseid=f1.courseid) • Query using such views Ri Ai where Ai is an expression containing the sub-queries in Pi R1 R2 …. Rn (R1 A1) (R2 A2) … θi θ1 θ2

13. Redundancy & Information Leakage in Fine grained access control Redundancy & Information Leakage in Fine grained access control • Redundancy • Information Leakage

14. Contents Introduction Redundancy Removal Redundancy Removal Information leakage Integrating RR & Safe plans Conclusion & Future Work

15. Redundancy Removal • Intuition: Most queries already access authorized data • Will adding authorization views cause redundancy?

16. σ σ σ σ σ σ authG F G F F σ G F Redundancy example Select * from grades G, facultycourses F where G.courseid=F.courseid and F.facid=‘123’ and G.year=2010 authG: Grades that a faculty is allowed to see

17. σ σ σ F σ G σ G F F Redundancy detection and removal-I • In general, RR is equivalent to query minimization • Heuristic approach: eliminate redundant semi-joins • If E2 subsumes E1, then transform E1 E2 to E1 • Added transformation rules in a rule based optimizer E2 E1 Apply RR

18. Redundancy detection and removal-II • Subsumption Test • E2 subsumes E1 in E1 E2 if • The predicates in selection of E2 are weaker than corresponding predicates in E1 • The semi-join condition in equates the columns of E1 and E2 that are equivalent under the mapping. θi θi

19. Redundancy detection and removal-II • Rule to detect and remove redundancy: • If E2 subsumes E1 then replace E1 E2 by E1 • In case of disjunction of sub-query expression: • Apply subsumption test to each disjunct • If any one is found to subsume E1, then discard the complete set of semi-joins. θi

20. RR at different levels • Transformation phase: • Explores all possibilities of redundancy • Inefficient • Simplification Phase : Normalized form by pulling up semi-joins. • Linear number of authorization checks • Depends on order of Ai’s • Easy to integrate with existing optimizers.

21. During simplification phase E1 E2 E1 E2

22. TPCH Query Execution Time Without RR Execution Time With RR Query 3 100.00 48.28 Query 6 56.03 38.79 Query 10 94.83 55.45 Query 12 77.57 43.97 Query 14 49.14 38.79 Performance benefits of RR • TPC-H Benchmark Queries, with authorization checks Comparing normalized execution times

23. Performance benefits of RR • Simplification versus transformation

24. RR for non-Truman model • Perform redundancy removal • If query remains the same, it is indeed a valid query nT M Q accepted

25. Redundancy & Information Leakage in Fine grained access control Redundancy & Information Leakage in Fine grained access control • Redundancy • Information Leakage

26. Contents Introduction Redundancy Removal Information leakage Information leakage Integrating RR & Safe plans Conclusion & Future Work

27. σmyudf(E.salary) σmyudf(E.salary) σmyudf(E.salary) A1 myemployees employees A1 employees Information leakage via UDFs • UDF may expose the values of the table • May print out values • Save the values to a table

28. Other channels of information leakage • Exceptions • Query: select * from employee where 1/(salary-100K) = 0.23 • Divide by zero exception if salary = 100K • Error Messages • to_Integer function may throw error revealing the content • Timing Analysis • Sub-query can perform an expensive computation only if certain tuples are present in its input.

29. σmyudf(E.salary) σmyudf(E.salary) A1 employees employees A1 Preventing Information Leakage via UDFs • UDFonTop: Keep UDFs at the top of query plan • Definitely safe, no information leakage • Better plans possible if UDF is selective • Optimal Safe plan • When is a plan safe? • How to search for optimal plan amongst alternative safe plans?

30. σudf2(E.name) A2 A2 σM.disease=‘AIDS’ σudf2(E.name) σM.disease=‘AIDS’ employees medical-record employees medical-record A2 Safe plans w.r.t. UDFs • Approach 1: If UDF uses attributes from R, apply authorization checks for R before UDF • Not sufficient; Full expression must be authorized • Expression that can be rewritten using authorized views [RMSR04] • How to efficiently infer which expressions are authorized? • Auth Views: employee (medical-record A2) • Query: Find names of all employee having AIDS σudf2(E.name) σM.disease=‘AIDS’ employees medical-record

31. Some definitions • Authorized Expression An expression is authorized if it is equivalent to an expression defined using only authorized views. • Safety of query plan w.r.t. USF’s A node in a query plan is safe w.r.t. USF’s if: • There are no USF’s in the node, and all inputs (if any) of the node are all safe, or • The node has a USF, it is not an apply operator, and all its inputs are safe and authorized. • The node is an apply operator, both its children are safe and either • Right child does not have any USF invocations, or • The left child is authorized Unsafe functions. What are they?

32. σmyudf(E.salary) employees A1 Examples • There are no USF’s in the node, and all inputs (if any) of the node are all safe, or • The node has a USF, it is not an apply operator, and all its inputs are safe and authorized. • The node is an apply operator, both its children are safe and either • Right child does not have any USF invocations, or • The left child is authorized • There are no USF’s in the node, and all inputs (if any) of the node are all safe, or • The node has a USF, it is not an apply operator, and all its inputs are safe and authorized. • The node is an apply operator, both its children are safe and either • Right child does not have any USF invocations, or • The left child is authorized • There are no USF’s in the node, and all inputs (if any) of the node are all safe, or • The node has a USF, it is not an apply operator, and all its inputs are safe and authorized. • The node is an apply operator, both its children are safe and either • Right child does not have any USF invocations, or • The left child is authorized • There are no USF’s in the node, and all inputs (if any) of the node are all safe, or • The node has a USF, it is not an apply operator, and all its inputs are safe and authorized. • The node is an apply operator, both its children are safe and either • Right child does not have any USF invocations, or • The left child is authorized Apply Safe Safe If the right child does not have any USF invocation, the left child may not be authorized. If the left child is authorized, right child may have USF invocations.

33. employees σ Q1 medical-records G6 G6 G5 G5 G7 G4 G3 G4 G3 G1 G1 employees Q1 employees Q1 σ σ G2 medical-records G2 medical-records Framework of rule based optimizer A DAG-like structure. Equivalence nodes : Group node

34. Inferring authorization of expressions • Authorization as a logical property of group • Start with the rewritten query: • Mark groups containing original authorization views as authorized

35. Rule IA • If all the children group nodes of an operation node are authorized, the parent-group-node of that operation node are also marked as authorized. • Propagate authorization upwards to the parent groups • A node which is not authorized initially may be inferred as authorized later. • This information must be propagated to the parents of the node

36. employees σ Q1 medical-records G6 G6 G6 G5 G5 G5 G7 G7 G4 G3 G4 G4 G3 G3 G1 G1 G1 employees Q1 employees employees Q1 Q1 σ σ σ G2 medical-records G2 G2 medical-records medical-records Inferring authorization of expressions • Authorization as a logical property of group • Start with the rewritten query: • Mark groups containing original authorization views as authorized • Propagate authorization upwards to the parent groups G6 G5 G1

37. Extending optimizer to find optimal safe plan • There are two approaches to find the optimal safe plan: • Only Safe Transformations • Allow UDF push-down/pull-up only on top of authorized expressions • Only safe alternatives are present in memo, pick the optimal plan • Pick Safe Plan • Allow all transformations for UDF • Use “required/derived feature” to pick only plans where UDF are on top of authorized expression

38. No RR With RR UDF on top 100 47.83 Safe Optimal 53.25 23.25 Both RR and Optimal Safe Plan are necessary: Motivation Comparing normalized execution times.

39. Contents Introduction Redundancy Removal Information leakage Integrating RR & Safe plans Integrating RR & Safe plans Conclusion & Future Work

40. Integrating RR and Optimal safe plan • Rule-based optimizers involve a simplification phase followed by a transformation phase • RR in simplification reduces query size and optimization time • But RR in simplification interferes with safety inference • Optimal safe plan generation requires preserving the following input plan until memo is created • RR can possibly remove some Ai • Possible integration: • RR in transformation phase • RR in simplification phase with conditioned authorization for safe plan generation

41. A2 RR during Transformation Phase • Introduce authorization-anchor nodes • These prevent transformations that pull-up Ri or Ai’s or push down any operation into the semi-join • At start of transformation, we remove these nodes, mark them as authorized, perform authorization propagation. σudf2(E.name) σM.disease=‘AIDS’ employees medical-record

42. RR during Transformation Phase • Introduce authorization-anchor nodes • These prevent transformations that pull-up Ri or Ai’s or push down any operation into the semi-join • At start of transformation, we remove these nodes, mark them as authorized, perform authorization propagation. • Then RR rules are applied • Disadvantage: • Increased optimization time due to multiple redundancy checks of semi-joins.

43. RR in simplification phase with conditioned authorization • Instead of marking an expression authorized, we mark it as conditioned-authorized. • For eg.: we have a relation Ri with authorization Ai • Ai could be removed/ moved elsewhere by Ri • So we mark Ri as authorized conditioned on Ai • i.e. Conditioned on it being semi-join/joined with Ai

44. RR in simplification phase with conditioned authorization • If simplification results in a empty condition, we can infer that the expression is unconditionally authorized. • For a group: • If any of the child is unconditionally authorized, so is the group. unconditional G1 unconditional E1 E2 E3

45. RR in simplification phase with conditioned authorization • If expression E is of the form E1 E2, where • E1 is authorized conditioned on Ai and • E2 is equivalent to Bj Ai, then • We infer that resultant expression is unconditionally authorized. unconditional Ai E1 E2 Bj Ai

46. Rule for propagation authorization • The extended propagation rule is: • If operation has two groups E1 and E2 each authorized on A1 and A2 resp., then result is authorized conditioned on A1 and A2 • If A1 subsumes E2, we drop A1 from the condition.

47. Handling Exceptions and Error Messages • For each built-in function, we create a safe version of the function that ignores exceptions and does not output error. • Predicates using USF’s are rewritten using the corresponding safe version. We can create a safe version of division function, which catches exception and returns a null value. for the predicate (1/(salary-100K)==0.2) we can use this safety predicate. • This may allow unauthorized tuples to pass through. However, we can rewrite such that it is weaker than the original condition. • We can push down the safe predicates while retaining the unsafe version on top.

48. Performance Evaluation • Study utility of RR and Optimal Safe Plan • Auth: Managers can see information only pertinent to their region • authNation:Nation ( (Region)) • authCustomer:Customer (Nation ( (Region))) • … • Query:Find supplier who fulfils “important” orders σ σ Authorization View replacement

49. Both RR and Optimal Safe Plan are necessary 100.00 47.83 Apply RR 53.25 23.25 UDF On Top Apply Both Safe Optimal No RR

50. Contents Introduction Redundancy Removal Information leakage Integrating RR & Safe plans Conclusion & Future Work Conclusion & Future Work