sql server 加密_列级SQL Server加密概述

sql server 加密

This article gives an overview of column level SQL Server encryption using examples.

本文使用示例概述列级SQL Server加密。

介绍 (Introduction)

Data security is a critical task for any organization, especially if you store customer personal data such as Customer contact number, email address, social security number, bank and credit card numbers. Our main goal is to protect unauthorized access to data within and outside the organization. To achieve this, we start by providing access to relevant persons. We still have a chance that these authorized persons can also misuse the data; therefore, SQL Server provides encryption solutions. We can use these encryptions and protect the data.

数据安全性对于任何组织都是至关重要的任务，尤其是当您存储客户的个人数据（例如客户联系电话，电子邮件地址，社会保险号，银行和信用卡号）时。 我们的主要目标是保护未经授权的访问组织内外的数据。 为此，我们首先提供与相关人员的联系。 这些授权人员仍有机会滥用数据； 因此，SQL Server提供了加密解决方案。 我们可以使用这些加密来保护数据。

It is a crucial aspect in classifying the data based on the information type and sensitivity. For example, we might have customer DOB in a column and depending upon the requirement, and we should classify it as confidential, highly confidential. You can read more about in the article SQL data classification – Add sensitivity classification in SQL Server 2019.

这是基于信息类型和敏感性对数据进行分类的关键方面。 例如，根据要求，我们可能在列中有客户DOB，我们应该将其分类为机密，高度机密。 您可以在文章SQL数据分类-在SQL Server 2019中添加敏感度分类中了解更多信息。

We have many encryptions available in SQL Server such as Transparent Data Encryption (TDE), Always Encrypted, Static data masking and Dynamic Data Masking. In this article, we will explore column level SQL Server encryption using symmetric keys.

SQL Server中提供了许多加密功能，例如透明数据加密（TDE），始终加密，静态数据屏蔽和动态数据屏蔽。 在本文中，我们将探讨使用对称密钥的列级SQL Server加密。

环境搭建 (Environment set up)

Let’s prepare the environment for this article.

让我们为本文准备环境。

• Create a new database and create CustomerInfo table

  创建一个新的数据库并创建CustomerInfo表

  CREATE DATABASE CustomerData;
              Go 
              USE CustomerData;
              GO
                   
              CREATE TABLE CustomerData.dbo.CustomerInfo
              (CustID        INT PRIMARY KEY, 
               CustName     VARCHAR(30) NOT NULL, 
               BankACCNumber VARCHAR(10) NOT NULL
              );
              GO
  

• Insert sample data into CustomerInfo table

  将样本数据插入CustomerInfo表

  Insert into CustomerData.dbo.CustomerInfo (CustID,CustName,BankACCNumber)
              Select 1,'Rajendra',11111111 UNION ALL
              Select 2, 'Manoj',22222222 UNION ALL
              Select 3, 'Shyam',33333333 UNION ALL
              Select 4,'Akshita',44444444 UNION ALL
              Select 5, 'Kashish',55555555
  

• View the records in CustomerInfo table

  查看CustomerInfo表中的记录

  

We use the following steps for column level encryption:

我们使用以下步骤进行列级加密：

1. Create a database master key
   创建数据库主密钥
2. Create a self-signed certificate for SQL Server
   为SQL Server创建自签名证书
3. Configure a symmetric key for encryption
   配置对称密钥进行加密
4. Encrypt the column data
   加密列数据
5. Query and verify the encryption
   查询并验证加密

We will first use these steps and later explain the overall process using Encryption Hierarchy in SQL Server using the following image (Reference – Microsoft Docs):

我们将首先使用这些步骤，然后使用下图说明在SQL Server中使用加密层次结构的总体过程（参考– Microsoft Docs ）：

创建用于列级SQL Server加密的数据库主密钥 (Create a database master key for column level SQL Server encryption)

In this first step, we define a database master key and provide a password to protect it. It is a symmetric key for protecting the private keys and asymmetric keys. In the above diagram, we can see that a service master key protects this database master key. SQL Server creates this service master key during the installation process.

