SQL语句自主存取控制

《SQL语句自主存取控制》由会员分享，可在线阅读，更多相关《SQL语句自主存取控制（3页珍藏版）》请在装配图网上搜索。

1、SQL语句（六）自主存取控制、关于自主存取控制实现自主存取控制主要是使用GRANT和REVOKE语句进行授权和回收操作，授权就是给数据库的用户赋予对某些数据库对象的操作;回收就是收回其权限。GRANT语句：GRANT权限，权限ON对象名，对象名TO用户，用户WITGRANTOPTION/表示获得某种权限的用户还可以将权限授予其他用户，可以省略REVOKE语句：REVOKE权限，权限ON对象名，对象名FROM用户，用户CASCADE|RESTRICT二、实例1创建用户 为采购、销售、客户管理等三个部门的经理创建用户标识，具有创建用户或者角色的权利。createloginDavidwithpass

2、word=david123,default_database=TPCcreateuserDavidforloginDavidwithdefault_schema=dbograntcreateroletoDavid;createloginTomwithpassword=tom123,default_database=TPCcreateuserTomforloginTomwithdefault_schema=dbograntcreateroletoTom;createloginKathywithpassword=kathy123,default_database=TPCcreateuserKath

3、yforloginKathywithdefault_schema=dbograntcreateroletoKathy; 为采购、销售、客户管理等三个部门的职员创建用户标识和用户口令。createloginJefferywithpassword=jeffery123,default_database=TPCcreateuserJefferyforloginJefferywithdefault_schema=dbo;createloginJanewithpassword=jane123,default_database=TPCcreateuserJaneforloginJanewithdefaul

4、t_schema=dbo;createloginMikewithpassword=mike123,default_database=TPCcreateuserMikeforloginMikewithdefault_schema=dbo;2创建角色并分配权限为各个部门分别创建一个查询角色，并分配查询权限。CREATEROLEPurchaseRole;-创建PurchaseRole角色GRANTSELECTONsupplierTOPurchaseRoleWITHGRANTOPTION;GRANTSELECTONpartTOPurchaseRoleWITHGRANTOPTION;GRANTSELEC

5、TONpartsuppTOPurchaseRoleWITHGRANTOPTION;CREATEROLESaleRole;-创建SaleRole角色GRANTSELECTONordersTOSaleRoleWITHGRANTOPTION;GRANTSELECTONlineitemTOSaleRoleWITHGRANTOPTION;CREATEROLECustomerRole;-创建CustomerRole角色GRANTSELECTONcustomerTOCustomerRoleWITHGRANTOPTION;GRANTSELECTONcustomerTOCustomerRoleWITHGRANT

6、OPTION;GRANTSELECTONnationTOCustomerRoleWITHGRANTOPTION;GRANTSELECTONregionTOCustomerRoleWITHGRANTOPTION; 为各个部门分别创建一个职员角色，对本部门的信息具有查看、插入权限。CREATEROLEPurchaseEmployeeRole;-创建PurchaseEmployeeRole角色GRANTSELECT,INSERTONsupplierTOPurchaseEmployeeRole;GRANTSELECT,INSERTONpartTOPurchaseEmployeeRole;GRANTSE

7、LECT,INSERTONpartsuppTOPurchaseEmployeeRole;CREATEROLESaleEmployeeRole;-创建SaleEmployeeRole角色GRANTSELECT,INSERTONordersTOSaleEmployeeRole;GRANTSELECT,INSERTONlineitemTOSaleEmployeeRole;CREATEROLECustomerEmployeeRole;-创建CustomerEmployeeRole角色GRANTSELECT,INSERTONcustomerTOCustomerEmployeeRole;GRANTSELE

8、CT,INSERTONnationTOCustomerEmployeeRole;GRANTSELECT,INSERTONregionTOCustomerEmployeeRole; 为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，对其他部门信息具有查询权，经理有权给本部门职员分配权限。CREATEROLEPurchaseManagerRole;-创建PurchaseManagerRole角色GRANTALLONsupplierTOPurchaseManagerRole;GRANTALLONpartTOPurchaseManagerRole;GRANTALLONpartsuppT

9、OPurchaseManagerRole;execsp_addrolememberSaleRole,PurchaseManagerRole;execsp_addrolememberCustomerRole,PurchaseManagerRole;CREATEROLESaleManagerRole;-创建SaleManagerRole角色GRANTALLONordersTOSaleManagerRole;GRANTALLONlineitemTOSaleManagerRole;execsp_addrolememberPurchaseRole,SaleManagerRole;execsp_addro

10、lememberCustomerRole,SaleManagerRole;CREATEROLECustomerManagerRole;-创建CustomerManagerRole角色GRANTALLONcustomerTOCustomerManagerRole;GRANTALLONnationTOCustomerManagerRole;GRANTALLONregionTOCustomerManagerRole;execsp_addrolememberPurchaseRole,CustomerManagerRole;execsp_addrolememberSaleRole,CustomerMan

11、agerRole;3给用户分配权限-给各部门经理分配权限execsp_addrolememberPurchaseManagerRole,David;execsp_addrolememberSaleManagerRole,Tom;execsp_addrolememberCustomerManagerRole,Kathy;给各部门职员分配权限execsp_addrolememberPurchaseEmployeeRole,Jeffery;execsp_addrolememberSaleEmployeeRole,Jane;execsp_addrolememberCustomerEmployeeRole,Mike;4. 回收角色或用户权限 回收客户经理角色的销售信息查看权限execsp_droprolememberSaleRole,CustomerManagerRole; 回收MIKE的客户部门职员权限execsp_droprolememberCustomerEmployeeRole,Mike;5. 验证权限分配的正确性 以David用户名登录数据库，验证采购部门经理的权限SELECT*FROMpart;SELECT*FROMorders; 回收MIKE的客户部门职员权限SELECT*FROMcustomer;SELECT*FROMpart;