实验三数据控制-安全性控制.doc

北京理工大学珠海学院实验报告
ZHUHAI CAMPAUS OF BEIJING INSTITUTE OF TECHNOLOGY
班级：网络2班 学号：120205021017 姓名：曾文雄 指导教师蔡培茂 成绩
实验题目：数据控制-安全性控制 实验时间2013.11.22

一、实验目的
1. 熟悉通过SQL语句对数据进行安全性控制；
2. 针对具体应用要求，完成授权和收回权限的操作；
二、实验平台SQL Server 2008
利用SQL Server 2008的SQL Server Management Studio中的查询工具来完成实验内容。
三、实验内容和要求
使用SQL对数据进行安全性控制，包括：授权和权力回收。操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。根据以上要求认真填写实验报告，记录所有的实验用例（包括所有用到的SQL）。

四、具体操作：
创建以下两个关系模式，并分别插入相应的数据。
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号码）
（1）请用SQL的Grant和Revoke语句（加上视图机制）完成以下授权定义或存取控制功能，并进行相应的安全性测试；
（a）用户王明对两个表有select权力；
（b）用户李勇对两个表有insert和delete权力；
（c）每个职工只对自己的记录有select权力；
（d）用户刘星对职工表有select权力，对工资字段具有更新权力；
（e）用户张新具有修改这两个表的结构的权力；
（f）用户周平具有对两个表所有权力（对，插，改，删数据），并具有给其他用户授权的权利；
（g）用户杨兰具有从每个部门职工中select最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。
（2）把上面（a）～（g）的每一种情况，撤销各用户所授予的权力。

五、实验报告
create database work
go
use work
--创建以下两个关系模式，并分别插入相应的数据。
--Worker（职工号，姓名，年龄，职务，工资，部门号）
go
create table Worker(
职工号 varchar(10),
姓名 char(10),
年龄 int,
职务 char(15),
工资 int,
部门号 char(50)
)
--Section（部门号，名称，经理名，地址，电话号码）
create table Section (
职工号 char(10),
名称 char(10),
经理名 char(10),
地址 char(50),
电话 char(15)
);
go

--插入部门信息
insert into Section values('a001','市场部门','李永','白理工','13750000000');
insert into Section values('a002','营销部门','李刚','白理工','13750000000');

--插入职工信息
insert into Worker values('a001001','王明',20,'市场营销员',2000,'a001');
insert into Worker values('a001002','李村',29,'市场营销员',2000,'a001');
insert into Worker values('a001003','李永',20,'经理',5000,'a001');
insert into Worker values('a002001','罗杰',18,'销售员',2000,'a002');
insert into Worker values('a002002','李红',24,'销售员',2000,'a002');
insert into Worker values('a002003','李刚',32,'经理',5000,'a002');

--查询部门表和职工表的数据
select *from Worker;
select *from Section;

go
--授权
use work
go
--（a）用户王明对两个表有select权力；
create login [王明] with password=N'1234';
create user [王明] for login [王明];
grant select on Worker to [王明];
grant select on Section to [王明];
--插入数据权限测试
insert into Worker values('a002004','罗红',28,'销售助理',4000,'a002');
--查询数据权限测试
select *from Worker;

--（b）用户李勇对两个表有insert和delete权力；
use work
go
create login [李勇] with password=N'1234';
create user [李勇] for login [李勇];
grant insert,delete on Worker to [李勇];
grant insert,delete on Section to [李勇];
--插入数据权限测试
insert into Worker values('a002005','罗莉',52,'销售助理',4000,'a002');
--查询数据权限测试
select *from Worker;
--删除权限测试
delete Worker from where 职工号='a002001';

--（c）每个职工只对自己的记录有select权力；
use work
go
create view Myself as select * from Worker
where 姓名=user;
grant select on Myself to public;

--（d）用户刘星对职工表有select权力，对工资字段具有更新权力；
use work
go
create login [刘星] with password=N'1234';
create user [刘星] for login [刘星];
grant select,update(工资) on Worker to [刘星];
--更新修改权限测试
update Worker set 工资=3500 where 职工号='a002001'
--查询数据权限测试
select *from Worker;

--（e）用户张新具有修改这两个表的结构的权力；
use work
go
create login [张新] with password=N'1234';
create user [张新] for login [张新];
grant all PRIVILEGES on Worker to 张新;
grant all PRIVILEGES on Section to 张新;

--（f）用户周平具有对两个表所有权力（对，插，改，删数据），并具有给其他用户授权的权利；
use work
go
create login [周平] with password=N'1234';
create user [周平] for login [周平];
grant all PRIVILEGES on Worker to 周平 with grant option;
grant all PRIVILEGES on Section to 周平 with grant option;

--（g）用户杨兰具有从每个部门职工中select最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。
use work
go
create login [杨兰] with password=N'1234';
create user [杨兰] for login [杨兰];
grant view Wage(max(工资)，min(工资)，avg(工资))
as select max(工资)，min(工资)，avg(工资) from Worker Group by 部门号；
grant select on Wage to 杨兰;

--（2）把上面（a）～（g）的每一种情况，撤销各用户所授予的权力。

use work
go
--注销用户王明，收回权力
revoke select on WOrker from [王明];
revoke select on Section from [王明];
drop user [王明];
drop login [王明];

use work
go
--注销用户李勇，收回权力
revoke select on Worker from [李勇];
revoke select on Section from [李勇];
drop user [李勇];
drop login [李勇]

--撤销用户对自己的记录有select权力
use work
go
revoke select on Myself from public;
drop view Myself cascade;

use work
go
--注销用户刘星，收回权力
revoke select,update(工资) on Worker from [刘星];
drop user [刘星];
drop login [刘星];

use work
go
--注销用户张新，收回权力
revoke alter on table Worker,Section from [张新];

drop user [张新];
drop login [张新];

use work
go
--注销用户周平，收回权力
revoke all PRIVILEGES on table Worker from [周平] cascade;
revoke all PRIVILEGES on Section from [周平] cascade;
drop user [周平];
drop login [周平];

--注销用户杨兰，收回权力
revoke select on Wage from [杨兰];
drop view Wage cascade;
drop user [杨兰];
drop login [杨兰];

use master
go
--删除数据库work
drop database work;

4.3 出现的问题：
在更改连接的时候使用SQL server身份验证登陆，用新建的用户名登陆失败，显示该用户与可信SQL server无关联

4.4 解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：
启用SQL Server身份验证
SQL Server Management Studio -- 对象资源管理器 -- 右键 你的服务器(.\SQLExpress或者localhost) -- 属性 -- 安全性 -- 服务器身份验证 -- SQL Server和Windows身份验证模式，最后重启一下数据库

6