463'5'1 Database Access Control Tutorial

1
463.5.1 Database Access Control Tutorial

• Lars Olson
• UIUC CS463
• Computer Security

2
Overview

• Standard SQL access control syntax
• Semantics of revocation
• Reflective databases
• Oracle Virtual Private Database policies
• Hippocratic databases

3
Required

• P. P. Griffiths and B. W. Wade An
  Authorization Mechanism for a Relational Database
  System
• Oracle Corporation Oracle Virtual Private
  Database (white paper)
• R. Agrawal et al. Hippocratic Databases
• Any database textbook or reference with SQL
• H. Garcia-Molina et al. Database Systems The
  Complete Book
• R. Ramakrishnan and J. Gehrke Database
  Management Systems

4
Access Control

• A scheme for mapping users to allowed actions
• Identity-Based Access Control
• Role-Based Access Control
• Attribute-Based Access Control
• Discretionary Access Control mechanisms
• An individual user can set the policy
• e.g. Unix file permissions
• Mandatory Access Control mechanisms
• The policy is built into the system, individuals
  cannot modify it
• e.g. memory protection mechanisms

5
Access Control for Databases

• Challenges
• Multiple operations select (read),
  insert/update/delete (write), reference, create
  trigger, execute stored procedure, create tables,
  ...
• Table-level access control is too coarse-grained,
  cell-level access control is too tedious (more on
  that later)
• SQL has standardized access control policy
  definition language
• Security model developed by Griffiths and Wade in
  1976

6
Quick SQL Review

• Creating tables
• create table table_name (
• column1 type1,
• column2 type2,
• ...
• )
• Deleting tables
• drop table table_name

7
Quick SQL Review

• Types
• int
• float
• date
• char(size)
• Always delimited by single quote (apostrophe)
• Use two single quotes to represent the apostrophe
  character
• varchar(size) (varchar2 in Oracle)
• text (long in Oracle)

8
Quick SQL Review

• Querying tables
• select column1, column2 from table_name
• or
• select from table_name
• Conditions
• select columns from table_name
• where condition

9
Quick SQL Review

• Inserting new rows
• insert into table_name values (value1, value2)
• or
• insert into table_name set column1value1,
  column2value2, ...
• Updating rows
• update table_name set column1value1
• where condition

10
Quick SQL Review

• Deleting rows
• delete from table_name where condition
• Set values in conditions
• select from table_name
• where column in (select_statement)
• or
• select from table_name
• where column in (value1, value2, ...)

11
Quick SQL Review

• Creating functions
• create or replace function function_name
  (parameters)
• return return_type as
• declare_local_variables
• begin
• ...
• end
• /

12
SQL grant Syntax

• grant privilege_list on resource to user_list
• Privileges include select, insert, etc.
• Resource may be a table, a database, a function,
  etc.
• User list may be individual users, or may be a
  user group

Griffiths Wade 76
13
Example Application

• Alice owns a database table of company employees
• name varchar(50),
• ssn int,
• salary int,
• email varchar(50)
• Some information (ssn, salary) should be
  confidential, others can be viewed by any
  employee.

14
Simple Access Control Rules

• Suppose Bob needs access to the whole table (but
  doesnt need to make changes)
• grant select on employee to bob
• Suppose Carol is another employee, who should
  only access public information
• grant select(name,email) on employee to carol
• not implemented in PostgreSQL (see next slide)
• not implemented for select in Oracle
• implemented in MySQL

15
Creating Views

• Careful with definitions!
• A subset of the database to which a user has
  access, or
• A virtual table created as a shortcut query of
  other tables
• View syntax
• create view view_name as query_definition
• Querying views is nearly identical to querying
  regular tables

16
View-Based Access Control

• Alternative method to grant Carol access to name
  and email columns
• create view employee_public as select name,email
  from employee
• grant select on employee_public to carol

17
Row-Level Access Control

• Suppose we also allow employees to view their own
  ssn, salary
• create view employee_Carol as select
  from employee where name'Carol'
• grant select on employee_Carol to carol
• And we allow them to update their e-mail
  addresses
• grant update(email) on employee_Carol to carol
• (Or create yet another new view)

18
Delegating Policy Authority

• grant privilege_list on resource to user_list
  with grant option
• Allows other users to grant privileges, including
  with grant option privileges
