数据库原理及应用实验报告-实验5-数据控制

5.1 实验目的

熟悉SQL的数据控制功能，能够使用SQL语句来向用户授予和收回权限。

5.2 实验内容

1. 1. 1. 1. 使用GRANT语句来对用户授权，对单个用户或多个用户授权，或使用保留字PUBLIC对所有用户授权。对不同的操作对象包括数据库、视图、基本表等进行不同权限的授权。
         2. 使用WITH GRANT OPTION字句授予用户传播该权限的权利。
         3. 当在授权时发生循环授权，考察DBS能否发现这个错误。如果不能，结合取消权限操作，查看DBS对循环授权的控制。
         4. 使用REVOKE子句收回授权，取消授权的级联反应。

5.3 实验步骤

用SSMS在数据库Xuni_University_Mis中建立三个用户Xuni_USER1、Xuni_USER2和Xuni_USER3，他们在数据库中的角色是PUBLIC。请按以下要求，分别以管理员身份或这三个用户的身份登陆到数据库中，进行操作，并记录操作结果。

1. 1. 1. 1. 1.  授予所有用户对表xun_Courses的查询权限。以Xuni_USER1的身份登陆到SSMS，用SQL语言查询xun_Courses和xun_Students表，查询结果如何？
            2. 授予用户Xuni_USER1对表xun_Students插入和更新的权限，但不授予删除权限，并且授予用户USER1传播这两个权限的权利。以Xuni_USER1的身份登陆到SSMS，用SQL语言插入和更新xun_Students表，结果如何？（注意更新操作的授权）
            3. 允许用户Xuni_USER2在表xun_Reports中插入元组，更新xun_Score列，可以查询除了xn_Sno以外的所有列。以Xuni_USER2的身份登陆到SSMS，用SQL语言插入更新并查询xun_reports表，结果如何？（注意更新操作的授权）
            4. 用户Xuni_USER1授予用户Xuni_USER2对表xun_Students插入和更新的权限，并且授予用户Xuni_USER2传播插入和更新操作的权利。分别以Xuni_USER1和Xuni_user2的的身份登陆到SSMS，用SQL语言验证以上授权操作，结果如何？
            5. 收回对用户USER1对表xun_Courses查询权限的授权。分别以Xuni_USER1和Xuni_USER2的身份登陆到SSMS，用SQL语言查询xun_Courses表，查询结果如何？
            6. 由上面（2）和（4）的授权，再由用户Xuni_USER2对用户Xuni_USER3授予表xun_Students插入和更新的权限，并且授予用户Xuni_USER3传播插入和更新操作的权力。这时候，如果由Xuni_USER3对Xuni_USER1授予表xun_Students的插入和更新权限是否能得到成功？如果能够成功，那么如果有用户Xuni_USER2取消Xuni_USER3的权限，对Xuni_USER1会有什么影响？如果再由DBA取消Xuni_USER1的权限，对Xuni_USER2有什么影响？
   2. 实验要求

(1) 实验之前请细细阅读实验总体要求与说明指导书

(2 ) 在SQL Server 2005或2008的SSMS环境中，完成以上实验(1)-(6)步所有SQL数据定义操作，并将其中粗体步骤的操作窗口剪贴到实验报告中。

5.4 实验结果

首先建立三个用户：

（1）授予所有用户对表xun_Courses的查询权限。以Xuni_USER1的身份登陆到SSMS，用SQL语言查询xun_Courses和xun_Students表，查询结果如何？

1.1

授予权限代码：

grant select
on xun_Courses
to Xuni_USER1,Xuni_USER2,Xuni_USER3

1.2

Xuni_USER1的身份登陆，登录成功，截图如下：

 1.3

查询语句，xun_Courses表：

select * from xun_Courses

查询成功，截图如下：

 1.4

查询语句，xun_Reports表：

select * from xun_Reports

报错，报错消息：

消息 229，级别 14，状态 5，第 1 行

拒绝了对对象 'xun_Reports' (数据库 'Xuni_University_Mis'，架构 'dbo')的 SELECT 权限。

截图如下：

 （3）允许用户Xuni_USER2在表xun_Reports中插入元组，更新xn_Scroce列，可以查询除了xn_Sno以外的所有列。以Xuni_USER2的身份登陆到SSMS，用SQL语言插入更新并查询xun_Reports表，结果如何？（注意更新操作的授权）

