Fine-Grained Authorization in Databases

1. Fine-Grained Authorization in Databases S. SudarshanIIT Bombay/Microsoft Research Parts of this talk joint work with Rizvi, Mendelzon and Roy

2. Access Control & Databases USERS DBA CASCON 2004

3. Roadmap • Motivation • Fine-grained access control models • View Replacement Model • Oracle VPD • Non-Truman Model • Cell-level Authorization and Nullification • Multi-Level Security • Unconditional and Conditional Validity • Inferring validity • Etc. • Conclusions CASCON 2004

4. Fine Grained Access Control • Fine-grained access control examples: • Students can see their own grades • Students can see grades of all students in courses they registered for • Variant: but not the associated student-ids • Public can see average grades for all courses • Faculty can see/update/insert/delete grades of courses they taught • SQL does not support such authorization • SQL authorization at the level of table/column • not row level CASCON 2004

5. Fine-Grained Authorization • Usual solution: fine-grained authorization handled by application programs • Application-layer access control limitations • Complex, redundant code • Malicious/careless programmers • SQL injection problems • Application code runs in “super-user” mode always • Repeated security logic • Solution: access control inside database CASCON 2004

6. create viewShawnGradesas select * fromGradeswherestudent_id = 'Shawn' v q select gradefrom ShawnGrades where course = 'CS262B' Access Control Using Views • Common solution: Views • Per-user views – difficult to administer • Solution: parametrized views • create viewMyGradesasselect * fromGradeswherestudent_id = $userid • Authorization-conscious querying • Instead of grades, must use MyGrades for students, another view for faculty, etc, CASCON 2004

7. Access Control Requirements • Authorization-transparent querying • Queries written on base relations • System can • Replace relations by authorized views, or • Accept and run as is if safe, else reject query • Avoid erroneous/misleading answers • No erroneous query rejections • Minimal time overhead in query processing CASCON 2004

8. Authorization-Transparent Querying • View-level data independence • Analogous to physical/logical data independence • Changes to underlying authorization should not directly affect queries • Querying base relations rather than views • Easy to build applications • Views can be user-specific, for multi-user apps • Generated queries better not be user-specific CASCON 2004

9. The View Replacement Approach • AKA: Filter model • Transparent query modification • Used in Oracle’s Virtual Private Database “Grades of all students” q select* fromGrades “Grades of current user (Shawn)” select*fromGrades where studeint_id = ‘Shawn' qm CASCON 2004

10. Oracle VPD • Predicates transparently added to query/update where clause • for each relation used in query/update • User-defined functions (specified by application) generate the predicates • Functions encode security logic, can be in C/Java • Secure application context stores session parameters, which can be accessed by function • Application context • Database user information is insufficient, need to know application user • Oracle provides mechanism for application to inform DB about end user • Handles DB connection pooling CASCON 2004

11. Oracle VPD (Cont.) • Example applications • Application service providers (hosted applications) • E.g predicate: companyid = AppContext.comp_id() • Web applications • E.g. predicate userid = AppContext.userid() • Extensions in 10g • E.g. Relevant column enforcement and masking • Flaw in the model that allows information leakage found recently • and apparently fixed Flaw: User defined functions with side effects can leak information if executed before VPD inserted predicate CASCON 2004

12. The Truman Show (1998) “He's a prisoner. Look at him, look at what you've done to him!” CASCON 2004

13. Drawbacks of View Replacement • May provide misleading information • Query executes in an artificial world • Inconsistencies between the answer and user’s external information • Even if query is actually authorized! “Average grade across all courses and across all students” q selectavg(grade) fromGrades “Average grade across all courses for the current user” selectavg(grade) fromGrades where student_id = ‘Shawn’ qm CASCON 2004

14. create authorization view MyGrades as select*fromGrades wheresid = $user-id v select*fromGrades wheresid = '16856612' q The Authorization Inferencing (Non-Truman) Model • User queries written on base relations • Authorization-transparent querying • But can access views if so desired • Idea: If query can be answered using information in authorized views • Then accept query and execute as is • Else reject query CASCON 2004

15. Authorization Inferencing: History • Query rewriting using views • (Motro [ICDE89, JIIS96]) • Partial results for unauthorized queries • Description of incompleteness • Query q authorized if any equivalent query q' (possibly using views) is authorized • (Rosenthal & Sciore [DBSec99, DBSec01, DMDW00]) • Applications in data warehousing • Extended to “conditional equivalence” • (Rizvi, Mendelzon, Sudarshan and Roy, [SIGMOD’04]) • More on this later CASCON 2004

