SQL语句（六） 自主存取控制

一、关于自主存取控制

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

GRANT语句：

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

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

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

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

REVOKE语句：

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

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

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

CASCADE|RESTRICT

二、实例

1.创建用户
①为采购、销售、客户管理等三个部门的经理创建用户标识，具有创建用户或者角色的权利。
create login David with password='david123',default_database=TPC
create user David for login David with default_schema=dbo
grant create role to David;

create login Tom with password='tom123',default_database=TPC
create user Tom for login Tom with default_schema=dbo
grant create role to Tom;

create login Kathy with password='kathy123',default_database=TPC
create user Kathy for login Kathy with default_schema=dbo
grant create role to Kathy;
②为采购、销售、客户管理等三个部门的职员创建用户标识和用户口令。
create login Jeffery with password='jeffery123',default_database=TPC
create user Jeffery for login Jeffery with default_schema=dbo;

create login Jane with password='jane123',default_database=TPC
create user Jane for login Jane with default_schema=dbo;

create login Mike with password='mike123',default_database=TPC
create 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;