SQLServer教程FG032-SQLServer权限安全实战

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

内容简介

本文档基于SQLServer官方文档的权限安全内容，结合生产环境实际情况，详细讲解SQLServer权限的管理、配置、审计等内容。风哥教程参考SQLServer官方文档Security、Permissions等相关章节。

Part01-基础概念与理论知识

1.1 SQLServer权限安全概念

SQLServer权限安全的概念：

权限安全是保护SQLServer数据库资源的重要手段
包括身份验证、授权和审计三个方面
身份验证确认用户身份
授权确定用户可以执行的操作
审计记录用户的操作

更多视频教程www.fgedu.net.cn

1.2 SQLServer权限体系

SQLServer权限体系：

服务器级别权限：控制服务器级别的操作
数据库级别权限：控制数据库级别的操作
对象级别权限：控制数据库对象的操作
语句级别权限：控制特定SQL语句的执行

学习交流加群风哥微信: itpux-com

1.3 SQLServer安全原则

SQLServer安全原则：

最小权限原则：只授予用户必要的权限
职责分离原则：不同职责的用户使用不同的账户
定期审计原则：定期检查权限配置
密码安全原则：使用强密码并定期更换
加密原则：对敏感数据进行加密

学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 SQLServer权限规划原则

权限规划原则：

根据业务需求确定权限需求
使用角色管理权限，避免直接授予用户权限
定期审查权限配置
建立权限变更流程
记录权限变更历史

风哥提示：生产环境应使用角色管理权限，便于维护和审计

2.2 SQLServer角色设计

角色设计：

服务器角色：sysadmin、serveradmin、securityadmin等
数据库角色：db_owner、db_datareader、db_datawriter等
自定义角色：根据业务需求创建

更多学习教程公众号风哥教程itpux_com

2.3 SQLServer权限最小化原则

权限最小化原则：

只授予用户必要的权限
避免使用sysadmin权限
定期审查和回收不必要的权限
使用应用程序角色或存储过程限制权限

from SQLServer视频:www.itpux.com

Part03-生产环境项目实施方案

3.1 SQLServer用户管理

用户管理：

— 步骤1：创建登录名
— 1. 创建SQL Server登录名
CREATE LOGIN fgedu_admin WITH PASSWORD = ‘Password123!’, CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
GO

— 2. 创建Windows登录名
CREATE LOGIN [fgedu\admin] FROM WINDOWS;
GO

— 步骤2：创建数据库用户
— 1. 创建数据库用户
USE fgedudb;
GO

CREATE USER fgedu_admin FOR LOGIN fgedu_admin;
GO

— 2. 创建数据库用户并映射到登录名
CREATE USER [fgedu\admin] FOR LOGIN [fgedu\admin];
GO

— 步骤3：管理用户
— 1. 修改用户密码
ALTER LOGIN fgedu_admin WITH PASSWORD = ‘NewPassword123!’;
GO

— 2. 启用/禁用登录名
ALTER LOGIN fgedu_admin DISABLE;
GO

ALTER LOGIN fgedu_admin ENABLE;
GO

— 3. 删除登录名
DROP LOGIN fgedu_admin;
GO

— 4. 删除数据库用户
USE fgedudb;
GO

DROP USER fgedu_admin;
GO

执行结果：

Commands completed successfully.

3.2 SQLServer角色管理

角色管理：

— 步骤1：使用系统角色
— 1. 将用户添加到服务器角色
ALTER SERVER ROLE serveradmin ADD MEMBER fgedu_admin;
GO

— 2. 将用户添加到数据库角色
USE fgedudb;
GO

ALTER ROLE db_owner ADD MEMBER fgedu_admin;
GO

— 步骤2：创建自定义角色
— 1. 创建服务器角色
CREATE SERVER ROLE [ServerMonitor] AUTHORIZATION [securityadmin];
GO

— 2. 创建数据库角色
USE fgedudb;
GO

CREATE ROLE [DataReader] AUTHORIZATION [db_owner];
GO

— 步骤3：管理角色
— 1. 向角色添加权限
GRANT SELECT ON fgedu.large_table TO [DataReader];
GO

— 2. 从角色中移除权限
REVOKE SELECT ON fgedu.large_table FROM [DataReader];
GO

— 3. 添加用户到角色
ALTER ROLE [DataReader] ADD MEMBER fgedu_admin;
GO

— 4. 从角色中移除用户
ALTER ROLE [DataReader] DROP MEMBER fgedu_admin;
GO

— 5. 删除角色
DROP ROLE [DataReader];
GO

— 6. 删除服务器角色
DROP SERVER ROLE [ServerMonitor];
GO

执行结果：

Commands completed successfully.

3.3 SQLServer权限管理

权限管理：

— 步骤1：授予权限
— 1. 授予服务器级别权限
GRANT VIEW SERVER STATE TO fgedu_admin;
GO

