You agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.
DisagreeAgree
Home
Blog
Column Level Encryption
...

Column-Level Encryption 101: What is It, implementation & Benefits

Security
Ariel Shiftan

CTO & Co-founder

June 20, 2023
8
 min read
Table of content:
Join our newsletter

Your privacy is important to us, privacy policy.

In recent years, data breaches have become a common occurrence, leaving companies scrambling to contain the fallout and consumers worried about the security of their personal information. The impact of such events is alarming, with Marriott experiencing multiple data breaches that compromised the name, email, phone numbers, and addresses of over 500 million guests, and Zynga's 2019 data breach that leaked 173 million unique email addresses, usernames, and locations.

One effective technological measure to mitigate this risk is column-level encryption, which provides an additional layer of protection for sensitive Personal Identifiable Information (PII) data such as usernames, email addresses, physical address, birth dates, phone numbers, and Payment Card Information (PCI) such as credit card numbers and card holder name, as well as Protected Health Information (PHI). By encrypting individual columns of data, organizations can limit access to the data, reduce the potential damage of a breach and help ensure the privacy of their customers' information.

In this post, we will explore the power of column-level encryption for data security. So let’s dive in.

Looking to protect data right away? You're welcome to create a free Vault account and start using our data protection APIs.

What is Column Level Encryption?

Column-level encryption is a technique that encrypts specific data columns within a database table, as opposed to encrypting the entire table or database. By implementing this method, sensitive data remains protected even if the rest of the database or table is compromised.

This security approach encrypts particular columns containing sensitive data, rendering it unreadable without the corresponding decryption keys. As a result, even if an attacker gains unauthorized access to the database or table, the encrypted data remains secure. Column-level encryption employs encryption keys, typically stored separately from the encrypted data, to provide an additional layer of security.

Separation inhibits unauthorized individuals from decrypting the data, even if they manage to access the encrypted data. Consequently, column-level encryption protects sensitive information from compromise and assists in maintaining data confidentiality amidst potential security breaches.

Ultimately, column-level encryption allows for granular control over which data elements are encrypted and is often employed to secure sensitive information, including passwords, bank account numbers, social security numbers, and credit card numbers. Common algorithms used in implementing column-level encryption encompass mostly Advanced Encryption Standard (AES), and then Blowfish, and Triple DES.

The graphic shows a table comparing important characteristics of encryption algorithms like block size, speed and security for the following: DES, 3DES, Blowfish and the ubiquitous standard AES.
Source

Benefits of Column Level Encryption for Data Security

Flexibility

The flexibility associated with column-level encryption stems from its capacity to selectively encrypt specific columns within a database. This enables organizations to customize their encryption strategy to meet specific data protection needs. Moreover, column-level encryption allows the encryption of data in diverse environments. It is applicable to various types of databases, from relational and NoSQL databases to cloud-based data storage systems.

Enhanced Data Security

With each column having its unique encryption key, column-level encryption provides enhanced data security and eliminates the risk of an attacker accessing all the sensitive data by merely cracking one encryption key. Furthermore, column-level encryption provides an extra layer of security on top of existing security measures like full disk encryption and access control.

Access Control

Perhaps the most valuable advantage. Column-level encryption enables organizations to have granular access control, and they can define access rights for each user or group of users, based on their roles and responsibilities. This makes it easier to manage access to data elements and ensures that sensitive data is always protected from unauthorized access. Moreover, access control helps in meeting regulatory compliance requirements such as GDPR and CCPA.

Cost-Effective

Column-level encryption is a highly cost-effective method for ensuring data security as it doesn't require additional hardware or software. It's an integral part of modern database management systems (DBMS). Furthermore, column-level encryption acts as a proactive security measure that helps alleviate potential financial consequences of data breaches. By encrypting sensitive data at the column level, organizations markedly decrease the risk of unauthorized access and the associated costly data breach remediation efforts.

Minimal Performance Impact

Column-level encryption minimally impacts database performance, realistically to a negligible hit. Column-level encryption offers granularity, permitting organizations to selectively encrypt only the sensitive columns. Furthermore, data is decrypted only upon access, ensuring the decryption process does not impair the overall performance of the database.

