如何检测和获取表列加密的信息

I'd like to make a query that checks for encryption and outputs all columns in a randomly given database table if they are encrypted. So I want the output to look like this

我想创建一个检查加密的查询，并输出随机给定的数据库表中的所有列（如果它们是加密的）。所以我希望输出看起来像这样

Column Name   |   Encryption Key Name  |  Encryption Type  |  Algorithm Used
Baby Power         Key name for bp           Randomized       AEAD_AES_256_CBC_HMAC_SHA_256
Diaper             Key name for Diaper       Deterministic    AEAD_AES_256_CBC_HMAC_SHA_256

1 个解决方案

#1

You can use following query:

您可以使用以下查询：

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    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

This will return all encrypted columns in current database. If you want only for specific table, add where condition with filter on t.name.

这将返回当前数据库中的所有加密列。如果只想要特定的表，请在t.name上添加带过滤器的where条件。

In general, if you want to find information about different database objects, system views under sys schema are the right place.

通常，如果要查找有关不同数据库对象的信息，则sys模式下的系统视图是正确的位置。

#1