sql - 在sql server中获取加密列名称及其加密 key 和证书

Question

我正在使用 SQL Server 2008/2012。在我的数据库中，我有一个包含加密列的表。该列使用以下查询进行加密 -

创建示例表

CREATE TABLE [HR].[Employees](
      [EmployeeID]      [int]         NOT NULL,
      [EmployeeName]    [varchar](50) NULL,
      [SSN]             [varchar](20) NOT NULL,
      [EncryptedSSN]    [varbinary] (200) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(     [EmployeeID] ASC)
)
GO

设置主 key

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'T3stP@ssword'
GO

创建对称 key 和证书

CREATE CERTIFICATE TestCert
   WITH SUBJECT = 'SSN Encryption';
GO

CREATE SYMMETRIC KEY HRKey
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE TestCert;
GO

加密数据

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;
Now we can update the EncryptedSSN column of our Employees table.

UPDATE [HR].[Employees]
   SET [EncryptedSSN] = EncryptByKey(Key_GUID('HRKey'), SSN);
GO

我正在使用查询解密此数据 -

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE TestCert;
SELECT [SSN], 
    CONVERT(VARCHAR, DecryptByKey([EncryptedSSN])) 
    AS 'Decrypted SSN'
    FROM [HR].[Employees]
GO

这里我们给出硬编码的 key /证书值。

我通过此查询获取加密列名称及其表 -

SELECT stab.name Table_Name, sc.name Column_Name FROM sys.columns sc
        INNER JOIN sys.types st ON sc.system_type_id=st.system_type_id
        INNER JOIN sys.tables stab ON stab.object_id=sc.object_id
        WHERE st.name='varbinary'
        AND stab.is_ms_shipped=0

以及此查询的 key 和证书列表 -

SELECT name, key_length, algorithm_desc, create_date, modify_date
FROM sys.symmetric_keys;
SELECT name, subject, start_date, expiry_date 
FROM sys.certificates

现在我想获取哪个 key /证书属于哪个加密列，这样我就可以在该列上应用解密，而无需给出硬编码值。请帮助我......

Answer 1

我使用的是 SQL Server 2016。

下面是获取所有必需的带有 key 的加密列的查询。

SELECT t.name AS TableName
    ,c.name AS ColumnName
    ,c.max_length
    ,k.name AS KeyName
    ,c.encryption_type_desc
    ,c.encryption_algorithm_name
FROM sys.columns c
INNER JOIN sys.column_encryption_keys k ON c.column_encryption_key_id = k.column_encryption_key_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE encryption_type IS NOT NULL