数据库原理 第6章 完整性和安全性

1、Chapter6:IntegrityandSecurity,DomainConstraintsReferentialIntegrityAssertions断言TriggersSecurityAuthorizationAuthorizationinSQL,DomainConstraints,Integrityconstraintsguardagainstaccidentaldamagetothedatabase,byensuringthatauthorizedchangestothedatabasedonotresultinalossofdataconsistency.Domainconstra

2、intsarethemostelementaryformofintegrityconstraint.Theytestvaluesinsertedinthedatabase,andtestqueriestoensurethatthecomparisonsmakesense.NewdomainscanbecreatedfromexistingdatatypesE.g.createdomainDollarsnumeric(12,2)createdomainPoundsnumeric(12,2)WecannotassignorcompareavalueoftypeDollarstoavalueofty

3、pePounds.把一个域中的值转换到另一个域中。However,wecanconverttypeasbelow(castr.AasPounds)(Shouldalsomultiplybythedollar-to-poundconversion-rate),DomainConstraints(Cont.),ThecheckclauseinSQL-92permitsdomainstoberestricted:Usecheckclausetoensurethatanhourly-wagedomainallowsonlyvaluesgreaterthanaspecifiedvalue.created

4、omainhourly-wagenumeric(5,2)constraintvalue-test约束名check(value=4.00)Thedomainhasaconstraintthatensuresthatthehourly-wageisgreaterthan4.00Theclauseconstraintvalue-testisoptional;usefultoindicatewhichconstraintanupdateviolated.,CanhavecomplexconditionsindomaincheckcreatedomainAccountTypechar(10)constr

