sql数据库实验九：T-SQL语言存储过程及数据库的安全性

1、sql数据库实验九：T-SQL语言、存储过程及数据库的安全性实验九：T-SQL语言、存储过程及数据库的安全性一、实验目的1 .掌握数据变量的使用；2 .掌握各种控制语句及系统函数的使用；3.掌握存储过程的实现；4 .掌握混合模式下数据库用户帐号的建立与取消方法；5.掌握数据库用户权限的 设置方法：6 .掌握在企业管理器中进行备份、恢复操作的步骤；二、实验学时2学时三、实验要求1. 了解T-SQL支持的各种基本数据类型及变量的使用；2. 了解T-SQL各种运算符、 控制语句及函数的功能及使用方法：3.掌握存储过程的编写和运行方法4.熟悉数据库 完全备份及恢复的方法：5. 了解SQL Server

2、 2021系统安全：6.熟悉数据库用户、服务器角色及数据库角色的用法7.完成实验报告。四、实验内容以student数据库为基础数据，完成以下内容1.变量及函数的使用：1）创建局部变量xh （学号）并赋值，然后输出数据表student中所有等于该值的学 生的学号、姓名、性别、所属院系及年龄等信息：2）将学号为202115008的学生的姓名赋值给变量name；3）计算学生信息表student中学生最高年龄和最低年龄之差，并将结果付给cz：4）定义一函数，按系别统计当前所有学生的平均年龄，并调用该函数。5）定义一 函数，通过姓名查询某学生的学号、性别、年龄、系别、选修课程名及成绩。2.编写并 执行存

3、储过程（1）创建一个无参存储过程pr_StuScore,查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。（2）创建一个带参数的存储过程stu_info,该存储过程根据传入的学生编号在student表中查询此学生的选修课程及成绩信息。（3）创建一个带参数的存储过程StuScoreInfo2,该存储过程根据传入的学生编号和课程名称查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。（4）编写带参数的存储过程，根据传入的课程名称统计该课程的平均成绩。3. *对数据库student进行完全备份和恢复操作：4. *以系统管理员身份登录到SQL Server服务器，在SQL Server2

4、021界面中实现以 下操作1）在当前计算机中增加一个用户zhang,密码为secret。使此用户通过windows模式下登录SQL Server服务器，登录名为zhang：2）新建以混合模式登录SQL Server服务器的用户登录名分别为stul、stu2和stu3,登录密码为secret,默认登录数据库为student； 3）在数据库student中创 建用户zhang,登录帐号为zhang；4）在数据库student中创建用户stul、stu2和stu3,登录帐号为stul、stu2和 stu3;5）给数据库用户zhang赋广创建数据表的权限；6）给数据库用户stul赋予对sc表进行插入、

5、修改、删除操作权限：7）给数据库 用户stu2和stu3赋予对student表、course表所有操作权限及查询SC的操作权限，并允许再授权给其他用户；8）撤销数据库用户stu2对student表和course表的删除操作的权限；五、实验步骤3 .对数据库student进行完全备份和恢复操作；1）对数据库student进行完全备 份操作：以管理员账号登录SQL Server并运行SQL Server资源管理器：以下方法任选其一方法1:利用资源管理器进行备份（1）打开【对象资源管理器】，在【服务器对象】节点下找到【备份设备】节点， 单击鼠标右键，弹出右键菜单，选择【新建备份设备】菜单，弹出新建备

6、份设备窗口。输 入备份设备逻辑名称data_backup,并指定备份设备的物理路径，例如uD:backupdata_full. bakM ,单击【确定】。（2）连接到相应的SQL Server服务器实例之后，在【对象资源管理器】中，单击服 务器名称以展开服务器树。找到【数据库】节点展开，选择STUDENT数据库，单击鼠标右 键，在弹出的快捷菜单中选择【任务】一【备份】命令。（3）在【备份数据库】对话框 中的【数据库】下拉列表中选择的数据库名STUDENT。在【备份类型】下拉列表中选择备 份类型为“完整”，在【名称】文本框中输入备份集的名称STUDENT_BAK,在【备份集过 期时间】选项中指定

