SQLServer数据库命令练习之安全性管理

SQLServer数据库命令练习之安全性管理

一、导语

练习核心：掌握自主存取控制权限的定义和维护方法

二、题目

设有一个企业，包括采购、销售和客户管理等三个部门，采购部门经理 David,采购员Emily； 销售部门经理Tom，销售员Jane；客户管理部门经理Kathy，职员Mike。该企业一个信息系统覆盖采购、销售和客户管理等三个部门的业务，其数据库模式为TPCH数据模式。针对此应用场景，使用自主存取控制机制设计一个具体的权限分配方案。

1、创建用户

（1）为采购、销售和客户管理等三个部门的经理（David、Tom和Kathy）创建用户标识和用户口令。

create login David with password='123'，default_database=tpch
create login Tom with password='123'，default_database=tpch
create login Kathy with password='123'，default_database=tpch

create user David for login David with default_schema=dbo
create user Tom for login Tom with default_schema=dbo
create user Kathy for login Kathy with default_schema=dbo

（2）为采购、销售和客户管理等三个部门的职员（Emily、Jane和Mike）创建用户标识和用户口令。

create login Emily with password='123',default_database=tpch
create login Jane with password='123',default_database=tpch
create login Mike with password='123',default_database=tpch

create user Emily for login Emily with default_schema=dbo
create user Jane for login Jane with default_schema=dbo
create user Mike for login Mike with default_schema=dbo

2、创建角色并分配权限

（1）为各个部门（采购、销售和客户管理）分别创建一个查询角色（PurchaseRole、SaleRole和CustomerRole），并分配相应的查询权限。

create role PurchaseQueryRole
create role SaleQueryRole
create role CustomerQueryRole

grant select on Part to PurchaseQueryRole
grant select on Supplier to PurchaseQueryRole
grant select on Partsupp to PurchaseQueryRole

grant select on Orders to SaleQueryRole
grant select on Lineitem to SaleQueryRole

grant select on Customer to CustomerQueryRole
grant select on Nation to CustomerQueryRole
grant select on Region to CustomerQueryRole

（2）为各个部门（采购、销售和客户管理）分别创建一个职员角色（PurchaseEmployeeRole、SaleEmployeeRole和CustomerEmployeeRole），对本部门信息具有查看、插入权限。

create role PurchaseEmployeeRole
create role SaleEmployeeRole
create role CustomerEmployeeRole

grant select,insert on part to PurchaseEmployeeRole
grant select,insert on supplier to PurchaseEmployeeRole
grant select,insert on partsupp to PurchaseEmployeeRole

grant select,insert on orders to SaleEmployeeRole
grant select,insert on lineitem to SaleEmployeeRole

grant select,insert on customer to CustomerEmployeeRole
grant select,insert on nation to CustomerEmployeeRole
grant select,insert on region to CustomerEmployeeRole

（3）为各部门（采购、销售和客户管理）创建一个经理角色（PurchaseManagerRole、SaleManagerRole和CustomerManagerRole），相应角色对本部门的信息具有完全控制权限，对其他部门信息具有查询权。

create role PurchaseManagerRole 
create role SaleManagerRole 
create role CustomerManagerRole

grant all privileges on part to PurchaseManagerRole 
grant all privileges on supplier to PurchaseManagerRole 
grant all privileges on partsupp to PurchaseManagerRole 

grant all privileges on orders to SaleManagerRole 
grant all privileges on lineitem to SaleManagerRole 

grant all privileges on customer to CustomerManagerRole
grant all privileges on nation to CustomerManagerRole
grant all privileges on region to CustomerManagerRole


exec sp_addrolemember 'SaleQueryRole','PurchaseManagerRole'
exec sp_addrolemember 'CustomerQueryRole','PurchaseManagerRole '

exec sp_addrolemember 'PurchaseQueryRole','SaleManagerRole'
exec sp_addrolemember 'CustomerQueryRole','SaleManagerRole'

exec sp_addrolemember 'PurchaseQueryRole','CustomerManagerRole'
exec sp_addrolemember 'SaleQueryRole','CustomerManagerRole'

3、使用角色给用户分配权限

（1）给各部门经理分配权限。

exec sp_addrolemember 'PurchaseManagerRole','David'
exec sp_addrolemember 'SaleManagerRole','Tom'
exec sp_addrolemember 'CustomerManagerRole','Kathy'

（2）给各部门职员分配权限。

exec sp_addrolemember 'PurchaseEmployeeRole','Emily'
exec sp_addrolemember 'SaleEmployeeRole','Jane'
exec sp_addrolemember 'CustomerEmployeeRole','Mike'

4、验证权限分配正确性

（1）以David用户名登录数据库，验证采购部门经理的权限。（查看和更新part表、orders表等）

（2）以Mike用户名登录数据库，验证客户管理部门职员的权限。（查看和更新customer表、orders表等）

5、回收角色或用户权限

（1）回收采购经理角色的销售信息查看权限。(验证David是否有查看销售部门的权限)
（2）回收Mike的客户部门职员权限。(验证Mike是否有查看customer表等权限)

Topic: 编辑不易，如若有所收获请点个赞吧，谢谢！