一、 实验目的

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