— 2. 授予数据库级别权限
USE fgedudb;
GO

GRANT CREATE TABLE TO fgedu_admin;
GO

— 3. 授予对象级别权限
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu.large_table TO fgedu_admin;
GO

— 4. 授予列级别权限
GRANT SELECT ON fgedu.large_table(col1, col2) TO fgedu_admin;
GO

— 步骤2：拒绝权限
— 1. 拒绝对象级别权限
DENY DELETE ON fgedu.large_table TO fgedu_admin;
GO

— 步骤3：回收权限
— 1. 回收对象级别权限
REVOKE INSERT, UPDATE ON fgedu.large_table FROM fgedu_admin;
GO

— 步骤4：查看权限
— 1. 查看服务器级别权限
SELECT
p.name AS principal_name,
p.type_desc,
perm.permission_name,
perm.state_desc
FROM sys.server_principals p
JOIN sys.server_permissions perm ON p.principal_id = perm.grantee_principal_id
WHERE p.name = ‘fgedu_admin’;
GO

— 2. 查看数据库级别权限
USE fgedudb;
GO

SELECT
p.name AS principal_name,
p.type_desc,
perm.permission_name,
perm.state_desc
FROM sys.database_principals p
JOIN sys.database_permissions perm ON p.principal_id = perm.grantee_principal_id
WHERE p.name = ‘fgedu_admin’;
GO

— 3. 查看对象级别权限
USE fgedudb;
GO

SELECT
p.name AS principal_name,
o.name AS object_name,
perm.permission_name,
perm.state_desc
FROM sys.database_principals p
JOIN sys.database_permissions perm ON p.principal_id = perm.grantee_principal_id
JOIN sys.objects o ON perm.major_id = o.object_id
WHERE p.name = ‘fgedu_admin’;
GO

执行结果：

Commands completed successfully.

principal_name permission_name state_desc
————– —————– ———–
fgedu_admin VIEW SERVER STATE GRANT

(1 row affected)

principal_name permission_name state_desc
————– ———————— ———–
fgedu_admin CREATE TABLE GRANT

(1 row affected)

principal_name object_name permission_name state_desc
————– ————- ———————— ———–
fgedu_admin large_table SELECT GRANT
fgedu_admin large_table DELETE DENY

(2 rows affected)

Part04-生产案例与实战讲解

4.1 SQLServer权限配置案例

权限配置实战：

— 案例：配置SQLServer权限
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb

— 步骤1：创建登录名和用户
— 1. 创建管理员登录名
CREATE LOGIN fgedu_admin WITH PASSWORD = ‘Password123!’, CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
GO

— 2. 创建开发人员登录名
CREATE LOGIN fgedu_dev WITH PASSWORD = ‘Password123!’, CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
GO

— 3. 创建只读用户登录名
CREATE LOGIN fgedu_read WITH PASSWORD = ‘Password123!’, CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
GO

— 4. 创建数据库用户
USE fgedudb;
GO

CREATE USER fgedu_admin FOR LOGIN fgedu_admin;
CREATE USER fgedu_dev FOR LOGIN fgedu_dev;
CREATE USER fgedu_read FOR LOGIN fgedu_read;
GO

— 步骤2：创建自定义角色
— 1. 创建开发人员角色
CREATE ROLE [Developer] AUTHORIZATION [db_owner];
GO

— 2. 创建只读角色
CREATE ROLE [ReadOnly] AUTHORIZATION [db_owner];
GO

— 步骤3：配置角色权限
— 1. 配置开发人员角色权限
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu.large_table TO [Developer];
GRANT EXECUTE ON fgedu.sp_test TO [Developer];
GO

— 2. 配置只读角色权限
GRANT SELECT ON fgedu.large_table TO [ReadOnly];
GO

— 步骤4：添加用户到角色
— 1. 添加开发人员到角色
ALTER ROLE [Developer] ADD MEMBER fgedu_dev;
GO

— 2. 添加只读用户到角色
ALTER ROLE [ReadOnly] ADD MEMBER fgedu_read;
GO

— 3. 添加管理员到db_owner角色
ALTER ROLE db_owner ADD MEMBER fgedu_admin;
GO

— 步骤5：验证权限配置
— 1. 查看用户权限
SELECT
p.name AS principal_name,
r.name AS role_name
FROM sys.database_principals p
JOIN sys.database_role_members rm ON p.principal_id = rm.member_principal_id
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE p.name IN (‘fgedu_admin’, ‘fgedu_dev’, ‘fgedu_read’);
GO

— 2. 测试权限
— 使用fgedu_read登录执行
SELECT * FROM fgedu.large_table;
— 应该成功

INSERT INTO fgedu.large_table (id, col1, col2) VALUES (1, ‘test’, 1);
— 应该失败

