加密数据列
适用于:SQL Server
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics
本文介绍如何使用 Transact-SQL,在 SQL Server 中通过对称加密对数据列进行加密。 这有时称为列级加密或单元级加密。
本文中的 Transact-SQL 代码示例使用 AdventureWorks2022
或 AdventureWorksDW2022
示例数据库,可从 Microsoft SQL Server 示例和社区项目主页下载它。
安全性
权限
下面的权限是执行以下步骤所必需的:
- 数据库的
CONTROL
权限。 - 数据库的
CREATE CERTIFICATE
权限。 只有 Windows 登录名、SQL Server 登录名和应用程序角色才能拥有证书。 其他组和角色不能拥有证书。 - 表的
ALTER
权限。 - 针对密钥的某些权限,并且必须未被拒绝授予
VIEW DEFINITION
权限。
创建数据库主密钥
要使用下面的示例,必须具有数据库主密钥。 如果数据库尚不具有数据库主密钥,请创建一个。 若要创建一个主密钥,请连接到数据库,然后运行以下脚本。 请确保使用复杂密码。
将以下示例复制并粘贴到连接到 AdventureWorks
示例数据库的查询窗口中。 选择“执行”。
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '<complex password>';
请始终备份数据库主密钥。 有关数据库主密钥的详细信息,请参阅 CREATE MASTER KEY (Transact-SQL)。
示例:通过对称加密和验证器进行加密
在 “对象资源管理器” 中,连接到 数据库引擎的实例。
在标准栏上,选择“新建查询” 。
将以下示例复制并粘贴到连接到
AdventureWorks
示例数据库的查询窗口中。 选择“执行”。CREATE CERTIFICATE Sales09 WITH SUBJECT = 'Customer Credit Card Numbers'; GO CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Sales09; GO -- Create a column in which to store the encrypted data. ALTER TABLE Sales.CreditCard ADD CardNumber_Encrypted varbinary(160); GO -- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09; -- Encrypt the value in column CardNumber using the -- symmetric key CreditCards_Key11. -- Save the result in column CardNumber_Encrypted. UPDATE Sales.CreditCard SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11') , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary , CreditCardID))); GO -- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09; GO -- Now list the original card number, the encrypted card number, -- and the decrypted ciphertext. If the decryption worked, -- the original number will match the decrypted number. SELECT CardNumber, CardNumber_Encrypted AS 'Encrypted card number', CONVERT(nvarchar, DecryptByKey(CardNumber_Encrypted, 1 , HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID)))) AS 'Decrypted card number' FROM Sales.CreditCard; GO
通过简单对称加密进行加密
在 “对象资源管理器” 中,连接到 数据库引擎的实例。
在标准栏上,选择“新建查询” 。
将以下示例复制并粘贴到连接到
AdventureWorks
示例数据库的查询窗口中。 选择“执行”。CREATE CERTIFICATE HumanResources037 WITH SUBJECT = 'Employee Social Security Numbers'; GO CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE HumanResources037; GO USE [AdventureWorks2022]; GO -- Create a column in which to store the encrypted data. ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber varbinary(128); GO -- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; -- Encrypt the value in column NationalIDNumber with symmetric -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber. UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber); GO -- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; GO -- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match. SELECT NationalIDNumber, EncryptedNationalIDNumber AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS 'Decrypted ID Number' FROM HumanResources.Employee; GO