{"id":1557,"date":"2021-05-17T07:00:00","date_gmt":"2021-05-17T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1557"},"modified":"2023-02-28T22:49:49","modified_gmt":"2023-02-28T19:49:49","slug":"pos-kanoyme-encrypt-mia-kolona-enos-pinaka-me","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-encrypt-mia-kolona-enos-pinaka-me\/","title":{"rendered":"How to encrypt a column of a table with sensitive data"},"content":{"rendered":"

In this article we will describe how to apply encryption to a table column via Column Level Encryption<\/strong> (CLE)<\/strong>. We will also mention what we should pay attention to in order to have a smooth transition.<\/p>\n\n\n\n

The CLE <\/strong>suggested as a solution when we want to hide sensitive data<\/strong> within the database. <\/p>\n\n\n\n

How is it different from Always Encrypted?<\/h3>\n\n\n\n

The CLE <\/strong>offers encryption via certificate \/ key which is done inside the database. Which means that an administrator who has permission can see this encrypted data.<\/p>\n\n\n\n

On the contrary Always Encrypted<\/strong> offers encryption through the application so that the database engine does not know the encryption key.<\/p>\n\n\n\n

For this reason, its solution is proposed from a security point of view Always Encrypted<\/strong>.<\/p>\n\n\n\n

The example<\/h2>\n\n\n\n

We create the database we will use.<\/p>\n\n\n\n

USE master;\nGO\n \nCREATE DATABASE EmpData2;\nGO<\/pre>\n\n\n\n
We close any other key that may be open.<\/p>\n\n\n\n
USE EmpData2;\nGO\nCLOSE ALL SYMMETRIC KEYS;<\/pre>\n\n\n\n
We make a table that we will use for the example.<\/p>\n\n\n\n
create table EmpData2..gdpr(\nid int identity(1,1) primary key,\nonoma varchar(50),\ntilefono varchar(50)\n)<\/pre>\n\n\n\n
We call a loop to populate the array with data.<\/p>\n\n\n\n
DECLARE @i as int;\nset @i = 0;\nwhile @i<10\nbegin\n    insert into EmpData2..gdpr (onoma,tilefono)\n    values('Stratos','2109090'+cast(@i as varchar(50)))\n    set @i=@i+1\nend<\/pre>\n\n\n\n
We will consider in this table the column containing the phone as sensitive data.<\/p>\n\n\n\n
select * from EmpData2..gdpr<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To begin with, we should create a master key by setting a code. Then we should open it.<\/p>\n\n\n\n
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'Pass!'\nGO\nOPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pass!'\ngo<\/pre>\n\n\n\n
If we did the procedure correctly, it should appear with this select.<\/p>\n\n\n\n
SELECT * FROM [sys].[openkeys]<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We should take a backup of the keys or certificates we create because if we lose them there is no going back...<\/p>\n\n\n\n
BACKUP SERVICE MASTER KEY TO FILE = 'c:\\service_master_key' ENCRYPTION BY PASSWORD = 'Pass!';  \nBACKUP MASTER KEY  TO FILE = 'C:\\DbMasterKey.key' ENCRYPTION BY PASSWORD = 'Pass!'<\/pre>\n\n\n\n
We also make the certificate. Which should also appear in select.<\/p>\n\n\n\n
CREATE CERTIFICATE gdpr_cert\n    WITH SUBJECT = 'Gdpr cert'\nGO\nselect * from sys.certificates<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We also backup this.<\/p>\n\n\n\n
BACKUP CERTIFICATE gdpr_cert TO FILE = 'C:\\backup\\gdpr_cert.cer' WITH PRIVATE KEY ( FILE = 'c:\\cert_key' ,  ENCRYPTION BY PASSWORD = 'Pass2!' );  <\/pre>\n\n\n\n
We make a key that is encoded with the certificate.<\/p>\n\n\n\n
CREATE SYMMETRIC KEY cle_key\n    WITH ALGORITHM = AES_256\n    ENCRYPTION BY CERTIFICATE gdpr_cert;\nGO<\/pre>\n\n\n\n
We check that this also appears in the select.<\/p>\n\n\n\n
SELECT * FROM [sys].[symmetric_keys]\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We open the key by decrypting it with the certificate.<\/p>\n\n\n\n
OPEN SYMMETRIC KEY cle_key\nDECRYPTION BY CERTIFICATE gdpr_cert\nGO\nSELECT * FROM [sys].[openkeys]\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We will now add a varbinary column to the table we want to encrypt.<\/p>\n\n\n\n
ALTER TABLE empdata2..GDPR ADD\n\tencrypt_tilefono varbinary(200) NULL\nGO\nALTER TABLE empdata2..GDPR SET (LOCK_ESCALATION = TABLE)\nGO<\/pre>\n\n\n\n
With update we will fill the pillar with the encrypted data from the phone pillar.<\/p>\n\n\n\n
UPDATE empdata2..GDPR\nSET encrypt_tilefono = ENCRYPTBYKEY(KEY_GUID('cle_key'), tilefono)\nGO<\/pre>\n\n\n\n
With a select on the table we will see something like this.<\/p>\n\n\n\n
select *  from empdata2..GDPR<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If we make sure we are OK we can delete the unencrypted pillar. <\/p>\n\n\n\n
It would be good to have backed up the database before deleting anything.<\/p>\n\n\n\n
alter table empdata2..GDPR\ndrop column  tilefono\nGO<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
GRANT CONTROL ON CERTIFICATE::[gdpr_cert] TO [xristi];\nGO\n\nGRANT VIEW DEFINITION ON SYMMETRIC KEY::[cle_key] TO [xristi];\nGO<\/pre>\n\n\n\n
To see the data without encryption.<\/p>\n\n\n\n
SELECT onoma,CONVERT([varchar](max), DECRYPTBYKEY([encrypt_tilefono]))\n FROM empdata2..GDPR\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To add a new encrypted record.<\/p>\n\n\n\n
insert into empdata2..GDPR (onoma,encrypt_tilefono)\nvalues('Stratos', ENCRYPTBYKEY(KEY_GUID('cle_key'), '21090999'))\nGO<\/pre>\n\n\n\n
SELECT onoma,CONVERT([varchar](max), DECRYPTBYKEY([encrypt_tilefono]))\n FROM empdata2..GDPR\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n