fine-grained authorization in databases s. sudarshan iit bombay/microsoft research parts of this...
TRANSCRIPT
Fine-Grained Authorization in Databases
S. SudarshanIIT Bombay/Microsoft ResearchParts of this talk joint work with Rizvi, Mendelzon and Roy
Oct 5, 2004CASCON 2004 2
Access Control & Databases
USERS
DBA
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 2004 6
Access Control Using Views Common solution: Views
Per-user views – difficult to administer Solution: parametrized views
create view MyGrades as select * from Grades where student_id = $userid
Authorization-conscious querying Instead of grades, must use MyGrades for students,
another view for faculty, etc,
create view ShawnGrades as select * from Grades where student_id = 'Shawn'
select grade from ShawnGrades where course = 'CS262B'
v
q
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 2004 9
The View Replacement Approach
AKA: Filter model Transparent query modification
Used in Oracle’s Virtual Private Database
select * from Grades
select * from Gradeswhere studeint_id = ‘Shawn'
q
qm
“Grades of all students”
“Grades of current user (Shawn)”
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 2004 12
The Truman Show (1998)
“He's a prisoner. Look at him, look at what you've done to him!”
Oct 5, 2004CASCON 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!
select avg(grade) from Grades
select avg(grade) from Gradeswhere student_id = ‘Shawn’
q
qm “Average grade across all courses for the current user”
“Average grade across all courses and across all students”
Oct 5, 2004CASCON 2004 14
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
create authorization view MyGrades as select * from Gradeswhere sid = $user-id
select * from Gradeswhere sid = '16856612'
v
q
Oct 5, 2004CASCON 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])
Oct 5, 2004CASCON 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?
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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
Extending Query Rewriting Techniques for Fine-Grained
Access Control
Shariq RizviUC Berkeley
Alberto MendelzonUniversity of Toronto
S. SudarshanIIT Bombay
Prasan RoyIBM IRL
Oct 5, 2004CASCON 2004 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
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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)
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 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)
Oct 5, 2004CASCON 2004 25
Unconditional Validity Is Too Strong!
What should happen?
create authorization view MyDocuments asselect Document.* from User, Documentwhere User.uid = $user-idand 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”
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
v
q
Oct 5, 2004CASCON 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!
Oct 5, 2004CASCON 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
Oct 5, 2004CASCON 2004 28
A Special Set of Database Instances
UP
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 databasstate
Oct 5, 2004CASCON 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”
Oct 5, 2004CASCON 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 1 from Document where doc-id = '5' and level <= 7
*Extensions and ongoing work (with Navneet Loiwal, IIT Bombay)
Oct 5, 2004CASCON 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?
Oct 5, 2004CASCON 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?
Extra Slides
Oct 5, 2004CASCON 2004 34
Take-away
dcurrent UP
Too strong
Just right
You don’t want to do this!
Oct 5, 2004CASCON 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 AllGrades as select * from Grades
create authorization view MyGrades as select * from Grades where sid = $user-id
v1
v2
Oct 5, 2004CASCON 2004 36
Non-Truman Model: User Queries User queries written on base relations
Authorization-transparent querying
create authorization view MyGrades as select * from Gradeswhere sid = $user-id
select * from Gradeswhere sid = '16856612'
v
q
Oct 5, 2004CASCON 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 * from Gradeswhere sid = $user-id
select * from Gradeswhere sid = '16856612'
v
q
Oct 5, 2004CASCON 2004 38
Example
create authorization view MyCourses as select course-id from Registeredwhere sid = $user-id
select Registered.course-id, Grades.gradefrom Registered, Gradeswhere Courses.course-id =Grades.course-idand Registered.sid = '16856612'
select course-id, grade from MyGrades
q
q'
create authorization view MyGrades as select MyCourses.course-id, Grades.grade from MyCourses, Gradeswhere MyCourses.course-id = Grades.course-id
“A user can see the course-id of all courses she has registered for”
“A user can see all her grades”
“Return all my grades”
v1
v2
Oct 5, 2004CASCON 2004 39
Inference Rules using Integrity Constraints
If:select Ac , Ar from Rc , Rr
where Pc ^ Pr ^ Pj
q1
select distinct Ac from Rc where Pcq2
A
qualifying tuples in Rc there is a qualifying matching tuple (w.r.t Pj) in Rr (e.g., foreign key constraints)
Integrity constraint
Infer:
And:
Oct 5, 2004CASCON 2004 40
Example
create authorization view RegStudents asselect s.name, r.course-id from Students s, Registered rwhere s.student-id=r.student-id
Integrity constraintEach student must register for at least one course
select distinct name from Students
Given the integrity constraint, the user can see all distinct names from the authorization view anyway!
“Names of students and course-id’s they have registered for”
q1 = v
q2 = q
Oct 5, 2004CASCON 2004 41
Naïve Take Leaks Information
Naïve TestIs 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 * from MyDocuments where doc-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!
select * from Document where doc-id = '5'
ACCEPT
REJECT
ACCEPT
q