实验5 数据的安全性管理

一、实验目的

1、掌握SQL Server身份验证模式。

2、掌握创建登录账户、数据库用户的方法。

3、掌握使用角色实现数据库安全性的方法。

4、掌握权限的分配。

二、实验内容

1、设置身份验证模式：Windows身份验证模式和混合模验证模式。

2、设置登录账户

3、设置数据库用户

4、设置数据库角色。

5、设置管理对象的权限

三、实验步骤

1、将SQL Server服务器设置为混合身份验证模式

如果安装时使用的是”“Windows身份验证模式”，则按以下步骤可以将SQL Server服务器设置为“混合身份验证模式”。

• 依次单击开始－>所有程序－>SQL Server－>SQL Server Management Studio 启动SQL Server数据库管理系统（其实就是打开SQL Server）
• 连接成功后，右键一个服务器实例，选择"属性"
• 在"属性"窗口中，转到"Security"(安全性)项，在"服务器身份验证"中设置为"SQL Server和Windows身份验证模式"，确定，根据提示，应该重新启动SQL服务
• 使用Windows身份验证登录，执行下面的语句启用sa用户，同时清除sa的密码(能成功登陆后再根据你的需要设置)，也可在“新建查询”窗口中执行以下SQL语句完成。

EXEC sp_password null, '123','sa'

ALTER LOGIN sa ENABLE

• 检查Microsoft SQL Server→配置工具→SQL Server Configuration Manager中是否将相关协议启用并按提示重启服务。
• 用sa登录连接，“服务器名称”以IP形式给出，如：127.0.0.1\SQLEXPRESS或172.16.67.158\SQLEXPRESS，（具体IP值查看本机或其他同学机器的“本地连接”，172.16.67.158只是示例），测试登录情况。
• 如果不能登录127.0.0.1\SQLEXPRESS服务器，打开Microsoft SQL Server→配置工具→SQL Server Configuration Manager，启用“SQL Server Browser”服务，并打开“SQL Server网络配置”，启用“TCP/IP”并右击修改属性中ip地址127.0.0.1为启用。

2、创建登录名mylogin

方法一：创建使用SQL Server身份验证的SQL Server登录名

• 在SQL Server Management Studio中，打开对象资源管理器并展开要在其中创建新登录名的服务器实例的文件夹。
• 右键单击“安全性”文件夹，指向“新建”，然后单击“登录名”。
• 在“常规”页上的“登录名”框中输入一个新登录名的名称。
• 选择“SQL Server身份验证”(注意：不要使用默认的“Windows身份验证”)。
• 输入登录名mylogin及其密码123。
• 选择应当应用于新登录名的密码策略选项。注意：不要钩选“下次登录时必须修改密码”。
• 单击“确定”。
• 用新创建的登录名登录系统，看看可以进行哪些操作，特别测试一下打开“学生_教学”数据库后的结果。

方法二：通过Transact-SQL创建使用SQL Server身份验证的SQL Server登录名

• 在查询编辑器中，输入以下Transact-SQL命令：

CREATE LOGIN mylogin WITH PASSWORD = '123';

GO

3、创建数据库用户myuser并映射登录名mylogin

    通过SSMS将附件压缩包“案例数据库”中的“学生-教学”数据库（含mdf和ldf两个文件）导入到本地数据库中，然后执行以下操作

方法一：通过SQL Server Management Studio创建数据库用户

• 打开对象资源管理器并展开要在其中创建新登录名的服务器实例文件夹。
• 依次展开数据库，打开“学生-教学”数据库，点击“安全性”-“用户”
• 右键单击用户，选择“新建用户”
• 输入用户名myuser，选择登录名mylogin，确定。

方法二：通过Transact-SQL创建数据库用户：

create user myuser for login mylogin with default_schema=dbo

指定数据库用户“myuser”的默认schema是“dbo”。这意味着用户“myuser”在执行“select * from t”，实际上执行的是 “select * from dbo.t”。

4、创建并设置数据库角色

方法一：通过SQL Server Management Studio新建数据库角色

• 打开对象资源管理器并展开要在其中创建新登录名的服务器实例文件夹
• 依次展开数据库，某个具体数据库（例如：“学生-教学”数据库），选择“安全性”-“角色”
• 右击选中新建数据库角色，输入角色名myrole，确定

方法二：通过Transact-SQL新建角色：

CREATE ROLE myrole

5、对用户赋予指定的角色

方法一：在对象资源管理器中设置数据库角色

• 在SQL Server Management Studio中，打开对象资源管理器并展开要在其中创建新登录名的服务器实例的文件夹。
• 依次展开数据库，打开“学生-教学”数据库，点击“安全性”-“用户”
• 选中“myuser”用户，右击“属性”，在展开的数据库用户属性对话框中为其赋予“db_owner”角色和“myrole”角色

方法二：通过Transact-SQL设置数据库角色

• 通过加入数据库角色，对数据库用户“myuser”赋予权限：

exec sp_addrolemember 'db_owner', 'myuser'

此时，myuser就可以全权管理数据库“学生-教学”中的对象了。

如果想让SQL Server登陆帐户“mylogin”访问多个数据库，比如S_T。可以让sa执行下面的语句：

use S_T

go

create user myuser for login mylogin with default_schema=dbo

go

exec sp_addrolemember 'db_owner', 'myuser'

go

此时，mylogin就可以有两个数据库（“学生-教学”、“S_T”）的管理权限了！

6、设置管理对象的权限

方法一：在对象资源管理器中授权、收回和拒绝权限：

• 选中需要授权的登录名、用户名或角色名
• 右键单击选择“属性”，在弹出的该对象的属性窗口中选择“安全对象”，进行权限分配。

方法二：通过Transact-SQL分配权限：通过GRANT和REVOKE语句

7、 以mylogin登录“127.0.0.1\SQLEXPRESS”服务器，在“学生_教学”数据库的Student表中进行增删改查的操作。示例如下：

四、实验任务

1、创建一个登录账号Testlogin。

2、在“学生_教学”数据库中创建对应于这个登录账号的数据库用户TestUser。

3、在“学生_教学”数据库中创建角色TestRole，它应该拥有创建表和视图的权限，并拥有对Student表的查询、修改、插入权限。

4、将用户TestUser添加到TestRole角色中。

5、以Testlogin登录“127.0.0.1\SQLEXPRESS”服务器，在“学生_教学”数据库的Student表中插入一条记录。

五、实验报告要求

1、写出完成实验任务1~4相对应的SQL语句

任务1

CREATE LOGIN Testlogin WITH PASSWORD='123'
GO

任务2

USE S_T
CREATE USER TestUser FOR LOGIN TestLogin WITH DEFAULT_SCHEMA = dbo

​​​​​

任务3

USE S_T
CREATE ROLE TestRole
GRANT CREATE TABLE, CREATE VIEW
TO TestRole
GRANT SELECT,UPDATE,INSERT
ON Student
TO TestRole

任务4

EXEC sp_addrolemember 'TestRole','TestUser'

2、给出实验任务5的执行界面截图。

参照“实验步骤”中的最后一幅示例图，要求同时显示Testlogin登录“127.0.0.1\SQLEXPRESS”服务器的界面信息及在“学生_教学”数据库的Student表中插入一个名字为“王小飞”的学生记录。

3、并记录在实验过程中遇到的问题、解决办法及心得体会。

无