Understanding Database Encryption

Database encryption, a vital aspect of data security, employs cryptographic techniques to protect sensitive data stored in databases from unauthorized access. The data is encrypted both in transit and at rest, making it indecipherable to those without the appropriate decryption keys. This ensures sensitive data remains confidential even if unauthorized individuals gain access to the database or underlying storage. Which is a big plus today in the era of public cloud providers, thus eliminating additional potential access paths to the data.

Moreover, database encryption adds an extra layer of security against physical theft or loss of storage media. Cryptographic techniques, such as message authentication codes (MACs) or digital signatures, are used to guarantee data integrity against tampering. Various encryption methods are available, and we will explore some of the most prevalent ones in this post.

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a feature offered by Database Management Systems (DBMS) that facilitates encryption at the storage level, providing significant transparency to the application and user. The primary benefit of TDE is its automated encryption of data as it's written to disk, and subsequent decryption when read, requiring no additional actions from the user.

As the process of encryption and decryption is transparent to both the application and the user, it has earned the moniker "transparent" data encryption. This implies that users don't need to perform specific actions to access encrypted data. The SQL Server instance automatically decrypts data upon access, allowing users to interact with the database as usual.

The graphic shows how the the master key being used together with the server certificate to encrypt and decrypt data.
Source

TDE encrypts the entire database, including data files, log files, and backups, without requiring any changes to the application. The encryption key used in this method is generally stored in the database to ensure that only authorized users can access the data.

As you might understand this is a weak security measurement in the era of cloud computing as physical hard disk theft is highly unlikely. However, it might still be recommended because of multi-tenants and other attack vectors that may come from being run on public shared servers.

Cell-Level Encryption

Cell-level encryption, also known as field-level encryption, is a technique that encrypts individual cells or fields within a database table. This technique provides granular access control, allowing only authorized users to access sensitive data.

The graphic shows three levels of encryption: windows level, SQL server level, and database level. At the Windows level, the Service Master Key is encrypted with DPAPI.
Source

With cell-level encryption, data is encrypted as soon as it enters the system or is added to the database, whether by the application or the Database Management System (DBMS). Application-level encryption allows for more customization and flexibility but demands extra development effort. Conversely, DBMS-level encryption offers a streamlined and integrated approach, albeit with potentially less flexibility. 

The choice primarily depends on the specific security requirements and considerations relevant to both the application and the organization. The encryption key, stored separately from the data, is only accessible to authorized individuals with the necessary credentials to decrypt the data. However, the implementation of cell-level encryption can be complex and resource-intensive. It often requires modifications to both the application code and database structure, which should be factored into planning and execution phases.

Column-Level Encryption

Column-level encryption is a technique that encrypts specific columns or fields within a database table. This process, conducted by the application or the Database Management System (DBMS), allows for granular access control, flexibility, enhanced data security, and minimal performance impact.

A significant benefit of column-level encryption lies in its ability to secure data even if an attacker gains access to the database and the underlying storage. Any unauthorized attempt to access the database or physical storage medium will only reveal encrypted values, which are meaningless without the corresponding encryption key.

To provide comprehensive data security, column-level encryption is often paired with other security measures, such as Transparent Data Encryption (TDE) and cell-level encryption. This integrated approach amplifies the defense against potential security breaches and ensures the utmost protection of sensitive data.

Comparing Encryption Methods

There are various methods for encrypting data in a database, each with its strengths and weaknesses. The optimal approach depends on the application's requirements and the stored data.

This image shows the three leyers of encryption, starting with Transparent Data Encryption (TDE), Cell-level encryption, and column-level encryption.
This image shows the three leyers of encryption, starting with Transparent Data Encryption (TDE), Cell-level encryption, and column-level encryption.
  • Transparent Data Encryption (TDE) encrypts the entire database without any application code changes, providing high security. However, it may impact database performance and lacks granular access control. The key is stored in the database and can be protected by a master key stored in an external key management system.
  • Cell-level encryption offers granular access control by restricting access to specific database table fields or cells. However, it can affect database speed and is complicated to implement. The encryption key is generally stored separately from the encrypted data, often in a key management system (KMS).
  • Column-level encryption encrypts specific columns with negligible performance impact and provides granular access control. However, it can be complex to implement and does not encrypt the entire database. The key is stored in a key management system such as Google Cloud KMS rather than the database itself.