16. Authorization Inference • Benefits: • Correctness of answers guaranteed • Query executed as written without modification • Drawback: • Inferencing undecidable in general • False rejection possible • More on inferencing later… • How to handle “show me everything I’m authorized to see” (within query result)? • Integration with view replacement model? CASCON 2004

17. Cell Level Authorization • E.g: P3P opt-in/opt-out model • Patient names are released to pharmacy • patient can opt-in to have email disclosed to pharmacies • Pharmacy issues “select name, email from patient” • Show information in cell if authorized, else return null • Limited disclosure models and implementation approaches • LeFevre et al [VLDB04] • Storage alternatives • Query alternatives: outerjoin vs. subquery in select clause CASCON 2004

18. Multi-Level Security • Security level with each tuple and security clearance level for each user • E.g. top-secret, secret, confidential, pubilc • Read allowed only on data with lower security level than users clearance • “Write down” may be prohibited • Not a fully general solution • E.g. cannot handle “students can see only their own grades” • Oracle Label Security • Allows sensitivity labels plus “compartment” plus “group” • Now allows SQL predicates to be added to label security • IBM DB2 for zOS • Work at IBM Toronto (Rjaibi and Bird, VLDB04) • Adds labels to MLS CASCON 2004

19. Extending Query Rewriting Techniques for Fine-Grained Access Control Shariq Rizvi UC Berkeley Alberto Mendelzon University of Toronto S. Sudarshan IIT Bombay Prasan Roy IBM IRL

20. Our Contributions • Non-Truman model • Formalization of access control by query rewriting using views • Conditional validity • A new dimension to query rewriting using views • Required to capture a special class of valid queries • Inference rules to deduce query validity • An implementation framework for this model CASCON 2004

21. The Non-Truman Model • Test: Is the query q “valid” under the given authorization? • If yes, execute it unmodified • Else, reject it • Hence • Authorization-transparent querying • No query modification CASCON 2004

22. Unconditional Validity DEFINITION (Unconditionally Valid Query) “A query q is said to be unconditionally valid if: there is a query q' that is written using only the instantiated authorization views, and is equivalent to q. That is, q and q' produce the same result on all database instances” (Equivalence refers to multiset equivalence) CASCON 2004

23. Basic Inference Rules for Unconditional Validity • Inference rule U1 • If the query is an authorization view, it is valid Auth View • Inference rule U2 • If the query combines only unconditionally valid subqueries, it is unconditionally valid CASCON 2004

24. Implementing inference rule U2: Query Rewriting Using Views • Extensive work by database community • Query optimization, view maintenance, data integration systems, … • Testing complete rewriting with conjunctive query and conjunctive views is NP-hard (Halevy [VLDBJ01]) • Queries with arithmetic comparisons (Chaudhuri et al. [ICDE95]) • Multiset semantics (Chaudhuri et al. [PODS94]) • Queries/views with aggregation/grouping (Gupta et al. [VLDB95], Levy et al [PODS95], Chaudhuri and Shim [EDBT96], Srivastava et al. [VLDB96], Cohen et al. [PODS99], Bello et al [VLDB98], Zaharioudakis et al. [SIGMOD00], Larson et al [SIGMOD01], etc) CASCON 2004

25. Unconditional Validity Is Too Strong! What should happen? create authorization viewMyDocuments as select Document.* fromUser, Document whereUser.uid = $user-id andDocument.level <= User.level “A user can see all documents rated lower than or equal to her level” v select * fromDocument where doc-id = '5' q “Return document 5” • Query is valid if • Document 5 is present, and is rated ≤ the user’s level • Query is invalid if • Document 5 is present, and is rated > the user’s level • Document 5 is absent CASCON 2004

26. Unconditional Validity Is Too Strong! What should happen? • Query is valid if • Document 5 is present, and is rated ≤ the user’s level • Query is invalid if • Document 5 is present, and is rated > the user’s level • Document 5 is absent What will happen with just unconditional validity? • Query is declared invalid • Unconditional validity decides without looking at the database Need a notion of validity that looks into the database and is sensitive to the above cases! False Negative! CASCON 2004

27. Beyond Unconditional Validity • Unconditional validity gives false negatives • q and q' - equivalence over all database instances • But user has some information on the current database instance (from the authorization views!) • So, equivalence over a more restricted set of instances should do fine! • Naïve take: Test equivalence on the current database state • Too weak: leaks information CASCON 2004

