kc第4讲-数据库的完整性与安全性

第4讲:(第4章、第8章)数据库的完整性与安全性重庆大学计算机学院,课程名称:数据库系统-,第4讲：数据库的完整性与安全性,项目驱动目标：如何实现一个更加灵活、安全和可靠的数据库：一、特殊数据类型及用途二、数据正确性的控制方法三、数据访问安全的控制方法主要讨论问题：关系数据库支持哪些特殊数据类型什么是数据完整性约束有哪些主要的数据约束如何有效控制对数据库的访问关系数据库提供哪些访问控制什么是授权图？有何作用,Exercise4,特殊数据类型及用途,1-1有哪些内建数据类型？,date:Dates,containinga(4digit)year,monthanddateExample:date2005-7-27time:Timeofday,inhours,minutesandseconds.Example:time09:00:30time09:00:30.75timestamp:(时间戳)dateplustimeofdayExample:timestamp2005-7-2709:00:30.75interval:(时段,一段时间)periodoftimeExample:interval1daySubtracting(减去)adate/time/timestampvaluefromanothergivesanintervalvalueIntervalvaluescanbeaddedtodate/time/timestampvalues,Built-inDataTypes,提取：Canextractvaluesofindividualfieldsfromdate/time/timestampExample:extract(yearfromr.starttime)字符串换为时间：Cancaststringtypestodate/time/timestamp加于Example:castasdateExample:castastime,问题1答案,定义新类型(SQL99)：createtypeconstructinSQLcreatesuser-definedtypecreatetypeDollarsasnumeric(12,2)final-并非有意义可忽略createtypePoundsasnumeric(12,2)final特点：强制类型！把Dollars类型的值赋予Pounds类型的变量时导致编译出错定义新的域类型(SQL92)：createdomainconstructinSQL-92createsuser-defineddomaintypescreatedomainperson_namechar(20)notnull特点：非强制类型！允许把一个域类型的值赋予另一个域类型可以指定完整性约束！Typesanddomainsaresimilar.不同的是Domainscanhaveconstraints,suchasnotnull,specifiedonthem.,1-2什么是用户自定义类型UDT？,User-DefinedTypes,特殊数据类型及用途,1-3类型和域类型有何不同？,Largeobjects(photos,videos,CADfiles,etc.)arestoredasalargeobject:二进制数据blob:binarylargeobject-objectisalargecollectionof不加解释的uninterpretedbinarydata(whoseinterpretationislefttoanapplicationoutsideofthedatabasesystem)字符数据clob:characterlargeobject-objectisalargecollectionofcharacterdataWhenaqueryreturnsalargeobject,apointerisreturnedratherthanthelargeobjectitself.高水平的小技巧!有效减少网络流量！,1-4什么是大对象类型？,Large-ObjectTypes,特殊数据类型及用途,1-5流量如此大，技术上如何实现？,二数据正确性的控制方法-完整性约束,2-1什么是数据完整性约束？,IntegrityConstraints,Integrityconstraintsguardagainstaccidentaldamagetothedatabase,byensuringthatauthorizedchangestothedatabasedonotresultinalossofdataconsistency.防止数据因意外地受到”破坏”导致数据失效！例子Achecking支票accountmusthaveabalance余额greaterthan$10,000.00Asalary薪酬ofabankemployeemustbeatleast$4.00anhourAcustomermusthavea(non-null)phonenumber,问题2答案,SQL支持的完整性约束类型,DomainConstraints域完整性约束ConstraintsonaSingleRelation表上的完整性约束ReferentialIntegrity参照完整性约束Assertions断言（下面依次进行介绍）,2-2SQL支持哪些完整性约束？,二完整性约束,问题3答案,2-3什么是域完整性约束？,DomainConstraints,解释：Domainconstraintsarethemostelementaryformofintegrityconstraint.Theytestvaluesinsertedinthedatabase,andtestqueriestoensurethatthecomparisonsmakesense.这是最基本的数据约束（数据类型与值必须在属性域允许范围内）在创建关系模式时DBMS即开始自动检查！例子：NewdomainscanbecreatedfromexistingdatatypesExample:createdomainDollarsnumeric(12,2)createdomainPoundsnumeric(12,2)WecannotassignorcompareavalueoftypeDollarstoavalueoftypePounds.However,wecanconverttypeasbelow(castr.AasPounds)(Shouldalsomultiplybythedollar-to-poundconversion-rate),2.1域完整性约束,ConstraintsonaSingleRelation,notnull*primarykey（上次课已介绍）UniqueCheck(P),wherePisapredicate,2-4SQL提供了哪些表上的完整性约束？,2.2表上的完整性约束,NotNullConstraint,Declarebranch_nameforbranchisnotnullbranch_namechar(15)notnullDeclarethedomainDollarstobenotnullcreatedomainDollarsnumeric(12,2)notnull,2-5什么是空值NULL完整性约束，如何定义？,2.2表上的完整性约束,TheUniqueConstraint,unique(A1,A2,Am)TheuniquespecificationstatesthattheattributesA1,A2,Amformacandidatekey候选关键字.Candidatekeysarepermittedtobenull(incontrasttoprimarykeys).,2-6什么是唯一性unique完整性约束，如何定义？,2.2表上的完整性约束,2-7这里的候选关键字与主键有何区别？,ThecheckclauseinSQL-92permitsdomainstoberestricted:Usecheckclausetoensurethatanhourly_wagedomainallowsonlyvaluesgreaterthanaspecifiedvalue.createdomainhourly_wagenumeric(5,2)constraintvalue_testcheck(value=4.00),Thecheckclause,check(P),wherePisapredicate通过谓词表达指定对数据的约束,Example:Declarebranch_nameastheprimarykeyforbranchandensurethatthevaluesofassetsarenon-negative.createtablebranch(branch_namechar(15),branch_citychar(30),assetsinteger,primarykey(branch_name),check(assets=0),2-8什么是检查check完整性约束，如何定义？,2.2表上的完整性约束,允许为约束取名,ReferentialIntegrity,说明：Ensuresthatavaluethatappearsinonerelationforagivensetofattributesalsoappearsforacertainsetofattributesinanotherrelation.Example:If“Perryridge”isabranchnameappearinginoneofthetuplesintheaccountrelation,thenthereexistsatupleinthebranchrelationforbranch“Perryridge”.PrimaryandcandidatekeysandforeignkeyscanbespecifiedaspartoftheSQLcreatetablestatement:Theprimarykeyclauselistsattributesthatcomprisetheprimarykey.Theuniquekeyclauselistsattributesthatcompriseacandidatekey.Theforeignkeyclauseliststheattributesthatcomprisetheforeignkeyandthenameoftherelationreferencedbytheforeignkey.Bydefault,aforeignkeyreferencestheprimarykeyattributesofthereferencedtable.例子：参照完整性,2-9什么是参照完整性约束，如何定义？,2.3参照完整性约束,ReferentialIntegrityinSQLExample,createtablecustomer(customer_namechar(20),customer_streetchar(30),customer_citychar(30),primarykey(customer_name)createtablebranch(branch_namechar(15),branch_citychar(30),assetsnumeric(12,2),primarykey(branch_name),createtableaccount(account_numberchar(10),branch_namechar(15),balanceinteger,primarykey(account_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),2.3参照完整性约束,Assertions,Anassertionisapredicate谓词expressingaconditionthatwewishthedatabasealwaystosatisfy.AnassertioninSQLtakestheformcreateassertioncheckWhenanassertionismade,thesystemtestsitforvalidity,andtestsitagainoneveryupdatethatmayviolatetheassertionThistestingmayintroduceasignificantamountofoverhead;henceassertionsshouldbeusedwithgreatcare.AssertingforallX,P(X)isachievedinaround-aboutfashionusingnotexistsXsuchthatnotP(X)例子：断言,2.4断言(完整性约束),2-10什么是断言，如何定义？,AssertionExample,Everyloanhasatleastoneborrowerwhomaintainsanaccountwithaminimumbalancegreaterorequalto$1000.00(与原PPT不同)每笔贷款(可能多个贷款人)的贷款人中至少有一人的账户余额不小于1000.00美元createassertionbalance_constraintcheck(notexists(select*fromloanwherenotexists(select*fromborrower,depositor,accountwhereloan.loan_number=borrower.loan_number原组变量andborrower.customer_name=depositor.customer_nameanddepositor.account_number=account.account_numberandaccount.balance=1000),余额,2.4断言(完整性约束),根据贷款号找到客户名-根据客户名找到客户账号-跟客户账号找到存款余额-查出该贷款的客户中余额-超过1000的所有记录,无这样的贷款记录-它的贷款人的余额都-小于1000美元,三数据访问安全的控制方法(安全性),3-1什么是授权，SQL提供哪些数据处理授权？,Authorization,1）数据授权：Formsofauthorizationonpartsofthedatabase:Read-allowsreading,butnotmodificationofdata.Insert-allowsinsertionofnewdata,butnotmodificationofexistingdata.Update-allowsmodification,butnotdeletionofdata.Delete-allowsdeletionofdata.2）数据结构授权：Formsofauthorizationtomodifythedatabaseschema(coveredinChapter8):Index-allowscreationanddeletionofindices.Resources-allowscreationofnewrelations.Alteration-allowsadditionordeletionofattributesinarelation.Drop-allowsdeletionofrelations.,问题4,5答案,3.1(表)授权类型,AuthorizationandViews,视图授权：Userscanbegivenauthorizationonviews,withoutbeinggivenanyauthorizationontherelationsusedintheviewdefinition隐藏数据：Abilityofviewstohidedataservesbothtosimplifyusageofthesystemandtoenhancesecuritybyallowingusersaccessonlytodatatheyneedfortheirjob结合使用：Acombinationofrelational-levelsecurityandview-levelsecuritycanbeusedtolimitausersaccesstopreciselythedatathatuserneeds.,3.2视图与授权,3-2视图也可以授权？,用View隐藏数据的例子,Supposeabankclerk银行职员needstoknowthenamesofthecustomersofeachbranch,butisnotauthorizedtoseespecificloaninformation.Approach:Denydirectaccesstotheloanrelation,butgrantaccesstotheviewcust-loan：createviewcust-loanasselectbranchname,customer-namefromborrower,loanwhereborrower.loan-number=loan.loan-number,3.2视图与授权,3-3如何利用视图隐藏数据？,Theclerkisauthorizedtoseetheresultofthequery:select*fromcust-loanWhenthequeryprocessortranslatestheresultintoaqueryontheactualrelationsinthedatabase,weobtainaqueryonborrowerandloan.Authorizationmustbecheckedontheclerksquerybeforequeryprocessingreplacesaviewbythedefinitionoftheview.,AuthorizationonViews,CreationofviewdoesnotrequireresourcesauthorizationsincenorealrelationisbeingcreatedThecreator创建者ofaviewgetsonlythoseprivilegesthatprovidenoadditionalauthorizationbeyondthathealreadyhad.E.g.ifcreatorofviewcust-loanhadonlyreadauthorizationonborrowerandloan,hegetsonlyreadauthorizationoncust-loan,3.2视图与授权,3-4如何看待视图上的权限？,GrantingofPrivileges,Thepassage传递ofauthorizationfromoneusertoanothermayberepresentedbyanauthorizationgraph.Thenodesofthisgrapharetheusers.Therootofthegraphisthedatabaseadministrator.例子：Considergraphforupdateauthorizationonloan.AnedgeUiUjindicatesthatuserUihasgrantedupdateauthorizationonloantoUj.,3-5权限可以传递/转授？,3.3授权图,AuthorizationGrantGraph,Requirement:Alledgesinanauthorizationgraphmustbepartofsomepathoriginatingwiththedatabaseadministrator(在上一授权图中)IfDBArevokesgrantfromU1:GrantmustberevokedfromU4sinceU1nolongerhasauthorizationGrantmustnotberevokedfromU5sinceU5hasanotherauthorizationpathfromDBAthroughU2Mustpreventcyclesofgrantswithnopathfromtheroot:DBAgrantsauthorizationtoU7U7grantsauthorizationtoU8U8grantsauthorizationtoU7这是：当DBArevokesauthorizationfromU7必然(DBA也强行收回两权)Mustrevoke回收grantU7toU8andfromU8toU7(因为)sincethereisnopathfromDBAtoU7ortoU8anymore.,3-6什么是授权图，有什么作用？,问题6答案,3.3授权图,AuthorizationSpecificationinSQL,Thegrantstatementisusedtoconferauthorizationgrant特权列表ontois:auser-idpublic,whichallowsallvaliduserstheprivilegegrantedArole(moreonthisinChapter8)Grantingaprivilegeonaviewdoesnotimplygrantinganyprivilegesontheunderlyingrelations.Thegrantor授权人oftheprivilegemustalreadyholdtheprivilegeonthespecifieditem(orbethedatabaseadministrator).,3-7在SQL中如何实现授权？,3.4SQL授权命令,PrivilegesinSQL,select:allowsreadaccesstorelation,ortheabilitytoqueryusingtheviewExample:grantusersU1,U2,andU3selectauthorizationonthebranchrelation:grantselectonbranchtoU1,U2,U3insert:theabilitytoinserttuplesupdate:theabilitytoupdateusingtheSQLupdatestatementdelete:theabilitytodeletetuples.allprivileges:usedasashortformforalltheallowableprivileges（moreinChapter8）,3-8SQL提供哪些特权privileges？,3.4SQL授权命令,PrivilegeToGrantPrivileges,withgrantoption:allowsauserwhoisgrantedaprivilegetopasstheprivilegeontootherusers.Example:grantselectonbranchtoU1withgrantoptiongivesU1theselectprivilegesonbranchandallowsU1tograntthisprivilegetoothers,3-9什么用户能够转授特权privileges？,3.4SQL授权命令,Roles,Rolespermitcommonprivilegesforaclassofuserscanbespecifiedjustoncebycreatingacorresponding“role”如：经理，出纳员Privilegescanbegrantedtoorrevokedfromroles,justlikeuserRolescanbeassignedtousers,andeventootherrolesSQL:1999supportsrolescreateroleteller出纳员createrolemanager经理grantselectonbranchtotellergrantupdate(balance)onaccounttotellergrantallprivilegesonaccounttomanagergranttellertomanagergranttellertoalice,bobgrantmanagertoavi,3-10什么是角色，有何作用？,3.5角色Roles及其重要作用,3-11如何创建和使用角色？,RevokingAuthorization,Therevokestatementisusedtorevokeauthorization.revokeonfromExample:revokeselectonbranchfromU1,U2,U3特别说明：maybealltorevokeallprivilegestherevokeemayhold.Ifincludespublic,alluserslosetheprivilegeexceptthosegranteditexplicitly.Ifthesameprivilegewasgrantedtwicetothesameuserbydifferentgrantees,theusermayretaintheprivilegeaftertherevocation.Allprivilegesthatdependontheprivilegebeingrevokedarealsorevoked.,3-12在SQL中如何收回权限？,3.6权限回收,连带回收问题：Revocationofaprivilegefromausermaycauseotherusersalsotolosethatprivilege;referredtoascascadingoftherevoke.Wecanpreventcascadingbyspecifyingrestrict:revokeselectonbranchfromU1,U2,U3restrictWithrestrict,therevokecommandfailsifcascadingrevokesarerequired.,LimitationsofSQLAuthorization,SQLdoesnotsupportauthorizationat