Formalizing and Ref ining Authorization in SQL by Aaron Rosenthal - - PowerPoint PPT Presentation

Formalizing and Ref ining Authorization in SQL

by Aaron Rosenthal and Edward Sciore (MI TRE)

J i-Won Byun Tr uSe Reading Group J anuar y 11, 2005

I ntroduction

Problems of current aut horizat ion semant ics of

SQL

– t oo complex due t o t riggers, obj ect s, and ot her f eat ures. – Numerous special cases and unnecessary rest rict ions. – DBA must cope wit h diverse user communit ies.

Goal

– Reduce t he ad hoc nat ure of aut horizat ion semant ics. – I nt roduce explicit , simple, and f ormal principles. – Formalizat ion and simplif icat ion st art f rom pract ice.

Formalizing authorization in SQL

A dat abase consist s of a set of obj ect s

– Obj ect s: schemas, base t ables, views, columns and

procedures.

– Each obj ect has a set of act ions t hat can be perf ormed

• n it ; e.g., select , updat e, insert , delet e and execut e.

Operat ion: (α, O)

– Specif ies a part icular act ion α on a part icular obj ect O.

I D (user): individuals, roles, groups, or Public. Privilege: (τ, θ)

– Allows an I D τ t o perf orm an operat ion θ.

Formalizing authorization in SQL

Given a st at ement S, SQL implicit ly def ines a set of

• per at ions, OPS(S), f or checking aut hor izat ion.

– That is, an I D τ is aut hor ized t o per f or m S if f τ has a

pr ivilege f or ever y oper at ion in OPS(S).

– OPS(S) can be f ound by t he f ollowing r ules:

I f S is a quer y, OPS(S) cont ains (select , A) f or all columns A

ment ioned in S.

I f S is an updat e, OPS(S) cont ains (updat e, A) f or each column A

being updat ed, plus (select , B) f or all columns B ment ioned in S.

I f S is a call t o r out ine P, OPS(S) cont ains (execut e, P), plus

(select , A) f or all columns A ment ioned in t he ar gument list .

I f S cont ains a nest ed st at ement S’, OPS(S) cont ains all

• perat ions of S’.

Formalizing authorization in SQL

Example: Updat e T set A = C + 2

wher e B1 in (select B2 f r om V)

OP

S(S) = { (select , T.B1), (select , T.C), (select , B.B2), (updat e, T.A)}

I f S is complex, t he comput at ion of OPS(S) may not

be st r aight f or war d.

– Unnecessary predicat es; e.g., t aut ologies and const raint s – select T.A f rom T where T.B is null or T.B * T.B >

= 0

(select , T.B) should not be in OPS(S)

– The det ect ion of such predicat es is not decidable; t hey

are not considered.

Formalizing authorization in SQL

• Grant

–

An I D receives privileges via grant st at ement s.

–

An I D is able t o issue a grant st at ement f or an operat ion if it s pr ivilege include a gr ant-opt ion privilege f or t he operat ion.

• Ownership

–

When an obj ect is creat ed, t he creat or is given administ rat ive aut horit y over t he obj ect .

–

Two aspect s: right s over t he def ined met adat a and right s

• ver t he inst ance populat ion

1.

Base t able: t he creat or is given all possible privileges.

2.

Derived obj ect : t he creat or is given f ull right s on t he met adat a and limit ed right s over t he inst ance populat ion.

Formalizing authorization in SQL

• Der ived obj ect s: pr ocedur es and views

–

Each derived obj ect Z has a def ining st at ement , DEF(Z).

–

Unlike base t ables, when a derived obj ect is creat ed, t he syst em inf ers t he appropriat e privileges based on t he cr eat or’s privileges on underlying obj ect s.

–

The general principle is t hat it is saf e t o inf er privileges f or t asks t he user could accomplish by ot her means; i.e., inf erence may increase convenience, but not power.

