How to encrypt a column of a table with sensitive data
Certifications:
In this article we will describe how to apply encryption to a table column via Column Level Encryption (CLE). We will also mention what we should pay attention to in order to have a smooth transition.
The CLE suggested as a solution when we want to hide sensitive data within the database.
How is it different from Always Encrypted?
The CLE offers encryption via certificate / key which is done inside the database. Which means that an administrator who has permission can see this encrypted data.
On the contrary Always Encrypted offers encryption through the application so that the database engine does not know the encryption key.
For this reason, its solution is proposed from a security point of view Always Encrypted.
The example
We create the database we will use.
USE master; GO CREATE DATABASE EmpData2; GO
We close any other key that may be open.
USE EmpData2; GO CLOSE ALL SYMMETRIC KEYS;
We make a table that we will use for the example.
create table EmpData2..gdpr( id int identity(1,1) primary key, onoma varchar(50), tilefono varchar(50) )
We call a loop to populate the array with data.
DECLARE @i as int;
set @i = 0;
while @i<10
begin
insert into EmpData2..gdpr (onoma,tilefono)
values('Stratos','2109090'+cast(@i as varchar(50)))
set @i=@i+1
end
We will consider in this table the column containing the phone as sensitive data.
select * from EmpData2..gdpr
To begin with, we should create a master key by setting a code. Then we should open it.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass!' GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pass!' go
If we did the procedure correctly, it should appear with this select.
SELECT * FROM [sys].[openkeys]
We should take a backup of the keys or certificates we create because if we lose them there is no going back...
BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key' ENCRYPTION BY PASSWORD = 'Pass!'; BACKUP MASTER KEY TO FILE = 'C:\DbMasterKey.key' ENCRYPTION BY PASSWORD = 'Pass!'
We also make the certificate. Which should also appear in select.
CREATE CERTIFICATE gdpr_cert
WITH SUBJECT = 'Gdpr cert'
GO
select * from sys.certificates
We also backup this.
BACKUP CERTIFICATE gdpr_cert TO FILE = 'C:\backup\gdpr_cert.cer' WITH PRIVATE KEY ( FILE = 'c:\cert_key' , ENCRYPTION BY PASSWORD = 'Pass2!' );
We make a key that is encoded with the certificate.
CREATE SYMMETRIC KEY cle_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE gdpr_cert;
GO
We check that this also appears in the select.
SELECT * FROM [sys].[symmetric_keys] GO
We open the key by decrypting it with the certificate.
OPEN SYMMETRIC KEY cle_key DECRYPTION BY CERTIFICATE gdpr_cert GO SELECT * FROM [sys].[openkeys] GO
We will now add a varbinary column to the table we want to encrypt.
ALTER TABLE empdata2..GDPR ADD encrypt_tilefono varbinary(200) NULL GO ALTER TABLE empdata2..GDPR SET (LOCK_ESCALATION = TABLE) GO
With update we will fill the pillar with the encrypted data from the phone pillar.
UPDATE empdata2..GDPR
SET encrypt_tilefono = ENCRYPTBYKEY(KEY_GUID('cle_key'), tilefono)
GO
With a select on the table we will see something like this.
select * from empdata2..GDPR
If we make sure we are OK we can delete the unencrypted pillar.
It would be good to have backed up the database before deleting anything.
alter table empdata2..GDPR drop column tilefono GO
In order for a simple user who is not an administrator to see this data, we have to give permission to the certificate and the key.
GRANT CONTROL ON CERTIFICATE::[gdpr_cert] TO [xristi]; GO GRANT VIEW DEFINITION ON SYMMETRIC KEY::[cle_key] TO [xristi]; GO
To see the data without encryption.
SELECT onoma,CONVERT([varchar](max), DECRYPTBYKEY([encrypt_tilefono])) FROM empdata2..GDPR GO
To add a new encrypted record.
insert into empdata2..GDPR (onoma,encrypt_tilefono)
values('Stratos', ENCRYPTBYKEY(KEY_GUID('cle_key'), '21090999'))
GO
SELECT onoma,CONVERT([varchar](max), DECRYPTBYKEY([encrypt_tilefono])) FROM empdata2..GDPR GO
