463 5 1 database access control tutorial l.
Skip this Video
Loading SlideShow in 5 Seconds..
463.5.1 Database Access Control Tutorial PowerPoint Presentation
Download Presentation
463.5.1 Database Access Control Tutorial

Loading in 2 Seconds...

play fullscreen
1 / 49

463.5.1 Database Access Control Tutorial - PowerPoint PPT Presentation

  • Uploaded on

463.5.1 Database Access Control Tutorial. Lars Olson UIUC CS463 Computer Security. Overview. Standard SQL access control syntax Semantics of revocation Reflective databases Oracle Virtual Private Database policies Hippocratic databases. Required.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about '463.5.1 Database Access Control Tutorial' - Jimmy

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
463 5 1 database access control tutorial

463.5.1 Database Access Control Tutorial

Lars Olson


Computer Security

  • Standard SQL access control syntax
  • Semantics of revocation
  • Reflective databases
  • Oracle Virtual Private Database policies
  • Hippocratic databases
  • 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
access control
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
access control for databases
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
quick sql review
Quick SQL Review
  • Creating tables:

createtable table_name (

column1 type1,

column2 type2,



  • Deleting tables:

droptable table_name;

quick sql review7
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)
quick sql review8
Quick SQL Review
  • Querying tables:

select column1, column2 from table_name;


select * from table_name;

  • Conditions:

select columns from table_name

where condition;

quick sql review9
Quick SQL Review
  • Inserting new rows:

insertinto table_name values (value1, value2);


insertinto table_name set column1=value1, column2=value2, ...;

  • Updating rows:

update table_name set column1=value1

where condition;

quick sql review10
Quick SQL Review
  • Deleting rows:

deletefrom table_name where condition;

  • Set values in conditions:

select * from table_name

where column in (select_statement);


select * from table_name

where column in (value1, value2, ...);

quick sql review11
Quick SQL Review
  • Creating functions:

create [orreplace] function function_name (parameters)

return return_type as






sql grant syntax
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

example application
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.
simple access control rules
Simple Access Control Rules
  • Suppose Bob needs access to the whole table (but doesn’t 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
creating views
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:

createview view_name as query_definition;

  • Querying views is nearly identical to querying regular tables
view based access control
View-Based Access Control
  • Alternative method to grant Carol access to name and email columns:

createview employee_public asselect name,email from employee;

grantselecton employee_public to carol;

row level access control
Row-Level Access Control
  • Suppose we also allow employees to view their own ssn, salary:

createview employee_Carol asselect * from employee where name='Carol';

grantselecton employee_Carol to carol;

  • And we allow them to update their e-mail addresses:

grantupdate(email) on employee_Carol to carol;

    • (Or create yet another new view…)
delegating policy authority
Delegating Policy Authority

grant privilege_list on resource to user_list withgrantoption;

  • 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: grantselecton table1 to bob withgrantoption;
    • Bob: grantselect(column1) on table1 to carol withgrantoption;
sql revoke syntax
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?
griffiths wade model
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.
grants from multiple sources
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)




not as easy as it looks
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)




cascading revocations
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)






meanwhile in the real world
Meanwhile, in the Real World...
  • Account privileges get changed all the time
  • We don’t 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

cascading revocations25
Cascading Revocations
  • How would “revokeselecton 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.
disadvantages to sql model
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
disadvantages cont
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.
reflective database policies
Reflective Database Policies
  • Computational Reflection
    • Objects contain metadata about their own computation
    • Modifying metadata changes the actual computation process
    • Common example: Java’s 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)
motivation for reflective dbs

Application b

Access Control Rules


User a


Motivation for Reflective DBs
  • Database applications often need to serve multiple users
  • Programmers often give their applications elevated privileges
motivation cont
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
ideal model

Enhanced Security Layer

Application b

(Optional access control)


Access Control Rules

User a


Ideal Model
  • Database enforces its own security
  • Can be implemented as wrapper on database or as part of the database
virtual private databases



Table policy function


function evaluator

User name

Other data

App-defined context

Rewritten query

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

  • 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
simple policy
Simple Policy
  • Two users, Alice and Bob
  • Alice creates a table:

createtable test (a intprimarykey, b varchar2(50));

insertinto test values(1, 'hello');

insertinto test values(2, 'world');


  • Alice wants to limit Bob’s access to the row where a=1
  • Three steps:
    • Grant Bob access to the table:

grantselecton test to bob;

    • Create a policy function
    • Attach the policy function to the table
simple policy35
Simple Policy

createorreplacefunction testFilter (p_schema varchar2, p_obj varchar2)



if (SYS_CONTEXT('userenv', 'SESSION_USER')

= 'BOB') then

return'a = 1';






simple policy36
Simple Policy

execute dbms_rls.add_policy( object_schema => 'alice', object_name => 'test', policy_name => 'FilterForBob', function_schema => 'alice', policy_function => 'testFilter', statement_types => 'select, update, insert',update_check => true);

  • Query the table, once as Alice and again as Bob:

select * from alice.test;

logging policy
Logging Policy
  • Alice wants to log all accesses to her test table
  • Create a log table:

createtable logtable (

when date,

entry long);

logging policy38
Logging Policy

createorreplacefunction testLogging(p_schema varchar2, p_obj varchar2)



insertinto alice.logtable values(



|| ',' ||






logging policy39
Logging Policy

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

logging policy40
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.
reflective policy
Reflective Policy
  • Alice wants the permissions on each row in her test table to be defined in another table, userperms.
  • Create the table:

createtable userperms (username varchar2(50), a intreferences test);

    • Note the foreign key reference
    • Not required, but it can help optimization
reflective policy42
Reflective Policy
  • Populate the table:

insertinto userperms values('BOB', 1);

insertinto userperms values('ALICE', 1);

insertinto userperms values('ALICE', 2);


    • Note the capital letters in BOB and ALICE
    • SQL commands are case-insensitive
    • Table values are case-sensitive
reflective policy43
Reflective Policy

createorreplacefunction testFilter(p_schema varchar2, p_obj varchar2)



return'a in (select a from alice.userperms '

|| 'where username = '''


|| ''')';



reflective policy44
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:

insertinto userperms values('BOB', 2);


reflective policy45
Reflective Policy
  • Notes:
    • Currently, Alice cannot insert new rows into test (since userperms only gives Alice access to a=1 or a=2)
    • 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:

grantexemptaccesspolicyto alice;

      • Must be granted from system administrator
      • Exempts from all access policies, cannot specify table-by-table basis
hippocratic databases
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

hippocratic db techniques
Hippocratic DB Techniques
  • Query rewriting
  • Boolean opt-in/opt-out columns
  • VPD-like policies for each data cell
key points
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)
software versions
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 don’t have testbeds