463 5 1 database access control tutorial
1 / 49

- PowerPoint PPT Presentation

  • Updated 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 '' - 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 l.jpg

463.5.1 Database Access Control Tutorial

Lars Olson


Computer Security

Overview l.jpg

  • Standard SQL access control syntax

  • Semantics of revocation

  • Reflective databases

  • Oracle Virtual Private Database policies

  • Hippocratic databases

Required l.jpg

  • 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 l.jpg
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 l.jpg
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 l.jpg
Quick SQL Review

  • Creating tables:

    createtable table_name (

    column1 type1,

    column2 type2,



  • Deleting tables:

    droptable table_name;

Quick sql review7 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Quick SQL Review

  • Creating functions:

    create [orreplace] function function_name (parameters)

    return return_type as






Sql grant syntax l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg

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 l.jpg
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 l.jpg

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 l.jpg



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

Features l.jpg

  • 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 l.jpg
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 l.jpg
Simple Policy

createorreplacefunction testFilter (p_schema varchar2, p_obj varchar2)



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

= 'BOB') then

return'a = 1';






Simple policy36 l.jpg
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 l.jpg
Logging Policy

  • Alice wants to log all accesses to her test table

  • Create a log table:

    createtable logtable (

    when date,

    entry long);

Logging policy38 l.jpg
Logging Policy

createorreplacefunction testLogging(p_schema varchar2, p_obj varchar2)



insertinto alice.logtable values(



|| ',' ||






Logging policy39 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Reflective Policy

createorreplacefunction testFilter(p_schema varchar2, p_obj varchar2)



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

|| 'where username = '''


|| ''')';



Reflective policy44 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Hippocratic DB Techniques

  • Query rewriting

  • Boolean opt-in/opt-out columns

  • VPD-like policies for each data cell

Key points l.jpg
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 l.jpg
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