463 5 1 database access control tutorial
Download
1 / 49

463.5.1 database access control tutorial - PowerPoint PPT Presentation


  • 294 Views
  • 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.

Related searches for 463.5.1 database access control tutorial

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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 l.jpg

463.5.1 Database Access Control Tutorial

Lars Olson

UIUC CS463

Computer Security


Overview l.jpg
Overview

  • Standard SQL access control syntax

  • Semantics of revocation

  • Reflective databases

  • Oracle Virtual Private Database policies

  • Hippocratic databases


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


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;

    or

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

    or

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

    or

    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

    [declare_local_variables]

    begin

    ...

    end;

    /


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)

Bob

Alice

Carol


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)

Bob

Alice

Carol


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)

Alice

?

Carol

David

Bob


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

Database

User a

b

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)

Database

Access Control Rules

User a

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

Database

Query

Table policy function

VPD

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


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');

    commit;

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

returnvarchar2as

begin

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

= 'BOB') then

return'a = 1';

else

return'';

endif;

end;

/


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)

returnvarchar2as

begin

insertinto alice.logtable values(

sysdate,

SYS_CONTEXT('userenv', 'SESSION_USER')

|| ',' ||

SYS_CONTEXT('userenv', 'CURRENT_SQL'));

commit;

return'';

end;

/


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

    commit;

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

returnvarchar2as

begin

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

|| 'where username = '''

|| SYS_CONTEXT('userenv', 'SESSION_USER')

|| ''')';

end;

/


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

    commit;


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


ad