数据库系统原理——数据库的安全性与完整性（一）

一、 实验目的

1、 掌握自主存取控制权限的定义和维护方法；
2、 掌握实体完整性、参照完整性和用户自定义完整性的定义和维护方法；
3、 掌握数据库触发器的设计和使用方法。

二、 实验内容

3.1数据库安全性实验

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

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

USE TPCH
create login David with password='123',default_database=TPCH
create user David for login David with default_schema=dbo

create login Tom with password= 123 ,default_database=TPCH
create user Tom for login Tom with default_ schema=dbo

create login Kathy with password='123',default_database=TPCH
create user Kathy for login Kathy with default_schema=dbo

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

create login Emily with password='123' ,default_ _database=TPCH
create user Emily for login Emily with default_ schema=dbo

create login Jane with password='123',default_ _database=TPCH
create user Jane for login Jane with default_ schema=dbo

create login Mike with password='123',default_ database=TPCH
create user Mike for login Mike with default_ schema=dbo;

2、创建角色并分配权限
（1）为各个部门分别创建一个查询角色，并分配相应的查询权限。

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;

（2）为各个部门分别创建一个职员角色，对本部门信息具有查看、插入权限。

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 SELECTINSERT 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;

（3）为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，对其他部门的信息具有查询权。

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、给用户分配权限
（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）收回客户经理角色的采购信息查看权限。

exec sp_droprolemember 'SaleRole','CustomerManagerRole' ;

（2）回收Mike的客户部门职员权限。

exec sp_ droprolemember 'CustomerEmployeeRole','Mike' ;