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

实验九：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 WITH GRANT OPTION

8) 撤销数据库用户stu2对student表和course表的删除操作的权限；

Revoke delete ON Student,Course FROM stu2 CASCADE

本文来源：https://www.bwwdw.com/article/zhr2.html