在sql server中使用加密密钥和证书获取加密的列名。

Question

我正在使用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

设置主密钥

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

创建对称密钥和证书

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

这里我们给出了硬编码的密钥/证书值。

我通过这个查询得到加密的列名和他们的表-

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

以及这个查询的密钥和证书列表-

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

现在，我希望获得哪个密钥/证书属于哪个加密列，这样我就可以在该列上应用解密，而无需提供硬编码的值。请帮帮我..。

Answer 1

经过搜索和尝试，我找到了一个解决方案，就是-

SELECT DISTINCT key_name(encryptedcol) FROM encryptedTable;

此查询为结果提供属于该列的加密密钥。

Answer 2

我正在使用Server 2016。

下面是获取所有加密列所需的密钥的查询。

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    

Answer 3

您不能，至少使用T内置的系统函数和视图。

此外，列的类型可以是varbinary，并且那里的数据不需要加密。

如果要构建动态then语句并避免证书和密钥的硬编码，则可以创建mapping表。例如，如下所示：

TableName
ColumnName
Certificate
EncryptionKey
IsSymmetric

这并不是一个完美的工作(是的，您需要维护数据)，但是如果新列没有不断加密，它可能会完成这项工作。