To ensure data security, reliance on a single encryption method might not suffice. Implementing multiple layers of protection is essential. By integrating different encryption methods, such as column-level and cell-level encryption, a robust and secure infrastructure can be established. This combination enables organizations to achieve enhanced data security and control.

Column-level encryption safeguards the overall structure and access to specific columns, while cell-level encryption provides an extra layer of protection for individual data cells. This dual approach significantly reduces the risk of unauthorized data access. Even if an attacker gains access to the database, the encrypted data remains unreadable without the corresponding keys.

Employing multiple encryption strategies compensates for any potential weaknesses or vulnerabilities in a single method, thereby ensuring comprehensive data security in the event of a breach. This multi-faceted approach fortifies defenses and instills confidence in the overall data protection strategy.

Implementing Column-Level Encryption in SQL

Choosing the appropriate encryption method for your requirements is crucial when encrypting sensitive data. The suitable encryption method depends on the specific use case, ensuring that the security and confidentiality of the data are maintained. Selecting the appropriate encryption algorithm requires careful consideration of requirements and factors such as performance, compliance, and available resources.

To better understand column-level encryption implementation, let's look at an example:

Step 1: Create a new database

CREATE DATABASE MyDatabase;
USE MyDatabase;

Step 2: Create a new table to store sensitive data

CREATE TABLE MySensitiveData
(
ID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
SocialSecurityNumber CHAR(9) NOT NULL,
CreditCardNumber CHAR(16) NOT NULL
);

Step 3: Enable encryption in SQL Server

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';

It should be noted that, in SQL Server, both Certificates and Symmetric keys can be used to encrypt data. Certificates are X.509 certificates that can be used to encrypt and sign data, and they are useful when the identity of the party encrypting or signing data needs to be verified.

Symmetric keys, on the other hand, are specifically used for column-level encryption. The name "Symmetric keys" comes from the fact that it uses the same key to both encrypt and decrypt data, making it suitable for fast encryption and decryption of large amounts of data.

Let's explore both options in more detail:

Step 4(a): Create a certificate for column-level encryption

CREATE CERTIFICATE MyCertificate
  WITH SUBJECT = 'My Column Encryption Certificate';

OR

Step 4(b): Create a symmetric key for column-level encryption

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';

CREATE SYMMETRIC KEY MySymmetricKey
  WITH ALGORITHM = AES_256
  ENCRYPTION BY MASTER KEY;

Step 5: Alter the table to add encrypted columns

USE MyDatabase;
ALTER TABLE MySensitiveData
ADD EncryptedSocialSecurityNumber VARBINARY(256),
  EncryptedCreditCardNumber VARBINARY(256);

Step 6: Encrypt the sensitive data using the symmetric key

OPEN SYMMETRIC KEY MySymmetricKey
  DECRYPTION BY MASTER KEY;

UPDATE MySensitiveData
SET EncryptedSocialSecurityNumber = EncryptByKey(Key_GUID('MySymmetricKey'), SocialSecurityNumber),
  EncryptedCreditCardNumber = EncryptByKey(Key_GUID('MySymmetricKey'), CreditCardNumber);

Step 7: Decrypt the data

OPEN SYMMETRIC KEY MySymmetricKey
  DECRYPTION BY PASSWORD = 'MyPassword';

SELECT ID, FirstName, LastName,
  CONVERT(NVARCHAR(50), DecryptByKey(EncryptedSocialSecurityNumber)) AS SocialSecurityNumber,
  CONVERT(NVARCHAR(50), DecryptByKey(EncryptedCreditCardNumber)) AS CreditCardNumber
FROM MySensitiveData;

Importance of Key Management in Column-Level Encryption

