463.5.1 Database Access Control Instructional exercise.

Uploaded on:
Category: News / Events
2. Diagram. Standard SQL access control syntaxSemantics of revocationReflective databasesOracle Virtual Private Database policiesHippocratic databases. 3. Required. P. P. Griffiths and B. W. Wade:
Slide 1

463.5.1 Database Access Control Tutorial Lars Olson UIUC CS463 Computer Security

Slide 2

Overview Standard SQL get to control language structure Semantics of renouncement Reflective databases Oracle Virtual Private Database strategies Hippocratic databases

Slide 3

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

Slide 4

Access Control A plan for mapping clients to permitted activities Identity-Based Access Control Role-Based Access Control Attribute-Based Access Control Discretionary Access Control instruments An individual client can set the strategy e.g. Unix record consents Mandatory Access Control components The arrangement is incorporated with the framework, people can\'t alter it e.g. memory security systems

Slide 5

Access Control for Databases Challenges: Multiple operations: select (read), embed/upgrade/erase (compose), reference, make trigger, execute put away methodology, make tables, ... Table-level get to control is excessively coarse-grained, cell-level get to control is excessively repetitive (more on that later) SQL has institutionalized get to control arrangement definition dialect Security show created by Griffiths and Wade in 1976

Slide 6

Quick SQL Review Creating tables: make table table_name ( column1 type1, column2 type2, ... ); Deleting tables: drop table table_name;

Slide 7

Quick SQL Review Types: int skim date char(size) Always delimited by single quote (punctuation) Use two single quotes to speak to the punctuation character varchar(size) ( varchar2 in Oracle) content ( long in Oracle)

Slide 8

Quick SQL Review Querying tables: select column1, column2 from table_name; or select * from table_name; Conditions: select segments from table_name where condition;

Slide 9

Quick SQL Review Inserting new lines: embed into table_name values (value1, value2); or embed into table_name set column1=value1, column2=value2, ...; Updating lines: redesign table_name set column1=value1 where condition;

Slide 10

Quick SQL Review Deleting lines: erase from table_name where condition; Set values in conditions: select * from table_name where section in (select_statement); or select * from table_name where segment in (value1, value2, ...);

Slide 11

Quick SQL Review Creating capacities: make [ or supplant ] work function_name (parameters) return return_type as [declare_local_variables] start ... end ;/

Slide 12

SQL give Syntax concede privilege_list on asset to user_list; Privileges incorporate select , embed , and so forth. Asset might be a table, a database, a capacity, and so forth. Client rundown might be singular clients, or might be a client assemble Griffiths Wade 76

Slide 13

Example Application Alice possesses a database table of organization workers: name varchar ( 50 ), ssn int , compensation int , email varchar ( 50 ) Some data (ssn, pay) ought to be secret, others can be seen by any representative.

Slide 14

