【数据加密】在Power BI中查看加密的SQL数据

介绍

在基于RDBMS的解决方案中，经常需要通过加密方式混淆敏感数据（如SSN或信用卡号）。这有助于将敏感信息的访问权限限制为仅限一组特权用户。

总的来说，有两种可能的方法可以做到这一点。

• 一种方法是在从数据库插入/检索数据之前，在应用程序级别加密/解密数据。
• 第二种方法是在数据库级别本身处理加密和解密。有多种方法可以在SQL Server中实现加密；请参阅此处的官方文档。

方法的选择取决于特定的业务需求、必要先决条件的可用性和整体解决方案设计。

本文的主要关注点是能够在通过Power BI创建的报告中查看加密数据的要求。在我的案例中，我发现通过SQL加密函数（ENCRYPTBYKEY和DECRYPTBYEY）处理列加密和解密的选项更实用。

在这篇博客文章的剩余部分中，我们将看到如何实现

使用对称密钥和的SQL Server列级加密

查看Power BI报告中的解密数据

目标

将Power BI中SQL Server的加密数据显示给特权用户。

使用的开发工具

• Azure SQL Server
• SQL Server Management Studio/Azure Data Studio
• Power BI桌面应用程序

数据库实现

1.数据库和表的创建

1. 从Azure门户创建一个Azure SQL Server实例（如果还不可用）。
2. 创建一个名为EmployeeRecords的数据库。
3. 通过SSMS或Azure Data Studio连接到EmployeeRecords数据库。
4. 创建一个名为EmployeeDetails的表，其中包含如下所示的列。
5. 在该表中，SSN列将保存每个员工的加密社会保障号码详细信息。请注意用于此列的数据类型（varbinary）。

2.SQL Server列加密

如引言部分所述，我们将使用具有对称密钥的SQL加密函数来处理列中数据的加密和解密。

SQL Server如何处理加密、加密层次结构和密钥管理基础结构将在此处进行解释。

使用对称密钥的第一步是创建数据库主密钥和自签名证书。

a.创建数据库主密钥和证书

用于创建DMK和证书的T-SQL语句如下所示。

b.创建对称密钥

在上一步中创建的数据库主密钥和证书将用于创建对称密钥。

此对称密钥将用于在表中插入/更新记录时对列数据进行加密。随后，为了查看敏感信息，将使用相同的密钥对数据进行解密。因此，它被称为“对称”方法。

下面的T-SQL语句使用上一步中创建的证书DBCert创建一个名为EmpSSN_SymmKey的对称密钥。

执行以下语句以验证数据库主密钥和对称密钥的创建。

查询应该返回所创建的键的详细信息。

3.将数据插入表格

1. 创建一个存储过程，将员工详细信息插入EmployeeDetails表。
2. 在其定义中，包括在INSERT语句之前打开对称密钥的语句。
3. 在INSERT语句中，使用ENCRYPTBYKEY函数传递对称密钥的GUID和要加密的值，即SSN。
4. 在INSERT语句之后关闭键。
5. 在下面的屏幕截图中，显示了存储过程sp_insert_empdetails的定义。

执行存储过程，传递适当的值作为参数。

验证表中的SSN值是否已加密。

4.查看解密值

创建一个存储过程sp_get_empdetails，它将EmpId作为输入参数。

若要查看原始SSN值，SELECT语句必须包含在“Open Symmetric Key”和“Close Symmetric Key'”语句之间，如sp_get_empdetails存储过程的定义中所示。

执行存储过程并验证是否返回了解密的值。

5.向特定用户授予权限

• 创建一个唯一的SQL登录名（下面屏幕截图中的empsecurereader），它将代表特权用户集。

在EmployeeDetails数据库中，创建一个映射到SQL登录名的用户。

• 授予数据库用户对数据库的EXECUTE权限。
• 授予empsecurereader用户对自签名证书DBCert的CONTROL权限。
• 类似地，对对称密钥EmpSSN_SymmKey授予REFERENCES和VIEW DEFINITION权限。
• 如果没有这些权限，用户将无法使用对称密钥进行加密和解密。

可以进行额外的检查，通过拒绝其他用户对存储过程所需的权限来限制他们执行存储过程或查看其定义。

此外，对证书和对称密钥的访问也可能受到限制，从而防止其他用户使用密钥或查看其定义。

Power BI实施

Power BI是微软的一种分析和数据可视化工具，可以连接到各种数据源，SQL Azure就是其中之一。Power BI可以通过两种可能的方式连接到SQL Azure——导入和DirectQuery。

使用Import方法，将数据对象导入Power BI，而使用Direct Query，可以向数据库发出SQL查询，并且仅将结果集导入Power BI。

对于我们的需求，其中我们需要查询EmployeeDetails表并显示解密的数据，我们将使用传递sp_get_empdetails存储过程的Direct query方法来执行。

1.创建数据源连接

• 在Power BI桌面应用程序中，创建一个到Azure SQL的连接，传递SQL语句以执行存储过程sp_get_empdetails，如下所示。
• 请注意，我们在该语句中将一个固定值传递给存储过程。在后面的步骤中，我们将通过使用参数来改变这种动态。

连接到数据库时，请使用上一节步骤5中创建的登录empsecurereader的凭据。我们已经授予该登录对证书和对称密钥的必要权限，使其能够使用该密钥进行加密和解密。

2.通过添加参数使查询具有动态性

• 创建数据连接后，打开Power Query编辑器以查看查询结果集。
• 创建一个名为“Emp-ID”的参数，如下所示。

打开“高级编辑器”并修改查询，如下屏幕截图所示。

保存查询并关闭高级编辑器。回到Power Query编辑器中，输入一个有效的Employee Id，并验证返回的相关详细信息是否已解密SSN值。

摘要：

正如我们所看到的，通过使用对称密钥，我们能够在SQL server中加密和解密列值。通过创建实现这些对称密钥的适当存储过程，我们能够根据需要解密和查看敏感数据。对这些存储过程设置适当的权限可以限制仅对特权用户集的访问。然后可以从Power BI Desktop应用程序调用这些存储过程，并在报告中使用结果集。