Data protection is not solely accomplished by utilizing an encryption algorithm. Effective key management is also essential to achieve the desired level of security for sensitive information. It is a critical component of any encryption solution, including column-level encryption.

Note that poor key management practices can lead to the compromise of the confidentiality of the data.

Some key management practices to consider when implementing column-level encryption include using a strong and unique encryption key for each column, utilizing a secure key storage mechanism, implementing key rotation policies, using a robust key distribution mechanism, and enforcing strong access control and authentication mechanisms.

Key Management Service (KMS) is a cloud-based key management system that provides a secure and highly available repository for storing and managing cryptographic keys used for data encryption, decryption, and signing. Most public clouds, such as AWS, GCP, and Azure, support KMS.

BTW, in our Vault solution, you don't need to use keys directly or manage them, it's all done transparently for you, hence a more hardened solution. You can create an account and start for free here.

As an example, let us take a look at how Google Cloud KMS can be used with column-level encryption in SQL Server using T-SQL and the Google Cloud KMS Client Library for Python:

Step 1: Create a keyring and a key in Google Cloud KMS:

This step creates a new keyring in Google Cloud KMS and then creates a new symmetric key within that keyring. The key is used for encrypting and decrypting data in SQL Server.

gcloud kms keyrings create [KEYRING_NAME] --location global
gcloud kms keys create [KEY_NAME] --location global --keyring [KEYRING_NAME] --purpose encryption


Replace [KEYRING_NAME] and [KEY_NAME] with your own values.

Step 2: Install the Google.Cloud.Kms.V1 Python package:

This step installs the Google Cloud KMS client library for Python. The library is used in Step 5 to decrypt the encrypted value of the column encryption key.

pip install google-cloud-kms

Step 3: Create a column master key in SQL Server using the Cloud KMS provider:

This step creates a new column master key (CMK) in SQL Server that uses the Cloud KMS provider. The CMK is used to protect one or more column encryption keys.

CREATE COLUMN MASTER KEY [MyCMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N'GCP_KMS',
KEY_PATH =
N'projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING_NAME]/cryptoKeys/[KEY_NAME]'
);

Note: Replace [PROJECT_ID], [LOCATION], [KEYRING_NAME], and [KEY_NAME] with your own values.

Step 4: Create a column encryption key using the Cloud KMS provider:

This process generates a new column encryption key (CEK) in SQL Server, leveraging the Google Cloud KMS provider's encryption capabilities. The encryption process yields an encrypted representation of the CEK, a binary value that cannot be interpreted. This ensures the CEK's safety and security. The encrypted value of the CEK is then stored within the database, specifically within a designated column reserved for encrypted key values. This strategic storage further bolsters the data's overall protection, maintaining the encryption integrity of the system.

CREATE COLUMN ENCRYPTION KEY [MyCEK]
WITH VALUES
(
COLUMN_MASTER_KEY = [MyCMK],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = [ENCRYPTED_VALUE]
);

Note: Replace [MyCMK] and [ENCRYPTED_VALUE] with your own values

Step 5: Use the Google Cloud KMS Client Library for Python for decryption:

This step retrieves the encrypted value of the column encryption key (CEK) from the database and uses the Google Cloud KMS client library for Python to decrypt it.

# Import the required libraries
from google.cloud import kms_v1
from google.oauth2 import service_account
import mysql.connector

# Connect to database to get cursor
cnx = mysql.connector.connect(user='[DB_USERNAME]', password='[DB_PASSWORD]', host='[DB_HOST]', database='[DB_NAME]')
cursor = cnx.cursor()

# Execute the SQL query to get the encrypted value of the column encryption key
query = "SELECT encrypted_value FROM [TABLE_NAME] WHERE [ID_COLUMN] = [ID_VALUE];"
cursor.execute(query)
encrypted_value = cursor.fetchone()[0]

# The credentials is not the encryption key, but Google cloud service account
credentials = service_account.Credentials.from_service_account_file('[PATH_TO_CREDENTIALS_JSON]')
client = kms_v1.KeyManagementServiceClient(credentials=credentials)
response = client.decrypt(
request={
'name': '[KEY_PATH]',
'ciphertext': encrypted_value,
}
)

