116 lines
5.0 KiB
Transact-SQL
116 lines
5.0 KiB
Transact-SQL
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 |