• The SQL I nf erence Principle: Let θ be an operat ion on

derived obj ect Z. Then Z’s creat or τ should receive privileges on θ provided t hat τ’s abilit y t o access and modif y dat a does not increase.

Formalizing authorization in SQL

• Example: creat e view Z as select A, C f rom T where T.B >

2

–

Say t he creat or τ has privileges on (select , T) and (updat e, T.A).

–

Then it is wrong t o give τ t he privilege on (updat e, Z).

–

But it is okay t o give τ t he privilege on (updat e, Z.A).

• I nf erences are j ust if ied by using query modif icat ion

–

Take a st at ement S involving derived obj ect Z, and produce an equivalent st at ement S’ by replacing ref erences t o Z t o t ables in DEF(Z).

–

Select Z.A f rom Z select T.A f rom T where T.B > 2

–

Thus, it would be wrong t o give τ an inf erred privilege on (select , Z.A) unless τ already has privileges on (select , T.A) and (select , T.B).

Formalizing authorization in SQL

• Query modif icat ion t echnique can provide a count erexample, but it

cannot prove t hat an inf erence is correct .

–

We would have t o examine ever y possible st at ement involving Z.

• Def init ion. Let Z be a derived obj ect , and let θ be an oper at ion on Z.

OPS(θ) is f ound as f ollows:

–

OPS((select , Z.B)) consist s of t hose oper at ions (select , T.A) such t hat changing A-value of T can change t he B-value of Z.

–

OPS((inser t , Z.B)) consist s of t hose oper at ions (inser t , T.A) if inser t ing int o Z can cause an inser t ion int o T, and Z.B is der ived f r om T.A.

–

OPS((delet e, Z)) consist s of (delet e, T) if delet ing Z can cause a delet ion f r om T.

–

OPS((updat e, Z.B)) consist s of t hose oper at ions (updat e, T.A) if updat ing t he B-value of Z can cause a change in t he A-value of T.

–

OPS((execut e, P)) consist s of t he oper at ions r equir ed t o execut e t he body of P. That is, it cont ains each oper at ion in OPS(DEF(P)).

Formalizing authorization in SQL

• The SQL Pr ivilege I nf er ence Rule: Let τ be t he cr eat or
• f der ived obj ect Z and let θ be an oper at ion on Z.

1.

I nf er t he pr ivilege (τ, θ) if τ has a privilege f or every

• perat ion in OPS(θ).

2.

I nf er t he pr ivilege (τ, grant θ) if τ has grant -opt ion privilege f or every operat ion in OPS(θ).

• Theorem. The privileges inf erred by t his rule sat isf ies

t he SQL I nf erence Principle.

–

Pr oved in t he paper

Proposed extension

• I nf erred privileges on derived obj ect s

–

I n st andard SQL, all privileges on a derived obj ect st em f rom t he creat or.

–

The ext ension is t o allow privileges on a derived obj ect s t o be inf erred t o any I D, not j ust t he obj ect ’s creat or.

• The inf er ence Pr inciple

–

Let θ be an operat ion on derived obj ect Z. An I D τ receive privilege on θ as long as τ’s abilit y t o access and modif y dat a does not increase.

Proposed extension

• Who may creat e a derived obj ect ?

–

As all privileges on a derived obj ect st em f rom t he creat or, SQL does not allow an I D t o creat e an obj ect unless t he creat or receives a reasonable number of privileges.

–

Wit hout t his rest rict ion, any user can creat e a derived obj ect and receives what ever privileges t he syst em inf er.

–

However, t he met adat a (def init ion) of derived obj ect must be explicit ly cont rolled.

–

I nt roduce a new act ion, Visible.

–

Privilege on (visible, Z) allows I D t o see Z’s def init ion.

–

Now some users can use Z wit hout knowing t he def init ion of Z. Also t he creat or can allow some users t o see t he def init ion of Z wit hout giving t hem privileges t o use it .