3.1

允许，更新代码：

grant insert,update(xn_Scroce),select(xn_Tno,xn_Cno,xn_Scroce)
on xun_Reports
to Xuni_USER2

截图如下，命令成功：

 

3.2

插入xun_Reports表：

insert into xun_Reports
values('S01','T04','C02','20')

命令成功，截图如下：

 

3.3

更新表其他除了xn_Scroce的列，例如xn_Tno：

update xun_Reports
set xn_Tno='T01' 

报错信息：

消息 230，级别 14，状态 1，第 1 行

拒绝了对对象“xun_Reports”(数据库“Xuni_University_Mis”，架构“dbo”)的列“xn_Tno”的 UPDATE 权限。

截图如下：

 

3.4

若更新xn_Scroce列

update xun_Reports
set xn_Scroce=20

成功

 

3.5

查询全部数据：

select *from  xun_Reports

报错

消息 230，级别 14，状态 1，第 1 行

拒绝了对对象“xun_Reports”(数据库“Xuni_University_Mis”，架构“dbo”)的列“xn_Sno”的 SELECT 权限。

 

3.6

查询其他列：

select xn_Tno,xn_Cno,xn_Scroce
from xun_Reports

成功

 （4）用户Xuni_USER1授予用户Xuni_USER2对表xun_Students插入和更新的权限，并且授予用户Xuni_USER2传播插入和更新操作的权利。分别以Xuni_USER1和Xuni_USER2的的身份登陆到SSMS，用SQL语言验证以上授权操作，结果如何？

4.1

Xuni_USER1授予权限：

grant insert,update
on xun_Students
to Xuni_USER2
with grant option

成功执行

 

4.2

Xuni_user2身份登录

①插入权限：

insert into xun_Students
values('S23','徐','XN@','34','女')

命令成功

②更新权限：

update xun_Students
set xn_Sname='徐徐'
where xn_Sno='S23'

报错

消息 229，级别 14，状态 5，第 2 行

拒绝了对对象 'xun_Students' (数据库 'Xuni_University_Mis'，架构 'dbo')的 SELECT 权限。

 

4.3

Xuni_USER1身份登录

可以

（6）由上面（2）和（4）的授权，再由用户Xuni_USER2对用户Xuni_USER3授予表xun_Students插入和更新的权限，并且授予用户Xuni_USER3传播插入和更新操作的权力。这时候，如果由Xuni_USER3对Xuni_USER1授予表xun_Students的插入和更新权限是否能得到成功？如果能够成功，那么如果有用户Xuni_USER2取消Xuni_USER3的权限，对Xuni_USER1会有什么影响？如果再由DBA取消Xuni_USER1的权限，对Xuni_USER2有什么影响？

6.1

用户Xuni_USER2对用户Xuni_USER3授予表xun_Students插入和更新的权限代码：

grant insert,update
on xun_Students
to Xuni_USER3
with grant option

 

6.2.

由Xuni_USER3对Xuni_USER1授予表xun_Students的插入和更新权限

代码：

grant insert,update
on xun_Students
to Xuni_USER1

命令成功

 

6.3.

用户Xuni_USER2取消Xuni_USER3的权限

代码：

revoke insert,update
on xun_Students
from Xuni_USER3 CASCADE

命令成功

 

影响：

取消对USER3的权限 通过这个指令,

USER3赋予USER1的相应权限也被收回，但最初管理员赋予USER1的权限还在。如下，查询权限和更新权限都在。

6.4

以Xuni_USER1身份登录，查询

user1
insert into xun_Students
values('S24','徐','XN@','34','男')

插入成功

 

更新：

update xun_Students
set xn_Ssex='男'

成功更新

 

6.5

如果再由DBA取消Xuni_USER1的权限，对Xuni_USER2有什么影响？

Xuni_USERS也没有了对xun_Students的更新和插入功能。

插入：

insert into xun_Students
values('S24','徐','XN@','34','男')

失败，报错。

消息 229，级别 14，状态 5，第 1 行

拒绝了对对象 'xun_Students' (数据库 'Xuni_University_Mis'，架构 'dbo')的 INSERT 权限。

 

更新：

update xun_Students
set xn_Ssex='男'

报错

消息 229，级别 14，状态 5，第 1 行

拒绝了对对象 'xun_Students' (数据库 'Xuni_University_Mis'，架构 'dbo')的 UPDATE 权限。

 

 

