sql server 加密_列级SQL Server加密

--//列加密

--创建一个新的数据库并创建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

--将样本数据插入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

--查看CustomerInfo表中的记录

SELECT * FROM CustomerInfo

/*

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

Create a database master key

创建数据库主密钥

Create a self-signed certificate for SQL Server

为SQL Server创建自签名证书

Configure a symmetric key for encryption

配置对称密钥进行加密

Encrypt the column data

加密列数据

Query and verify the encryption

查询并验证加密

*/

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

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

USE CustomerData;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '#sql6819118';

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

SELECT name KeyName,

symmetric_key_id KeyID,

key_length KeyLength,

algorithm_desc KeyAlgorithm

FROM sys.symmetric_keys;

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

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

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

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

USE CustomerData;

GO

CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data';

GO

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

SELECT name CertName,

certificate_id CertID,

pvt_key_encryption_type_desc EncryptType,

issuer_name Issuer

FROM sys.certificates;

/*

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

Encrypt Type: 加密类型：在此列中，我们获得一个值

ENCRYPTED_BY_MASTER_KEY, 它表明SQL Server使用上一步中创建的数据库主密钥并保护此证书。

CertName：这是我们在CREATE CERTIFICATE语句中定义的证书名称

Issuer: 发行者 ：我们没有证书颁发机构的证书； 因此，它显示了我们在CREATE CERTIFICATE语句中定义的主题值

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

*/

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

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

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

--ALGORITHM: AES_256 算法： AES_256

--ENCRYPTION BY CERTIFICATE：应与我们之前使用CREATE CERTIFICATE语句指定的证书名称相同

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

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

SELECT name KeyName,

symmetric_key_id KeyID,

key_length KeyLength,

algorithm_desc KeyAlgorithm

FROM sys.symmetric_keys;

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

--//数据加密 (Data encryption)//

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

ALTER TABLE CustomerData.dbo.CustomerInfo

ADD BankACCNumber_encrypt varbinary(MAX)

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

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

OPEN SYMMETRIC KEY SymKey_test

DECRYPTION BY CERTIFICATE Certificate_test;

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

UPDATE CustomerData.dbo.CustomerInfo

SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), BankACCNumber)

FROM CustomerData.dbo.CustomerInfo;

GO

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

CLOSE SYMMETRIC KEY SymKey_test;

GO

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

SELECT * FROM CustomerInfo

go

--让我们也删除旧列：

ALTER TABLE CustomerData.dbo.CustomerInfo DROP COLUMN BankACCNumber;

GO

SELECT * FROM CustomerInfo

--//至此，列加密完毕//

-----------------

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

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

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

OPEN SYMMETRIC KEY SymKey_test

DECRYPTION BY CERTIFICATE Certificate_test;

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

SELECT CustID, CustName,BankACCNumber_encrypt AS 'Encrypted data',

CONVERT(varchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'

FROM CustomerData.dbo.CustomerInfo;

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

--具有读取权限的用户无法使用对称密钥解密数据。 让我们模拟这个问题。 为此，我们将创建一个用户并在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

--现在，使用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;

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

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

--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;

--

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;

--此时SQLShack有了解密权限