• Copy right from Access Control lecture (slide
  21)
• Can grant subset privileges too
• Alice grant select on table1 to bob with grant
  option
• Bob grant select(column1) on table1 to carol
  with grant option

19
SQL revoke Syntax

• revoke privilege_list on resource from user_list
• What happens when a user is granted access from
  two different sources, and one is revoked?
• What happens when a with grant option privilege
  is revoked?

20
Griffiths-Wade Model

• Sequences of grant / revoke operations
• When a privilege is revoked, the ACLs should be
  indistinguishable from a sequence in which the
  grant never occurred.

21
Grants from Multiple Sources

• grant(Alice,Bob)
• grant(Alice,Carol)
• grant(Carol,Bob)
• revoke(Alice,Bob)

• grant(Alice,Bob)
• grant(Alice,Carol)
• grant(Carol,Bob)
• revoke(Alice,Bob)

Bob
Alice
Carol
22
Not as Easy as it Looks!

• grant(Alice,Bob)
• grant(Bob,Carol)
• grant(Carol,Bob)
• revoke(Alice,Bob)

• grant(Alice,Bob)
• grant(Bob,Carol)
• grant(Carol,Bob)
• revoke(Alice,Bob)

Bob
Alice
Carol
23
Cascading Revocations

• grant(Alice,Bob)
• grant(Alice,Carol)
• grant(Carol,David)
• grant(Bob,Carol)
• revoke(Alice,Carol)

• grant(Alice,Bob)
• grant(Alice,Carol)
• grant(Carol,David)
• grant(Bob,Carol)
• revoke(Alice,Carol)

Alice
?
Carol
David
Bob
24
Meanwhile, in the Real World...

• Account privileges get changed all the time
• We dont always want to redo everything
• Tedious
• Involves other users actions
• SQL revoke command has two optional arguments
• cascade undoes all dependent grant commands
• restrict exits with failure if there exist
  dependent grants

Ramakrishnan Gehrke 03
25
Cascading Revocations

• How would revoke select on table1 from carol
  cascade work in the previous example?
• Only privileges granted solely through the
  revoked privileges will also be revoked.
• If there exists a path in the graph from the
  grantor, then do not revoke.
• Allows us to specify exceptions preemptively.

26
Disadvantages to SQL Model

• Too many views to create
• Tedious for many users, each with their own view
• View redefinitions that change the view schema
  require dropping the view, redefining, then
  reissuing privileges
• Fine-grained policies each require their own
  view and no obvious way to see that the views
  come from the same table

27
Disadvantages (cont)

• Complicated policy logic can be difficult to
  express and to update
• Update anomalies
• Updates need to be made in multiple places
• If any steps are forgotten, the database is in an
  inconsistent state
• e.g. Suppose we have an employees table, and all
  managers in this table get special update
  privileges.

28
Reflective Database Policies

• Computational Reflection
• Objects contain metadata about their own
  computation
• Modifying metadata changes the actual computation
  process
• Common example Javas java.lang.reflect package
• Apply to database access control
• The policy itself contains a database query
• SQL Views may be reflective (in a limited way)

29
Motivation for Reflective DBs

• Database applications often need to serve
  multiple users
• Programmers often give their applications
  elevated privileges

30
Motivation (cont)

• Violates principle of least-privilege
• Programming errors
• Malicious attacks (e.g. SQL injection attacks)
• Separates access control from the database
• New policy may require updates on database and on
  application
• Database may have other entry points
• So why do programmers still do this?
• Too many users to give database accounts
• Complicated access policy logic

31
Ideal Model

• Database enforces its own security
• Can be implemented as wrapper on database or as
  part of the database

32
Virtual Private Databases

• Security model for Oracle
• Policies are user-defined functions that return a
  condition for an SQL where clause
• Applications can also define a context, e.g.
  for role-based access control

Oracle 05
33
Features

• Functions are executed each time the table is
  accessed.
• Multiple functions can be attached to a table.
• Different functions can be defined depending on
• Operation (read vs. write)
• Columns being accessed

34
Simple Policy

• Two users, Alice and Bob
• Alice creates a table
• create table test (a int primary key, b
  varchar2(50))
• insert into test values(1, 'hello')
• insert into test values(2, 'world')
• commit
• Alice wants to limit Bobs access to the row
  where a1