5.5实验体会

1）实验反思

1.实验先要理清几个用户之间的关系，不然很容易打错代码

2.grant语句可以一次向一个用户授权，也可以一次向多个用户授权。

2）实验收获

1.当在授权时发生循环授权，考察DBS不能发现这个错误。

2.使用REVOKE子句收回授权，会发生级联反应，但如果有从其他用户得到此权限，那这权限还是有。

附录：

1.1
授予查询权限
grant select
on xun_Courses
to Xuni_USER1,Xuni_USER2,Xuni_USER3
 
1.2
查询
select * from xun_Courses
 
1.3
查询
select * from xun_Reports
 
报错
 
消息 229，级别 14，状态 5，第 1 行
拒绝了对对象 'xun_Reports' (数据库 'Xuni_University_Mis'，架构 'dbo')的 SELECT 权限。
 
2.1
 
授予权限：
 
grant insert,update
on xun_Students
to Xuni_USER1
with grant option
 
 
命令已成功完成。
 
2.2
user1插入：
insert into xun_Students
values('S22','徐妮','XN@.cn','25','女')
 
显示：
(1 行受影响)
 
2.3
更新：
update xun_Students
set xn_Ssex='男'
 
成功
 
 
3.1
grant insert,update(xn_Scroce),select(xn_Tno,xn_Cno,xn_Scroce)
on xun_Reports
to Xuni_USER2
 
命令成功
 
3.2
insert into xun_Reports
values('S01','T04','C02','20')
 
一行受影响
 
3.3
update xun_Reports
set xn_Tno='T01'
 
消息 230，级别 14，状态 1，第 1 行
拒绝了对对象“xun_Reports”(数据库“Xuni_University_Mis”，架构“dbo”)的列“xn_Tno”的 UPDATE 权限。
 
3.4
update xun_Reports
set xn_Scroce=20
 
成功
3.5
select *from  xun_Reports
 
消息 230，级别 14，状态 1，第 1 行
拒绝了对对象“xun_Reports”(数据库“Xuni_University_Mis”，架构“dbo”)的列“xn_Sno”的 SELECT 权限。
 
3.6
select xn_Tno,xn_Cno,xn_Scroce
from xun_Reports
 
成功
4.1
user1
grant insert,update
on xun_Students
to Xuni_USER2
with grant option
 
成功执行
 
4.2
user2身份
insert into xun_Students
values('S23','徐','XN@','34','女')
 
命令成功
 
4.3
user2身份
update xun_Students
set xn_Sname='徐徐'
where xn_Sno='S23'
 
报错
消息 229，级别 14，状态 5，第 2 行
拒绝了对对象 'xun_Students' (数据库 'Xuni_University_Mis'，架构 'dbo')的 SELECT 权限。
 
5.1
管理员
revoke select
on xun_Courses
from Xuni_USER1
 
命令成功
 
user1身份.
select * from xun_Courses
 
报错
消息 229，级别 14，状态 5，第 1 行
拒绝了对对象 'xun_Courses' (数据库 'Xuni_University_Mis'，架构 'dbo')的 SELECT 权限。
 
user2身份.
select * from xun_Courses
 
命令成功
 
6.1
user2
grant insert,update
on xun_Students
to Xuni_USER3
with grant option
 
命令成功
6.2
user3
 
grant insert,update
on xun_Students
to Xuni_USER1
命令成功
 
6.3
USER2
 
revoke insert,update
on xun_Students
from Xuni_USER3 CASCADE
 
成功
 
取消对USER3的权限 通过这个指令,
USER3赋予USER1的相应权限也被收回，但最初管理员赋予USER1的权限还在
 
 
6.4
user1
insert into xun_Students
values('S24','徐','XN@','34','男')
 
插入成功
 
6.5
更新
update xun_Students
set xn_Ssex='男'
 
6.6
insert into xun_Students
values('S24','徐','XN@','34','男')
 
报错
消息 229，级别 14，状态 5，第 1 行
拒绝了对对象 'xun_Students' (数据库 'Xuni_University_Mis'，架构 'dbo')的 INSERT 权限。
 
6.7
update xun_Students
set xn_Ssex='男'
 
报错
 
消息 229，级别 14，状态 5，第 1 行
拒绝了对对象 'xun_Students' (数据库 'Xuni_University_Mis'，架构 'dbo')的 UPDATE 权限。