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
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

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

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

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,

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

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

9
The View Replacement Approach

• AKA Filter model
• Transparent query modification
• Used in Oracles Virtual Private Database

Grades of all students
q
select from Grades
Grades of current user (Shawn)
select from Grades where studeint_id Shawn'
qm
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

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
12
The Truman Show (1998)
He's a prisoner. Look at him, look at what
you've done to him!
13
Drawbacks of View Replacement

• May provide misleading information
• Query executes in an artificial world
• Inconsistencies between the answer and users
  external information
• Even if query is actually authorized!

Average grade across all courses and across
all students
q
select avg(grade) from Grades
Average grade across all courses for the
current user
select avg(grade) from Grades where student_id
Shawn
qm
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

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,
  SIGMOD04)
• More on this later

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 Im authorized
  to see (within query result)?
• Integration with view replacement model?

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

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

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

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

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)

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

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)

25
Unconditional Validity Is Too Strong!
create authorization view MyDocuments as select
Document. from User, Document where User.uid
user-id and Document.level lt User.level
A user can see all documents rated lower than
or equal to her level
v
select from Document where doc-id '5'
q
Return document 5

• What should happen?

• Query is valid if
• Document 5 is present, and is rated the users
  level
• Query is invalid if
• Document 5 is present, and is rated gt the users
  level
• Document 5 is absent

26
Unconditional Validity Is Too Strong!

• What should happen?
• Query is valid if
• Document 5 is present, and is rated the users
  level
• Query is invalid if
• Document 5 is present, and is rated gt the users
  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!
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

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
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

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 lt 7
Extensions and ongoing work (with Navneet
Loiwal, IIT Bombay)
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?

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?

33
Extra Slides
34
Take-away
Too strong
You dont want to do this!
dcurrent
Just right
U
P
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
v1
create authorization view MyGrades as select
from Grades where sid user-id
v2
36
Non-Truman Model User Queries

• User queries written on base relations
• Authorization-transparent querying

create authorization view MyGrades as select
from Grades where sid user-id
v
select from Grades where sid '16856612'
q
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 Grades where sid user-id
v
select from Grades where sid '16856612'
q
38
Example
A user can see the course-id of all courses she
has registered for
create authorization view MyCourses as select
course-id from Registered where sid user-id
v1
create authorization view MyGrades as select
MyCourses.course-id, Grades.grade from
MyCourses, Grades where MyCourses.course-id
Grades.course-id
v2
A user can see all her grades
q
select Registered.course-id, Grades.grade from
Registered, Grades where Courses.course-id
Grades.course-id and Registered.sid '16856612'
Return all my grades
q'
select course-id, grade from MyGrades
39
Inference Rules using Integrity Constraints

• If

• And

Integrity constraint

• Infer

40
Example
create authorization view RegStudents as select
s.name, r.course-id from Students s, Registered
r where s.student-idr.student-id
q1 v
Names of students and course-ids they have
registered for
Integrity constraint Each student must register
for at least one course
q2 q
select distinct name from Students

• Given the integrity constraint, the user can see
  all distinct names from the authorization view
  anyway!

41
Naïve Take Leaks Information
q
select from Document where doc-id '5'

• 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 users
  level
• Equivalent query is select from MyDocuments
  where doc-id '5'
• Case 2 document 5 present AND its level gt users
  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!

ACCEPT
REJECT
ACCEPT