• Three steps
• Grant Bob access to the table
• grant select on test to bob
• Create a policy function
• Attach the policy function to the table

35
Simple Policy

• create or replace function testFilter
  (p_schema varchar2, p_obj varchar2)
• return varchar2 as
• begin
• if (SYS_CONTEXT('userenv', 'SESSION_USER')
• 'BOB') then
• return 'a 1'
• else
• return ''
• end if
• end
• /

36
Simple Policy

• execute dbms_rls.add_policy( object_schema
  gt 'alice', object_name gt 'test',
  policy_name gt 'FilterForBob',
  function_schema gt 'alice', policy_function gt
  'testFilter', statement_types gt 'select, update,
  insert', update_check gt true)
• Query the table, once as Alice and again as Bob
• select from alice.test

37
Logging Policy

• Alice wants to log all accesses to her test table
• Create a log table
• create table logtable (
• when date,
• entry long)

38
Logging Policy

• create or replace function testLogging(p_schema
  varchar2, p_obj varchar2)
• return varchar2 as
• begin
• insert into alice.logtable values(
• sysdate,
• SYS_CONTEXT('userenv', 'SESSION_USER')
• ','
• SYS_CONTEXT('userenv', 'CURRENT_SQL'))
• commit
• return ''
• end
• /

39
Logging Policy

• execute dbms_rls.add_policy( object_schema
  gt 'alice', object_name gt 'test',
  policy_name gt 'LogAccesses', function_schema
  gt 'alice', policy_function gt 'testLogging',
  statement_types gt 'select, update, insert',
  update_check gt true)

40
Logging Policy

• Query the test table again, once as Alice and
  again as Bob
• select from alice.test
• Query the logtable table
• select from logtable
• Note that the original query is recorded, not the
  rewritten query.
• Note also that Bob cannot query logtable.

41
Reflective Policy

• Alice wants the permissions on each row in her
  test table to be defined in another table,
  userperms.
• Create the table
• create table userperms (username varchar2(50), a
  int references test)
• Note the foreign key reference
• Not required, but it can help optimization

42
Reflective Policy

• Populate the table
• insert into userperms values('BOB', 1)
• insert into userperms values('ALICE', 1)
• insert into userperms values('ALICE', 2)
• commit
• Note the capital letters in BOB and ALICE
• SQL commands are case-insensitive
• Table values are case-sensitive

43
Reflective Policy

• create or replace function testFilter(p_schema
  varchar2, p_obj varchar2)
• return varchar2 as
• begin
• return 'a in (select a from alice.userperms '
• 'where username '''
• SYS_CONTEXT('userenv', 'SESSION_USER')
• ''')'
• end
• /

44
Reflective Policy

• Query the test table, both as Alice and as Bob
• select from alice.test
• Have Alice allow additional access to Bob, and
  then have Bob query test again
• insert into userperms values('BOB', 2)
• commit

45
Reflective Policy

• Notes
• Currently, Alice cannot insert new rows into test
  (since userperms only gives Alice access to a1
  or a2)
• Alice cannot insert new rows into userperms
  (since the foreign key constraint requires the a
  value to exist in test)
• Solutions
• Alice can exempt herself by writing the logic in
  the function
• Alice can be exempted after adding the policy
• grant exempt access policy to alice
• Must be granted from system administrator
• Exempts from all access policies, cannot specify
  table-by-table basis

46
Hippocratic Databases

• Hippocrates Greek physician, 400 B.C.
• Hippocratic Oath statement of ethical conduct
  in medicine, includes respecting privacy of
  patients
• Hippocratic Databases
• database with built-in privacy controls
• requires merging security policies from database
  owner and from data owner(s)

Agrawal et al. 02
47
Hippocratic DB Techniques

• Query rewriting
• Boolean opt-in/opt-out columns
• VPD-like policies for each data cell

48
Key Points

• Access control for databases requires scalability
• SQL standard
• grant, revoke
• with grant option
• view-based access control
• Reflective databases
• Oracle VPD policy functions
• Hippocratic databases (data ownership and privacy
  policies)

49
Software Versions

• PostgreSQL
• testbed version 8.2
• MySQL
• testbed version 5.0
• Oracle
• testbed version 10.2
• Only Enterprise Edition has VPD
• Other standard SQL databases
• Microsoft SQL Server, IBM DB2, Sybase
• should all work, but we dont have testbeds