How To Secure PII Data In SQL Server Using CLE (Column Level Encryption)

Nitesh Jha
Oct 03, 2017
3.4k
0
0
Article

PII (Personally Identifiable Information) is used to identify individual identity such as SSN, DOB, Bank Account Number etc. When you are working with a financial application, you face the scenario where you have to store the data in an encrypted format so that no one can see the actual data.

While showing this data on the UI screen, it needs to be decrypted and masked as well. It means, we need to mask the PII data before sending to UI. As you have seen your account number, credit card number, mobile number, and SSN, these all are obfuscated (ex-Mobile SSN- XXXXX2398).

To secure the PII data in the database, the following techniques can be used.

.NET Assembly
CLE (Column-level encryption)
TDE (Transparent Data Encryption)

We have already implemented the PII with .NET assembly in the previous article. So now, let’s talk about the CLE.

CLE

Column level encryption is also known as Cell Level Encryption. It is the inbuilt feature of SQL Server introduced in SQL Server 2005. And, it is much faster compared to the .NET assembly encryption.

As we have a few columns which need to be protected like SSN, Account Number, DOB, Credit Card Number, so it does not make sense to encrypt the complete database. To implement CLE in your table, identify the column and make sure the column data type is VARBINARY. The process will have involved creating Database master key, Certificate, and symmetric key. When you save the PII data, it will be stored in the encrypted format, and you can create the View to show the PII data into original format because you can provide the specific role/ access to view the PII data.

Here, there is a scenario where the customer data needs to be stored in the encrypted format and the data also needs to be shown in obfuscated/ masked on some UI screens and Reports. To achieve this functionality, I have created a few SQL objects, such as stored procedure, functions, and View. The detailed steps are mentioned in the below section.

Solution

Perform the following steps to secure the Customer PII data using CLE.

Step 1

Create Master key in your database. (Use below query).

USE SampleDemo --Database Name
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='DEMOSU#12345&SAMI#SU'
GO

Step 2

Create the backup for your master key.

BACKUP MASTER KEY
TO FILE ='G:\Key\MyKey'
ENCRYPTION BY PASSWORD ='DEMOSU#12345&SAMI#SU'
GO

Creating a backup of the master key will help us to move the database from one server to another server. When you move the database from one server to another server, you may not be able to retrieve the encrypted data, so in that case, either you have to restore the master key through the backup which you have created or you have to use the query to open the master key. In the above query, I have created the backup in ‘G:\Key’ path with MyKey file name.

SQL

Step 3

Create Certificate in the database.

CREATE CERTIFICATE SampleCertificate
WITH SUBJECT ='DEMO',
EXPIRY_DATE='20251031'
GO

‘SampleCertificate’ is created with expiry date 31-10-2025.

Step 4

Create a symmetric key in the database.

CREATE SYMMETRIC KEY DemoKey
WITH ALGORITHM= AES_256 ENCRYPTION
BY CERTIFICATE SampleCertificate
GO

Once the Symmetric key is created, we can use this key to store the PII data in encrypted format.

Step 5

Create the table where the PII data needs to be stored.