在第一步中，我们定义一个数据库主密钥并提供一个密码来保护它。 它是用于保护私钥和非对称密钥的对称密钥。 在上图中，我们可以看到服务主密钥可以保护该数据库主密钥。 SQL Server在安装过程中创建此服务主密钥。

We use CREATE MASTER KEY statement for creating a database master key:

我们使用CREATE MASTER KEY语句创建数据库主密钥：

USE CustomerData;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1';

We can use sys.symmetric_keys catalog view to verify the existence of this database master key in SQL Server encryption:

我们可以使用sys.symmetric_keys目录视图来验证此数据库主密钥在SQL Server加密中是否存在：

SELECT name KeyName, 
    symmetric_key_id KeyID, 
    key_length KeyLength, 
    algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;

In the output, we can notice that it creates a ##MS_DatabaseMasterKey## with key algorithm AES_256. SQL Server automatically chooses this key algorithm and key length:

在输出中，我们可以注意到它使用密钥算法AES_256创建了一个## MS_DatabaseMasterKey ## 。 SQL Server自动选择以下密钥算法和密钥长度：

创建用于列级SQL Server加密的自签名证书 (Create a self-signed certificate for Column level SQL Server encryption )

In this step, we create a self-signed certificate using the CREATE CERTIFICATE statement. You might have seen that an organization receives a certificate from a certification authority and incorporates into their infrastructures. In SQL Server, we can use a self-signed certificate without using a certification authority certificate.

在此步骤中，我们使用CREATE CERTIFICATE语句创建一个自签名证书。 您可能已经看到组织从证书颁发机构收到证书，并将其合并到其基础结构中。 在SQL Server中，我们可以使用自签名证书而不使用证书颁发机构证书。

Execute the following query for creating a certificate:

执行以下查询以创建证书：

USE CustomerData;
GO
CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data';
GO

We can verify the certificate using the catalog view sys.certificates:

我们可以使用目录视图sys.certificates验证证书：

SELECT name CertName, 
    certificate_id CertID, 
    pvt_key_encryption_type_desc EncryptType, 
    issuer_name Issuer
FROM sys.certificates;

In the output, we can note the following fields:

在输出中，我们可以注意以下字段：

• Encrypt Type: In this column, we get a value 加密类型：在此列中，我们获得一个值ENCRYPTED_BY_MASTER_KEY, and it shows that SQL Server uses the database master key created in the previous step and protects this certificate ENCRYPTED_BY_MASTER_KEY，它表明SQL Server使用上一步中创建的数据库主密钥并保护此证书。
• CertName: It is the certificate name that we defined in the CREATE CERTIFICATE statement CertName ：这是我们在CREATE CERTIFICATE语句中定义的证书名称
• Issuer: We do not have a certificate authority certificate; therefore, it shows the subject value we defined in the CREATE CERTIFICATE statement 发行者 ：我们没有证书颁发机构的证书； 因此，它显示了我们在CREATE CERTIFICATE语句中定义的主题值

Optionally, we can use ENCRYPTION BY PASSWORD and EXPIRY_DATE parameters in the CREATE CERTIFICATE; however, we will skip it in this article.

（可选）我们可以在CREATE CERTIFICATE中使用ENCRYPTION BY PASSWORD和EXPIRY_DATE参数； 但是，我们将在本文中跳过它。

为列级SQL Server加密配置对称密钥 (Configure a symmetric key for column level SQL Server encryption)

In this step, we will define a symmetric key that you can see in the encryption hierarchy as well. The symmetric key uses a single key for encryption and decryption as well. In the image shared above, we can see the symmetric key on top of the data. It is recommended to use the symmetric key for data encryption since we get excellent performance in it. For column encryption, we use a multi-level approach, and it gives the benefit of the performance of the symmetric key and security of the asymmetric key.

在此步骤中，我们将定义一个对称密钥，您也可以在加密层次结构中看到该对称密钥。 对称密钥也使用单个密钥进行加密和解密。 在上面共享的图像中，我们可以看到数据顶部的对称密钥。 建议使用对称密钥进行数据加密，因为我们会获得出色的性能。 对于列加密，我们使用多级方法，它提供了对称密钥的性能和非对称密钥的安全性的好处。

