Encrypting data at the cell level is important in order to protect information within the database. While all users have access to view data not all users are equal. Therefore it makes sense to encrypt data in such a way that only authorized users who have a key to decrypt the data are the only ones viewing it. Cell level encryption has a number of drawbacks especially when querying and searching for data however when there is a need to protect very sensitive information there really is no other way to do so.
--- CREATE DATABASE ENCRYPTION
--- MASTER KEY
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'ENCRYPTDATABASE'
--- CREATE CERTIFICATE
CREATE CERTIFICATE MYCERTIFICATE
WITH SUBJECT = 'ENCRPTYDATABASECERTIFICATE'
--- CREATE SYMMETRIC KEY
CREATE SYMMETRIC KEY SYMMKEY
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MYCERTIFICATE
--- INSERT ENCRYPTED DATA
OPEN SYMMETRIC KEY SYMMKEY DECRYPTION
BY CERTIFICATE MYCERTIFICATE
INSERT INTO example.customer
SELECT 'Jayanth Kurup' ,ENCRYPTBYKEY(KEY_GUID('SYMMKEY'),'jayanth.kurup@*******.com')
--- CREATE FUNCTION TO CHECK CUSTOMER NAME EXISTANCE
OPEN SYMMETRIC KEY SYMMKEY DECRYPTION
BY CERTIFICATE MYCERTIFICATE
SELECT
CustomerName,
CONVERT(varchar(120), DecryptByKey(CustomerEmail))
AS 'Decrypted ID Number'
FROM example.customer;
select
*
from
customer
The difference in execution plans for cell encrypted and non-encrypted searches
Please Consider Subscribing