# Get the final value
plaintext_value = response.plaintext

Note: Replace [DB_USERNAME], [DB_PASSWORD], [DB_HOST], [DB_NAME], [TABLE_NAME], [ID_COLUMN], [ID_VALUE], [PATH_TO_CREDENTIALS_JSON], and [KEY_PATH] with your own values.

Step 6: Use the plaintext value of the column encryption key to encrypt and decrypt data in SQL Server:

This step shows how to use the plaintext value of the column encryption key to encrypt and decrypt sensitive data in SQL Server. Data is encrypted using the EncryptByKey function and decrypted using the DecryptByKey function.

-- Encrypt data using the column encryption key
INSERT INTO [TABLE_NAME] ([ENCRYPTED_COLUMN])
VALUES (CONVERT(varbinary, EncryptByKey(Key_GUID('[MyCEK]'), '[SENSITIVE_DATA]')));

-- MyCEK is hardcoded

-- Decrypt data using the column encryption key
SELECT CONVERT(varchar, DecryptByKey([ENCRYPTED_COLUMN])) AS
[DECRYPTED_COLUMN]
FROM [TABLE_NAME];

Note: Replace [TABLE_NAME], [MyCEK], and [SENSITIVE_DATA] with your own values.

Format-Preserving Encryption

Format-Preserving Encryption (FPE) is a unique encryption type that enables data encryption while preserving the original data's format, including length and character set as required. Some FPE algorithms, such as the FF1 algorithm outlined in the NIST SP 800-38G standard, strive to retain the length of sensitive data. However, other FPE algorithms may prioritize different security aspects, such as achieving robust randomness or ensuring semantic security.

Consider the following scenario to comprehend the significance of FPE: Suppose you have a database storing user email addresses designed to accept entries in the 'user@domain.com' format. You need to encrypt these email addresses for security, yet you don't wish to alter the database structure or disrupt the integration of services dependent on this specific email format (e.g. another off SaaS connected to your backend).

In such a case, FPE can encrypt the email addresses while conserving their original format, therefore not breaking functionality of existing systems that expect to see a specific type of data. It ensures that the encrypted values retain the same format, length, and character set as the original email addresses. For instance, if you encrypt 'user.name@example.com' using FPE, the resulting ciphertext would still appear as a valid email address, such as 'zZ1xY.@d94c.k26Y'.

Therefore, FPE allows you to protect sensitive email information without disrupting the database operation or any downstream systems requiring particular email address formatting.

This graphic shows the process of format-preserving encryption (FPE). The sample input is user.name@example.com. When it undergoes AES encryption alone, the output is a different format: BtgEDY+2E4ogkrto… When it undergoes AES encryption with FPE, the format is preserving and so the output is zZ1xY.@d94c.k26Y.
This graphic shows the process of format-preserving encryption (FPE). The sample input is user.name@example.com. When it undergoes AES encryption alone, the output is a different format: BtgEDY+2E4ogkrto… When it undergoes AES encryption with FPE, the format is preserving and so the output is zZ1xY.@d94c.k26Y.

FPE works by converting the original data into a binary string and then applying an encryption algorithm to that binary string. This approach helps to safeguard against data breaches while minimizing disruption to business processes that rely on data format. However, by the nature of being limited by the length of the original data, this encryption might be weaker in practice.

Security Considerations for Column-Level Encryption

Column-level encryption offers robust data security, but certain critical security factors must be considered to ensure the encryption's effectiveness. By implementing best practices for key management, access control, performance optimization, and compliance requirements, column-level encryption can provide reliable data protection for sensitive data in databases.

Performance Implications

Column-level encryption can affect the performance of database operations, particularly for large databases or frequently accessed columns. Therefore, it's vital to consider these potential performance impacts when designing database structures and indexes.

Access Control and User Authentication

Access to encrypted data should be strictly limited to authorized individuals. User authentication mechanisms should be employed to regulate access to such data. This control can be achieved using database roles and permissions or via external access control systems.

