Use master;
Go
-- 创建用户
-- 1. 为采购、销售和客户管理等三个部门的经理创建用户标识，要求具有创建用户和角色的权利。
CREATE ROLE DepartmentManager;
GRANT ALTER ANY USER TO DepartmentManager;
GRANT ALTER ANY ROLE TO DepartmentManager;
CREATE LOGIN David WITH PASSWORD = '123456';
CREATE USER David FOR LOGIN David;
ALTER ROLE DepartmentManager ADD MEMBER David;
CREATE LOGIN Tom WITH PASSWORD = '123456';
CREATE USER Tom FOR LOGIN Tom;
ALTER ROLE DepartmentManager ADD MEMBER Tom;
CREATE LOGIN Kathy WITH PASSWORD = '123456';
CREATE USER Kathy FOR LOGIN Kathy;
ALTER ROLE DepartmentManager ADD MEMBER Kathy;
Go
-- 2. 为采购、销售和客户经理等三个部门的职员创建用户标识和用户口令。
CREATE LOGIN Jeffery WITH PASSWORD = '123456';
CREATE USER Jeffery FOR LOGIN Jeffery;
CREATE LOGIN Jane WITH PASSWORD = '123456';
CREATE USER Jane FOR LOGIN Jane;
CREATE LOGIN Mike WITH PASSWORD = '123456'; 
CREATE USER Mike FOR LOGIN Mike;
Go
-- 创建角色并分配权限
-- 1. 为各个部门分别创建一个查询角色，并分配相应的查询权限。
CREATE ROLE PurchaseQueryRole;
GRANT SELECT ON Sales.Part TO PurchaseQueryRole;
GRANT SELECT ON Sales.Supplier TO PurchaseQueryRole;
GRANT SELECT ON Sales.Partsupp TO PurchaseQueryRole;
CREATE ROLE SalesQueryRole;
GRANT SELECT ON Sales.Orders TO SalesQueryRole;
GRANT SELECT ON Sales.Lineitem TO SalesQueryRole;
CREATE ROLE CustomerQueryRole;
GRANT SELECT ON Sales.Customer TO CustomerQueryRole;
GRANT SELECT ON Sales.Nation TO CustomerQueryRole;
GRANT SELECT ON Sales.Region TO CustomerQueryRole;
Go
-- 2. 为各个部门分别创建一个职员角色，对本部门信息具有查看、插入权限。
CREATE ROLE PurchaseEmployeeRole;
GRANT SELECT, INSERT ON Sales.Part TO PurchaseEmployeeRole;
GRANT SELECT, INSERT ON Sales.Supplier TO PurchaseEmployeeRole;
GRANT SELECT, INSERT ON Sales.Partsupp TO PurchaseEmployeeRole;
CREATE ROLE SalesEmployeeRole;
GRANT SELECT, INSERT ON Sales.Orders TO SalesEmployeeRole;
GRANT SELECT, INSERT ON Sales.Lineitem TO SalesEmployeeRole;
CREATE ROLE CustomerEmployeeRole;
GRANT SELECT, INSERT ON Sales.Customer TO CustomerEmployeeRole;
GRANT SELECT, INSERT ON Sales.Nation TO CustomerEmployeeRole;
GRANT SELECT, INSERT ON Sales.Region TO CustomerEmployeeRole;
Go
-- 3. 为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，对其他部门的信息具有查询权。经理有权给本部门职员分配权限。
CREATE ROLE PurchaseManagerRole;
GRANT CONTROL ON Sales.Part TO PurchaseManagerRole;
GRANT CONTROL ON Sales.Supplier TO PurchaseManagerRole;
GRANT CONTROL ON Sales.Partsupp TO PurchaseManagerRole;
GRANT SELECT ON Sales.Orders TO PurchaseManagerRole;
GRANT SELECT ON Sales.Lineitem TO PurchaseManagerRole;
GRANT SELECT ON Sales.Customer TO PurchaseManagerRole;
GRANT SELECT ON Sales.Nation TO PurchaseManagerRole;
GRANT SELECT ON Sales.Region TO PurchaseManagerRole;
GRANT ALTER ON ROLE::PurchaseEmployeeRole TO PurchaseManagerRole;
CREATE ROLE SalesManagerRole;
GRANT CONTROL ON Sales.Orders TO SalesManagerRole;
GRANT CONTROL ON Sales.Lineitem TO SalesManagerRole;
GRANT SELECT ON Sales.Part TO SalesManagerRole;
GRANT SELECT ON Sales.Supplier TO SalesManagerRole;
GRANT SELECT ON Sales.Partsupp TO SalesManagerRole;
GRANT SELECT ON Sales.Customer TO SalesManagerRole;
GRANT SELECT ON Sales.Nation TO SalesManagerRole;
GRANT SELECT ON Sales.Region TO SalesManagerRole;
GRANT ALTER ON ROLE::SalesEmployeeRole TO SalesManagerRole;
CREATE ROLE CustomerManagerRole;
GRANT CONTROL ON Sales.Customer TO CustomerManagerRole;
GRANT CONTROL ON Sales.Nation TO CustomerManagerRole;
GRANT CONTROL ON Sales.Region TO CustomerManagerRole;
GRANT SELECT ON Sales.Part TO CustomerManagerRole;
GRANT SELECT ON Sales.Supplier TO CustomerManagerRole;
GRANT SELECT ON Sales.Partsupp TO CustomerManagerRole;
GRANT SELECT ON Sales.Orders TO CustomerManagerRole;
GRANT SELECT ON Sales.Lineitem TO CustomerManagerRole;
GRANT ALTER ON ROLE::CustomerEmployeeRole TO CustomerManagerRole;
Go

-- 给用户分配权限
-- 1. 给各部门经理分配权限。
ALTER ROLE PurchaseManagerRole ADD MEMBER David;
ALTER ROLE SalesManagerRole ADD MEMBER Tom;
ALTER ROLE CustomerManagerRole ADD MEMBER Kathy;
Go
-- 2. 给各部门职员分配权限。
ALTER ROLE PurchaseEmployeeRole ADD MEMBER Jeffery;
ALTER ROLE SalesEmployeeRole ADD MEMBER Jane;
ALTER ROLE CustomerEmployeeRole ADD MEMBER Mike;
Go
-- 回收角色或用户权限
-- 1. 收回客户经理角色的销售信息查看权限。
REVOKE SELECT ON Sales.Orders FROM CustomerManagerRole;
REVOKE SELECT ON Sales.Lineitem FROM CustomerManagerRole;
Go
-- 2. 回收MIKE的客户部门职员权限。
ALTER ROLE CustomerEmployeeRole DROP MEMBER Mike;
Go

-- 验证权限分配正确性
-- 1. 以David用户名登录数据库，验证采购部门经理的权限
EXECUTE AS USER = 'David';
SELECT * FROM Sales.Part;
DELETE * FROM Sales.Orders;
Go
-- 2. 回收MIKE的客户部门职员权限
ALTER ROLE CustomerEmployeeRole DROP MEMBER Mike;
SELECT * FROM Sales.Customer;
SELECT * FROM Sales.Part;
Go