7、备份集过期时间为30天，在“选择页”窗格中，单击【选项】，在 【可靠性】选项中选择“完成后验证备份”选项，最后单击【确定】按钮。方法2：用 T-SQL语句进行数据库完全备份使用逻辑名STUDENTBAK创建一个命名的备份设备，并将数据库STUDENT完全备份到 该设备。在查询分析器的窗口输入如下的语句并执行：USE master EXECsp_addumpdevicedisk,STUDENTBAK D:backupSTUDENTBAK. BAKBackUp DataBase STUDENT To STUDENTBAK 2）在资源管理器中进行数据库恢复；（1）展开【数据库】节点，用鼠标右键单击S

8、TUDENT,在弹出的快捷菜单中选择【属 性】命令。打开“数据库属性”对话框，在“选择页”列表中，单击“选项” o在“恢 复模式”下拉列表中选择【完整】恢复模式。（2）用鼠标右健单击STUDENT,在弹出的快捷菜单中选择【任务】一）【还原】一） 【数据库】命令。打开“还原数据库”对话框。在“常规”选项卡上，”目标数据库”下 拉列表框中选择STUDENT。在“目标时间点”文本框中，使用默认值“最近状态”。在 “选择用于还原的备份集”表格中，选择用于还原的备份，单击“确定”按钮。4 . *以系统管理员身份登录到SQL Server服务器，在SQL Server2021界面中实现以 下操作1）在当前

9、计算机中增加一个用户zhang,密码为secret。使此用户通过windows模式下登录SQL Server服务器，登录名为zhang：在计算机中增加用户的方法如下：单击【开始I管理工具】，选择【计算机管理】， 在【计算机管理】的窗口中单击左边的【本地用户和组】，在右侧【用户】文件夹上单击 右键，选择菜单上的【新用户】，然后在弹出的【新用户】对话框中键入您准备使用的用 户名、密码，然后清除【用户下次登录时须更改密码】复选框的选中状态，再单击【创建】 按钮，然后单击【关闭】按钮关闭对话框。创建windows身份验证登陆用户方法：在【对象资源管理器】中，展开【安全性】节 点，然后右键单击【登录名】

10、，在弹出的快捷菜单中选择【新建登录名】。在“登录名- 新建”对话框中单击【登录名】后面的搜索按钮，选择【高级】，在弹出的对话框中选择 【立即查找】，在搜索结果中选择己经建立的用户名，单击【确定】按钮，回到“登录名 -新建”对话框中，最后单击确定按钮完成创建。2）新建以混合模式登录SQL Server服务器的用户登录名分别为stul、stu2和stu3,登录密码为secret,默认登录数据库为student；创建SQL Server身份验证登陆用户方法：在【对象资源管理器】中，展开【安全性】 节点，然后右键单击【登录名】，在弹出的快捷菜单中选择【新建登录名】。在“登录名 -新建”对话框中选择SQ

11、L Server身份验证选项，在【登录名】后面输入用户名，在密码 及确认密码后面输入密码。取消【强制密码过期】选项，单击【确定】按钮。3）在数据库student中创建用户zhang,登录帐号为zhang；在【对象资源管理器】中展开student数据库节点，展开【安全性】节点，在用户上 单击鼠标右键选择【新建用户】命令，在弹出的新建对话框中单击【登陆名】后面的搜索 按钮，在弹出的【选择登录名】对话框中单击【浏览】按钮，在相应的账号前面选中并单 击【确定】按钮，最后在用户名后面输入用户名称，单击【确定】按钮完成创建。CREATE USER zhang for login zhang4）在数据库st

12、udent中创建用户stul、stu2和stu3,登录帐号为stul、stu2和 stu3;CREATE USER stul for login stul 5）给数据库用户zhang赋予创建数据表的权 限：在student数据库安全性节点下的用户名上单击限标右键，选择【属性】,在打开的 对话框中选择【安全对象】页，单击右边的【搜索】按钮，在弹出的【添加对象】对话框 中直接单击【确定】按钮，然后单击【对象类型】按钮，弹出【选择对象类型】对话框， 选中【数据库】，单击【确定】按钮。再在选择对象对话框中单击【浏览】，在student 数据库前面选中，单击【确定】。最后在【安全对象】页下方的student的权限中创建表选项后的【授】复选框中选中，单击【确定】完成设置。GRANT create table to zhang6）给数据库用户stul赋予对sc表进行插入、修改、删除操作权限：GRANT insert, update, delete ON sc to stul7）给数据库用户stu2和stu3赋对student表、course表所有操作