How to encrypt a column of a table with sensitive data

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

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

Sources:

• Encrypt a Column with Cell-Level-Encryption