数据库安全性管理

一：实验目的

1.掌握账户的建立。

2.掌握权限的分配与回收。

3.掌握数据的备份与恢复。

二：实验内容

1、使用企业管理器完成

（1）用企业管理器(对象资源管理器）建立登录账户：user1，user2，user3。

（2）将user1，user2，user3映射为“syy”数据库的用户。

（3）授予user1，user2，user3具有对S、C、SC三张表的查询权。

（4）授予user1具有对S、C表的插入、删除权。

（5）在“学生－课程”数据库中建立用户角色ROLE1，把S表的插入、删除、查询权授予它，并将user1，user2添加到此角色中。

（6）回收user1对S表的查询权。

（7）备份数据库。

（8）还原数据库。

2、操作步骤：

（1）打开企业管理器（对象资源管理器），点击“安全性”，然后右击“登录”图标，选择“新建登录”。建立三个登录帐户。

（2）在syy数据库中选择安全，右键点击“用户”，在弹出的快捷菜单中选择“新建数据库用户”。把三个登录账户映射为syy数据库的三个用户。

（3）在syy数据库中选中一个用户，右击它，在弹出的快捷菜单中选中“属性”，在对话框中点击“权限”，给这个用户分配权限。另外两个用户也依次进行。在学生－课程数据库中选中“角色”，右击它，在弹出的快捷菜单中选中“新建角色”，建立角色ROLE1，同时将三个用户添加到此角色中。

（4）为了给角色分配权限，可以在右击已经建立好了的角色，选择快捷菜单中的“属性”。点击“权限”，在对话框中给ROLE1分配权限。

（5）使用企业管理器回收user1对S表的查询权。

（6）备份数据库。右击要备份的数据库，从“所有任务”里面选择“备份数据库”，实现对数据库的备份。 

（7）还原数据库。打开企业管理器，选中“数据库”图标，右击它，从快捷菜单中选择“所有任务”中的“还原数据库”，根据备份数据实现数据库的还原。 

（8）导入导出数据。将一个表中的数据导出到一个EXCEL文件中。 可在企业理器中右击要导出的表，在弹出的快捷菜单中选择“所有任务”→“导出数据”，按向导可将表中的数据导出到目的位置。

3、使用查询分析器命令完成

（1）建立一个U1的登录用户、数据库用户

（2）使用相同的方法创建u2,u3,u4,u5

（3）把查询学生表权限授给用户U1

（4）把对学生表和课程表的全部权限授予用户U2和U3

（5）把对表成绩的查询权限授予所有用户

（6）把查询学生表和修改学生学号的权限授给用户U4

（7）把用户U4修改学生学号的权限收回

（8）收回所有用户对表成绩的查询权限

（9）把用户U5对成绩表的INSERT权限收回

（10）分别以u1,u2，u3，u4登录，并测试其权限是否生效

（11）在 MIS 库下创建角色 r_test

（12）授予 r_test对 成绩 表的SELECT访问权限

（13）授予角色 r_test 对 学生 表的 SELECT 权限

（14）添加登录 l_test,设置密码为pwd,默认数据库为MIS

（15）为登录 l_test 在数据库 MIS 中添加安全账户 u_test

（16）添加 u_test 为角色 r_test 的成员

（17）拒绝安全账户 u_test 对 学生 表的 SELECT 权限

/*--完成上述步骤后,用 l_test 登录,可以对表进行所有操作,但无法对学生表查询,虽然角色 r_test 有学生表的select权限,但已经在安全账户中明确拒绝了对学生的select权限,所以l_test无学生表的select权限--*/

（18）从数据库 MIS 中删除安全账户

（19）删除登录 l_test

（20）删除角色 r_test

(21)以管理员用户身份登录，完成以下操作
1) 创建角色testrole, 授予testrole可以在mis中对学生表具有update权限
2) 将用户u1充当testrole角色
3) 以u1登录，并测试其权限是否生效

三：实验平台

操作系统：Windows x64 

DBMS：SQL Server 2016 

四：程序清单、调试和测试结果及分析

四、程序清单：

使用企业管理器完成

