实验九：T-SQL语言、存储过程及数据库的安全性
一、实验目的
1. 掌握数据变量的使用；
2. 掌握各种控制语句及系统函数的使用；
3. 掌握存储过程的实现；
4. 掌握混合模式下数据库用户帐号的建立与取消方法；
5. 掌握数据库用户权限的设置方法；
6. 掌握在企业管理器中进行备份、恢复操作的步骤；
二、实验学时
2学时
三、实验要求
1. 了解T-SQL支持的各种基本数据类型及变量的使用；
2. 了解T-SQL各种运算符、控制语句及函数的功能及使用方法；
3. 掌握存储过程的编写和运行方法
4. 熟悉数据库完全备份及恢复的方法；
5. 了解SQL Server 2008系统安全；
6. 熟悉数据库用户、服务器角及数据库角的用法
7. 完成实验报告。
四、实验内容
以student数据库为基础数据，完成以下内容
1. 变量及函数的使用：
1）创建局部变量@xh（学号）并赋值，然后输出数据表student中所有等于该值的学生的学号、姓名、性别、所属院系及年龄等信息；
2）将学号为200515008的学生的姓名赋值给变量@name；
3）计算学生信息表student中学生最高年龄和最低年龄之差，并将结果付给@cz；
4）定义一函数，按系别统计当前所有学生的平均年龄，并调用该函数。
5）定义一函数，通过姓名查询某学生的学号、性别、年龄、系别、选修课程名及成绩。
2. 编写并执行存储过程
(1) 创建一个无参存储过程pr_StuScore，查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。
(2) 创建一个带参数的存储过程stu_info，该存储过程根据传入的学生编号在student表中查询此学生的选修课程及成绩信息。
(3) 创建一个带参数的存储过程StuScoreInfo2，该存储过程根据传入的学生编号和课程名称查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。
(4) 编写带参数的存储过程，根据传入的课程名称统计该课程的平均成绩。
3. *对数据库student进行完全备份和恢复操作；
4.*以系统管理员身份登录到SQL Server服务器，在SQL Server2008界面中实现以下操作
1) 在当前计算机中增加一个用户zhang，密码为secret。使此用户通过windows模式下登录SQL Server服务器，登录名为zhang；
2) 新建以混合模式登录SQL Server服务器的用户登录名分别为stu1、stu2和stu3，登录密码为secret，默认登录数据库为student；
3) 在数据库student中创建用户zhang，登录帐号为zhang；
4) 在数据库student中创建用户stu1、stu2和stu3，登录帐号为stu1、stu2和stu3;
5) 给数据库用户zhang赋予创建数据表的权限；
6) 给数据库用户stu1赋予对sc表进行插入、修改、删除操作权限；
7) 给数据库用户stu2和stu3赋予对student表、course表所有操作权限及查询sc的操作权限，并允许再授权给其他用户；
8) 撤销数据库用户stu2对student表和course表的删除操作的权限；
五、实验步骤
3. 对数据库student进行完全备份和恢复操作；
1）对数据库student进行完全备份操作；
以管理员账号登录SQL Server并运行SQL Server资源管理器；以下方法任选其一
方法1：利用资源管理器进行备份
（1）打开【对象资源管理器】，在【服务器对象】节点下到【备份设备】节点，单击鼠标右键，弹出右键菜单，选择【新建备份设备】菜单，弹出新建备份设备窗口。输入备份设
备逻辑名称data_backup，并指定备份设备的物理路径，例如“D:\backup\data_full.bak”，单击【确定】。
（2）连接到相应的SQL Server服务器实例之后，在【对象资源管理器】中，单击服务器名称以展开服务器树。到【数据库】节点展开，选择STUDENT数据库，单击鼠标右键，在弹出的快捷菜单中选择【任务】—>【备份】命令。
（3）在【备份数据库】对话框中的【数据库】下拉列表中选择的数据库名STUDENT。在【备份类型】下拉列表中选择备份类型为“完整”，在【名称】文本框中输入备份集的名称STUDENT_BAK，在【备份集过期时间】选项中指定备份集过期时间为30天，在“选择页”窗格中，单击【选项】，在【可靠性】选项中选择“完成后验证备份”选项，最后单击【确定】按钮。
方法2：用T-SQL语句进行数据库完全备份
使用逻辑名STUDENTBAK创建一个命名的备份设备，并将数据库STUDENT完全备份到该设备。在查询分析器的窗口输入如下的语句并执行：
USE master
EXEC sp_addumpdevice ‘disk’,’ STUDENTBAK’,’D:\backup\ STUDENTBAK.BAK’
BackUp DataBase STUDENT To STUDENTBAK
2）在资源管理器中进行数据库恢复；
（1）展开【数据库】节点，用鼠标右键单击STUDENT，在弹出的快捷菜单中选择【属性】命令。打开“数据库属性”对话框，在“选择页”列表中，单击“选项” 。在“恢复模式”下拉列表中选择【完整】恢复模式。
（2）用鼠标右键单击STUDENT，在弹出的快捷菜单中选择【任务】—>【还原】—>【数据库】命令。打开“还原数据库”对话框。在“常规”选项卡上，“目标数据库”下拉列表框中选择STUDENT。在“目标时间点”文本框中，使用默认值“最近状态”。在“选择用于还原的备份集”表格中，选择用于还原的备份，单击“确定”按钮。
4.*以系统管理员身份登录到SQL Server服务器，在SQL Server2008界面中实现以下操作
1) 在当前计算机中增加一个用户zhang，密码为secret。使此用户通过windows模式下登录SQL Server服务器，登录名为zhang；
在计算机中增加用户的方法如下：单击【开始|管理工具】，选择【计算机管理】，在【计算机管理】的窗口中单击左边的【本地用户和组】，在右侧【用户】文件夹上单击右键，选择菜单上的【新用户】，然后在弹出的【新用户】对话框中键入您准备使用的用户名、密码，然后清除【用户下次登录时须更改密码】复选框的选中状态，再单击【创建】按钮，然后单击【关闭】按钮关闭对话框。
创建windows身份验证登陆用户方法：在【对象资源管理器】中，展开【安全性】节点，然后右键单击【登录名】，在弹出的快捷菜单中选择【新建登录名】。在“登录名-新建”对话框中单击【登录名】后面的搜索按钮，选择【高级】，在弹出的对话框中选择【立即查】，在搜索结果中选择已经建立的用户名，单击【确定】按钮，回到“登录名-新建”对话框中，最后单击确定按钮完成创建。
2) 新建以混合模式登录SQL Server服务器的用户登录名分别为stu1、stu2和stu3，登录密码为secret，默认登录数据库为student；
创建SQL Server身份验证登陆用户方法：在【对象资源管理器】中，展开【安全性】节点，然后右键单击【登录名】，在弹出的快捷菜单中选择【新建登录名】。在“登录名-新建”对话框中选择SQL Server身份验证选项，在【登录名】后面输入用户名，在密码及确认密码后面输入密码。取消【强制密码过期】选项，单击【确定】按钮。
3) 在数据库student中创建用户zhang，登录帐号为zhang；
在【对象资源管理器】中展开student数据库节点，展开【安全性】节点，在用户上单击鼠标右键选择【新建用户】命令，在弹出的新建对话框中单击【登陆名】后面的搜索按钮，在弹出的【选择登录名】对话框中单击【浏览】按钮，在相应的账号前面选中并单击【确定】按钮，最后在用户名后面输入用户名称，单击【确定】按钮完成创建。
CREATE  USER  zhang for login zhang
4) 在数据库student中创建用户stu1、stu2和stu3，登录帐号为stu1、stu2和stu3;
CREATE  USER  stu1 for login stu1
5) 给数据库用户zhang赋予创建数据表的权限；
在student数据库安全性节点下的用户名上单击鼠标右键，选择【属性】，在打开的对话框中选择【安全对象】页，单击右边的【搜索】按钮，在弹出的【添加对象】对话框中直接单击【确定】按钮，然后单击【对象类型】按钮，弹出【选择对象类型】对话框，选中【数据库】，单击【确定】按钮。再在选择对象对话框中单击【浏览】，在student数据库前面选中，单击【确定】。最后在【安全对象】页下方的student的权限中创建表选项后的【授予】复选框中选中，单击【确定】完成设置。
GRANT  create  table  to  zhang
6) 给数据库用户stu1赋予对sc表进行插入、修改、删除操作权限；
GRANT insert,update,delete ON  sc to stu1
7) 给数据库用户stu2和stu3赋予对student表、course表所有操作权限及查询sc的操作权限，并允许再授权给其他用户；
GRANT  ALL PRIVILEGES  ON  Student,Course  TO  stu2,stu3
sql存储过程实例
WITH GRANT OPTION
8) 撤销数据库用户stu2对student表和course表的删除操作的权限；
Revoke  delete  ON Student,Course  FROM stu2 CASCADE

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。