数据库实验-实验13 用户权限管理实验

实验报告

一、实验目的

掌握SQL Server中有关用户、角色及操作权限的设置方法。 二、实验内容

1. 用超级用户登录学生数据库student。

2. 建立两个新用户:用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名stu3,密码stu3（登录名login3）。

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

4．授予stu2对SCORE具有select权限,授予stu2对student具有select权限, 授予stu2对Course具有select权限；授予stu2对Course具有更新属性tname的权限。 5．测试授权

◆以用户Stu1登录学生数据库：

（1）查询SCORE、student和Course表中的所有数据； （2）分别向SCORE、student和Course表中插入一组合法数据； （3）分别对SCORE、student和Course表更改一个元组； （4）分别对SCORE、student和Course表删除一个元组。 观察运行结果并分析原因。 ◆以用户Stu2登录学生数据库：

（1）分别向SCORE、student和Course表中插入一组合法数据； （2）更新Course表中课程号为’1’的课程的名称为’人工智能’； （3）更新Course表中课程号为’1’的课程的学分为3。 观察运行结果并分析原因。 ◆以用户Stu1登录学生数据库：

（1）收回stu1对SCORE表的select权限，对student表的update和insert权限，对Course表的delete权限；

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

（3）分别向SCORE、student和Course表中插入一组合法数据，观察运行结果； （4）分别对SCORE、student和Course表更改一个元组，观察运行结果； （5）分别对SCORE、student和Course表删除一个元组，观察运行结果。 思考Course是否具有delete权限执行效果有何不同？

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

录查看验证。

7．使用SQL命令完成：

删除登录名login1、login2和login3；删除用户名stu1、stu2和stu3；删除角色Myrole。 三、实验学时

2学时 四、实验设备与环境

--1. Windows 平台 + SQL Server 2005以上版本

--2. 建立新用户:用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名stu3,密码stu3（登录名login3）

create login login1 with password ='stu1'; --创建sql server 登录

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;

自己创建windows 登录：Lxxx（学号后三位）并添加为数据库用户 SXXX

--CREATE LOGIN [\\] FROM WINDOWS; --windows 登录

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

授予stu1对Course具有select,update,insert和delete权限,并测试

--授权之前，执行一次相关操作，并查看相关结果，数据自定。 如：

SELECT * FROM SCORE --??? (以login1登录sql server,打开一个查询窗口) insert score ….

grant select,insert on SCORE103 to stu1;

--授权之后，执行一次相关操作，并查看相关结果，数据自定。

SELECT *FROM SCORE --??? (以login1登录sql server,打开一个查询窗口) insert score ….

Windows/sa 登录 login1 / login2/ login3登录 SELECT * FROM SCORE --??? insert score … --??? SELECT * FROM SCORE --??? insert score … --??? grant select,insert on SCORE to stu1; …

--其他类似

SELECT *FROM student

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

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

…

--4．授予stu2对SCORE具有select权限,授予stu2对student具有select权限, 授予stu2对Course具有select权限；授予stu2对Course具有更新属性CNAME的权限，并测试

授权之前执行相关操作并查看记过

grant select on Score103 to stu2;

授权之后执行相关操作并查看记过

grant select on student to stu2;

grant select on course to stu2;

grant update(CNAME) on course to stu2;

UPDATE course103 SET Cname = '高等数学A' WHERE Cname = '高等数学' SELECT * FROM course103

--5．再次测试授权（选做）

--◆以用户Stu1登录学生数据库：

--（1）查询SCORE、student和Course表中的所有数据； select *

from SCORE103;

select *

from student103;

select *

from course103;

--（2）分别向SCORE、student和Course表中插入一组合法数据（自定）； Insert into SCORE values(…);

没有权限，然后去给stu1插入权限

然后插入：

Insert into student values(…);

Insert into course values(…);

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

update SCORE set DEGREE= …WHERE …;

update student set SBIRTHDAY…WHERE …

update course set CNAME=… WHERE …;

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

Delete from SCORE WHERE …

Delete from student WHERE …

Delete from Course WHERE …;

--◆以用户Stu2登录学生数据库：

--（1）分别向SCORE、student和Course表中插入一组合法数据(自定)；

insert

into SCORE values(…);

insert

into student values(…);

insert

into course values(…);

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

update course

set Cname='人工智能' where Cno=' 3-105';

--（3）更新Course表中课程号为’3-105’的tno改为804。

