FineGrained Authorization in Databases S Sudarshan IIT BombayMicrosoft

Fine-Grained Authorization in Databases S. Sudarshan IIT Bombay/Microsoft Research Parts of this talk joint work with Rizvi, Mendelzon and Roy Oct 5, 2004 CASCON 2004

Access Control & Databases USERS DBA Oct 5, 2004 CASCON 2004 2

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

Fine Grained Access Control l Fine-grained access control examples: l l l Students can see their own grades Students can see grades of all students in courses they registered for l 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 l Oct 5, 2004 SQL authorization at the level of table/column l not row level CASCON 2004 4

Fine-Grained Authorization l l Usual solution: fine-grained authorization handled by application programs Application-layer access control limitations l l l 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 Oct 5, 2004 CASCON 2004 5

Access Control Using Views l l l Common solution: Views create view Shawn. Grades as select * from Grades where student_id = 'Shawn' q select grade from Shawn. Grades where course = 'CS 262 B' Per-user views – difficult to administer Solution: parametrized views l l v create view My. Grades as select * from Grades where student_id = $userid Authorization-conscious querying l Oct 5, 2004 Instead of grades, must use My. Grades for students, another view for faculty, etc, CASCON 2004 6

Access Control Requirements l Authorization-transparent querying l l Queries written on base relations System can l l l 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 Oct 5, 2004 CASCON 2004 7

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

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

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

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

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

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

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

Authorization Inferencing: History l Query rewriting using views l l Partial results for unauthorized queries Description of incompleteness Query q authorized if any equivalent query q' (possibly using views) is authorized l l l (Motro [ICDE 89, JIIS 96]) (Rosenthal & Sciore [DBSec 99, DBSec 01, DMDW 00]) Applications in data warehousing Extended to “conditional equivalence” l l Oct 5, 2004 (Rizvi, Mendelzon, Sudarshan and Roy, [SIGMOD’ 04]) More on this later CASCON 2004 15

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

Cell Level Authorization l E. g: P 3 P opt-in/opt-out model l l Patient names are released to pharmacy l 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 l Le. Fevre et al [VLDB 04] Storage alternatives l Query alternatives: outerjoin vs. subquery in select clause l Oct 5, 2004 CASCON 2004 17

Multi-Level Security level with each tuple and security clearance level for each user l l l Oracle Label Security l l E. g. top-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 l E. g. cannot handle “students can see only their own grades” Allows sensitivity labels plus “compartment” plus “group” Now allows SQL predicates to be added to label security IBM DB 2 for z. OS Work at IBM Toronto (Rjaibi and Bird, VLDB 04) l Oct 5, 2004 Adds labels to MLS CASCON 2004 18

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

Our Contributions l Non-Truman model l l Conditional validity l l Formalization of access control by query rewriting using views 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 Oct 5, 2004 CASCON 2004 20

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

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) Oct 5, 2004 CASCON 2004 22

Basic Inference Rules for Unconditional Validity l Inference rule U 1 l If the query is an authorization view, it is valid Auth View l Inference rule U 2 l Oct 5, 2004 If the query combines only unconditionally valid subqueries, it is unconditionally valid CASCON 2004 23

Implementing inference rule U 2: Query Rewriting Using Views l Extensive work by database community l l Query optimization, view maintenance, data integration systems, … Testing complete rewriting with conjunctive query and conjunctive views is NP-hard (Halevy [VLDBJ 01]) l Queries with arithmetic comparisons (Chaudhuri et al. [ICDE 95]) l l Multiset semantics (Chaudhuri et al. [PODS 94]) Queries/views with aggregation/grouping (Gupta et al. [VLDB 95], Levy et al [PODS 95], Chaudhuri and Shim [EDBT 96], Srivastava et al. [VLDB 96], Cohen et al. [PODS 99], Bello et al [VLDB 98], Zaharioudakis et al. [SIGMOD 00], Larson et al [SIGMOD 01], etc) Oct 5, 2004 CASCON 2004 24

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

Unconditional Validity Is Too Strong! What should happen? l Query is valid if l l False Negative! Document 5 is present, and is rated ≤ the user’s level Query is invalid if l l Document 5 is present, and is rated > the user’s level Document 5 is absent What will happen with just unconditional validity? l Query is declared invalid l 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! Oct 5, 2004 CASCON 2004 26

Beyond Unconditional Validity l Unconditional validity gives false negatives l l 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 l Oct 5, 2004 Too weak: leaks information CASCON 2004 27

A Special Set of Database Instances l Need equivalence on a set of database instances that captures exactly what the user knows l 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 l Current databas state U P Oct 5, 2004 CASCON 2004 28

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” Oct 5, 2004 CASCON 2004 29

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

Conclusions (for Authorization Checking) l l Inference rules implemented (partially) into a Volcano-style query optimizer l Piggy-back on the rewriting capability of the query optimizer Sufficient conditions for conditional validity are expensive l Requires “test queries” to be executed l 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 l Can users live with notion that a query may be unfairly rejected even if actually authorized? l How rare would this be in reality? Oct 5, 2004 CASCON 2004 31

Overall Conclusions l l Lots of interest in fine-grained access control View replacement vs. authorization checking model l 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 l l Oct 5, 2004 User interface needs to check for authorization, even if enforced at database Are we easing developers task or doubling their work? CASCON 2004 32

Extra Slides Oct 5, 2004 CASCON 2004

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

Non-Truman Model: Authorization Specification l l Normal relational views Parameterized views l l Instantiated on access Administered to users using SQL grant/revoke Oct 5, 2004 v 1 create authorization view All. Grades as select * from Grades v 2 create authorization view My. Grades as select * from Grades where sid = $user-id CASCON 2004 35

Non-Truman Model: User Queries l User queries written on base relations l Authorization-transparent querying v q Oct 5, 2004 create authorization view My. Grades as select * from Grades where sid = $user-id select * from Grades where sid = '16856612' CASCON 2004 36

Non-Truman Model: The Test l DBMS tests if the query is valid l l l Oct 5, 2004 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): v create authorization view My. Grades as select * from Grades where sid = $user-id q select * from Grades where sid = '16856612' CASCON 2004 37

Example “A user can see the course-id of all courses she has registered for” create authorization view My. Courses as select course-id from Registered where sid = $user-id v 1 v 2 “A user can see all her grades” q create authorization view My. Grades as select My. Courses. course-id, Grades. grade from My. Courses, Grades where My. Courses. course-id = Grades. course-id select Registered. course-id, Grades. grade from Registered, Grades where Courses. course-id =Grades. course-id and Registered. sid = '16856612' q' Oct 5, 2004 “Return all my grades” select course-id, grade from My. Grades CASCON 2004 38

Inference Rules using Integrity Constraints If: q 1 select Ac , Ar from Rc , Rr where Pc ^ Pr ^ Pj And: Integrity constraint qualifying tuples in Rc there is a qualifying matching tuple (w. r. t Pj) in Rr (e. g. , foreign key constraints) A Infer: q 2 select distinct Ac from Rc where Pc Oct 5, 2004 CASCON 2004 39

Example q 1 = v create authorization view Reg. Students as select s. name, r. course-id from Students s, Registered r where s. student-id=r. student-id “Names of students and course-id’s they have registered for” Integrity constraint Each student must register for at least one course q 2 = q select distinct name from Students Given the integrity constraint, the user can see all distinct names from the authorization view anyway! Oct 5, 2004 CASCON 2004 40

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