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.
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:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'VeryStrongPasswd';
- 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:
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'MyStrongPassword';
- 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:
UPDATE MyTable SET MyColumn = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), MyColumn);
To decrypt the column, you can use the following T-SQL command:
SELECT CONVERT(varchar, DECRYPTBYKEY(MyColumn)) AS MyColumn
FROM MyTable;
- 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:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStrongPassword';
- 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:
CREATE ASYMMETRIC KEY MyAsymmetricKey
WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'MyStrongPassword';
- Step 3: Create a Certificate
Once you have created the asymmetric key pair, you can create a certificate using the following T-SQL command:
CREATE CERTIFICATE MyCertificate
AUTHORIZATION dbo FROM ASYMMETRIC KEY MyAsymmetricKey;
- Step 4: Encrypt the Column
Finally, you can use the certificate to encrypt the column using the following T-SQL command:
UPDATE MyTable
SET MyColumn = ENCRYPTBYCERT(CERT_ID('MyCertificate'), MyColumn);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
