如何撤销SQL Server中用于解密列的对称密钥访问权限？

问题背景 #

我在SQL Server 2022中配置了列级加密，完成了解密列的实现后，执行了以下操作：

1. 创建用户SQLShack：

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

2. 授予该用户解密相关权限：

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

3. 尝试撤销权限但未生效：

revoke control on symmetric key::SymKey_test TO SQLShack
revoke control ON Certificate::[Certificate_test] TO SQLShack;

执行上述撤销语句后，SQLShack用户仍能登录并解密加密列，需找到正确的权限撤销语句。

完整加密配置代码 #

USE AdventureWorks2022

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1';
CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data';
CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test;

-- 加密列数据
SELECT * FROM AdventureWorks2022.HumanResources.Employee

ALTER TABLE AdventureWorks2022.HumanResources.Employee
ADD BankACCNumber_encrypt varbinary(MAX)

OPEN SYMMETRIC KEY SymKey_test
        DECRYPTION BY CERTIFICATE Certificate_test;

UPDATE AdventureWorks2022.HumanResources.Employee
SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), NationalIDNumber)
FROM AdventureWorks2022.HumanResources.Employee;
GO

CLOSE SYMMETRIC KEY SymKey_test;
GO

-- 解密测试
OPEN SYMMETRIC KEY SymKey_test
        DECRYPTION BY CERTIFICATE Certificate_test;

SELECT 
    nationalIDNumber, BankACCNumber_encrypt AS 'Encrypted data',
    CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
FROM 
    AdventureWorks2022.HumanResources.Employee

问题原因 #

之前的撤销操作仅收回了CONTROL权限，但用户仍持有VIEW DEFINITION权限。要打开对称密钥并解密数据，用户需要同时具备对称密钥和证书的VIEW DEFINITION权限，以及证书的CONTROL权限（用于解密对称密钥）。仅撤销CONTROL不足以阻止用户解密。

正确的撤销语句 #

需要同时撤销之前授予的所有相关权限，执行以下SQL：

USE AdventureWorks2022;
GO
-- 撤销对称密钥的VIEW DEFINITION权限
REVOKE VIEW DEFINITION ON SYMMETRIC KEY::SymKey_test TO SQLShack;
-- 撤销证书的VIEW DEFINITION权限
REVOKE VIEW DEFINITION ON CERTIFICATE::Certificate_test TO SQLShack;
-- 撤销证书的CONTROL权限
REVOKE CONTROL ON CERTIFICATE::Certificate_test TO SQLShack;
GO

验证方法 #

执行撤销语句后，以SQLShack用户登录并尝试执行解密操作，会收到类似如下的权限错误，说明权限已成功收回：

消息 15209，级别 16，状态 10，第 1 行
没有对称密钥 'SymKey_test' 的查看定义权限，或者该密钥不存在。

内容的提问来源于stack exchange，提问作者frank