CREATE TABLE [dbo].[Customer](
[CustomerID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NULL,
[CustomerDOB] [varbinary](max) NULL,
[CustomerSSN] [varbinary](max) NULL,
[CustomerPhone] [varchar](12) NULL
)

The important thing is here. We need to make sure that the data type of the column is VARBINARY if the PII data are required to be stored in encrypted format. In the above query, you can see that the DOB and SSN column data types are VARBINARY.

Step 6

After creating the Customer table, create the stored procedure to insert the record.

CREATE PROC usp_AddCustomer
@CustomerName VARCHAR(50),
@CustomerDOB VARCHAR(50),
@CustomerSSN VARCHAR(10),
@CustomerPhone VARCHAR(12)
AS
BEGIN
OPEN SYMMETRIC KEY DemoKey DECRYPTION
BY CERTIFICATE SampleCertificate
INSERT INTO Customer(CustomerName,CustomerDOB,CustomerSSN,CustomerPhone)
VALUES(@CustomerName,ENCRYPTBYKEY(KEY_GUID('DemoKey'),@CustomerDOB),
ENCRYPTBYKEY(KEY_GUID('DemoKey'),@CustomerSSN),@CustomerPhone)
END

The usp_AddCustomer SP is used to insert the record into Customer table. In this SP, you can observe, we are using symmetric key and certificate to encrypt the PII data. The EncryptedByKey() SQL function is used to encrypt the data. In customer table, only two columns, DOB and SSN, are the PII data.

Step 7

To add the record, run the below query.

[dbo].[usp_AddCustomer] 'Sam','1990-01-01','888899995','9876543212'

You can see the below record inserted into the Customer table.

SQL

Note: When we work with PII data, then somewhere in the database, we need to store the actual data and provide the limited access to that. To storing the actual data, usually, we create the SQL Views.

Step 8

Create View for your Customer table.

CREATE VIEW vw_Customer
AS
SELECT
CustomerID,
CustomerName,
CONVERT(VARCHAR(50),DECRYPTByKeyAutoCert(cert_id('SampleCertificate'),NULL,CustomerDOB)) AS CustomerDOB,
CONVERT(VARCHAR(50),DECRYPTByKeyAutoCert(cert_id('SampleCertificate'),NULL,CustomerSSN)) AS CustomerSSN,
CustomerPhone
FROM Customer

While creating the View, we have used the SQL function to decrypt the data and only authorized person can see it. DecryptByKeyAutoCer() function is used to decrypt the data with the help of certificate. The important thing here is to provide the authorization for your SQL Views so that the PII data can be restricted.

When we select the View, we can see the actual data below. We are fetching the data from newly created View.

SQL

Once the View is created, we can create the stored procedure to get customer details and send that back to UI.

Step 9

Crea a stored procedure to get the Customer details.

CREATE PROC usp_GetCustomerByID
@ID BIGINT
AS
BEGIN
SELECT CustomerID
,CustomerName
,CustomerDOB
,CustomerSSN
,CustomerPhone
FROM vw_Customer
WHERE CustomerID=@ID
END

This SP fetches the data based on customer ID. Let’s run this SP.

SQL

Now, our stored procedure is ready to fetch the record based on Customer ID but just observe the above screen. We are sending the actual PII data to the UI screen or reports. Actually, the data should be obfuscated/masked before sending to the UI. To achieve this functionality, we need to create the SQL function to obfuscate/ mask the DOB and SSN.

Step 10

Create function to mask the DOB.

CREATE FUNCTION [dbo].[ufn_DOB](@DOB VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
RETURN 'XX-XX-'+ CAST(YEAR(CAST(@DOB AS DATE)) AS VARCHAR(4))
END

This function takes the DOB as input and returns the masked data.

Step 11

Create function to mask the SSN.

CREATE Function [dbo].[ufn_SSN](@input nvarchar(100))
returns nvarchar(100)
AS
BEGIN
DECLARE @data nvarchar(100)
SELECT @data= 'XXXXX'+SUBSTRING(@input,6,4)
return @data
END

This function take SSN number as input and returns the masked SSN.

Step 12

Now, change your stored procedure which is used to fetch the customer data.

ALTER PROC usp_GetCustomerByID
@ID BIGINT
AS
BEGIN
SELECT CustomerID
,CustomerName
,dbo.ufn_DOB(CustomerDOB) AS CustomerDOB
,dbo.ufn_SSN(CustomerSSN) AS CustomerSSN
,CustomerPhone
FROM vw_Customer
WHERE CustomerID=@ID
END

Here, we are modifying the usp_getCustomerById SP to fetch the customer data with masking. You can see that in the observer in the above query, we have applied function for DOB and SSN.

Now, run this SP.

SQL

The final obfuscated/masked data can be sent to the UI screen or reports.