We use CREATE SYMMETRIC KEY statement for it using the following parameters:

我们使用以下参数为其使用CREATE SYMMETRIC KEY语句：

• ALGORITHM: AES_256 算法： AES_256
• ENCRYPTION BY CERTIFICATE: It should be the same certificate name that we specified earlier using CREATE CERTIFICATE statement ENCRYPTION BY CERTIFICATE：应与我们之前使用CREATE CERTIFICATE语句指定的证书名称相同

CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test;

Once we have created this symmetric key, check the existing keys using catalog view for column level SQL Server Encryption as checked earlier:

一旦创建了对称密钥，就可以使用目录视图为列级SQL Server加密检查现有密钥，如前所述：

SELECT name KeyName, 
    symmetric_key_id KeyID, 
    key_length KeyLength, 
    algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;

We can see two key entries now as it includes both the database master key and the symmetric key:

现在我们可以看到两个密钥条目，因为它既包含数据库主密钥又包含对称密钥：

We have created the required encryption keys in this demo. It has the following setup that you can see in the image shown above as well:

我们在此演示中创建了所需的加密密钥。 您还可以在上面的图像中看到以下设置：

• SQL Server installation creates a Service Master Key (SMK), and Windows operating system Data Protection API (DPAPI) protects this key
  SQL Server安装会创建一个服务主密钥（SMK），Windows操作系统的数据保护API（DPAPI）将保护该密钥
• This Service Master Key (SMK) protects the database master key (DMK)
  此服务主密钥（SMK）保护数据库主密钥（DMK）
• A database master key (DMK) protects the self-signed certificate
  数据库主密钥（DMK）保护自签名证书
• This certificate protects the Symmetric key
  该证书保护对称密钥

数据加密 (Data encryption)

SQL Server encrypted column datatype should be VARBINARY. In our CustomerData table, the BankACCNumber column data type is Varchar(10). Let’s add a new column of VARBINARY(max) datatype using the ALTER TABLE statement specified below:

SQL Server加密列数据类型应为VARBINARY 。 在我们的CustomerData表中， BankACCNumber列的数据类型为Varchar（10）。 让我们使用下面指定的ALTER TABLE语句添加一个新的VARBINARY（max）数据类型的列：

ALTER TABLE CustomerData.dbo.CustomerInfo
ADD BankACCNumber_encrypt varbinary(MAX)

Let’s encrypt the data in this newly added column.

让我们加密此新添加的列中的数据。

• In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier

  在查询窗口中，打开对称密钥并使用证书解密。 我们需要使用与先前创建的相同的对称密钥和证书名称

  OPEN SYMMETRIC KEY SymKey_test
          DECRYPTION BY CERTIFICATE Certificate_test;
  

• In the same session, use the following UPDATE statement. It uses EncryptByKey function and uses the symmetric function for encrypting the BankACCNumber column and updates the values in the newly created BankACCNumber_encrypt column

  在同一会话中，使用以下UPDATE语句。 它使用EncryptByKey函数并使用对称函数对BankACCNumber列进行加密，并更新新创建的BankACCNumber_encrypt列中的值

  UPDATE CustomerData.dbo.CustomerInfo
          SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), BankACCNumber)
          FROM CustomerData.dbo.CustomerInfo;
          GO
  

• Close the symmetric key using the CLOSE SYMMETRIC KEY statement. If we do not close the key, it remains open until the session is terminated

  使用CLOSE SYMMETRIC KEY语句关闭对称密钥 。 如果我们不关闭密钥，它将保持打开状态，直到会话终止

  CLOSE SYMMETRIC KEY SymKey_test;
              GO
  

• CustomerInfo table CustomerInfo表中的记录

We can see the encrypted records in the newly added column. If the user has access to this table also, he cannot understand the data without decrypting it:

我们可以在新添加的列中看到加密的记录。 如果用户也有权访问此表，则他不解密就无法理解数据：

Let’s remove the old column as well:

让我们也删除旧列：

