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 on 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 oper 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 operat 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 – over t he inst ance populat ion Base t able: t he creat or is given all possible privileges. 1. Derived obj ect : t he creat or is given f ull right s on t he 2. 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 � of der ived obj ect Z and let θ be an oper at ion on Z. I nf er t he pr ivilege ( τ , θ ) if τ has a privilege f or every 1. operat ion in OPS( θ ). I nf er t he pr ivilege ( τ , grant θ ) if τ has grant -opt ion 2. 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.
Recommend
More recommend
