实验课速通SQLServer期末考点五：数据库维护

一、实验内容

SQL Server 2017 环境下教学信息管理系统的数据库安全性控制（用户 / 角色 / 权限）与备份恢复实现。

二、实验目的

1. 掌握 SQL Server 数据库安全性控制的核心机制：能通过CREATE LOGIN/CREATE USER创建登录名与数据库用户，通过CREATE ROLE创建自定义角色；

2. 学会基于 “最小权限原则” 分配权限（GRANT/DENY/REVOKE），实现不同角色（学生、教师、管理员）的权限隔离；

3. 掌握数据库备份的 SQL 实现：包括完整备份BACKUP DATABASE、差异备份，理解不同备份类型的适用场景；

4. 掌握数据库恢复的 SQL 实现：包括完整恢复RESTORE DATABASE、差异恢复，能模拟数据丢失场景并成功恢复数据，保障教学信息管理系统数据安全。

三、实验任务

任务 1：数据库安全性控制（用户、角色、权限）

1. 创建登录名与数据库用户：
   ◦ 创建 SQL Server 登录名：Login_Student（学生角色，密码Stu@2025）、Login_Teacher（教师角色，密码Tea@2025）、Login_Admin（管理员角色，密码Adm@2025）；
   ◦ 为上述登录名在TeachingSysDB数据库中创建对应用户：User_Student、User_Teacher、User_Admin。

2. 创建自定义角色并分配权限：
   ◦ 创建自定义数据库角色：Role_Student（学生角色）、Role_Teacher（教师角色）、Role_Admin（管理员角色）；
   ◦ 为角色分配权限：
   ▪ Role_Student：仅允许查询V_Student_Computer视图、V_Student_Score视图（无修改 / 删除权限）；
   ▪ Role_Teacher：允许查询Teacher表、Course表、SC表，允许修改SC表的成绩字段（无删除权限）；
   ▪ Role_Admin：拥有TeachingSysDB数据库的所有权限（db_owner角色权限）。

3. 用户关联角色：将User_Student关联Role_Student，User_Teacher关联Role_Teacher，User_Admin关联Role_Admin，验证不同用户的权限范围。

任务 2：数据库备份（完整备份与差异备份）

1. 完整备份：使用BACKUP DATABASE对TeachingSysDB进行完整备份，备份文件路径为D:\SQLBackup\TeachingSysDB_Full.bak，备注 “实验六初始完整备份”；

2. 模拟数据变更：向SC表插入 3 条新的选课记录（如学生2023010108选 “线性代数” 课程，成绩 88 分），模拟日常数据更新；

3. 差异备份：基于步骤 1 的完整备份，对TeachingSysDB进行差异备份，备份文件路径为D:\SQLBackup\TeachingSysDB_Diff.bak，备注 “实验六差异备份（含新选课记录）”。

任务 3：数据库恢复（完整恢复与差异恢复）

1. 模拟数据丢失：删除SC表中步骤 2 插入的 3 条新选课记录，模拟数据误删除场景；

2. 完整恢复验证：使用步骤 1 的完整备份文件，将TeachingSysDB恢复到备份时的状态，验证删除的新记录是否未恢复（因完整备份不含后续变更）；

3. 差异恢复验证：基于完整恢复后的数据库，使用步骤 3 的差异备份文件进行恢复，验证删除的 3 条新选课记录是否成功恢复；

4. 恢复后数据校验：查询SC表数据，确认恢复后的数据与模拟变更后的状态一致，无数据丢失或冗余。

四、实验步骤与参考代码

步骤 1：数据库安全性控制

1.1 创建登录名与数据库用户

-- 切换到master数据库（创建登录名需在master库执行）
USE master;
GO
-- 1. 创建学生登录名Login_Student（SQL身份验证）
CREATE LOGIN Login_Student
WITH PASSWORD = 'Stu@2025',  -- 密码需符合复杂度要求（含大小写、数字、特殊符号）DEFAULT_DATABASE = TeachingSysDB,  -- 默认数据库为TeachingSysDBCHECK_EXPIRATION = ON,  -- 启用密码过期CHECK_POLICY = ON;  -- 启用密码策略（如长度、复杂度）
GO
-- 2. 创建教师登录名Login_Teacher
CREATE LOGIN Login_Teacher
WITH PASSWORD = 'Tea@2025',DEFAULT_DATABASE = TeachingSysDB,CHECK_EXPIRATION = ON,CHECK_POLICY = ON;
GO
-- 3. 创建管理员登录名Login_Admin
CREATE LOGIN Login_Admin
WITH PASSWORD = 'Adm@2025',DEFAULT_DATABASE = TeachingSysDB,CHECK_EXPIRATION = ON,CHECK_POLICY = ON;
GO
-- 切换到TeachingSysDB数据库，为登录名创建对应数据库用户
USE TeachingSysDB;
GO
-- 1. 为Login_Student创建用户User_Student
CREATE USER User_Student FOR LOGIN Login_Student;
GO
-- 2. 为Login_Teacher创建用户User_Teacher
CREATE USER User_Teacher FOR LOGIN Login_Teacher;
GO
-- 3. 为Login_Admin创建用户User_Admin
CREATE USER User_Admin FOR LOGIN Login_Admin;
GO
PRINT '登录名与数据库用户创建完成';

