• 数据库安全性管理

    一：实验目的

    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的登录用户、数据库用户

    1. EXEC sp_addlogin 'U1','123','syy'
    2.  
    3. Use syy
    4.  
    5. Exec sp_grantdbaccess 'u1'

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

    1. EXEC sp_addlogin 'U2','123','syy'
    2.  
    3. Use syy
    4.  
    5. Exec sp_grantdbaccess 'u2'
    6.  
    7. EXEC sp_addlogin 'U3','123','syy'
    8.  
    9. Use syy
    10.  
    11. Exec sp_grantdbaccess 'u3'
    12.  
    13. EXEC sp_addlogin 'U4','123','syy'
    14.  
    15. Use syy
    16.  
    17. Exec sp_grantdbaccess 'u4'
    18.  
    19. EXEC sp_addlogin 'U5','123','syy'
    20.  
    21. Use syy
    22.  
    23. Exec sp_grantdbaccess 'u5'

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

    1. GRANT   SELECT
    2.  
    3. ON  学生
    4.  
    5. TO   U1;

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

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

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

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

    1. GRANT ALL ON  学生 TO U2,U3;
    2.  
    3. GRANT ALL ON  课程 TO U2,U3;

    也可以这么做：

    1. GRANT SELECT,DELETE,INSERT,UPDATE
    2.  
    3. ON  学生
    4.  
    5. TO U2,U3;
    6.  
    7. GRANT SELECT,DELETE,INSERT,UPDATE
    8.  
    9. ON  课程
    10.  
    11. TO U2,U3;

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

    GRANT SELECT ON 成绩 TO PUBLIC;

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

    1. GRANT UPDATE(学号), SELECT
    2.  
    3. ON 学生
    4.  
    5. TO U4;

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

    1. REVOKE UPDATE(学号)
    2.  
    3. ON 学生
    4.  
    5. FROM U4;

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

    1. REVOKE SELECT
    2.  
    3. ON 成绩
    4.  
    5. FROM PUBLIC;

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

    1. REVOKE  INSERT
    2.  
    3. ON 成绩
    4.  
    5. FROM U5 CASCADE;

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

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

    测试U2：

    1. /*插入*/
    2.  
    3. insert into 学生 values('991029','田平','女','08/05/1980',101)/*插入学生表*/
    4.  
    5. insert into 学生 values('992133 ','郭黎','男','03/04/1981',102)/*插入学生表*/
    6.  
    7. insert into 学生 values('994099 ','何明','女','04/12/1982',104)/*插入学生表*/
    8.  
    9. insert into 成绩 values('992124','c101',77)/*插入成绩表*/
    10.  
    11. insert into 成绩 values('992124','c102',95)/*插入成绩表*/
    12.  
    13. insert into 成绩 values('992124','c103',45)/*插入成绩表*/
    14.  
    15. /*删除*/
    16.  
    17. delete  from 成绩 where 学号 = '992124'/*删除成绩表*/
    18.  
    19. delete  from 学生 where 学号 = '999124'/*删除学生表*/
    20.  
    21.  
    22.  
    23. /*修改*/
    24.  
    25. update 学生 set 学号='999999' where 学号='994165'/*修改学生表*/
    26.  
    27. update 成绩 set 分数='99' where 学号='991022' AND 课程编号='101'/*修改成绩表*/
    28.  
    29. /*查询*/
    30.  
    31. SELECT 学号  FROM 成绩  GROUP BY 学号 HAVING COUNT(*)>=3/*查询成绩表*/
    32.  
    33. SELECT 性别, COUNT (*)AS 人数 FROM  学生 GROUP BY 性别/*查询学生表*/

    测试U3：

    1. /*插入*/
    2.  
    3. insert into 课程 values('c105','计算机导论',68,102)/*插入课程表*/
    4.  
    5. insert into 课程 values('c106','数据库',85,102)/*插入课程表*/
    6.  
    7. insert into 课程 values('c107','数据结构',102,102)/*插入课程表*/
    8.  
    9. insert into 课程 values('c108','计算机网络',51,102)/*插入课程表*/
    10.  
    11. insert into 成绩 values('992124','c101',77)/*插入成绩表*/
    12.  
    13. insert into 成绩 values('992124','c102',95)/*插入成绩表*/
    14.  
    15. insert into 成绩 values('992124','c103',45)/*插入成绩表*/
    16.  
    17. /*修改*/
    18.  
    19. update 学生 set 学号='999999' where 学号='994165'/*修改学生表*/
    20.  
    21. update 成绩 set 分数='99' where 学号='991022' AND 课程编号='101'/*修改成绩表*/
    22.  
    23. update 课程 set 学时='99' where 课程编号='101'/*修改课程表*/
    24.  
    25. /*删除*/
    26.  
    27. delete  from 成绩 where 学号 = '992124'/*删除成绩表*/
    28.  
    29. delete  from 学生 where 学号 = '999124'/*删除学生表*/
    30.  
    31. delete  from 课程 WHERE 课程编号='C104'/*删除课程表*/
    32.  
    33. delete  from 课程 WHERE 课程编号='C105'/*删除课程表*/
    34.  
    35. delete  from 课程 WHERE 课程编号='C107'/*删除课程表*/
    36.  
    37. delete  from 课程 WHERE 课程编号='C108'/*删除课程表*/
    38.  
    39. /*查询*/
    40.  
    41. SELECT 学号  FROM 成绩  GROUP BY 学号 HAVING COUNT(*)>=3/*查询成绩表*/
    42.  
    43. SELECT 性别, COUNT (*)AS 人数 FROM  学生 GROUP BY 性别/*查询学生表*/
    44.  
    45. 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权限

    1. EXEC sp_addrole 'testrole'
    2.  
    3. 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的区别。

  • 相关阅读:
    Spring常见问题解决 - 自定义ApplicationEnvironmentPreparedEvent监听器失效了？
    游戏前端工作流程总结
    TFT-LCD显示中英文
    Pytorch实现波阻抗反演
    第3章 内存管理
    再谈谷歌GMS认证之Android 13
    深度学习使用GPU问题
    SpringBoot项目Redis使用
    提升预算管控精度，助力保险资管协会财务管理数字化转型
    大学物理（上）-期末知识点结合习题复习（3）——质点运动学-惯性系 非惯性系 惯性力 动量定理 动量守恒定律
  • 原文地址：https://blog.csdn.net/capture3333/article/details/128095818