数据库系统authorization.ppt

Chapter10AdvancedTopicsinRelationalDatabase 第10章关系数据库高级议题 10 1SecurityandUserAuthorizationinSQL Afilesystemidentifiescertainprivilegesontheobjects files itmanages Typicallyread write execute Afilesystemidentifiescertainparticipantstowhomprivilegesmaybegranted Typicallytheowner agroup allusers SQLidentifiesamoredetailedsetofprivilegesonobjects relations thanthetypicalfilesystem 10 1SecurityandUserAuthorizationinSQL Nineprivilegesinall someofwhichcanberestrictedtoonecolumnofonerelation SELECT righttoquerytherelation INSERT righttoinserttuples Mayapplytoonlyoneattribute DELETE righttodeletetuples UPDATE righttoupdatetuples Mayapplytoonlyoneattribute REFERENCES righttorefertotherelationinanintegrityconstraint USAGE righttousesomeelementinone sowndeclaration TRIGGER righttodefinetriggersontherelation EXECUTE righttoexecuteapieceofcode UNDER righttocreatesubtypesofagiventype 10 1SecurityandUserAuthorizationinSQL Forthestatementbelow INSERTINTOCustomer custid SELECTcustidFROMSalesorderWHERENOTEXISTS SELECT FROMCustomerWHEREcustid Salesorder custid WerequireprivilegesSELECTonCustomerandSalesorder andINSERTonCustomerorCustomer custid 10 1SecurityandUserAuthorizationinSQL Theobjectsonwhichprivilegesexistincludestoredtablesandviews Otherprivilegesaretherighttocreateobjectsofatype e g triggers Viewsformanimportanttoolforaccesscontrol 10 1SecurityandUserAuthorizationinSQL WemightnotwanttogivetheSELECTprivilegeonEmps name addr salary ButitissafertogiveSELECTon CREATEVIEWSafeEmpsASSELECTname addrFROMEmps QueriesonSafeEmpsdonotrequireSELECTonEmps justonSafeEmps 10 1SecurityandUserAuthorizationinSQL DBA DatabaseAdministrator hasthehighestpriorityforallobjects DBAcanbeassignedasaspecialprioritytosomeonebyauthorizationDBAcancreatedatabaseobjectsforothers Owner Whensomeusercreateadatabaseobject thentheuseris owner ofthisobject andhastheprioritytoperformalltheoperationsfortheobject Everydatabaseobjecthasanuniqueowner Fullnameofatableis owner table suchasdba customer Ownercanbeomittedonlyifcurrentuseristheownerofthetable 10 1SecurityandUserAuthorizationinSQL Youhaveallpossibleprivilegesontheobjects suchasrelations thatyoucreate Youmaygrantprivilegestootherusers authorizationID s includingDBA YoumayalsograntprivilegesWITHGRANTOPTION whichletsthegranteealsograntthisprivilege 10 1SecurityandUserAuthorizationinSQL Howtograntauthoritytoauseroragroup Grantinsert delete update selectOndba customertocommonuser GRANTONTO WITHGRANTOPTION Ifyouwanttherecipient s tobeabletopasstheprivilege s toothers GRANT ALL PRIVILEGES ALTER DELETE INSERT REFERENCES column name SELECT column name UPDATE column name ON owner table name TO PUBLIC GROUPgroup username WITHGRANTOPTION 10 1SecurityandUserAuthorizationinSQL Yourgrantoftheseprivilegescannolongerbeusedbytheseuserstojustifytheiruseoftheprivilege Buttheymaystillhavetheprivilegebecausetheyobtaineditindependentlyfromelsewhere REVOKE CONNECT DBA INTEGRATEDLOGIN GROUP MEMBERSHIPINGROUPuserid RESOURCE FROMuserid CASCADE RESTRICT CASCADE Now anygrantsmadebyarevokearealsonotinforce nomatterhowfartheprivilegewaspassed RESTRICT Iftheprivilegehasbeenpassedtoothers theREVOKEfailsasawarningthatsomethingelsemustbedoneto chasetheprivilegedown MySQL grant语法详解 MySQL5 X grant普通数据用户 查询 插入 更新 删除数据库中所有表数据的权利 grantselectontestdb tocommon user grantinsertontestdb tocommon user grantupdateontestdb tocommon user grantdeleteontestdb tocommon user grantselect insert update deleteontestdb tocommon user MySQL grant语法详解 MySQL5 X grant数据库开发人员 创建表 索引 视图 存储过程 函数等权限 grant创建 修改 删除MySQL数据表结构权限 grantcreateontestdb todeveloper 192 168 0 grantalterontestdb todeveloper 192 168 0 grantdropontestdb todeveloper 192 168 0 grant操作MySQL外键权限 grantreferencesontestdb todeveloper 192 168 0 grant操作MySQL临时表权限 grantcreatetemporarytablesontestdb todeveloper 192 168 0 grant操作MySQL索引权限 grantindexontestdb todeveloper 192 168 0 grant操作MySQL视图 查看视图源代码权限 grantcreateviewontestdb todeveloper 192 168 0 grantshowviewontestdb todeveloper 192 168 0 grant操作MySQL存储过程 函数权限 grantcreateroutineontestdb todeveloper 192 168 0 now canshowprocedurestatusgrantalterroutineontestdb todeveloper 192 168 0 now youcandropaproceduregrantexecuteontestdb todeveloper 192 168 0 MySQL grant语法详解 MySQL5 X grant普通DBA管理某个MySQL数据库的权限 grantallprivilegesontestdbtodba localhost 其中 关键字 privileges 可以省略 grant高级DBA管理MySQL中所有数据库的权限 grantallon todba localhost MySQLgrant权限 分别可以作用在多个层次上 grant作用在整个MySQL服务器上 grantselecton todba localhost dba可以查询MySQL中所有数据库中的表 grantallon todba localhost dba可以管理MySQL中的所有数据库grant作用在单个数据库上 grantselectontestdb todba localhost dba可以查询testdb中的表 grant作用在单个数据表上 grantselect insert update deleteontestdb orderstodba localhost grant作用在表中的列上 grantselect id se rank ontestdb apache logtodba localhost grant作用在存储过程 函数上 grantexecuteonproceduretestdb pr addto dba localhost grantexecuteonfunctiontestdb fn addto dba localhost MySQL grant语法详解 MySQL5 X 查看MySQL用户权限查看当前用户 自己 权限 showgrants 查看其他MySQL用户权限 showgrantsfordba localhost 撤销已经赋予给MySQL用户权限的权限 revoke跟grant的语法差不多 只需要把关键字 to 换成 from 即可grantallon todba localhost revokeallon fromdba localhost MySQLgrant revoke用户权限注意事项grant revoke用户权限后 该用户只有重新连接MySQL数据库 权限才能生效 如果想让授权的用户 也可以将这些权限grant给其他用户 需要选项 grantoption grantselectontestdb todba localhostwithgrantoption 10 1SecurityandUserAuthorizationinSQL GrantDiagrams Nodes user privilege grantoption isowner UPDATEONR UPDATE a onR andUPDATE b ONRliveindifferentnodes SELECTONRandSELECTONRWITHGRANTOPTIONliveindifferentnodes EdgeX YmeansthatnodeXwasusedtograntY 10 1SecurityandUserAuthorizationinSQL NotationforNodes UseAPforthenoderepresentingauthorizationIDAhavingprivilegeP P privilegePwithgrantoption P thesourceoftheprivilegeP I e AistheowneroftheobjectonwhichPisaprivilege Note impliesgrantoption 10 1SecurityandUserAuthorizationinSQL ManipulatingEdges WhenAgrantsPtoB WedrawanedgefromAP orAP toBP OrtoBP ifthegrantiswithgrantoption IfAgrantsasubprivilegeQofP sayUPDATE a onRwhenPisUPDATEONR thentheedgegoestoBQorBQ instead Fundamentalrule UserChasprivilegeQaslong