— 使用fgedu_dev登录执行
SELECT * FROM fgedu.large_table;
— 应该成功

INSERT INTO fgedu.large_table (id, col1, col2) VALUES (1, ‘test’, 1);
— 应该成功

执行结果：

Commands completed successfully.

principal_name role_name
————– ————–
fgedu_admin db_owner
fgedu_dev Developer
fgedu_read ReadOnly

(3 rows affected)

— fgedu_read测试结果：
— SELECT成功
— INSERT失败，错误信息：权限不足

— fgedu_dev测试结果：
— SELECT成功
— INSERT成功

4.2 SQLServer权限审计案例

权限审计实战：

— 案例：审计SQLServer权限
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb

— 步骤1：启用审计
— 1. 创建服务器审计
CREATE SERVER AUDIT [PermissionAudit] TO FILE (FILEPATH = ‘E:\SQLServer\Audit\’);
GO

— 2. 启用服务器审计
ALTER SERVER AUDIT [PermissionAudit] WITH (STATE = ON);
GO

— 3. 创建数据库审计规范
USE fgedudb;
GO

CREATE DATABASE AUDIT SPECIFICATION [PermissionAuditSpec]
FOR SERVER AUDIT [PermissionAudit]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP);
GO

— 4. 启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [PermissionAuditSpec] WITH (STATE = ON);
GO

— 步骤2：执行权限操作
— 1. 创建用户
CREATE LOGIN fgedu_audit WITH PASSWORD = ‘Password123!’;
GO

CREATE USER fgedu_audit FOR LOGIN fgedu_audit;
GO

— 2. 授予权限
GRANT SELECT ON fgedu.large_table TO fgedu_audit;
GO

— 3. 回收权限
REVOKE SELECT ON fgedu.large_table FROM fgedu_audit;
GO

— 4. 删除用户
DROP USER fgedu_audit;
GO

DROP LOGIN fgedu_audit;
GO

— 步骤3：查看审计日志
— 1. 使用SQL Server Management Studio查看审计日志
— 2. 或使用T-SQL查询审计日志
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\PermissionAudit_*.sqlaudit’, DEFAULT, DEFAULT);
GO

— 步骤4：创建权限审计作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’AuditPermissionChanges’,
@enabled = 1,
@description = N’审计权限变更’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’AuditPermissionChanges’,
@step_name = N’Check Permission Changes’,
@subsystem = N’TSQL’,
@command = N’
— 查看最近的权限变更
SELECT
TOP 100
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(”E:\SQLServer\Audit\PermissionAudit_*.sqlaudit”, DEFAULT, DEFAULT)
ORDER BY event_time DESC;
‘,
@database_name = N’master’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’AuditPermissionChanges’,
@name = N’Daily’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 000000,
@active_end_time = 235959;
GO

执行结果：

Commands completed successfully.

event_time action_id succeeded server_principal_name database_principal_name object_name statement
————————- ——– ——— ——————– ——————— ————- ——————————————————————————–
2025-04-08 10:00:00.000 GDR 1 sa NULL NULL CREATE LOGIN fgedu_audit WITH PASSWORD = ‘********’, CHECK_POLICY = ON
2025-04-08 10:00:01.000 ADDMEMBER 1 sa fgedu_audit NULL CREATE USER fgedu_audit FOR LOGIN fgedu_audit
2025-04-08 10:00:02.000 GRANT 1 sa fgedu_audit large_table GRANT SELECT ON fgedu.large_table TO fgedu_audit
2025-04-08 10:00:03.000 REVOKE 1 sa fgedu_audit large_table REVOKE SELECT ON fgedu.large_table FROM fgedu_audit
2025-04-08 10:00:04.000 DROP 1 sa NULL fgedu_audit DROP USER fgedu_audit
2025-04-08 10:00:05.000 DROP 1 sa NULL NULL DROP LOGIN fgedu_audit

(6 rows affected)

(1 row affected)

4.3 SQLServer权限故障处理案例

权限故障处理实战：

— 案例：权限故障处理
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb

— 场景1：用户无法登录
— 问题：用户fgedu_user无法登录SQLServer
— 解决方案：
— 1. 检查登录名状态
SELECT
name,
is_disabled,
is_expired,
is_locked
FROM sys.server_principals
WHERE name = ‘fgedu_user’;
GO

— 2. 如果登录名被禁用，启用它
ALTER LOGIN fgedu_user ENABLE;
GO

— 3. 如果密码过期，重置密码
ALTER LOGIN fgedu_user WITH PASSWORD = ‘NewPassword123!’, CHECK_POLICY = OFF;
GO

— 场景2：用户没有足够的权限
— 问题：用户fgedu_user无法执行SELECT操作
— 解决方案：
— 1. 检查用户权限
USE fgedudb;
GO

