Cell level encryption

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