本文共 3932 字，大约阅读时间需要 13 分钟。

一、关于自主存取控制

       实现自主存取控制主要是使用GRANT和REVOKE语句进行授权和回收操作，授权就是给数据库的用户赋予对某些数据库对象的操作；回收就是收回其权限。

GRANT语句：

GRANT 权限，权限......

ON 对象名，对象名......

TO 用户，用户......

WITGRANT OPTION//表示获得某种权限的用户还可以将权限授予其他用户，可以省略

REVOKE语句：

REVOKE 权限，权限......

ON 对象名，对象名......

FROM 用户，用户......

CASCADE|RESTRICT

二、实例

1.创建用户①为采购、销售、客户管理等三个部门的经理创建用户标识，具有创建用户或者角色的权利。create login David with password='david123',default_database=TPCcreate user David for login David with default_schema=dbogrant create role to David;create login Tom with password='tom123',default_database=TPCcreate user Tom for login Tom with default_schema=dbogrant create role to Tom;create login Kathy with password='kathy123',default_database=TPCcreate user Kathy for login Kathy with default_schema=dbogrant create role to Kathy;②为采购、销售、客户管理等三个部门的职员创建用户标识和用户口令。create login Jeffery with password='jeffery123',default_database=TPCcreate user Jeffery for login Jeffery with default_schema=dbo;create login Jane with password='jane123',default_database=TPCcreate user Jane for login Jane with default_schema=dbo;create login Mike with password='mike123',default_database=TPCcreate user Mike for login Mike with default_schema=dbo;2.创建角色并分配权限①为各个部门分别创建一个查询角色，并分配查询权限。CREATE ROLE PurchaseRole;--创建PurchaseRole角色GRANT SELECT ON supplier TO PurchaseRole WITH GRANT OPTION;GRANT SELECT ON part TO PurchaseRole WITH GRANT OPTION;GRANT SELECT ON partsupp TO PurchaseRole WITH GRANT OPTION;CREATE ROLE SaleRole;--创建SaleRole角色GRANT SELECT ON orders TO SaleRole WITH GRANT OPTION;GRANT SELECT ON lineitem TO SaleRole WITH GRANT OPTION;CREATE ROLE CustomerRole;--创建CustomerRole角色GRANT SELECT ON customer TO CustomerRole WITH GRANT OPTION;GRANT SELECT ON nation TO CustomerRole WITH GRANT OPTION;GRANT SELECT ON region TO CustomerRole WITH GRANT OPTION;②为各个部门分别创建一个职员角色，对本部门的信息具有查看、插入权限。CREATE ROLE PurchaseEmployeeRole;--创建PurchaseEmployeeRole角色GRANT SELECT,INSERT ON supplier TO PurchaseEmployeeRole;GRANT SELECT,INSERT ON part TO PurchaseEmployeeRole;GRANT SELECT,INSERT ON partsupp TO PurchaseEmployeeRole;CREATE ROLE SaleEmployeeRole;--创建SaleEmployeeRole角色GRANT SELECT,INSERT ON orders TO SaleEmployeeRole;GRANT SELECT,INSERT ON lineitem TO SaleEmployeeRole;CREATE ROLE CustomerEmployeeRole;--创建CustomerEmployeeRole角色GRANT SELECT,INSERT ON customer TO CustomerEmployeeRole;GRANT SELECT,INSERT ON nation TO CustomerEmployeeRole;GRANT SELECT,INSERT ON region TO CustomerEmployeeRole;③为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，对其他部门信息具有查询权，经理有权给本部门职员分配权限。CREATE ROLE PurchaseManagerRole;--创建PurchaseManagerRole角色GRANT ALL ON supplier TO PurchaseManagerRole;GRANT ALL ON part TO PurchaseManagerRole;GRANT ALL ON partsupp TO PurchaseManagerRole;exec sp_addrolemember 'SaleRole','PurchaseManagerRole';exec sp_addrolemember 'CustomerRole','PurchaseManagerRole';CREATE ROLE SaleManagerRole;--创建SaleManagerRole角色GRANT ALL ON orders TO SaleManagerRole;GRANT ALL ON lineitem TO SaleManagerRole;exec sp_addrolemember 'PurchaseRole','SaleManagerRole';exec sp_addrolemember 'CustomerRole','SaleManagerRole';CREATE ROLE CustomerManagerRole;--创建CustomerManagerRole角色GRANT ALL ON customer TO CustomerManagerRole;GRANT ALL ON nation TO CustomerManagerRole;GRANT ALL ON region TO CustomerManagerRole;exec sp_addrolemember 'PurchaseRole','CustomerManagerRole';exec sp_addrolemember 'SaleRole','CustomerManagerRole';3.给用户分配权限--①给各部门经理分配权限exec sp_addrolemember 'PurchaseManagerRole','David';exec sp_addrolemember 'SaleManagerRole','Tom';exec sp_addrolemember 'CustomerManagerRole','Kathy';②给各部门职员分配权限exec sp_addrolemember 'PurchaseEmployeeRole','Jeffery' ;exec sp_addrolemember 'SaleEmployeeRole','Jane' ;exec sp_addrolemember 'CustomerEmployeeRole','Mike' ;4.回收角色或用户权限①回收客户经理角色的销售信息查看权限exec sp_droprolemember 'SaleRole','CustomerManagerRole' ;②回收MIKE的客户部门职员权限exec sp_droprolemember 'CustomerEmployeeRole','Mike' ;5.验证权限分配的正确性①以David用户名登录数据库，验证采购部门经理的权限SELECT *FROM part;SELECT *FROM orders;②回收MIKE的客户部门职员权限SELECT *FROM customer;SELECT *FROM part;

转载地址：http://yxyni.baihongyu.com/