ALTER TABLE CustomerData.dbo.CustomerInfo DROP COLUMN BankACCNumber;
GO

Now, we have only an encrypted value for the bank account number:

现在，我们只有一个加密的银行帐号值：

解密列级SQL Server加密数据 (Decrypt column level SQL Server encryption data)

We need to execute the following commands for decrypting column level encrypted data:

我们需要执行以下命令来解密列级加密数据：

• In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier

  在查询窗口中，打开对称密钥并使用证书解密。 我们需要使用与先前创建的相同的对称密钥和证书名称

  OPEN SYMMETRIC KEY SymKey_test
          DECRYPTION BY CERTIFICATE Certificate_test;
  

• Use the SELECT statement and decrypt encrypted data using the DecryptByKey() function

  使用SELECT语句并使用DecryptByKey（）函数解密加密的数据

  SELECT CustID, CustName,BankACCNumber_encrypt AS 'Encrypted data',
              CONVERT(varchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
              FROM CustomerData.dbo.CustomerInfo;
  

We can see both encrypted and decrypted data in the following screenshot:

在以下屏幕截图中，我们可以看到加密和解密的数据：

解密数据所需的权限 (Permissions required for decrypting data)

A user with the read permission cannot decrypt data using the symmetric key. Let’s simulate the issue. For this, we will create a user and provide db_datareader permissions on CustomerData database:

具有读取权限的用户无法使用对称密钥解密数据。 让我们模拟这个问题。 为此，我们将创建一个用户并在CustomerData数据库上提供db_datareader权限：

USE [master]
GO
CREATE LOGIN [SQLShack] WITH PASSWORD=N'sqlshack', DEFAULT_DATABASE=[CustomerData], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [CustomerData]
GO
CREATE USER [SQLShack] FOR LOGIN [SQLShack]
GO
USE [CustomerData]
GO
ALTER ROLE [db_datareader] ADD MEMBER [SQLShack]
GO

Now connect to SSMS using SQLShack user and execute the query to select the record with decrypting BankACCNumber_encrypt column:

现在，使用SQLShack用户连接到SSMS并执行查询以解密BankACCNumber_encrypt列以选择记录：

OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;
    
SELECT CustID, CustName,BankACCNumber_encrypt AS 'Encrypted data',
CONVERT(varchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
FROM CustomerData.dbo.CustomerInfo;

In the output message, we get the message that the symmetric key does not exist, or the user does not have permission to use it:

在输出消息中，我们得到以下消息：对称密钥不存在，或者用户没有使用对称密钥的权限：

Click on the results, and we get the NULL values in the decrypted column, as shown below:

单击结果，然后在解密列中获得NULL值，如下所示：

We can provide permissions to the Symmetric key and Certificate:

我们可以提供对对称密钥和证书的权限：

• Symmetric key permission: GRANT VIEW DEFINITION 对称密钥许可 ：GRANT VIEW DEFINITION
• Certificate permission: GRANT VIEW DEFINITION and GRANT CONTROL permissions 证书权限： GRANT VIEW DEFINITION和GRANT CONTROL权限

Execute these scripts with from a user account with admin privileges:

使用具有管理员特权的用户帐户执行以下脚本：

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKey_test TO SQLShack; 
GO
GRANT VIEW DEFINITION ON Certificate::[Certificate_test] TO SQLShack;
GO
GRANT CONTROL ON Certificate::[Certificate_test] TO SQLShack;

Now, go back and re-execute the SELECT statement:

现在，返回并重新执行SELECT语句：

结论 (Conclusion)

In this article, we explored column level SQL Server encryption using the symmetric key. We can use the same key for encrypting other table columns as well. You should explore the encryption and decryption mechanism; however, you should consider the requirements first and then consider the appropriate encryption mechanism as per your need.

在本文中，我们探讨了使用对称密钥的列级SQL Server加密。 我们也可以使用相同的密钥来加密其他表列。 您应该探索加密和解密机制； 但是，您应该首先考虑需求，然后再根据需要考虑适当的加密机制。

翻译自: https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

sql server 加密