28. A Special Set of Database Instances • Need equivalence on a set of database instances that captures exactly what the user knows • PA-Equivalent database states: database instances (states) that give the same result for all authorization views • P = database states PA-equivalent to the current database state Intuition: The user can not distinguish any two instances in P from each other but can distinguish an instance in P from an instance outside P Current databas state U P CASCON 2004

29. Conditional Validity DEFINITION (Conditionally Valid Query) “A query q is said to be conditionally valid 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 the current database instance” CASCON 2004

30. Inferring Conditional Validity • Basic idea: generate “test queries” to lookup the database • Back to example • Result should be non-empty • This query itself should be conditionally valid • Set of inference rules incomplete* • But handles a large class of queries select distinct 1fromDocument where doc-id = '5' and level <= 7 *Extensions and ongoing work (with Navneet Loiwal, IIT Bombay) CASCON 2004

31. Conclusions (for Authorization Checking) • Inference rules implemented (partially) into a Volcano-style query optimizer • Piggy-back on the rewriting capability of the query optimizer • Sufficient conditions for conditional validity are expensive • Requires “test queries” to be executed • Ideas on caching/reusing inferences need to be tested • Exact (but extremely expensive) test for conditional equivalence for conjunctive queries (Zhang and Mendelzon, ICDT 05) • Unconditional and conditional validity testing are undecidable in general • Can users live with notion that a query may be unfairly rejected even if actually authorized? • How rare would this be in reality? CASCON 2004

32. Overall Conclusions • Lots of interest in fine-grained access control • View replacement vs. authorization checking model • Each has its benefits and drawbacks\ • Multi-level security of limited value without integration with other forms of fine-grain authorization • Need to worry about application developer • User interface needs to check for authorization, even if enforced at database • Are we easing developers task or doubling their work? CASCON 2004

33. Extra Slides

34. Take-away Too strong You don’t want to do this! dcurrent Just right U P CASCON 2004

35. Non-Truman Model: Authorization Specification • Normal relational views • Parameterized views • Instantiated on access • Administered to users using SQL grant/revoke create authorization view AllGradesas select*fromGrades v1 create authorization view MyGrades as select*fromGrades where sid = $user-id v2 CASCON 2004

36. Non-Truman Model: User Queries • User queries written on base relations • Authorization-transparent querying create authorization view MyGrades as select*fromGrades wheresid = $user-id v select*fromGrades wheresid = '16856612' q CASCON 2004

37. Non-Truman Model: The Test • DBMS tests if the query is valid • Intuition: A query is valid if it can be answered using the information contained in the authorization views available to the user • We develop the formalization next • Example (assuming the user is 16856612): create authorization view MyGrades as select*fromGrades wheresid = $user-id v select*fromGrades wheresid = '16856612' q CASCON 2004

38. Example “A user can see the course-id of all courses she has registered for” create authorization view MyCourses as selectcourse-idfromRegistered wheresid = $user-id v1 create authorization view MyGradesas selectMyCourses.course-id, Grades.grade fromMyCourses, Grades whereMyCourses.course-id = Grades.course-id v2 “A user can see all her grades” q selectRegistered.course-id, Grades.grade from Registered, Grades whereCourses.course-id =Grades.course-id and Registered.sid = '16856612' “Return all my grades” q' selectcourse-id, gradefromMyGrades CASCON 2004

39. selectAc , ArfromRc , RrwherePc ^ Pr ^ Pj q1 qualifying tuples in Rc there is a qualifying matching tuple (w.r.t Pj) in Rr (e.g., foreign key constraints) A q2 select distinctAcfromRcwherePc Inference Rules using Integrity Constraints If: And: Integrity constraint Infer: CASCON 2004

40. Example Given the integrity constraint, the user can see all distinct names from the authorization view anyway! create authorization viewRegStudentsas selects.name, r.course-id fromStudents s, Registered r wheres.student-id=r.student-id q1 = v “Names of students and course-id’s they have registered for” Integrity constraint Each student must register for at least one course q2 = q select distinctnamefromStudents CASCON 2004

41. Naïve Take Leaks Information Naïve Test Is there a query q' written on view MyDocuments equivalent to q over the current database instance? • Case 1: document 5 present AND its level ≤user’s level • Equivalent query is select * fromMyDocumentswheredoc-id = '5' • Case 2: document 5 present AND its level >user’s level • No equivalent query • Case 3: document 5 not present • Equivalent query is empty query Information Leak: Cases 2 and 3 were indistinguishable to the user with just the MyDocuments view, but now they are distinguishable! q select * fromDocumentwhere doc-id = '5' ACCEPT REJECT ACCEPT CASCON 2004