SQL Server 安全加密篇(一) ->> 证书密钥加密

SQL Server加密是指通过使用密钥或密码对数据进行模糊处理的过程,可以在SQL Server中对连接、数据和存储过程使用加密。SQL Server 2005之前是不支持数据库加密的,从2005开始可以对数据库的列进行数据加密。

 

我觉得要理解SQL Server数据加密应该从下面几个步骤去理解:

1、数据加密的层次结构是什么?

2、证书、对称密钥和非对称密钥的区别

3、加密算法有哪些?

4、T-SQL编程实现数据加密(包括数据库函数、加密解密编程)

 

数据加密的层次结构

SQL Server 使用分层加密和密钥管理基础结构来加密数据。 每一层都使用证书、非对称密钥和对称密钥的组合对它下面的一层进行加密。

 

下面这个图是MSDN在线文档截来的

 

 

第一层:数据库实例在安装的时候就会生成一个服务主密钥(Service Master Key),本身由Windows提供的数据保护API进行保护(Data Pertection API)。它除了提供后面数据库级别的加密服务外,还提供一些实例级别的加密功能,比如实例的登录名密码或者链接服务器的信息。

第二层:数据库主密钥(Database Master Key)。服务主密钥和所有数据库主密钥是对称密钥。

第三层:证书\对称密钥\非对称密钥,对称密钥或非对称密钥保存在 SQL Server 外部的可扩展密钥管理 (EKM) 模块。对称密钥的加密解密性能肯定是大大好于证书或非对称密钥,所以加密数据应该选择对称密钥,然后用非对称密钥或证书加密对称密钥来达到安全性和性能的平衡。

 

 总结:

1、加密层次也是由实例->数据库->数据这样一层层提供保护的

2、非对称密钥由于加密解密的复杂度使得它对CPU资源的消耗巨大,不适合用于直接加密数据,应该是选择对称密钥来加密数据,然后用非对称密钥来加密对称密钥;

 

证书、对称密钥和非对称密钥的区别

 

证书

证书加密是通过CA签名颁发公钥证书,且每个证书都是有有效期限。证书签名用于证明主体的公钥和标识符信息之间的绑定的有效性。

SQL Server创建证书有几种方式:

1、自签名生成;

2、外部导入公钥证书pfx文件,指定加密密码对证书进行保护

  2.1 外部导入公钥证书pfx文件,同时指定pvk私钥文件路径

 

自签名例子:

CREATE CERTIFICATE Shipping04   
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
   WITH SUBJECT = 'Sammamish Shipping Records',   
   EXPIRY_DATE = '20201031';  

 

外部导入CER公钥和PVK私钥文件例子,同时指定私钥文件解密密码

CREATE CERTIFICATE Shipping11   
    FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'   
    WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',   
    DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');  

 

通过带有私钥的PFX证书文件创建证书,同时指定私钥文件解密密码

CREATE CERTIFICATE Shipping04
    FROM FILE = 'c:\storedcerts\shipping04cert.pfx'
    WITH 
    FORMAT = 'PFX', 
    PRIVATE KEY (
        DECRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh'
    );  

 

非对称密钥

“非对称密钥”是数据库级的安全对象实体。 该实体的默认格式包含公钥和私钥。 当未使用 FROM 子句执行时,CREATE ASYMMETRIC KEY 会生成新的密钥对。 使用 FROM 子句执行时,CREATE ASYMMETRIC KEY 会从文件中导入密钥对。如果没有指定ENCRYPTION BY PASSWORD,私钥受数据库主密钥保护。非对称密钥的算法只支持RSA,只是不同的长度。

 

CREATE ASYMMETRIC KEY PacificSales09   
    WITH ALGORITHM = RSA_2048   
    ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>';   

 

对称密钥

对称密钥可以由证书、非对称密钥或密码加密。对称密钥加密算法有DES、3DES和AES。推荐AES。

 

CREATE SYMMETRIC KEY JanainaKey09   
WITH ALGORITHM = AES_256  
ENCRYPTION BY CERTIFICATE Shipping04;  

 

 加密算法有哪些

SQL Server支持DES、Triple DES、TRIPLE_DES_3KEY、RC2、RC4、128 位 RC4、DESX、128 位 AES、192 位 AES 和 256 位 AES。从 SQL Server 2016 (13.x)开始,除 AES_128、AES_192 和 AES_256 以外的所有算法都已过时。 若要使用旧算法(不推荐),必须将数据库设置为兼容级别 120 或更低。所以对称加密算法推荐AES且128以上长度,非对称算法使用RSA。

 

T-SQL编程实现数据加密(包括数据库函数、加密解密编程)

 

SQL Server在2005引入了列加密的功能。使得可以利用证书,对称密钥和非对称密钥对特定的列进行加密。在具体的实现上,根据加密解密的方式不同,内置了4对函数用于加密解密:

1. EncryptByCert()  和DecryptByCert()—利用证书对数据进行加密和解密

2. EncryptByAsymKey()  and DecryptByAsymKey()—利用非对称密钥对数据进行加密和解密

3. EncryptByKey()  and DecryptByKey()—利用对称密钥对数据进行加密和解密

