--//列加密
--创建一个新的数据库并创建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有了解密权限