Proposed extension: Benef its

• Creat ors need not be administ rat ors.

–

Subj ect s wit h (visible, Z) and privileges on OPS(θ) are immediat ely able t o use θ wit hout any explicit grant by t he creat or.

–

The creat or can give access t o Z t o anyone wit h suf f icient aut horizat ion on t he underlying obj ect by grant ing (visible, Z) t o Public.

• P

rivileges can be kept consist ent aut omat ically.

–

Consider a dat a warehouse, whose cont ent s are a mat erialized view of it s underlying source dat abases.

–

The proposed model provides a way t o enf orce consist ency bet ween t he warehouse privileges and t he source privileges.

Proposed extension: Benef its

• Explicit cont rol over met adat a privileges

–

SQL allows an I D wit h any privilege on an obj ect t o have t he abilit y t o see all met adat a about t he obj ect ; more is revealed t hat required.

–

A user wit h select privilege can see t he const raint s.

–

A user who can execut e a procedure can see t he def init ion.

–

I n some cases, t his is not desirable.

• Unt rust ed I Ds can creat e usef ul derived obj ect s.

–

As t he creat or of a derived obj ect is t he source of all privileges in SQL, only t rust ed users can creat e usef ul views.

–

I n t he proposed model, I Ds can access t he obj ect even if t he creat or is unt rust ed or lazy.

Proposed extension: Benef its

• I nvoker’s right s are int egrat ed int o t he model.

–

The SQL st andard requires t hat t he creat or of a procedure have gr ant-opt ion privileges on all operat ions in t he procedure.

–

Oracle int roduced t he invoker-right mechanism, which requires users have not only an execut e privilege, but also all t he privileges t o execut e operat ions in t he procedure.

–

A cont ract programmer can writ e complex procedures and grant execut e privilege t o public. Then only t he users having suf f icient privileges can act ually use t he procedure.

–

The model ext ends invoker-right f eat ures beyond procedure t o any operat ion θ on a derived obj ect Z.

–

An administ rat or can choose t o grant explicit privileges on θ t o some I Ds, and t o allow possible inf erence of θ t o ot her I Ds by grant ing Visible privileges t o t hem.

Another issue

• Base t able ownership

–

I t is benef icial t o separat e t he met adat a privileges on a derived

• bj ect f rom t he privileges on it s cont ent .

–

I s t his separat ion possible f or base t ables? The creat or obviously deserves all met adat a privileges, but how do we assign t he privileges on it s cont ent ?

–

For example, a programmer or DBA can creat e a t able, but should not have t he right t o see t he dat a.

–

I n SQL, one cannot remove t he creat or’s right s since delet ion cascades.

–

A simple way is t o provide a way t o remove t he right s f rom t he creat or wit hout af f ect ing t heir delegat ees. (non-cascading r evoke)

Question?

View Security as the Basis f or Data Warehouse Security

by Aaron Rosenthal and Edward Sciore (MI TRE)

J i-Won Byun Tr uSe Reading Group J anuar y 11, 2005

I ntroduction

Problem

– Current ly, access permissions in a dat a warehouse are

managed in a separat e world f rom t he sources’ policies.

– The warehouse DBA has t o manually specif y access

right s on all warehouse dat a.

– The warehouse DBA must be t rust ed by all sources. – The consequences are inconsist encies, slow response t o

change, and a heavy work load f or administ rat ors.

– Thus, t he crit ical problem of dat a warehousing securit y

is how t o aut omat ically coordinat e t he access right s of t he warehouse wit h t hose of t he sources.

Proposed extensions

• Three ext ensions t o SQL
• 1. Split t he not ion of “access per mission on a t able” int o t wo

separat e issues:

a.

who is allowed t o access what inf or mat ion (inf or mat ion per missions): ent er pr ise-wide decision

• Employee salary inf ormat ion is releasable t o payroll clerks.