4. EncryptByPassphrase()  and DecryptByPassphrase()—利用密码字段产生对称密钥对数据进行加密和解密

 

对称密钥+证书的加密例子

USE AdventureWorks2019
GO

--创建证书,指定证书加密密码,指定过期日期。如果下面不指定密码,意味着证书由数据库主密钥保护,后面的OPEN SYMMETRIC KEY的时候就不需要指定PASSWORD了
CREATE CERTIFICATE Test_Certficate
ENCRYPTION BY PASSWORD = 'mypassword'  
WITH SUBJECT = 'for data encryption',   
EXPIRY_DATE = '2025/12/31';  
GO

--创建对称密钥,通过证书加密,加密算法用AES,长度256
CREATE SYMMETRIC KEY SymmetrickKeyForCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Test_Certficate;
GO

DROP TABLE IF EXISTS dbo.test_table_for_encrpt
GO

--创建测试表
CREATE TABLE dbo.test_table_for_encrpt
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    mobile VARBINARY(8000),
    mobile2 VARBINARY(8000)
)
GO

/*使用对称密钥之前(也就是解密和加密),都需要用OPEN SYMMETRIC KEY语句先打开对称密钥,
这里如果你没有对称密钥的使用权限你是没办法解密数据的
*/
OPEN SYMMETRIC KEY SymmetrickKeyForCert
DECRYPTION BY CERTIFICATE Test_Certficate
WITH PASSWORD='mypassword'

-- 加密是区分UNICODE和非UNICODE
INSERT INTO dbo.test_table_for_encrpt(mobile,mobile2)
SELECT ENCRYPTBYKEY(KEY_GUID('SymmetrickKeyForCert'),'13715918123')
,ENCRYPTBYKEY(KEY_GUID('SymmetrickKeyForCert'),N'13715918123')

-- 这里可以看到,如果CONVERT的数据类型错了,解密出来的文本是不对的
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(mobile)),CONVERT(NVARCHAR, DECRYPTBYKEY(mobile2)),
CONVERT(NVARCHAR, DECRYPTBYKEY(mobile)),CONVERT(VARCHAR, DECRYPTBYKEY(mobile2))
FROM dbo.test_table_for_encrpt

 

结果

 

 

非对称密钥加密例子

USE AdventureWorks2019
GO

--创建数据库主密钥,不然后面创建非对称密钥就需要指定密码,不指定密码就默认由数据库主密钥保护
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword';
GO

--创建非对称密钥
CREATE ASYMMETRIC KEY Test_AsymKey
WITH ALGORITHM = RSA_2048
GO

--创建对称密钥,通过非对称密钥加密,加密算法用AES,长度256
CREATE SYMMETRIC KEY SymmetrickKeyForAsymKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY Test_AsymKey;
GO

DROP TABLE IF EXISTS dbo.test_table_for_encrpt2
GO

--创建测试表
CREATE TABLE dbo.test_table_for_encrpt2
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    mobile VARBINARY(8000),
    mobile2 VARBINARY(8000)
)
GO

/*使用对称密钥之前(也就是解密和加密),都需要用OPEN SYMMETRIC KEY语句先打开对称密钥,
这里如果你没有对称密钥的使用权限你是没办法解密数据的
*/
OPEN SYMMETRIC KEY SymmetrickKeyForAsymKey
DECRYPTION BY ASYMMETRIC KEY Test_AsymKey


-- 加密是区分UNICODE和非UNICODE
INSERT INTO dbo.test_table_for_encrpt2(mobile,mobile2)
SELECT ENCRYPTBYKEY(KEY_GUID('SymmetrickKeyForAsymKey'),'13715918123')
,ENCRYPTBYKEY(KEY_GUID('SymmetrickKeyForAsymKey'),N'13715918123')
--SELECT ENCRYPTBYCERT(CERT_ID('Test_Certficate'),'13715918123')

-- 这里可以看到,如果CONVERT的数据类型错了,解密出来的文本是不对的
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(mobile)),CONVERT(NVARCHAR, DECRYPTBYKEY(mobile2)),
CONVERT(NVARCHAR, DECRYPTBYKEY(mobile)),CONVERT(VARCHAR, DECRYPTBYKEY(mobile2))
FROM dbo.test_table_for_encrpt2

 

 结果

 

 

 

参考:

加密层次结构

透明数据加密 (TDE)

加密密钥

加密算法

可扩展的密钥管理 (EKM)

CREATE CERTIFICATE (Transact-SQL)

CREATE ASYMMETRIC KEY (Transact-SQL)

OPEN SYMMETRIC KEY (Transact-SQL)

ENCRYPTBYKEY (Transact-SQL)

DECRYPTBYKEY (Transact-SQL)

CREATE ASYMMETRIC KEY (Transact-SQL)

CREATE MASTER KEY (Transact-SQL)

CREATE CRYPTOGRAPHIC PROVIDER (Transact-SQL)

使用 EKM 在 SQL Server 上启用 TDE

使用 Azure 密钥保管库设置 SQL Server TDE 可扩展密钥管理

CREATE CRYPTOGRAPHIC PROVIDER (Transact-SQL)

posted @ 2022-05-30 18:25  Jerry_Chen  阅读(2259)  评论(0编辑  收藏  举报