数据库系统英第三版课件衷宜2011版 authorization(10).ppt

《数据库系统英第三版课件衷宜2011版 authorization(10).ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Chapter10AdvancedTopicsinRelationalDatabase第10章关系数据库高级议题10.1SecurityandUserAuthorizationinSQLAfilesystemidentifiescertainprivilegesontheobjects(files)itmanages.Typicallyread,write,execute.Afilesystemidentifiescertainparticipantstowhomprivilegesmaybegranted.Ty

2、picallytheowner,agroup,allusers.SQLidentifiesamoredetailedsetofprivilegesonobjects(relations)thanthetypicalfilesystem.10.1SecurityandUserAuthorizationinSQLNineprivilegesinall,someofwhichcanberestrictedtoonecolumnofonerelation.SELECT=righttoquerytherelation.IN

3、SERT=righttoinserttuples.Mayapplytoonlyoneattribute.DELETE=righttodeletetuples.UPDATE=righttoupdatetuples.Mayapplytoonlyoneattribute.REFERENCES=righttorefertotherelationinanintegrityconstraint.USAGE=righttousesomeelementinone’sowndeclaration.TRIGGER=righttode

4、finetriggersontherelation.EXECUTE=righttoexecuteapieceofcode.UNDER=righttocreatesubtypesofagiventype.10.1SecurityandUserAuthorizationinSQLForthestatementbelow:INSERTINTOCustomer(custid)SELECTcustidFROMSalesorderWHERENOTEXISTS(SELECT*FROMCustomerWHEREcustid=Sa

5、lesorder.custid);WerequireprivilegesSELECTonCustomerandSalesorder,andINSERTonCustomerorCustomer.custid.CustomersthatdonotappearinCustomer.WeaddthemtoCustomerwithotherattributesNULL.10.1SecurityandUserAuthorizationinSQLTheobjectsonwhichprivilegesexistincludest

6、oredtablesandviews.Otherprivilegesaretherighttocreateobjectsofatype,e.g.,triggers.Viewsformanimportanttoolforaccesscontrol.10.1SecurityandUserAuthorizationinSQLWemightnotwanttogivetheSELECTprivilegeonEmps(name,addr,salary).ButitissafertogiveSELECTon:CREATEVIE

7、WSafeEmpsASSELECTname,addrFROMEmps;QueriesonSafeEmpsdonotrequireSELECTonEmps,justonSafeEmps.10.1SecurityandUserAuthorizationinSQLDBA:DatabaseAdministrator,hasthehighestpriorityforallobjects.DBAcanbeassignedasaspecialprioritytosomeonebyauthorizationDBAcancreat

8、edatabaseobjectsforothers.Owner:Whensomeusercreateadatabaseobject,thentheuseris“owner”ofthisobject,andhastheprioritytoperformalltheoperationsfortheobject.Everydatabaseobjecthasanuniqueown