SeekCyber's Blog

SeekCyber's Blog

SQL Server学习笔记：安全管理

2018年5月15日 · 1409 字

学习背景

在上一篇笔记中，我学习了SQL Server的备份和恢复操作。今天的学习主题是SQL Server的安全管理，这是数据库管理的重要组成部分，能够确保数据库的安全性和完整性。

安全管理概述

1. 安全管理的重要性

安全管理是数据库管理的关键环节，它具有以下重要性：

  • 保护敏感数据：防止未授权访问和数据泄露
  • 确保数据完整性：防止数据被篡改
  • 符合法规要求：满足各种行业法规和合规要求
  • 维护系统稳定：防止恶意攻击和系统故障

2. 安全管理层次

  • 服务器级别安全：管理对SQL Server实例的访问
  • 数据库级别安全：管理对数据库的访问
  • 对象级别安全：管理对数据库对象（如表、视图、存储过程等）的访问
  • 列级别安全：管理对表中特定列的访问
  • 行级别安全：管理对表中特定行的访问

服务器级别安全

1. 登录名

创建登录名

-- 创建SQL Server身份验证登录名
CREATE LOGIN [JohnDoe]
WITH PASSWORD = 'StrongPassword123!',
     DEFAULT_DATABASE = TestDB,
     CHECK_POLICY = ON,
     CHECK_EXPIRATION = ON;
GO

-- 创建Windows身份验证登录名
CREATE LOGIN [DOMAIN\JohnDoe]
FROM WINDOWS
WITH DEFAULT_DATABASE = TestDB;
GO

修改登录名

-- 修改登录名密码
ALTER LOGIN [JohnDoe]
WITH PASSWORD = 'NewStrongPassword456!';
GO

-- 启用登录名
ALTER LOGIN [JohnDoe]
ENABLE;
GO

-- 禁用登录名
ALTER LOGIN [JohnDoe]
DISABLE;
GO

删除登录名

DROP LOGIN [JohnDoe];
GO

2. 服务器角色

  • sysadmin：具有SQL Server的所有权限
  • serveradmin：可以更改服务器范围的配置选项和关闭服务器
  • dbcreator：可以创建、更改、删除和还原任何数据库
  • diskadmin：可以管理磁盘文件
  • processadmin：可以终止在SQL Server实例中运行的进程
  • securityadmin：可以管理登录名及其属性
  • setupadmin：可以添加和删除链接服务器
  • bulkadmin：可以执行BULK INSERT语句

分配服务器角色

ALTER SERVER ROLE [dbcreator]
ADD MEMBER [JohnDoe];
GO

数据库级别安全

1. 用户

创建用户

-- 基于登录名创建用户
CREATE USER [JohnDoe]
FOR LOGIN [JohnDoe]
WITH DEFAULT_SCHEMA = dbo;
GO

-- 创建无登录名的用户（用于应用程序角色）
CREATE USER [AppUser]
WITHOUT LOGIN;
GO

修改用户

-- 修改用户默认架构
ALTER USER [JohnDoe]
WITH DEFAULT_SCHEMA = Sales;
GO

删除用户

DROP USER [JohnDoe];
GO

2. 数据库角色

固定数据库角色

  • db_owner：具有数据库的所有权限
  • db_securityadmin：可以管理数据库角色和成员资格
  • db_accessadmin：可以添加或删除用户对数据库的访问权限
  • db_backupoperator：可以备份数据库
  • db_ddladmin：可以执行任何数据定义语言(DDL)语句
  • db_datawriter：可以添加、删除或修改表中的数据
  • db_datareader：可以读取所有用户表中的数据
  • db_denydatawriter：不能添加、删除或修改表中的数据
  • db_denydatareader：不能读取所有用户表中的数据

分配数据库角色

ALTER ROLE [db_datareader]
ADD MEMBER [JohnDoe];
GO

ALTER ROLE [db_datawriter]
ADD MEMBER [JohnDoe];
GO

创建自定义数据库角色

CREATE ROLE [SalesRole];
GO

-- 为角色分配权限
GRANT SELECT, INSERT, UPDATE ON Sales.Orders TO [SalesRole];
GO

-- 添加成员到角色
ALTER ROLE [SalesRole]
ADD MEMBER [JohnDoe];
GO

对象级别安全

1. 权限管理

授予权限

-- 授予对表的权限
GRANT SELECT, INSERT, UPDATE ON Employees TO [JohnDoe];
GO

-- 授予对存储过程的权限
GRANT EXECUTE ON GetEmployeeInfo TO [JohnDoe];
GO

拒绝权限

-- 拒绝对表的权限
DENY DELETE ON Employees TO [JohnDoe];
GO

撤销权限

-- 撤销对表的权限
REVOKE INSERT ON Employees FROM [JohnDoe];
GO

2. 架构

创建架构

CREATE SCHEMA Sales;
GO

移动对象到架构

ALTER SCHEMA Sales
TRANSFER dbo.Orders;
GO

删除架构

DROP SCHEMA Sales;
GO

高级安全特性

1. 行级别安全

创建行级别安全策略

-- 创建函数定义行访问逻辑
CREATE FUNCTION Sales.fn_securitypredicate(@Department AS NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @Department = USER_NAME() OR USER_NAME() = 'dbo';
GO

-- 创建行级别安全策略
CREATE SECURITY POLICY Sales.SalesSecurityPolicy
ADD FILTER PREDICATE Sales.fn_securitypredicate(Department)
ON Sales.Orders
WITH (STATE = ON);
GO

2. 动态数据掩码

创建动态数据掩码

-- 创建带掩码的表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    Phone NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0, "xxx-xxx-", 4)'),
    CreditCard NVARCHAR(16) MASKED WITH (FUNCTION = 'default()')
);
GO

-- 授予掩码权限
GRANT UNMASK TO [JohnDoe];
GO

3. 透明数据加密 (TDE)

启用TDE

-- 创建数据库主密钥
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKeyPassword123!';
GO

-- 创建证书
CREATE CERTIFICATE TDECertificate
WITH SUBJECT = 'TDE Certificate';
GO

-- 创建数据库加密密钥
USE TestDB;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate;
GO

-- 启用TDE
ALTER DATABASE TestDB
SET ENCRYPTION ON;
GO

实践练习

练习1：安全配置

  1. 创建SQL Server身份验证登录名
  2. 创建数据库用户并分配适当的权限
  3. 创建自定义数据库角色并分配权限

练习2：高级安全特性

  1. 实现行级别安全，限制用户只能访问自己部门的数据
  2. 实现动态数据掩码，保护敏感数据
  3. 启用透明数据加密，保护数据库文件

总结

通过今天的学习，我掌握了SQL Server的安全管理功能。安全管理是数据库管理的重要组成部分，能够确保数据库的安全性和完整性。在实践过程中，我了解到SQL Server提供了多层次的安全机制，从服务器级别到行级别，可以根据实际需求灵活配置。接下来，我将学习SQL Server的性能调优，提高数据库的运行效率。