指导书原文
- 安全性管理二
【实验目的】
1) 通过语句设置登录用户的权限，实现对不同用户的操作权限的限定。
【实验原理】
1) 定义数据库角色，授予该角色特定权限，建立不同的用户将其置入不同的角色之下，即等于分配了该用户不同的权限，使用这种角色管理办法可以提高数据库的管理安全性。
2）通过“use databasename;”语句或”展开数据库->右击 databasename->选择新建查询“，选择要操作的数据库，再执行查询、插入等操作命令，不选择数据库则会出现报错。
【实验环境】
windows7
SQL SERVER 2008
【实验步骤】
一、建立数据库及各表项
1.1 建立stumanage数据库。如图1所示
create database stumanage
图1
1.2 建表student，添加两组数据。如图2所示
create table student(
sno char(8) not null primary key,
sname Varchar(20) not null,
sex Char(2) not null,
dept Varchar(20) not null)
insert into student values('20050101','王飞','男','计算机系')
insert into student values('20050102','李丽','女','信息系')
图2
1.3 建表course，添加两组数据。如图3所示
create table course(
cno Char(6) primary key,
cname Varchar(20) not null)
insert into course values('01','数据结构')
insert into course values('02','数据库原理')
图3
1.4 建表sc，添加两组数据。如图4所示
create table sc(
sno Char(8),
cno Char(6),
grade int)
insert into sc values('20050101','01','70')
insert into sc values('20050102','02','90')
图4
二、建立数据库角色
2.1 在stumanage数据库中建立用户定义的角色：SelectRole，并授予该角色对 Student、Course和SC表具有查询权。如图5所示
create role SelectRole
grant select on student to SelectRole
grant select on course to SelectRole
grant select on sc to SelectRole
图5
2.2 亦可右键该角色—“属性”—“安全对象”，在下方勾选所赋予的权限。如图6所示
图6
三、建立用户pub_user
3.1 新建立一个SQL Server身份验证模式的登录名pub_user，并让该登录名成为数据库stumanage中的合法用户。如图7所示
create login pub_user with password = '1'
create user pub_user
图7
3.2 用pub_user登录。如图8所示
图8
3.3 建立一个新建查询，执行Select * from course；因为pub_user虽然是stumanage中的用户，却没有对于数据库stumanage的查询权限，所以查询失败。如图9所示
图9
四、改变用户成员身份
4.1 以Windows身份将pub_user 添加到SelectRole角色中。exec sp_addrolemember 'SelectRole' , 'pub_user'。如图10所示
图10
4.2 右键数据库角色SelectRole—“属性”—“常规”，可以查看到pub_user已经成为SelectRole角色下的成员了。如图11所示
图11
4.3 使用pub_user身份登录，新建并执行SQL语句select * from course。因为pub_user已经成为了SelectRole中的一员，而SelectRole具有对stumanage具有查询权限，所以pub_user可以对stumanage中的表进行查询。如图12所示
图12
【实验思考】
- 如何用T-SQL语句实现将WINDOWS登录名user2添加到stumanage数据库中的SelectRole角色中？
- 安全性管理1
【实验目的】
1) 通过语句设置登录用户的权限，实现对不同用户的操作权限的限定。
【实验原理】
1) 将用户置于目标数据库下，并对其设置相应的权限，以实现该用户对数据库的特定操作。
【实验环境】
windows7
SQL SERVER 2008
【实验步骤】
一、建立登录名及数据库
1.1 建立登录名log1，log2。如图1所示
create login log1 with password = '1'
create login log2 with password = '1'
图1
1.2 建立stumanage数据库。如图2所示
create database stumanage
图2
1.3 建表student，添加两组数据。如图3所示
create table student(
sno char(8) not null primary key,
sname Varchar(20) not null,
sex Char(2) not null,
dept Varchar(20) not null)
insert into student values('20050101','王飞','男','计算机系')
insert into student values('20050102','李丽','女','信息系')
图3
1.4 建表course，添加两组数据。如图4所示
create table course(
cno Char(6) primary key,
cname Varchar(20) not null)
insert into course values('01','数据结构')
insert into course values('02','数据库原理')
图4
1.5 建表sc，添加两组数据。如图5所示
create table sc(
sno Char(8),
cno Char(6),
grade int)
insert into sc values('20050101','01','70')
insert into sc values('20050102','02','90')
图5
二、登录log1，访问数据库
2.1 用log1身份登录，点击stumanage数据库。如图6所示
图6
2.2 因为log1登录名不是stumanage数据库中的用户，所以无法访问。如图7所示
图7
三、增加用户映射
3.1 以windows用户的身份登录数据库，打开“安全性”--“登录名”，右键“log1”选择“属性”，点击“用户映射”，勾选stumanage数据库进行log1与数据库stumanage的映射。如图8所示
图8
3.2 同理对log2进行此项操作。如图9所示
图9
3.3 用log1身份登录数据库，由于log1已经是students数据库中的用户了，所以可以正常选中stumanage。如图10所示
图10
四、增加权限
4.1 log1中在stumanage下执行语句Select * from Course，由于log1没有查询权限，查询没有成功。如图11所示
图11
4.2 以Windows身份登录，新建查询“grant select on course to log1”对log1授予Course查询权限。如图12所示
图12
4.3 此时再用log1身份对course进行查询select * from Course，发现可以查询。如图13所示
图13
4.4 同理对log2用户增加插入权限。如图14所示
grant insert on course to log2
图14
4.5 用log2建立一个新建查询，执行语句 Insert into course values('03','c语言程序设计')。如图15所示
图15
4.6 以Windows身份查询course内容：select * from Course，发现内容已成功插入。如图16所示
图16
【实验思考】
- 如何用T-SQL语句实现给予用户建表权限？
- 完成以上操作的同学可以完成mysql角色访问控制实验。
实验Warning
1.做实验二的时候，我一开始出现这个信息：
消息 15151，级别 16，状态 1，第 1 行 无法对 用户 'log1' 执行 查找，因为它不存在，或者您没有所需的权限。
我以为是什么权限设置的问题，但是实际上是创建的代码一个别省着复制粘贴，我就是漏掉了create table course。
2.无论是log1的Select * from Course，还是log2的Insert into course values('03','c语言程序设计')，都发现失败。
根据我摸爬滚打二十分钟的经验，设置用户映射，你还要勾中master！再执行授权，才有用。
实验报告
五、实验过程原始记录(数据、图表、计算等)
实验一：
以下是尝试用SQL Server身份验证登陆服务器
下图是建立一个新建查询，执行Select * from course，发现查询失败的情况。
下图是授权后再次查询，执行Select * from course，发现查询成功的情况。
实验二：
用log1身份登录，点击stumanage数据库。发现无法访问的情况。
增加用户映射的操作说明：
现在的数据库可查看了
但是没有查询权限
发现可以查询
发现可以插入
六、实验结果及分析
1. 利用backup1和backup2备份设备对students数据库的备份，恢复数据库。完全恢复后，在Course表中有新插入的记录吗？为什么？
答：恢复后的 Course 表中不会有新插入的记录，因为完全恢复（FULL RECOVERY）会还原数据库到备份时的状态，包括下面两种结果：如果 backup1 是完整备份，backup2 是差异或日志备份，恢复后数据库会回到 backup2 备份的时间点；备份之后的所有更改（包括新插入的记录）都会丢失，除非有后续的事务日志备份（LOG BACKUP）并应用到恢复过程。
如果没有后续日志备份，恢复后的数据库只会包含备份时的数据，不会有备份后新增的数据。
2.如何用T-SQL语句实现给予用户建表权限？
答：有以下3种方法：
方法 1：授予 CREATE TABLE 权限
USE YourDatabase; -- 替换成目标数据库
-- 授予用户建表权限（仅限当前数据库）
GRANT CREATE TABLE TO user_log1;
方法 2：授予 db_ddladmin 角色（允许所有 DDL 操作）
USE YourDatabase;
-- 授予用户 DDL 权限（建表、修改表、删除表等）
ALTER ROLE db_ddladmin ADD MEMBER user_log1;
方法 3：授予架构级 ALTER 权限（允许在特定架构下建表）
USE YourDatabase;
-- 授予用户在 dbo 架构下建表的权限
GRANT ALTER ON SCHEMA::dbo TO user_log1;