In SQL Server 2022, I am adding a decrypted column on my db, as per https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/
I followed suggestion to grant access to use a certificate to decode the column with:
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;
But now I want to revoke access to the certificate with:
revoke control on symmetric key::SymKey_test TO SQLShack
revoke control ON Certificate::[Certificate_test] TO SQLShack;
But when I logon as SQLShack, I still have access to the certificate to decrypt my column. What is the correct call to remove access to use the certificate to decode an encrypted column
for ease, the entire code is below:
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;
-- encode using symmetric keys
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
-- decode
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