数据加密第一篇：值加密

2020 年 5 月 8 日
筆記
encryption, Security, SQL Server 安全

加密是保护数据的一个方法，用户只有拿到密钥和证书把数据解密之后，才能使用数据。如果没有密钥和证书，即使得到了数据，也无法得知数据的原始值，数据就没有价值了。由于数据的加密和解密是一种资源密集性（特别是CPU和I/O ）的操作，同时加密后的数据量会增大，因此，往往会带来查询性能的下降，一般只会对敏感数据加密。SQL Server提供的加密方式主要是值级加密（Cell Level）和文件级别加密（TDE）。

SQL Server提供的加密机制主要有：

加密函数
非对称键（Asymmetric keys）和对称键（Symmetric keys）
凭证（Certificates）
透明数据加密（Transparent Data Encryption，简称TDE）

一，加密算法

加密算法用于把数据转换成密文，从SQL Server 2016 (13.x)开始，除了AES_128, AES_192 和 AES_256 之外，其他加密算法都已经过时了。

在选择加密机制时，可以借鉴一些成功的经验：

强的加密通常意味着消耗更多的CPU资源；
长的密钥比短密钥的加密效果更好；
长密码比短密码的加密效果更好
非对称加密比对称加密更慢，但是加密效果更好；
如果要加密大量的数据，推荐使用对称密钥加密数据，然后使用非对称密钥加密该非对称密钥。
密文不能被加密，但是，压缩后的数据可以被加密，推荐在压缩后再加密数据。

二，使用密码或短语来加密数据

对于单个值，可以使用TSQL函数EncryptByPassPhrase()进行加密，使用DecryptByPassPhrase()进行解密。传递一个短语作为密码，使用128bit键长的TRIPLE DES 算法进行加密。

EncryptByPassPhrase ( 'passphrase', 'cleartext' [ , add_authenticator , authenticator ] )
DecryptByPassPhrase ( 'passphrase', 'ciphertext' [ , add_authenticator ,authenticator  ] )

参数注释：

passphrase：字符串，用于生成对称键
cleartext：需要加密的字符串，数据类型可以是：nvarchar, char, varchar, binary, varbinary, 或 nchar，不能超过8000Bytes。
ciphertext：密文，是加密之后的数据，数据类型是varbinary。
add_authenticator：布尔值，是否把authenticatory和cleartext以及加密。如果是1，authenticator参数必须有值。默认值是0，可以省略参数add_authenticator和authenticator。
authenticator：sysname类型，用于指示身份验证者

返回值：这两个函数的返回值都是varbinary，最大长度是8000Bytes。

举个例子，使用这两个函数对数据进行加密和解密：

declare @cipher varbinary(8000)
select @cipher=Encryptbypassphrase(N'悦光阴','A good man')
select @cipher as EncryptedText,cast(Decryptbypassphrase(N'悦光阴',@cipher) as varchar(128)) as DecryptedText

三，密钥和证书

密钥分为对称密钥和非对称密钥。

对称密钥（Symmetric Keys）是指加密和解密的过程使用相同的算法，是加密中最弱的算法，但是性能最好。对于对称密钥，可以使用密码或者另一个密钥甚至一个证书来加密。
非对称密钥（Asymmetric Keys）使用一对密钥（算法），一个密钥用于加密，另一个密钥用于解密，加密的密钥称为私钥（private key），解密的密钥称为公钥（public key）。

证书（Certificates）全称是公钥证书，是一种数字签名，它把公钥绑定到拥有相应私钥的个人身份。证书由证书颁发机构（CA）颁发和签名。从CA接收证书的实体是证书主题，证书主题拥有以下信息：

公钥
证书的标识信息
证书的有效期
发行者标识符信息。
发行人的数字签名。

证书的主要好处是，使管理员避免了对每一个实体都需要维护一个密码的，取而代之，管理员仅建立对证书颁发者的信任，然后证书颁发者可以签署无限数量的证书。

1，创建证书

管理员使用CREATE CERTIFICATE命令来创建证书：

CREATE CERTIFICATE certificate_name 
[ ENCRYPTION BY PASSWORD = 'password' ]   
WITH SUBJECT = 'certificate_subject_name'   
[ , START_DATE = 'datetime' | EXPIRY_DATE = 'datetime']

证书的子句注释：

ENCRYPTION BY PASSWORD =’password‘：指定用于加密私钥的密码，私钥其实就是证书名称。只有在使用密码加密证书时才会使用该选项，如果省略该选项，那么使用数据库主密钥（Database Master Key）来加密私钥。
SUBJECT =’certificate_subject_name‘：证书的主题名称

管理员可以把已创建的证书授权给相应的人员来使用：

GRANT permission  [ ,...n ]    
    ON CERTIFICATE :: certificate_name   
    TO principal [ ,...n ]

2，创建对称密钥

当创建对称密钥之后，对称密钥必须用至少一个方式来加密：certificate, password, symmetric key, asymmetric key, 或 PROVIDER，密钥可以同时有多种加密方式。

CREATE SYMMETRIC KEY key_name   
WITH ALGORITHM = { AES_128 | AES_192 | AES_256 }
     , ENCRYPTION BY <encrypting_mechanism> [ , ... n ] 
  
<encrypting_mechanism> ::=  
      CERTIFICATE certificate_name   
    | PASSWORD = 'password'   
    | SYMMETRIC KEY symmetric_key_name   
    | ASYMMETRIC KEY asym_key_name

四，使用对称密钥来加密和解密数据

创建证书来对对称密钥进行加密。

1，使用对称密钥加密数据

Step1，创建证书，并使用数据库主密钥来加密证书

CREATE CERTIFICATE CreditCardCert 
WITH SUBJECT = 'Credit Card Numbers';

Step2：创建对称密钥

创建一个名称为CreditCardKey的对称密钥，使用AES_128加密算法，并使用证书对密钥进行加密

CREATE SYMMETRIC KEY CreditCardKey 
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE CreditCardCert;

从 SQL Server 2016开始，除 AES_128、AES_192 和 AES_256 以外的所有算法都已过时。

Step 3：解密对称密钥并使其可供使用

使用密钥前需要解密对称密钥，然后打开密钥，否则密钥不可用

OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCert;

Step4：使用密钥对数据进行加密

调用ENCRYPTBYKEY()函数使用对称密钥对数据进行加密

UPDATE Sales.CreditCard 
SET CardNumberEncrypted = ENCRYPTBYKEY(Key_GUID('CreditCardKey'), CardNumber);

函数 KEY_GUID(‘key_name’)返回对称密钥的GUID。

Step 5：关闭密钥

密钥的打开状态对当前Session起作用，在使用玩密钥之后，应该及时把密钥关闭。

CLOSE SYMMETRIC KEY CreditCardKey ;

2，解密对称密钥

首先打开对称密钥，然后使用对称密钥解密数据，最后关闭密钥

OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCert; 
 
SELECT CONVERT(NVARCHAR(30), DECRYPTBYKEY(CardNumberEncrypted)) AS CreditCardNumber 
FROM Sales.CreditCard ; 

CLOSE SYMMETRIC KEY CreditCardKey ;

参考文档：

SQL Server Encryption

Choose an Encryption Algorithm

Tags: encryption Security SQL Server 安全