1.2 创建自定义角色并分配权限

USE TeachingSysDB;
GO
-- 1. 创建自定义角色Role_Student（学生角色）
CREATE ROLE Role_Student;
GO
-- 为Role_Student分配权限：仅查询指定视图
GRANT SELECT ON V_Student_Computer TO Role_Student;  -- 允许查询计算机学院学生视图
GRANT SELECT ON V_Student_Score TO Role_Student;    -- 允许查询学生选课成绩视图
DENY INSERT, UPDATE, DELETE ON V_Student_Computer TO Role_Student;  -- 拒绝修改/删除
DENY INSERT, UPDATE, DELETE ON V_Student_Score TO Role_Student;    -- 拒绝修改/删除
GO
-- 2. 创建自定义角色Role_Teacher（教师角色）
CREATE ROLE Role_Teacher;
GO
-- 为Role_Teacher分配权限：查询多表+修改成绩
GRANT SELECT ON Teacher TO Role_Teacher;  -- 允许查询教师表
GRANT SELECT ON Course TO Role_Teacher;   -- 允许查询课程表
GRANT SELECT ON SC TO Role_Teacher;       -- 允许查询选课表
GRANT UPDATE (Grade) ON SC TO Role_Teacher;  -- 仅允许修改SC表的Grade字段
DENY DELETE ON SC TO Role_Teacher;        -- 拒绝删除选课表数据
GO
-- 3. 创建自定义角色Role_Admin（管理员角色），直接关联系统角色db_owner
CREATE ROLE Role_Admin;
GO
EXEC sp_addrolemember 'db_owner', 'Role_Admin';  -- db_owner拥有数据库所有权限
GO
PRINT '自定义角色创建与权限分配完成';

1.3 用户关联角色并验证

USE TeachingSysDB;
GO
-- 1. User_Student关联Role_Student
EXEC sp_addrolemember 'Role_Student', 'User_Student';
GO
-- 2. User_Teacher关联Role_Teacher
EXEC sp_addrolemember 'Role_Teacher', 'User_Teacher';
GO
-- 3. User_Admin关联Role_Admin
EXEC sp_addrolemember 'Role_Admin', 'User_Admin';
GO
-- 权限验证（可选，使用对应登录名登录SSMS执行以下语句）
-- 1. 用Login_Student登录，执行查询：
-- SELECT * FROM V_Student_Computer;  -- 正常返回
-- UPDATE V_Student_Computer SET 年龄=22 WHERE 学号='2023010101';  -- 报错：权限不足
-- 2. 用Login_Teacher登录，执行查询：
-- SELECT * FROM SC;  -- 正常返回
-- UPDATE SC SET Grade=90 WHERE Sno='2023010101' AND Cno='CS101';  -- 正常执行
-- DELETE FROM SC WHERE Sno='2023010101';  -- 报错：权限不足
-- 3. 用Login_Admin登录，执行查询：
-- DROP TABLE TestTable;  -- 可正常执行（db_owner权限）
PRINT '用户与角色关联完成，可通过对应登录名验证权限';

步骤 2：数据库备份

-- 1. 任务2-1：完整备份TeachingSysDB
BACKUP DATABASE TeachingSysDB
TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TeachingSysDB_Full.bak'  -- 备份文件路径（需提前创建D:\SQLBackup文件夹）
WITHNAME = 'TeachingSysDB_Full_Backup',  -- 备份集名称DESCRIPTION = '实验六：教学信息管理系统初始完整备份',  -- 备份描述COMPRESSION,  -- 启用备份压缩（减少文件大小）INIT;  -- 覆盖现有备份文件（首次备份建议用INIT，后续用NOINIT追加）
GO
-- 2. 任务2-2：模拟数据变更（插入3条新选课记录）
USE TeachingSysDB;
GO
INSERT INTO SC(Sno, Cno, Grade)--2023000101
VALUES
('2023010118', 'MA102', 88),  -- 学生2023010108选线性代数（MA102），成绩88
('2023010117', 'MA101', 92),  -- 学生2023010107选高等数学（MA101），成绩92
('2023010116', 'CS102', 85);  -- 学生2023010106选Python编程（CS102），成绩85
GO
-- 验证插入结果
SELECT * FROM SC WHERE Sno IN ('2023010108', '2023010107', '2023010106');
GO
-- 3. 任务2-3：差异备份TeachingSysDB（基于完整备份）
BACKUP DATABASE TeachingSysDB
TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TeachingSysDB_Diff.bak'
WITHNAME = 'TeachingSysDB_Diff_Backup',DESCRIPTION = '实验六：教学信息管理系统差异备份（含3条新选课记录）',COMPRESSION,DIFFERENTIAL,  -- 指定为差异备份（仅备份完整备份后变更的数据）INIT;
GO
PRINT '数据库完整备份与差异备份完成';