5、aintaccount-type-testcheck(valuein(Checking,Saving)在deposit关系中指定约束：check(branch-namein(selectbranch-namefrombranch)这个条件必须不仅仅是在deposit中插入或修改一个元组的时候检测，而且在关系branch中的元组被删除或修改时也要检测。,dropdomainhourly-wagealterdomainhourly-wagedropconstraintvalue-testalterdomainhourly-wageaddconstraintvalue-testcheck(value

6、=4.00),ReferentialIntegrity,Ensuresthatavaluethatappearsinonerelationforagivensetofattributesalsoappearsforacertainsetofattributesinanotherrelation.Example:If“Perryridge”isabranchnameappearinginoneofthetuplesintheaccountrelation,thenthereexistsatupleinthebranchrelationforbranch“Perryridge”.Danglingt

7、uples悬挂元组FormalDefinitionLetr1(R1)andr2(R2)berelationswithprimarykeysK1andK2respectively.ThesubsetofR2isaforeignkeyreferencingK1inrelationr1,ifforeveryt2inr2theremustbeatuplet1inr1suchthatt1K1=t2.Referentialintegrityconstraint参照完整性约束alsocalledsubsetdependency子集依赖sinceitscanbewrittenas(r2)K1(r1),参照完整

8、性示例,主码,外码，不是Student的主码，但是Department的主码。Student中Sdept中的值都取自Department表中相应Sdept的值。指定外码时，列名不一定要求相同。,Student,Department,ReferentialIntegrityintheE-RModel,ConsiderrelationshipsetRbetweenentitysetsE1andE2.TherelationalschemaforRincludestheprimarykeysK1ofE1andK2ofE2.ThenK1andK2formforeignkeysontherelationa

9、lschemasforE1andE2respectively.Weakentitysetsarealsoasourceofreferentialintegrityconstraints.Fortherelationschemaforaweakentitysetmustincludetheprimarykeyoftheentitysetonwhichitdepends.,CheckingReferentialIntegrityonDatabaseModification,Thefollowingtestsmustbemadeinordertopreservethefollowingreferen

10、tialintegrityconstraint:(r2)K(r1)Insert.Ifatuplet2isinsertedintor2,thesystemmustensurethatthereisatuplet1inr1suchthatt1K=t2.Thatist2K(r1)Delete.Ifatuple,t1isdeletedfromr1,thesystemmustcomputethesetoftuplesinr2thatreferencet1:=t1K(r2)Ifthissetisnotempty,eitherthedeletecommandisrejectedasanerror,orthe

11、tuplesthatreferencet1mustthemselvesbedeleted(cascadingdeletionsarepossible).,参照完整性示例,(r2),r2,r1,K(r1),t2,insert,=t1K(r2),delete,DatabaseModification(Cont.),Update.Therearetwocases:Ifatuplet2isupdatedinrelationr2andtheupdatemodifiesvaluesforforeignkey,thenatestsimilartotheinsertcaseismade.Lett2denote

12、thenewvalueoftuplet2.Thesystemmustensurethatt2K(r1)Ifatuplet1isupdatedinr1,andtheupdatemodifiesvaluesfortheprimarykey(K),thenatestsimilartothedeletecaseismade.Thesystemmustcompute=t1K(r2)usingtheoldvalueoft1(thevaluebeforetheupdateisapplied).Ifthissetisnotempty,theupdatemayberejectedasanerror,ortheu

13、pdatemaybecascadedtothetuplesintheset,orthetuplesinthesetmaybedeleted.,ReferentialIntegrityinSQL,Primary,candidatekeysandforeignkeyscanbespecifiedaspartoftheSQLcreatetablestatement:Theprimarykeyclauseofthecreatetablestatementincludesalistoftheattributesthatcomprisetheprimarykey.Theuniquekeyclauseoft

14、hecreatetablestatementincludesalistoftheattributesthatcompriseacandidatekey.Theforeignkeyclauseofthecreatetablestatementincludesbothalistoftheattributesthatcomprisetheforeignkeyandthenameoftherelationreferencedbytheforeignkey.,ReferentialIntegrityinSQLExample,createtablecustomer(customer-namechar(20

15、),customer-streetchar(30),customer-citychar(30),primarykey(customer-name)createtablebranch(branch-namechar(15),branch-citychar(30),assetsinteger,primarykey(branch-name),ReferentialIntegrityinSQLExample(Cont.),createtableaccount(account-numberchar(10),branch-namechar(15),balanceinteger,primarykey(acc

16、ount-number),foreignkey(branch-name)referencesbranch)createtabledepositor(customer-namechar(20),account-numberchar(10),primarykey(customer-name,account-number),foreignkey(account-number)referencesaccount,foreignkey(customer-name)referencescustomer),CascadingActionsinSQL,createtableaccount(.foreignke

17、y(branch-name)referencesbranchondeletecascade/setnull/setdefaultonupdatecascade/setnull/setdefault.)restrict.无关系依赖它，才可以删除或修改，否则拒绝。On短语不写表示此方式。Cascadesetnull/setdefault删除或修改主键表时，将外键表中所有与主键表中被删除或修改的主键值对应的外键值置为null或default。Cascadingupdatesaresimilar.Ifthereisachainofforeign-keydependenciesacrossmultipl

18、erelations,withondeletecascadespecifiedforeachdependency,adeletionorupdateatoneendofthechaincanpropagate传播acrosstheentirechain.,CascadingActionsinSQL(Cont.),Ifacascadingupdateordeletecausesaconstraintviolationthatcannotbehandledbyafurthercascadingoperation,thesystemabortsthetransaction.Asaresult,all

19、thechangescausedbythetransactionanditscascadingactionsareundone.NullvaluesinforeignkeyattributescomplicateSQLreferentialintegritysemantics,andarebestpreventedusingnotnullifanyattributeofaforeignkeyisnull,thetupleisdefinedtosatisfytheforeignkeyconstraint!Referentialintegrityisonlycheckedattheendofatr

20、ansactionIntermediatestepsareallowedtoviolatereferentialintegrityprovidedlaterstepsremovetheviolation允许中间步骤违反约束，只要最终结果不违反即可。Otherwiseitwouldbeimpossibletocreatesomedatabasestates,e.g.inserttwotupleswhoseforeignkeyspointtoeachother(e.g.spouse配偶attributeofrelationmarriedperson),Assertions,Anassertioni

21、sapredicateexpressingaconditionthatwewishthedatabasealwaystosatisfy.AnassertioninSQLtakestheformcreateassertioncheckdropassertionWhenanassertionismade,thesystemtestsitforvalidity,andtestsitagainoneveryupdatethatmayviolatetheassertionThistestingmayintroduceasignificantamountofoverhead;henceassertions

22、shouldbeusedwithgreatcare.AssertingforallX,P(X)isachievedinaround-aboutfashionusingnotexistsXsuchthatnotP(X),AssertionExample,Thesumofallloanamountsforeachbranchmustbelessthanthesumofallaccountbalancesatthebranch.createassertionsum-constraintcheck(notexists(select*frombranchwhere(selectsum(amount)fr

23、omloanwhereloan.branch-name=branch.branch-name)=(selectsum(amount)fromaccountwhereaccount.branch-name=branch.branch-name),AssertionExample,Everyloanhasatleastoneborrowerwhomaintainsanaccountwithaminimumbalanceor$1000.00设最小余额即$1000createassertionbalance-constraintcheck(notexists(select*fromloanwheren

24、otexists(select*fromborrower,depositor,accountwhereloan.loan-number=borrower.loan-numberandborrower.customer-name=depositor.customer-nameanddepositor.account-number=account.account-numberandaccount.balance=1000),Triggers,Atriggerisastatementthatisexecutedautomaticallybythesystemasasideeffect（副作用）ofa

25、modificationtothedatabase.Todesignatriggermechanism,wemust:Specifytheconditionsunderwhichthetriggeristobeexecuted.Specifytheactionstobetakenwhenthetriggerexecutes.event-condition-actionmodel事件条件动作模型TriggersintroducedtoSQLstandardinSQL:1999,butsupportedevenearlierusingnon-standardsyntaxbymostdatabase

26、s.Supposethatinsteadofallowingnegativeaccountbalances,thebankdealswithoverdraftsbysettingtheaccountbalancetozero不是将余额设成负值creatingaloanintheamountoftheoverdraftgivingthisloanaloannumberidenticaltotheaccountnumberoftheoverdrawnaccount,Steps:,insertanewtuplesintheloanrelationwith,sbranch-name=tbranch-n

27、ame,insertanewtupleuintheborrowerrelationwith,sloan-number=taccount-number,samount=-tbalance,ucustomer-name=“Jones”,uloan-number=taccount-number,settbalanceto0,TriggerExampleinSQL:1999,createtriggeroverdraft-triggerafterupdateonaccountreferencingnewrowasnrowforeachrowwhennrow.balance0beginatomicinse

28、rtintoborrower(selectcustomer-name,account-numberfromdepositorwherenrow.account-number=depositor.account-number);insertintoloanvalues(nrow.account-number,nrow.branch-name,nrow.balance);updateaccountsetbalance=0whereaccount.account-number=nrow.account-numberend,TriggeringEventsandActionsinSQL,Trigger

29、ingeventcanbeinsert,deleteorupdateTriggersonupdatecanberestrictedtospecificattributesE.g.createtriggeroverdraft-triggerafterupdateofbalanceonaccountValuesofattributesbeforeandafteranupdatecanbereferencedreferencingoldrowas:fordeletesandupdatesreferencingnewrowas:forinsertsandupdatesTriggerscanbeacti

30、vatedbeforeanevent,whichcanserveasextraconstraints.避免非法更新的额外约束E.g.convertblankstonull.createtriggersetnull-triggerbeforeupdateonrreferencingnewrowasnrowforeachrowwhennrow.phone-number=setnrow.phone-number=null,StatementLevelTriggers,Insteadofexecutingaseparateactionforeachaffectedrow,asingleactionca

31、nbeexecutedforallrowsaffectedbyasingletransactionUseforeachstatementinsteadofforeachrowUsereferencingoldtableorreferencingnewtabletorefertotemporarytablescontainingtheaffectedrowstemporarytablescantbeusedinbeforetriggers.CanbemoreefficientwhendealingwithSQLstatementsthatupdatealargenumberofrows,Exte

32、rnalWorldActions,Wesometimesrequireexternalworldactions,suchasre-orderinganitemwhosequantityinawarehousehasbecomesmall,orturningonanalarmlight,tobetriggeredonadatabaseupdateTriggerscannotbeusedtodirectlyimplementexternal-worldactions,BUTTriggerscanbeusedtorecordactions-to-be-takeninaseparatetable,an

33、dwecanhaveanexternalprocessthatrepeatedlyscansthetableandcarriesoutexternal-worldactions.E.g.Supposeawarehousehasthefollowingtablesinventory(item,level):Howmuchofeachitemisinthewarehouseminlevel(item,level):Whatistheminimumdesiredlevelofeachitemreorder(item,amount):Whatquantityshouldwere-orderforeac

34、hitematatimeorders(item,amount):Thequantityofordertobeplacedforeachitem(readbyexternalprocess),ExternalWorldActions(Cont.),createtriggerreorder-triggerafterupdateofleveloninventoryreferencingoldrowasorow,newrowasnrowforeachrowwhennrow.level(selectlevelfromminlevelwhereminlevel.item=orow.item)beginin

35、sertintoorders(selectitem,amountfromreorderwherereorder.item=orow.item)end注意，如果只检查更新后的新值是否小于最小值，就可能在商品已经被订购的时候错误地下另一个订购单。,新值比最小值更小,旧值满足最小值要求,TriggersinMS-SQLServerSyntax,createtriggeroverdraft-triggeronaccountforupdateasifnrow.balance0begininsertintoborrower(selectcustomer-name,account-numberfromdep

36、ositor,insertedwhereinserted.account-number=depositor.account-number)insertintoloanvalues(inserted.account-number,inserted.branch-name,inserted.balance)updateaccountsetbalance=0fromaccount,insertedwhereaccount.account-number=inserted.account-numberend,透支,Security,Security-protectionfrommaliciousatte

37、mptstostealormodifydata.DatabasesystemlevelAuthenticationandauthorizationmechanismstoallowspecificusersaccessonlytorequireddataWeconcentrateonauthorizationintherestofthischapterOperatingsystemlevelOperatingsystemsuper-userscandoanythingtheywanttothedatabase!Goodoperatingsystemlevelsecurityisrequired

38、.Networklevel:mustuseencryptiontopreventEavesdropping窃听(unauthorizedreadingofmessages)Masquerading冒充(pretendingtobeanauthorizeduserorsendingmessagessupposedlyfromauthorizedusers),Security(Cont.),PhysicallevelPhysicalaccesstocomputersallowsdestructionofdatabyintruders;traditionallock-and-keysecurityi

39、sneededComputersmustalsobeprotectedfromfloods,fire,etc.MoreinChapter17(Recovery)HumanlevelUsersmustbescreenedtoensurethatanauthorizedusersdonotgiveaccesstointrudersUsersshouldbetrainedonpasswordselectionandsecrecy,Authorization,Formsofauthorizationonpartsofthedatabase:Readauthorization-allowsreading

40、,butnotmodificationofdata.Insertauthorization-allowsinsertionofnewdata,butnotmodificationofexistingdata.Updateauthorization-allowsmodification,butnotdeletionofdata.Deleteauthorization-allowsdeletionofdata,Authorization(Cont.),Formsofauthorizationtomodifythedatabaseschema:Indexauthorization-allowscre

41、ationanddeletionofindices.Resourcesauthorization-allowscreationofnewrelations.Alterationauthorization-allowsadditionordeletionofattributesinarelation.Dropauthorization-allowsdeletionofrelations.,AuthorizationandViews,Userscanbegivenauthorizationonviews,withoutbeinggivenanyauthorizationontherelations

42、usedintheviewdefinitionAbilityofviewstohidedataservesbothtosimplifyusageofthesystemandtoenhancesecuritybyallowingusersaccessonlytodatatheyneedfortheirjobAcombinationorrelational-levelsecurityandview-levelsecuritycanbeusedtolimitausersaccesstopreciselythedatathatuserneeds.,ViewExample,Supposeabankcle

43、rkneedstoknowthenamesofthecustomersofeachbranch,butisnotauthorizedtoseespecificloaninformation.Approach:Denydirectaccesstotheloanrelation,butgrantaccesstotheviewcust-loan,whichconsistsonlyofthenamesofcustomersandthebranchesatwhichtheyhavealoan.Thecust-loanviewisdefinedinSQLasfollows:createviewcust-l

44、oanasselectbranchname,customer-namefromborrower,loanwhereborrower.loan-number=loan.loan-number,ViewExample(Cont.),Theclerkisauthorizedtoseetheresultofthequery:select*fromcust-loanWhenthequeryprocessortranslatestheresultintoaqueryontheactualrelationsinthedatabase,weobtainaqueryonborrowerandloan.Autho

45、rizationmustbecheckedontheclerksquerybeforequeryprocessingbegins.,AuthorizationonViews,CreationofviewdoesnotrequireresourcesauthorizationsincenorealrelationisbeingcreatedThecreatorofaviewgetsonlythoseprivilegesthatprovidenoadditionalauthorizationbeyondthathealreadyhad.E.g.ifcreatorofviewcust-loanhad

46、onlyreadauthorizationonborrowerandloan,hegetsonlyreadauthorizationoncust-loan,GrantingofPrivileges,Thepassageofauthorizationfromoneusertoanothermayberepresentedbyanauthorizationgraph.权限图Thenodesofthisgrapharetheusers.Therootofthegraphisthedatabaseadministrator.Considergraphforupdateauthorizationonlo

47、an.AnedgeUiUjindicatesthatuserUihasgrantedupdateauthorizationonloantoUj.,AuthorizationGraph,Requirement:AlledgesinanauthorizationgraphmustbepartofsomepathoriginatingwiththedatabaseadministratorIfDBArevokesgrantfromU1:GrantmustberevokedfromU4sinceU1nolongerhasauthorizationGrantmustnotberevokedfromU5s

48、inceU5hasanotherauthorizationpathfromDBAthroughU2Mustpreventcyclesofgrantswithnopathfromtheroot:DBAgrantsauthorizationtoU7U7grantsauthorizationtoU8U8grantsauthorizationtoU7DBArevokesauthorizationfromU7MustrevokegrantfromU7toU8andfromU8toU7sincethereisnopathfromDBAtoU7ortoU8anymore.,AttempttoDefeatAu

49、thorizationRevocation,SecuritySpecificationinSQL,Thegrantstatementisusedtoconferauthorizationgrantontois:auser-idpublic,whichallowsallvaliduserstheprivilegegrantedArole(moreonthislater)Grantingaprivilegeonaviewdoesnotimplygrantinganyprivilegesontheunderlyingrelations.Thegrantoroftheprivilegemustalre

50、adyholdtheprivilegeonthespecifieditem(orbethedatabaseadministrator).,PrivilegesinSQL,select:allowsreadaccesstorelation,ortheabilitytoqueryusingtheviewgrantselectonbranchtoU1,U2,U3insert:theabilitytoinserttuplesupdate:theabilitytoupdateusingtheSQLupdatestatementgrantupdate(amount)onloantoU1,U2,U3dele

51、te:theabilitytodeletetuples.references:abilitytodeclareforeignkeyswhencreatingrelations.grantreferences(branch_name)onbranchtoU1usage:InSQL-92;authorizesausertouseaspecifieddomaingrantusageondomainAccountTypetoU1allprivileges:usedasashortformforalltheallowableprivilegeswithgrantoption:allowsauserwho

52、isgrantedaprivilegetopasstheprivilegeontootherusers.grantselectonbranchtoU1withgrantoption,Roles,Rolespermitcommonprivilegesforaclassofuserscanbespecifiedjustoncebycreatingacorresponding“role”Privilegescanbegrantedtoorrevokedfromroles,justlikeuserRolescanbeassignedtousers,andeventootherrolesSQL:1999

53、supportsrolescreateroletellercreaterolemanagergrantselectonbranchtotellergrantupdate(balance)onaccounttotellergrantallprivilegesonaccounttomanagergranttellertomanagergranttellertoalice,bobgrantmanagertomary,RevokingAuthorizationinSQL,Therevokestatementisusedtorevokeauthorization.Revokeonfromrestrict

54、|cascadeInmostDBS,cascadeisdefaultrevokeselectonbranchfromU1,U2,U3cascadeWithrestrict,therevokecommandfailsifcascadingrevokesarerequired.revokeselectonbranchfromU1,U2,U3restrictrevokegrantoptionforselectonbranchfromU1revoketheprivilegetopasstheprivilegeontootherusers,donotrevoketheselectprivilege.,L

55、imitationsofSQLAuthorization,SQLdoesnotsupportauthorizationatatuplelevelE.g.wecannotrestrictstudentstoseeonly(thetuplesstoring)theirowngradesAllend-usersofanapplication(suchasawebapplication)maybemappedtoasingledatabaseuserThetaskofauthorizationinabovecasesfallsontheapplicationprogram,withnosupportf

56、romSQL（由应用程序承担基于元组的授权，但其缺陷明显）Authorizationmustbedoneinapplicationcode,andmaybedispersed分散alloveranapplicationCheckingforabsenceofauthorizationloopholes漏洞becomesverydifficultsinceitrequiresreadinglargeamountsofapplicationcode,Encryption,Datamaybeencryptedwhendatabaseauthorizationprovisionsdonotoffers

57、ufficientprotection.Propertiesofgoodencryptiontechnique:Relativelysimpleforauthorizeduserstoencryptanddecryptdata.Encryptionschemedependsnotonthesecrecyofthealgorithmbutonthesecrecyofaparameterofthealgorithmcalledtheencryptionkey.Extremelydifficultforanintrudertodeterminetheencryptionkey.DataEncrypt

58、ionStandard(DES)substitutescharactersandrearrangestheirorderonthebasisofanencryptionkeywhichisprovidedtoauthorizedusersviaasecuremechanism.Schemeisnomoresecurethanthekeytransmissionmechanismsincethekeyhastobeshared.（密钥的传递降低了安全性）,Encryption(cont.),AdvancedEncryptionStandard(AES)isanewstandardreplacin

59、gDES,andisbasedontheRijndaelalgorithm,butisalsodependentonsharedsecretkeysPublic-keyencryptionisbasedoneachuserhavingtwokeys（私用密钥不传递）:publickeypubliclypublishedkeyusedtoencryptdata,butcannotbeusedtodecryptdataprivatekey-keyknownonlytoindividualuser,andusedtodecryptdata.Neednotbetransmittedtothesitedoingencryption.Encryptionschemeissuchthatitisimpossibleorextremelyhardtodecryptdatagivenonlythepublickey.TheRSApublic-keyencryptionschemeisbasedonthehardnessoffactoring因子averylargenumber(100sofdigits)intoitsprimecomponents.,Authentication,Passwordbasedauthenticationiswidel