Simple Access Control Rules Suppose Bob needs access to the entire table (however doesn\'t have to roll out improvements): concede select on representative to weave; Suppose Carol is another worker, who ought to just get to open data: allow select(name,email) on worker to song; not actualized in PostgreSQL (see next slide) not executed for select in Oracle executed in MySQL

Slide 15

Creating Views Careful with definitions! A subset of the database to which a client has admittance, or: A virtual table made as an "easy route" inquiry of different tables View grammar: make see view_name as query_definition; Querying perspectives is about indistinguishable to questioning normal tables

Slide 16

View-Based Access Control Alternative technique to concede Carol access to name and email segments: make see employee_public as select name,email from representative; give select on employee_public to hymn;

Slide 17

Row-Level Access Control Suppose we additionally permit workers to see their own ssn, pay: make see employee_Carol as select * from worker where name= "Ditty" ; give select on employee_Carol to tune; And we permit them to overhaul their email addresses: give upgrade (email) on employee_Carol to song; (Or make yet another new view… )

Slide 18

Delegating Policy Authority give privilege_list on asset to user_list with give choice ; Allows different clients to give benefits, including "with give choice" benefits "Duplicate appropriate" from Access Control address (slide 21) Can give subset benefits excessively Alice: give select on table1, making it impossible to sway with give choice ; Bob: give select (column1) on table1 to tune with give choice ;

Slide 19

SQL disavow Syntax renounce privilege_list on asset from user_list; What happens when a client is allowed access from two distinct sources, and one is repudiated? What happens when a "with give alternative" benefit is renounced?

Slide 20

Griffiths-Wade Model Sequences of give/repudiate operations When a benefit is renounced, the ACLs ought to be indistinct from an arrangement in which the concede never happened.

Slide 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

Slide 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

Slide 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 ? Song David Bob

Slide 24

Meanwhile, in the Real World... Account benefits get changed all the time We would prefer dependably not to re-try everything Tedious Involves other clients\' activities SQL deny order has two discretionary contentions: course : fixes all needy concede orders limit : exits with disappointment if there exist subordinate stipends Ramakrishnan Gehrke 03

Slide 25

Cascading Revocations How might " renounce select on table1 from song course " work in the past illustration? Just benefits allowed exclusively through the renounced benefits will likewise be repudiated. On the off chance that there exists a way in the diagram from the grantor, then don\'t renounce. Permits us to indicate special cases preemptively.

Slide 26

Disadvantages to SQL Model Too many perspectives to make Tedious for some clients, each with their own View redefinitions that change the view pattern require dropping the view, reclassifying, then reissuing benefits Fine-grained approaches each require their own view — and no undeniable approach to see that the perspectives originate from a similar table

Slide 27

Disadvantages (cont) Complicated strategy rationale can be hard to express and to overhaul Update peculiarities Updates should be made in numerous spots If any means are overlooked, the database is in a conflicting state e.g. Assume we have a representatives table, and all supervisors in this table get uncommon redesign benefits.

Slide 28

Reflective Database Policies Computational Reflection Objects contain metadata about their own particular calculation Modifying metadata changes the genuine calculation handle Common illustration: Java\'s java.lang.reflect bundle Apply to database get to control The arrangement itself contains a database inquiry SQL Views might be intelligent (limitedly)

Slide 29

Application b Access Control Rules Database User a b Motivation for Reflective DBs Database applications regularly need to serve different clients Programmers frequently give their applications raised benefits

Slide 30

Motivation (cont) Violates standard of minimum benefit Programming mistakes Malicious assaults ( e.g. SQL infusion assaults) Separates get to control from the database New arrangement may require upgrades on database and on application Database may have other section focuses So why do software engineers still do this? An excessive number of clients to give database accounts Complicated get to arrangement rationale

Slide 31

Enhanced Security Layer Application b (Optional get to control) Database Access Control Rules User an Ideal Model Database upholds its own particular security Can be executed as wrapper on database or as a major aspect of the database

Slide 32

Database Query Table strategy work VPD work evaluator User name Other information App-characterized setting Rewritten inquiry Virtual Private Databases Security display for Oracle Policies are client characterized capacities that arrival a condition for a SQL where statement Applications can likewise characterize a "unique situation," e.g. for part based get to control Oracle 05

Slide 33

Features Functions are executed every time the table is gotten to. Numerous capacities can be joined to a table. Diverse capacities can be characterized relying upon: Operation (read versus compose) Columns being gotten to

Slide 34

Simple Policy Two clients, Alice and Bob Alice makes a table: make table test (an int essential key , b varchar2 ( 50 )); embed into test values ( 1 , " hi " ); embed into test values ( 2 , " world " ); confer ; Alice needs to farthest point Bob\'s entrance to the line where a=1 Three stages: Grant Bob access to the table: concede select on test to sway; Create an approach work Attach the strategy capacity to the table

Slide 35

Simple Policy make or supplant work testFilter (p_schema varchar2 , p_obj varchar2 ) return varchar2 as start if (SYS_CONTEXT( "userenv" , "SESSION_USER" ) = "Weave" ) then give back \'a = 1\' ; else return "" ; end if ; end ;/

View more...