b.

who is allowed t o access which physical t ables (physical per missions): local decision

• P

ayroll clerks are allowed t o run queries on t he warehouse.

• 2. Provide a powerf ul inf erence mechanism

−

I n SQL, a user is allowed t o execut e a quer y Q if t he user has per missions on all t ables ment ioned in Q.

−

I n t he pr oposed model, a user can also execut e Q if t her e is an equivalent quer y Q’, called a wit ness f or Q, f or which t he user has per missions.

Proposed extensions

• Three ext ensions t o SQL
• 3. Broaden t he creat ion of views

–

I n SQL, a view can be cr eat ed only if t her e is a user t hat has Gr ant aut hor it y on all ment ioned t ables.

–

The ext ension allows t he views t o be over sever al mut ually- suspicious sour ces, wher e no one is t r ust ed t o have Gr ant per mission over all of t hem.

Basics

• P

ermission: (subj ect , operat ion, obj ect , mode)

–

Subj ect : individual users, roles, group, process, et c.

–

Obj ect : t ables belonging t o eit her a source or warehouse.

–

Oper at ion: SQL oper at ions (f ocus on Read and Gr ant-read).

–

Mode: eit her inf ormat ion or physical

• A view

–

Every warehouse t able is a view over t he t ables export ed by sources.

–

Def ined by an SQL query Q.

–

The input s t o Q are t he obj ect s ment ioned in Q; Q(T1, … , Tn).

Permissions

• A subj ect s is allowed t o access a t able only if s has bot h

inf ormat ion and physical permissions on t he t able.

–

A permission (s, op, T, “inf ormat ion”) indicat es t hat t he cont ent in T should be accessible t o s f or operat ion op. I t concerns releasabilit y of knowledge, not physical access t o T. They are globally applied and unaf f ect ed by creat ion of redundant copies

• r new int erf ace.

–

A permission (s, op, T, “physical”) aut horizes an execut ion st rat egy t o use a single physical resource; i.e., local policy.

Permission I nf erence

• A permission is explicit if it is grant ed direct ly by an

aut horized grant or.

• P

ermissions can be inf erred by t he syst em as well.

–

A subj ect should have permission t o execut e a query if f t he query can be expressed in t erms of t ables (base or view) f or which user has explicit permissions.

• Two usef ul def init ions f or inf erence

–

A query Q is equivalent t o T if t he out put of Q always cont ains t he same t uples as T.

–

A permission (s, op, T, mode) is implied if t here exist s an equivalent query Q(T1, … , Tn) such t hat each permission (s, op, Ti, mode) has been grant ed explicit ly. Query Q is called t he wit ness f or t he implied permission.

Permission I nf erence

• An implied inf ormat ion permission (s, read, T) means t hat

t he inf ormat ion in T is releasable t o s.

–

I n ef f ect , a subj ect need not care whet her an inf ormat ion permission is explicit or implied, nor whet her T is a mat erialized view or base t able.

• An implied physical permission (s, read, T) assert s t hat

t here exist s at least one way t o comput e T f or which t he physical permissions are available.

–

I f T is a mat erialized t able, t he subj ect does not have physical access t o T. The subj ect need t o use t he t ables f rom t he wit ness query.

Permission I nf erence

• Now, it is required f or t he syst ems t o be able t o a wit ness

query equivalent t o T.

–

Three import ant rewrit ing st rat egies

1.

View subst it ut ion: I f a subj ect s has t he necessar y per missions on t he sour ce t ables ment ioned in a view, t hen s also has per mission on t he view. (SQL r equir es an explicit gr ant t o access a view.)

2.

Semant ic quer y opt imizat ion: I f t he user quer ies a view V, some sour ce dat a t hat under lies V may be ir r elevant t o t he quer y r esult . (Let V be a j oin of t wo t ables. SQL r equir es per missions on bot h t ables. This is not necessar y in some cases.)