SELECT
p.name AS principal_name,
perm.permission_name,
perm.state_desc
FROM sys.database_principals p
JOIN sys.database_permissions perm ON p.principal_id = perm.grantee_principal_id
WHERE p.name = ‘fgedu_user’;
GO

— 2. 授予必要的权限
GRANT SELECT ON fgedu.large_table TO fgedu_user;
GO

— 场景3：角色权限问题
— 问题：角色[Developer]没有执行存储过程的权限
— 解决方案：
— 1. 检查角色权限
USE fgedudb;
GO

SELECT
r.name AS role_name,
perm.permission_name,
perm.state_desc
FROM sys.database_principals r
JOIN sys.database_permissions perm ON r.principal_id = perm.grantee_principal_id
WHERE r.name = ‘Developer’;
GO

— 2. 授予执行权限
GRANT EXECUTE ON fgedu.sp_test TO [Developer];
GO

— 场景4：权限继承问题
— 问题：用户属于多个角色，权限冲突
— 解决方案：
— 1. 检查用户所属的角色
USE fgedudb;
GO

SELECT
p.name AS principal_name,
r.name AS role_name
FROM sys.database_principals p
JOIN sys.database_role_members rm ON p.principal_id = rm.member_principal_id
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE p.name = ‘fgedu_user’;
GO

— 2. 调整角色成员关系
ALTER ROLE [Role1] DROP MEMBER fgedu_user;
GO

— 场景5：权限审计问题
— 问题：需要查看谁修改了权限
— 解决方案：
— 1. 查看审计日志
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\PermissionAudit_*.sqlaudit’, DEFAULT, DEFAULT)
WHERE statement LIKE ‘%GRANT%’ OR statement LIKE ‘%REVOKE%’ OR statement LIKE ‘%DENY%’
ORDER BY event_time DESC;
GO

执行结果：

— 场景1结果：
name is_disabled is_expired is_locked
———- ———– ———- ———
fgedu_user 1 0 0

Commands completed successfully.

— 场景2结果：
principal_name permission_name state_desc
————– ———————— ———–

Commands completed successfully.

— 场景3结果：
role_name permission_name state_desc
———- ———————— ———–
Developer SELECT GRANT
Developer INSERT GRANT
Developer UPDATE GRANT
Developer DELETE GRANT

Commands completed successfully.

— 场景4结果：
principal_name role_name
————– ———-
fgedu_user Developer
fgedu_user ReadOnly

Commands completed successfully.

— 场景5结果：
event_time action_id succeeded server_principal_name database_principal_name object_name statement
————————- ——– ——— ——————– ——————— ————- ——————————————————————————–
2025-04-08 10:00:00.000 GRANT 1 sa fgedu_user large_table GRANT SELECT ON fgedu.large_table TO fgedu_user

(1 row affected)

Part05-风哥经验总结与分享

5.1 SQLServer权限安全最佳实践

使用角色管理权限，避免直接授予用户权限
遵循最小权限原则，只授予必要的权限
定期审查权限配置，回收不必要的权限
启用审计，记录权限变更和访问
使用强密码并定期更换
限制sysadmin权限的使用
使用Windows身份验证优先于SQL Server身份验证
定期备份权限配置

5.2 SQLServer权限常见问题

权限不足：原因是用户没有被授予必要的权限，解决方法是授予相应的权限
权限冲突：原因是用户属于多个角色，权限设置冲突，解决方法是调整角色成员关系
密码过期：原因是密码策略设置了过期时间，解决方法是重置密码
登录名被禁用：原因是登录名被手动禁用或违反了密码策略，解决方法是启用登录名
权限审计缺失：原因是没有启用审计，解决方法是配置审计

5.3 SQLServer权限安全加固

启用SQL Server密码策略
限制SQL Server身份验证的使用
使用Windows组管理权限
定期审查和清理未使用的登录名和用户
使用应用程序角色限制权限
配置数据库级别的权限审核
使用透明数据加密(TDE)保护数据
定期进行安全评估和渗透测试

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

SQLServer教程FG032-SQLServer权限安全实战

目录大纲

内容简介

Part01-基础概念与理论知识

1.1 SQLServer权限安全概念

1.2 SQLServer权限体系

1.3 SQLServer安全原则

Part02-生产环境规划与建议

2.1 SQLServer权限规划原则

2.2 SQLServer角色设计

2.3 SQLServer权限最小化原则

Part03-生产环境项目实施方案

3.1 SQLServer用户管理

3.2 SQLServer角色管理

3.3 SQLServer权限管理

Part04-生产案例与实战讲解

4.1 SQLServer权限配置案例

4.2 SQLServer权限审计案例

4.3 SQLServer权限故障处理案例

Part05-风哥经验总结与分享

5.1 SQLServer权限安全最佳实践

5.2 SQLServer权限常见问题

5.3 SQLServer权限安全加固

相关推荐

联系我们