实验四：用户权限管理实验.doc

实验四：用户权限管理实验
一、实验目的
掌握SQL Server中有关用户、角色及操作权限的设置方法。
二、实验内容
1. 用超级用户登录学生数据库student。
2. 建立两个新用户:用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名stu3,密码stu3（登录名login3）。
3. 授予stu1对SC具有select和insert权限,授予stu1对student具有select,update,insert和delete权限, 授予stu1对Course具有select,update,insert和delete权限
4．授予stu2对SC具有select权限,授予stu2对student具有select权限, 授予stu2对Course具有select权限；授予stu2对Course具有更新属性Ccredit的权限。
5．测试授权
◆以用户Stu1登录学生数据库：
（1）查询SC、student和Course表中的所有数据；
（2）分别向SC、student和Course表中插入一组合法数据；
（3）分别对SC、student和Course表更改一个元组；
（4）分别对SC、student和Course表删除一个元组。
观察运行结果并分析原因。
◆以用户Stu2登录学生数据库：
（1）分别向SC、student和Course表中插入一组合法数据；
（2）更新Course表中课程号为’1’的课程的名称为’人工智能’；
（3）更新Course表中课程号为’1’的课程的学分为3。
观察运行结果并分析原因。
◆以用户Stu1登录学生数据库：
（1）收回stu1对SC表的select权限，对student表的update和insert权限，对Course表的delete权限；
（2）查询SC、student和Course表中的数据，观察运行结果；
（3）分别向SC、student和Course表中插入一组合法数据，观察运行结果；
（4）分别对SC、student和Course表更改一个元组，观察运行结果；
（5）分别对SC、student和Course表删除一个元组，观察运行结果。思考Course是否具有delete权限执行效果有何不同？
6．创建数据库角色 Myrole，设置访问student表的select和insert权限，并添加用户成员stu3。以stu3登录查看验证。
7．使用SQL命令完成：
删除登录名login1、login2和login3；删除用户名stu1、stu2和stu3；删除角色Myrole。
三、实验学时
2学时
四、实验设备与环境
Windows 2003平台 + SQL Server 2008系统
--2 建立两个新用户:用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名stu3,密码stu3（登录名login3）

create login login1 with password ='stu1';

create User stu1 for login login1;

create login login2 with password ='stu2';

create User stu2 for login login2;

create login login3 with password ='stu3';

create User stu3 for login login3;

--3. 授予stu1对SC具有select和insert权限,授予stu1对student具有select,update,insert和delete权限, 授予stu1对Course具有select,update,insert和delete权限

grant select,insert
on SC
to stu1;

grant select,update,insert,delete
on student
to stu1;

grant select,update,insert,delete
on course
to stu1;

--4．授予stu2对SC具有select权限,授予stu2对student具有select权限, 授予stu2对Course具有select权限；授予stu2对Course具有更新属性Ccredit的权限

grant select
on SC
to stu2;

grant select
on student
to stu2;

grant select
on course
to stu2;

grant update(Ccredit)
on course
to stu2;

--5．测试授权
--◆以用户Stu1登录学生数据库：
--（1）查询SC、student和Course表中的所有数据；
select *
from SC;

select *
from student;

select *
from course;

--（2）分别向SC、student和Course表中插入一组合法数据；
insert
into SC
values('200215123','1',88);

insert
into student
values('200215124','张三','男',21,'CS');

insert
into course
values('8','大学语文',NULL,3);

--（3）分别对SC、student和Course表更改一个元组；

update SC
set Grade=Grade*0.8;

update student
set Sage=Sage+1;

update course
set Ccredit=Ccredit+1;

--（4）分别对SC、student和Course表删除一个元组。

delete
from SC
where Cno='1';

delete
from student
where (Sdept='CS' and Sage<20);

delete
from Course
where Cname='大学语文';

--◆以用户Stu2登录学生数据库：
--（1）分别向SC、student和Course表中插入一组合法数据；

insert
into SC
values('200215123','1',88);

insert
into student
values('200215124','张三','男',21,'CS');

insert
into course
values('8','大学语文',NULL,3);

--（2）更新Course表中课程号为’1’的课程的名称为’人工智能’；

update course
set Cname='人工智能'
where Cno='1';

--（3）更新Course表中课程号为’1’的课程的学分为3。

update course
set Ccredit=3
where Cno='1';

--◆以用户Stu1登录学生数据库：
--（1）收回stu1对SC表的select权限，对student表的update和insert权限，对Course表的delete权限；

revoke select
on SC
from stu1;

revoke update,insert

on student
from stu1;

revoke delete
on Course
from stu1;

--（2）查询SC、student和Course表中的数据，观察运行结果；

select *
from SC;

select *
from student;

select *
from course;

--（3）分别向SC、student和Course表中插入一组合法数据，观察运行结果；

insert
into SC
values('200215124','5',90);

insert
into student
values('200215126','李思','男',20,'MA');

insert
into course
values('9','大学体育',NULL,2);

--（4）分别对SC、student和Course表更改一个元组，观察运行结果；

update SC
set Grade=Grade*0.8;

update student
set Sage=Sage+1;

update course
set Ccredit=Ccredit+1;

--（5）分别对SC、student和Course表删除一个元组，观察运行结果。

delete
from SC
where Cno='2';

delete
from student
where Sdept='IS';

delete
from Course
where Cname='大学体育';

--6．创建数据库角色 Myrole，设置访问student表的select和insert权限，并添加用户成员stu3。以stu3登录查看验证。

create role Myrole;

grant select,insert
on student
to Myrole;

exec sp_addrolemember Myrole,stu3;

select *
from student;

insert
into student
values('200215125','张三','男',21,'CS');

--7．删除登录名login1、login2和login3；删除用户名stu1、stu2和stu3；删除角色Myrole。

drop login login1;

drop login login2;

drop login login3;

drop user stu1;

drop user stu2;

drop user stu3;

drop role Myrole;