错误原因：不满足参照完整性，因为主表Student中缺少该字段的值。

步骤 3：数据库恢复

-- 1. 任务3-1：模拟数据丢失（删除步骤2插入的3条新记录）
USE TeachingSysDB;
GO
DELETE FROM SC
WHERE Sno IN ('2023010108', '2023010107', '2023010106')AND Cno IN ('MA102', 'MA101', 'CS102');
GO
-- 验证数据丢失：查询结果应为空
SELECT * FROM SC WHERE Sno IN ('2023010108', '2023010107', '2023010106');
GO
-- 2. 任务3-2：完整恢复（恢复到初始完整备份状态）
-- 注意：恢复数据库需先断开所有用户连接，设置数据库为单用户模式
USE master;
GO
-- 设置数据库为单用户模式（避免其他连接占用）
ALTER DATABASE TeachingSysDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  -- 立即回滚未完成事务
GO
-- 执行完整恢复
RESTORE DATABASE TeachingSysDB
FROM DISK = 'D:\SQLBackup\TeachingSysDB_Full.bak'
WITHRECOVERY,  -- 恢复后数据库处于可用状态（差异恢复需先设为NORECOVERY）REPLACE,  -- 覆盖现有数据库（谨慎使用，避免误删数据）DESCRIPTION = '实验六：完整恢复到初始备份状态';
GO
-- 恢复后设置数据库为多用户模式
ALTER DATABASE TeachingSysDB
SET MULTI_USER;
GO
-- 验证完整恢复结果：新插入的3条记录未恢复（完整备份不含变更）
USE TeachingSysDB;
GO
SELECT * FROM SC WHERE Sno IN ('2023010108', '2023010107', '2023010106');  -- 结果为空
GO
-- 3. 任务3-3：差异恢复（恢复到差异备份状态）
USE master;
GO
-- 再次设置为单用户模式
ALTER DATABASE TeachingSysDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- 执行差异恢复（需先将完整恢复设为NORECOVERY，此处重新执行完整恢复并加NORECOVERY）
-- 步骤1：重新执行完整恢复，不恢复数据库（为差异恢复做准备）
RESTORE DATABASE TeachingSysDB
FROM DISK = 'D:\SQLBackup\TeachingSysDB_Full.bak'
WITHNORECOVERY,  -- 不恢复数据库，保持“正在恢复”状态，允许后续差异恢复REPLACE;
GO
-- 步骤2：执行差异恢复
RESTORE DATABASE TeachingSysDB
FROM DISK = 'D:\SQLBackup\TeachingSysDB_Diff.bak'
WITHRECOVERY,  -- 差异恢复后恢复数据库，使其可用DESCRIPTION = '实验六：差异恢复到含新选课记录的状态';
GO
-- 恢复后设置为多用户模式
ALTER DATABASE TeachingSysDB
SET MULTI_USER;
GO
-- 4. 任务3-4：恢复后数据校验
USE TeachingSysDB;
GO
-- 查询新插入的3条记录，确认已恢复
SELECT * FROM SC WHERE Sno IN ('2023010108', '2023010107', '2023010106');
GO
-- 对比恢复前后的数据总量（确保无其他数据丢失）
SELECT COUNT(*) AS '恢复后SC表总记录数' FROM SC;
-- （可与差异备份前的总记录数对比，应一致）
PRINT '数据库完整恢复与差异恢复完成，数据校验通过';

五、实验结果与分析

1. 安全性控制结果：
   ◦ 成功创建 3 个 SQL 登录名（Login_Student/Login_Teacher/Login_Admin）及对应数据库用户，登录名密码符合复杂度策略，默认数据库正确指向TeachingSysDB；
   ◦ 自定义角色权限生效：Login_Student仅能查询指定视图，无法修改数据；Login_Teacher可查询多表并修改成绩，但无法删除数据；Login_Admin拥有全库权限，可执行创建 / 删除表等操作，实现了 “最小权限原则” 下的角色隔离。

2. 数据库备份结果：
   ◦ 完整备份文件TeachingSysDB_Full.bak成功生成，大小约等于数据库实际数据量（因启用压缩，文件体积更小）；
   ◦ 差异备份文件TeachingSysDB_Diff.bak仅包含完整备份后插入的 3 条记录，文件体积远小于完整备份，验证了 “差异备份仅备份变更数据” 的特性，适合日常高频备份场景。

3. 数据库恢复结果：
   ◦ 完整恢复后，恢复到故障前的状态。

六、思考题

到此为止，所有实验均以完成，回顾一学期的学习，请思考并回答：数据库管理系统有哪些功能？
数据定义，提供数据定义的语言，如CREATE, ALTER, DROP等，用于建立、修改和删除数据库中的对象，比如表、视图和索引。数据操作，提供数据操作的语言，如SELECT, INSERT, UPDATE, DELETE等，实现对库中数据的增删改查。数据控制与安全，通过权限管理、角色控制和加密等手段保障数据安全性、完整性和并发访问的一致性。数据的存储与维护，确保数据的持久性、可靠性和高效访问。