update course set tnot=‘804’ where Cno='3-105';

--◆以用户Stu1登录学生数据库：

--（1）收回stu1对SCORE表的select权限，对student表的update和insert权限，对Course表的delete权限；并测试

revoke select on SCORE from stu1;

revoke update,insert on student from stu1;

revoke delete on Course from stu1;

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

select *

from SCORE;

select *

from student;

select *

from course;

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

insert

into SCORE values( …);

insert

into student values( …);

insert

into course values( …);

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

update SCORE

set DEGREE= … WHERE …;

update student

set SBIRTHDAY =….;

update course

set CNAME= …WHERE … ; ;

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

delete

from SCORE WHERE … ;

delete

from student WHERE … delete

from Course WHERE …;

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

create role Myrole;

grant select,insert on student103 to Myrole;

exec sp_addrolemember Myrole,stu3;

select *

from student;

insert

into student values …;

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

8.固定服务器

由于固定服务器是在服务器层次上定义的，因此它们位于从属于数据库服务器的数据库外面。下表列出了所有现有的固定服务器角色。 固定服务器角色 sysadmin serveradmin setupadmin securityadmin processadmin dbcreator diskadmin 说 明 执行SQL Server中的任何动作 配置服务器设置 安装复制和管理扩展过程 管理登录和CREATE DATABASE的权限以及阅读审计 管理SQL Server进程 创建和修改数据库 管理磁盘文件

系统过程用来添加或删除固定服务器角色成员：

sp_addsrvrolemember 如：

sp_addsrvrolemember 'LOGIN1' ,''sysadmin''

sp_dropsrvrolemember 如：

sp_dropsrvrolemember 'LOGIN1' ,'sysadmin'

（1）添加login2为固定服务器角色dbcreator成员，并验证相关权限（创建修改删除数据库（自定））

注意：您不能添加、修改或删除固定服务器角色。另外，只有固定服务器角色的成员才能执行上述两个系统过程来从角色中添加或删除登录账户。

创建一个登录，并添加到某固定服务器角色成员，测试其权限，如dbcreator。

9. 固定数据库角色

固定数据库角色在数据库层上进行定义，因此它们存在于属于数据库服务器的每个数据库中。下表列出了所有的固定数据库角色。 固定数据库角色 db_owner db_accessadmin db_datareader db_datawriter db_ddladmin db_securityadmin db_backoperator db_denydatareader db_denydatawriter 说 明 可以执行数据库中技术所有动作的用户 可以添加、删除用户的用户 可以查看所有数据库中用户表内数据的用户 可以添加、修改或删除所有数据库中用户表内数据的用户 可以在数据库中执行所有DDL操作的用户 可以管理数据库中与安全权限有关所有动作的用户 可以备份数据库的用户(并可以发布DBCC和CHECKPOINT语句，这两个语句一般在备份前都会被执行) 不能看到数据库中任何数据的用户 不能改变数据库中任何数据的用户

除了上表中列出的固定数据库角色之外，还有一种特殊的固定数据库角色，名为public，这里将首先介绍这一角色。

public角色

public角色是一种特殊的固定数据库角色，数据库的每个合法用户都属于该角色。它为数据库中的用户提供了所有默认权限。这样就提供了一种机制，即给予那些没有适当权限的所有用户以一定的(通常是有限的)权限。public角色为数据库中的所有用户都保留了默认的权限，因此是不能被删除的。

系统过程用来添加或删除固定数据库角色成员：

sp_addrolemember

将安全帐户作为当前数据库中现有数据库角色的成员进行添加。 用法

sp_addrolemember [ @rolename = ] 'role' , [ @membername = ] 'security_account'

EXEC sp_addrolemember ' db_ddladmin ', 'dbuser1'

sp_droprolemember

从当前数据库中的 角色中删除安全帐户。 语法

sp_droprolemember [ @rolename = ] 'role' , [ @membername = ] 'security_account'

EXEC sp_droprolemember ' db_ddladmin ', 'dbuser1'

创建一个数据库用户，并添加到某固定数据库角色成员，测试其权限，如db_ddladmin。

（2）添加stu3固定数据库角色db_ddladmin成员，并验证相关权限（创建修改删除表（自定），添加修改删除数据）

五、实验小结：

1、实验一开始login1登录不了，一直在找原因，后来在网上看到文章，具体解决办法如下：

然后重启

2、实验5中插入不了，有一些外键约束，不知道这个怎么搞，下来再研究一下。