Configure Data Access – Column Level Encryption

Configure Data Access – Column Level Encryption

Introduction

In today’s world, data security is more critical than ever. The ability to encrypt data at the column level is a critical capability in any modern database engine with the increasing number of data breaches, organizations must protect their sensitive information from unauthorized access. One way to accomplish this is through column-level encryption in SQL Server also applicable in Oracle. In this article, we will discuss how to implement column-level, including examples and use cases, while emphasizing the importance of security and performance efficiency.

What is Column-Level Encryption?

Column-level encryption is a method of encrypting individual columns within a database table. This type of encryption protects sensitive information such as credit card numbers, social security numbers, and personal information. When implemented correctly, column-level encryption can provide a higher level of security than database-level encryption.

To understand and implement encryption in SQL Server you need to understand its encryption hierarchy and key management architecture. Layers of encryption are protected by preceding layers of encryption that can use asymmetric keys, certificates, and symmetric keys.

enables the encryption keys that protect the database files to be stored outside of the SQL Server environment such as a smartcard, a USB device, and the EKM module of Hardware Security Module (HSM). It also helps secure the SQL Server instance from database administrators because they will not necessarily have access to the external EKM/HSM module.

is the root of the database engine’s encryption hierarchy and is generated automatically the first time it is needed to encrypt another key. By default, the SMK is encrypted using the Windows data protection API (DPAPI) at the operating system level, which uses the local machine key. The SMK can only be opened by the Windows service account that created it, or by a principal that knows the service account name and its password.

The Database Master Key (DMK) is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When created it is encrypted using AES_256 and a password you provide. Query the [sys].[symmetric_keys] catalog view to get information about the DMK.

An asymmetric key consists of a private and corresponding public key. Asymmetric encryption is computationally more expensive, but more secure than symmetric encryption. You can use an asymmetric key to encrypt a symmetric key within a database.

A symmetric key is a single key that uses encryption.
Symmetric encryption is generally used over asymmetric encryption because it is faster and less computationally expensive. Certificate Certificates are a digitally signed security object that contain a public (and optionally a private) key for SQL Server, which can generate certificates. You can also use externally generated certificates, and just like with asymmetric keys, certificates can be used in asymmetric encryption.

Implementing Column-Level Encryption in SQL Server

SQL Server offers two options for column-level encryption: symmetric and asymmetric encryption. Let’s take a look at both options.

To implement symmetric encryption in SQL Server, follow these steps:

• Step 1: Create a Master Key

The first step is to create a master key that will be used to protect the symmetric key. You can create a master key using the following T-SQL command:

• Step 2: Create a Symmetric Key

The next step is to create a symmetric key that will be used to encrypt and decrypt the data. You can create a symmetric key using the following T-SQL command:

• Step 3: Encrypt the Column

Once you have created the symmetric key, you can use it to encrypt the column using the following T-SQL command:

To decrypt the column, you can use the following T-SQL command:

To implement asymmetric encryption in SQL Server, follow these steps:

• Step 1: Create a Master Key

The first step is to create a master key that will be used to protect the asymmetric key. You can create a master key using the following T-SQL command:

• Step 2: Create an Asymmetric Key

The next step is to create an asymmetric key pair. You can create an asymmetric key pair using the following T-SQL command:

• Step 3: Create a Certificate

Once you have created the asymmetric key pair, you can create a certificate using the following T-SQL command:

• Step 4: Encrypt the Column

Finally, you can use the certificate to encrypt the column using the following T-SQL command:

Check this out another exercise!

USE tempdb; GO 

-- Create sample table 

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,EmployeeName VARCHAR(300),Position VARCHAR(100), Salary VARBINARY(128) ); GO 

-- Create SMK 

CREATE SYMMETRIC KEY SMK_Emp WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO 

-- Open SMK 

OPEN SYMMETRIC KEY SMK_Emp DECRYPTION BY PASSWORD = 'Pa$$w0rd'; GO 

-- Verify open keys 

SELECT * FROM sys.openkeys; GO 

-- Insert data 

INSERT Employees VALUES (1, 'Marcus', 'CTO', ENCRYPTBYKEY(KEY_GUID('SMK_Emp'),'$100000')); INSERT Employees VALUES (2, 'Christopher', 'CIO',ENCRYPTBYKEY(KEY_GUID('SMK_Emp'),'$200000')); INSERT Employees VALUES (3, 'Isabelle', 'CEO',ENCRYPTBYKEY(KEY_GUID('SMK_Emp'),'$300000')); GO 

-- Query table with encrypted values 

SELECT * FROM Employees; GO 

-- Query table with decrypted values 

SELECT *, CONVERT(VARCHAR, DECRYPTBYKEY(Salary)) AS DecryptedSalary FROM Employees; GO 

-- Close SMK 

CLOSE SYMMETRIC KEY SMK_Emp GO 

-- Query table with decrypted values after key SMK is closed 

SELECT *, CONVERT(VARCHAR, DECRYPTBYKEY(Salary)) AS DecryptedSalary FROM Employees; GO 

-- Clever CTO updates their salary to match CEO's salary 

UPDATE Employees SET Salary = (SELECT Salary FROM Employees WHERE Position = 'CEO') WHERE EmployeeName = 'Marcus'; GO 

-- Open SMK and query table with decrypted values 

OPEN SYMMETRIC KEY SMK_Emp DECRYPTION BY PASSWORD = 'Pa$$w0rd'; SELECT *, CONVERT(VARCHAR, DECRYPTBYKEY(Salary)) AS DecryptedSalary FROM Employees; GO 

-- Cleanup (Optional) 

DROP TABLE Employees; 
DROP SYMMETRIC KEY SMK_Emp; 
GO