Backup and Recovery

It's crucial not only to encrypt sensitive data but also to secure its backup. Moreover, backups of encryption keys should be considered to avoid a single point of failure. These backup keys should be stored separately from the encrypted data, possibly in secure offsite locations or using secure backup solutions. This approach ensures their availability in case of a disaster or system failure.

Key Accessible to All Relevant Parties

In certain scenarios, multiple parties or microservices may need access to the same encrypted data. This can increase the exposure of encryption keys, making them potential vulnerability points. Also, providing access to encryption keys to developers and DBAs can introduce the risk of key mishandling or unauthorized data access. To prevent such incidents, clear norms and procedures should be established for distributing encryption keys, keeping key access restricted.

Searching Encrypted Data

Encryption transforms data into unreadable ciphertext, which hinders direct search operations on encrypted values. Organizations often need to use alternative methods, like deterministic encryption algorithms or maintaining separate unencrypted search/blind indexes, to execute search operations. This careful balance between data security and search functionality adds complexity and overhead to the search process and requires thoughtful design.

Conclusion

By implementing column-level encryption, organizations can take a proactive step towards building a more secure and resilient data infrastructure to protect against modern security threats. This powerful technique can significantly enhance data security in databases, practically eliminating many potential threats and breaches. As technology advances and cyber threats evolve, it is essential for organizations to stay ahead of these threats by adopting robust security measures such as column-level encryption.

Create a free Vault account and start using our data protection APIs, they provide encryption and many more necessary data attack mitigations built-in.

Share article
About the author
Ariel Shiftan

CTO & Co-founder

Ariel, despite holding a PhD in Computer Science, doesn't strictly conform to the traditional academic archetype. His heart lies in the realm of hacking, a passion he has nurtured since his early years. As a proficient problem solver, Ariel brings unmatched practicality and resourcefulness to every mission he undertakes.

# Tags: 
No items found.

Powering Data Protection

Skip PCI compliance with our tokenization APIs

Skip PCI compliance with our tokenization APIs

It all begins with the cloud, where applications are accessible to everyone. Therefore, a user or an attacker makes no difference per se. Technically, encrypting all data at rest and in transit might seem like a comprehensive approach, but these methods are not enough anymore. For cloud hosted applications, data-at-rest encryption does not provide the coverage one might expect.

John Marcus

Senior Product Owner

const protectedForm = 
pvault.createProtectedForm(payment Div, 
secureFormConfig);

Continue your reading

See all articels
Text Link
Security
x

Introduction to Software Reverse Engineering [To Non-techies]

By 

Gil Dabah

8
 min read
April 24, 2024
Text Link
Security
x

A New Emerging Data Security Category: AppSec for Data

By 

Gil Dabah

5
 min read
April 1, 2024
Text Link
Security
x

PCI DSS Compliance Checklist: The 12 Requirements [2024]

By 

David Erel

7
 min read
March 26, 2024
Text Link
Security
x

What Is Data Leakage? Types, Causes & Prevention

By 

Gil Dabah

7
 min read
February 13, 2024
No items found.
Thank you! Your submission has been received!

We care about your data in our privacy policy

Oops! Something went wrong while submitting the form.
Submit
This is some text inside of a div block.

Piiano offers developer-friendly privacy and security products.

Piiano Privacy Solutions US, Inc

135 W. 50th St. Suite 200

New York, NY 10020

Product

Piiano VaultPCI TokenizationPiiano FlowsPricing

Company

About usCareersContact usBook a demo

Resources

DocsBlogPrivacy by DesignData Tokenization
What is PIIPII ProtectionColumn - Level Encryption 101
PII By Design TM Cheat Sheet

Piiano offers developer-friendly privacyand security products.

Piiano offers developer-friendly privacy and security products.

Piiano Privacy Solutions US, Inc

135 W. 50th St. Suite 200

New York, NY 10020

Piiano® is a registered trademark owned by Piiano Privacy Solutions LTD. © 2021-2024

Terms of UsePrivacy Policy