3.

Rewr it e in t er ms of ot her views: Subj ect s ar e of t en given access t o inf or mat ion t hough views when t hey do not have per missions on t he base t able.

–

A complet e set of equivalent s is impossible because t he general rewrit e problem is undecidable. Thus, t he benchmark is t o do bet t er t han ot hers, rat her t han pursuing complet eness.

Administering a warehouse

• SQL inf ers view privileges only when a view is def ined.

–

The view def iner receives t he int ersect ion of her privileges on t he input t ables.

–

Then t he def iner must explicit ly specif y all ot her permissions

• n t he view.
• I n t he proposed model, view privileges are inf erred

whenever t he view is accessed by any user.

–

Provides a more f lexible way t o coordinat e t he cont rol of bot h t he source administ rat or (cont rolling inf ormat ion permissions) and t he warehouse administ rat or (cont rolling physical permissions).

Administering a warehouse

• Comput ing local permissions

–

When a query is issued t o a warehouse, t est ing permissions should be perf ormed t here, not ref erring t he sources.

–

The syst em needs t o populat e permission t ables on t he warehouse.

–

The set of users aut horized t o execut e a query (not considering equivalence) is t he int ersect ion of t he user set s of it s input s. Thus, t he user set of a view can be det ermined by t aking t he union of t hese individual user set s f or each equivalent query f ound.

–

Given T, f irst comput es all queries equivalent t o T, and st ruct ure as a DAG where each subexpr ession appear s only

• nce. Then t raverse t he graph (bot t om up), comput ing

permissions f or each t able in t he graph f rom it s predecessors.

Within- view permissions

• Consider a warehouse where mult iple part s of an
• rganizat ion or mult iple organizat ions part icipat e.

–

Wit h t he current SQL, each organizat ion must grant a war ehouse DBA Read and Gr ant-read permissions on t heir export ed dat a. Then a W-DBA def ine and administ er a view

• ver t he combined inf ormat ion.

–

What happens if no one can be universally t rust ed?

–

One approach is t o allow a source t o st ipulat e t hat it s export ed dat a can be used only f or comput ing a less sensit ive view.

–

grant select t o s on T wit hin V

–

s can access T, but only f rom wit hin V.

Within- view permissions

• Examples

1.

Tot als over a lar ge set

−

A war ehouse suppor t ing f inancial st udies of hospit als.

−

Each hospit al chooses t o r elease it s inf or mat ion wit hin st at e-wide t ot als or cit y-wide t ot als.

2.

Peer-to-peer int ersect ion

−

Table Ent r ant (bor der pat r ol): people ent er ing t he count r y

−

Table Want ed (Police): people who ar e want ed

−

Select * f r om Ent r ant , Want ed wher e mat ch(Ent r ant , Want ed)

3.

I nt er sect ion of child and par ent

−

Table Pat ient (P# , Age, … ): Par ent t able

−

Table Sur ger y(P# , Pr ocedur e, Dat e, … ): Child t able

−

Select * f r om Pat ient , Sur ger y wher e Pat ient .P# = Sur ger y.P# and Pat ient .Age > 80

Within- view permissions

• Semant ics

–

A wit hin-view per mission: (subj ect , oper at ion, obj ect , mode, view).

–

A subj ect s is able t o access view V if s has access t o each input t able wit hin V.

–

The wit ness semant ics is ext ended as f ollows: A per mission (s, op, T, mode) is implied if t her e exist s a quer y Q and views {vi} equivalent t o T, such t hat f or each obj ect Ti ment ioned in Q, eit her

• The permission (s, op, Ti, mode) has been explicit ly grant ed, or
• The wit hin-view permission (s, op, Ti, mode, Vi) has been explicit ly grant ed,

where Q is equivalent t o Vi.

–

User s ar e able t o access a view even when nobody is t r ust ed t o r eceive per missions t o all under lying t ables.

Question?