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

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

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

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

4、08 界面中实现以下操作1) 在当前计算机中增加一个用户 zhang，密码为 secret 。使此用户通过 windows 模式下登录 SQL Server 服务器，登录名为 zhang；2) 新建以混合模式登录 SQLServer 服务器的用户登录名分别为 stu1 、stu2 和 stu3 ，登录密码为 secret ，默认登录数据库为 student ；3) 在数据库 student 中创建用户 zhang，登录帐号为 zhang；4) 在数据库 student 中创建用户 stu1 、stu2 和 stu3 ，登录帐号为 stu1 、stu2和 stu3;5) 给数据库用户 zhang

5、 赋予创建数据表的权限；6) 给数据库用户 stu1 赋予对 sc 表进行插入、修改、删除操作权限；7) 给数据库用户 stu2 和 stu3 赋予对 student 表、course 表所有操作权限及查询 sc 的操作权限，并允许再授权给其他用户；8) 撤销数据库用户 stu2 对 student 表和 course 表的删除操作的权限；五、实验步骤3. 对数据库 student 进行完全备份和恢复操作；1）对数据库 student进行完全备份操作；以管理员账号登录SQL Server 并运行 SQL Server 资源管理器；以下方法任选其一方法 1：利用资源管理器进行备份（ 1）打开【对

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

7、】下拉列表中选择备份类型为“完整” ，在【名称】文本框中输入备份集的名称 STUDENT_BAK ，在【备份集过期时间】 选项中指定备份集过期时间为 30 天，在“选择页”窗格中，单击【选项】 ，在【可靠性】选项中选择“完成后验证备份”选项，最后单击【确定】按钮。方法 2：用 T-SQL 语句进行数据库完全备份使用逻辑名STUDENTBAK创建一个命名的备份设备，并将数据库STUDENT 完全备份到该设备。在查询分析器的窗口输入如下的语句并执行：USE masterEXECsp_addumpdevicedisk,STUDENTBAK ,D:backupSTUDENTBAK.BAK BackUp

8、 DataBase STUDENT To STUDENTBAK2）在资源管理器中进行数据库恢复；（1）展开【数据库】节点，用鼠标右键单击STUDENT ，在弹出的快捷菜单中选择【属性】命令。打开“数据库属性”对话框，在“选择页”列表中，单击“选项”。在“恢复模式”下拉列表中选择【完整】恢复模式。（2）用鼠标右键单击 STUDENT ，在弹出的快捷菜单中选择 【任务】 【还原】 【数据库】命令。打开“还原数据库”对话框。在“常规”选项卡上，“目标数据库”下拉列表框中选择STUDENT 。在“目标时间点”文本框中，使用默认值“最近状态” 。在“选择用于还原的备份集”表格中，选择用于还原的备份，单击

9、“确定”按钮。4.* 以系统管理员身份登录到SQL Server 服务器，在 SQL Server2008 界面中实现以下操作1) 在当前计算机中增加一个用户 zhang，密码为 secret 。使此用户通过 windows 模式下登录 SQL Server 服务器，登录名为 zhang；在计算机中增加用户的方法如下：单击【开始 | 管理工具】，选择【计算机管理】，在【计算机管理】的窗口中单击左边的【本地用户和组】 ，在右侧【用户】文件夹上单击右键，选择菜单上的【新用户】 ，然后在弹出的【新用户】对话框中键入您准备使用的用户名、密码，然后清除【用户下次登录时须更改密码】复选框的选中状态，再单击

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

11、SQL Server 身份验证登陆用户方法：在【对象资源管理器】中，展开【安全性】节点，然后右键单击【登录名】 ，在弹出的快捷菜单中选择【新建登录名】。在“登录名 - 新建”对话框中选择 SQL Server 身份验证选项，在【登录名】后面输入用户名，在密码及确认密码后面输入密码。取消【强制密码过期】选项，单击【确定】按钮。3) 在数据库 student 中创建用户 zhang，登录帐号为 zhang；在【对象资源管理器】中展开 student 数据库节点，展开【安全性】节点，在用户上单击鼠标右键选择【新建用户】命令，在弹出的新建对话框中单击【登陆名】后面的搜索按钮， 在弹出的【选择登录名】

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