（1）用企业管理器(对象资源管理器）建立登录账户：user1，user2，user3。(由于user2，user3与user1操作一样，故省略）

密码全都设置的是123。

可以看见建立登录名user1,user2,user3：

 (2)将user1，user2，user3映射为“学生－课程”数据库的用户。 (由于user2，user3与user1操作一样，故省略）

(3)授予user1，user2，user3具有对学生、课程三张表的查询权。 (由于user2，user3与user1操作一样，故省略）

 (4)授予user1具有对学生、课程表的插入、删除权。

插入权：

 删除权：

（5）在数据库中建立用户角色ROLE1，把学生表的插入、删除、查询它，并将user1，user2添加到此角色中。

 建立用户角色ROLE1，将user1，user2添加到此角色：

 授予权限：

（6）回收user1对学生表表的查询权。

（7）备份数据库。

右键选中数据库点击任务后再点击备份后确定。

 

（8）还原数据库。

右键点击任务后再点击还原后确定。

 

使用查询分析器命令完成

（1）建立一个U1的登录用户、数据库用户

EXEC sp_addlogin 'U1','123','syy'

Use syy

Exec sp_grantdbaccess 'u1'

（2） 使用相同的方法创建u2,u3,u4,u5

EXEC sp_addlogin 'U2','123','syy'

Use syy

Exec sp_grantdbaccess 'u2'

EXEC sp_addlogin 'U3','123','syy'

Use syy

Exec sp_grantdbaccess 'u3'

EXEC sp_addlogin 'U4','123','syy'

Use syy

Exec sp_grantdbaccess 'u4'

EXEC sp_addlogin 'U5','123','syy'

Use syy

Exec sp_grantdbaccess 'u5'

（3）把查询学生表权限授给用户U1

GRANT   SELECT

ON  学生

TO   U1;

（4）把对学生表和课程表的全部权限授予用户U2和U3

这么做写法不错，但无法运行，SQL SEVER不允许，必须分开

GRANT ALL ON 学生,课程 TO U2, U3;

下面这么做会显示ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。但是成功授予。

GRANT ALL ON  学生 TO U2,U3;

GRANT ALL ON  课程 TO U2,U3;

也可以这么做：

GRANT SELECT,DELETE,INSERT,UPDATE

ON  学生

TO U2,U3;

GRANT SELECT,DELETE,INSERT,UPDATE

ON  课程

TO U2,U3;

（5）把对表成绩的查询权限授予所有用户

GRANT SELECT ON 成绩 TO PUBLIC;

（6）把查询学生表和修改学生学号的权限授给用户U4

GRANT UPDATE(学号), SELECT

ON 学生

TO U4;

（7）把用户U4修改学生学号的权限收回

REVOKE UPDATE(学号)

ON 学生

FROM U4;

（8）收回所有用户对表成绩的查询权限

REVOKE SELECT

ON 成绩

FROM PUBLIC;

（9）把用户U5对成绩表的INSERT权限收回

REVOKE  INSERT

ON 成绩

FROM U5 CASCADE;

（10）分别以u1,u2，u3，u4登录，并测试其权限是否生效

测试代码（u1,u4已经测试过了，这里只测试u2,u3）：

测试U2：

/*插入*/

insert into 学生 values('991029','田平','女','08/05/1980',101)/*插入学生表*/

insert into 学生 values('992133 ','郭黎','男','03/04/1981',102)/*插入学生表*/

insert into 学生 values('994099 ','何明','女','04/12/1982',104)/*插入学生表*/

insert into 成绩 values('992124','c101',77)/*插入成绩表*/

insert into 成绩 values('992124','c102',95)/*插入成绩表*/

insert into 成绩 values('992124','c103',45)/*插入成绩表*/

/*删除*/

delete  from 成绩 where 学号 = '992124'/*删除成绩表*/

delete  from 学生 where 学号 = '999124'/*删除学生表*/



/*修改*/

update 学生 set 学号='999999' where 学号='994165'/*修改学生表*/

update 成绩 set 分数='99' where 学号='991022' AND 课程编号='101'/*修改成绩表*/

/*查询*/

SELECT 学号  FROM 成绩  GROUP BY 学号 HAVING COUNT(*)>=3/*查询成绩表*/

SELECT 性别, COUNT (*)AS 人数 FROM  学生 GROUP BY 性别/*查询学生表*/

测试U3：

/*插入*/

insert into 课程 values('c105','计算机导论',68,102)/*插入课程表*/

insert into 课程 values('c106','数据库',85,102)/*插入课程表*/

insert into 课程 values('c107','数据结构',102,102)/*插入课程表*/

insert into 课程 values('c108','计算机网络',51,102)/*插入课程表*/

insert into 成绩 values('992124','c101',77)/*插入成绩表*/

insert into 成绩 values('992124','c102',95)/*插入成绩表*/

insert into 成绩 values('992124','c103',45)/*插入成绩表*/

/*修改*/

update 学生 set 学号='999999' where 学号='994165'/*修改学生表*/

update 成绩 set 分数='99' where 学号='991022' AND 课程编号='101'/*修改成绩表*/

update 课程 set 学时='99' where 课程编号='101'/*修改课程表*/

/*删除*/

delete  from 成绩 where 学号 = '992124'/*删除成绩表*/

delete  from 学生 where 学号 = '999124'/*删除学生表*/

delete  from 课程 WHERE 课程编号='C104'/*删除课程表*/

delete  from 课程 WHERE 课程编号='C105'/*删除课程表*/

delete  from 课程 WHERE 课程编号='C107'/*删除课程表*/

delete  from 课程 WHERE 课程编号='C108'/*删除课程表*/

/*查询*/

SELECT 学号  FROM 成绩  GROUP BY 学号 HAVING COUNT(*)>=3/*查询成绩表*/

SELECT 性别, COUNT (*)AS 人数 FROM  学生 GROUP BY 性别/*查询学生表*/

SELECT 课程编号,课程名称 FROM 课程/*查询课程表*/

（11）在syy库下创建角色 r_test

EXEC sp_addrole 'r_test'

（12）授予 r_test对 成绩 表的SELECT访问权限

下面这么做会显示ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。但是成功授予。

GRANT ALL ON 成绩 TO r_test

也可以：

GRANT SELECT,DELETE,INSERT,UPDATE ON  成绩 TO r_test

(13) 授予角色 r_test 对 学生 表的 SELECT 权限

GRANT SELECT ON 学生 TO r_test

（14）添加登录 l_test,设置密码为pwd,默认数据库为syy

EXEC sp_addlogin 'l_test','pwd','syy'

（15）为登录 l_test 在数据库syy中添加安全账户 u_test

EXEC sp_grantdbaccess 'l_test','u_test'

（16）添加 u_test 为角色 r_test 的成员

EXEC sp_addrolemember 'r_test','u_test'

（17）拒绝安全账户 u_test 对 学生 表的 SELECT 权限

DENY SELECT ON 学生 TO u_test

测试：

SELECT 性别, COUNT (*)AS 人数 FROM  学生 GROUP BY 性别/*查询学生表*/

/*--完成上述步骤后,用 l_test 登录,可以对表进行所有操作,但无法对学生表查询,虽然角色 r_test 有学生表的select权限,但已经在安全账户中明确拒绝了对学生的select权限,所以l_test无学生表的select权限--*/

（18）从数据库syy中删除安全账户

EXEC sp_revokedbaccess 'u_test'

(19)删除登录 l_test

注：不能在登陆状态下删除，会失败。

EXEC sp_droplogin 'l_test'

(20)删除角色 r_test

EXEC sp_droprole 'r_test'

(21)以管理员用户身份登录，完成以下操作

1) 创建角色testrole, 授予testrole可以在syy数据库中对学生表具有update权限

EXEC sp_addrole 'testrole'

GRANT UPDATE ON 学生 TO testrole

2) 将用户u1充当testrole角色

EXEC sp_addrolemember 'testrole','u1'

3) 以u1登录，并测试其权限是否生效

测试代码：

update 学生 set 学号='999999' where 学号='994165'/*修改学生表*/

五、实验分析

（1）SQLServer 错误:15405 无法使用特殊主体'sa' 的解决办法：

直接选择附加进来的数据库，右键属性，如上图，文件--〉所有者--〉选择用户sa，即可用此sa用户登录数据库。

（2）SQLServer 错误：15007 user1是无效登录名或没有相应权限.应该先建立登录名，再建立数据库用户。

（3）【SQL Server】还原数据库失败：提示数据库正在使用。解决方法：选中无法还原的数据库，右键属性，选择选项-状态-限制访问-SINGLE_USER，此时数据库前会显示为（单个用户）；此时进行数据库还原即可，同时限制访问属性也会默认变更MULTI_USER属性。

（4）关键字 'TO' （‘，’）附近有语法错误。或者“TO”（‘，’）附近有语法错误。应为DOUBLECOLON，或ID。这是因为SQL Server不支持GRANT ALL

ON TABLE 学生,课程 TO U2,U3;这种写法（MySQL似乎也不可以），但是Oracle上是能运行的。on的后面只能接一个对象，也就是说只能分别授权。另外，用SQL SEVER不能像教材一样加上Table，这是标准SQL和T-SQL的区别。