Articles

Database Encryption Types: TDE, Column, App Explained

March 15, 2026 6 min read

Database encryption is often thought of as a single switch, but it’s actually a layered defense, and the most surprising truth is that encryption at rest doesn’t protect you from many of the most common data breaches.

Let’s see it in action. Imagine a simple table of customer data:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    credit_card_number VARCHAR(16) -- Sensitive data
);

INSERT INTO customers (customer_id, first_name, last_name, email, credit_card_number)
VALUES (1, 'Alice', 'Smith', 'alice.smith@example.com', '1234567890123456');

Now, let’s consider the different ways we can encrypt this data.

Full-Disk Encryption (FDE)

This is the most basic layer. FDE encrypts the entire storage device where the database files reside. When the operating system boots, it decrypts the disk, making the data accessible to the database engine.

How it works: The entire block device (e.g., /dev/sda1) is encrypted. Think of it like a locked safe where all your files are stored. The key to the safe is managed by the OS.

What it protects: Physical theft of the server or hard drives. If someone steals the server, they can’t read the data off the disks without the decryption key, which is typically tied to the running OS or a hardware security module.

What it doesn’t protect:

  • Insider threats: A privileged user on the running system can access the data.
  • Malware on the running system: If the OS is compromised, the attacker can access decrypted data.
  • Network-based attacks: If the database is accessible over the network and an attacker gains access to the running database process, FDE offers no protection.

Diagnosis: You can’t directly diagnose FDE’s effectiveness from within the database. It’s an OS-level configuration. Check your cloud provider’s documentation (e.g., AWS EBS encryption, Azure Disk Encryption) or your server’s OS configuration (e.g., cryptsetup on Linux).

Fix: Enable disk encryption at the OS or cloud provider level. For example, on AWS, you’d enable EBS encryption for your EC2 instances. This ensures that data written to the disk is automatically encrypted. The benefit is that any data residing on the physical storage is protected against unauthorized access if the drive is removed.

Database-Level Encryption (Transparent Data Encryption - TDE)

Most major database systems (SQL Server, Oracle, PostgreSQL, MySQL) offer Transparent Data Encryption. TDE encrypts the database files themselves – data files, log files, and backups – at rest. The database engine handles the encryption and decryption transparently to the applications connecting to it.

How it works: The database engine uses a master key (often stored in a key management system or the OS) to encrypt other keys, which in turn encrypt the data files. When a query requests data, the engine decrypts it on the fly before returning it.

What it protects:

  • Unauthorized access to database files: If someone gains direct access to the database files (e.g., by copying them from a backup or a misconfigured file share), the data will be unreadable without the database’s encryption keys.
  • Data in transit (indirectly): While TDE itself is for data at rest, it’s often paired with SSL/TLS for data in transit, creating a more robust security posture.

What it doesn’t protect:

  • Privileged database users: Users with SELECT or UPDATE privileges can still read and modify the data. The decryption happens within the database process.
  • Application-level vulnerabilities: If an application has a SQL injection vulnerability, an attacker can exploit it to read data that the database user the application connects with has access to.

Diagnosis: Check your database’s configuration.

  • SQL Server: SELECT * FROM sys.dm_database_encryption_keys; or SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; (TDE is often enabled via ALTER DATABASE YourDatabaseName SET ENCRYPTION ON; or ALTER DATABASE YourDatabaseName ENCRYPTION ENFORCED;).
  • Oracle: SELECT * FROM V$ENCRYPTION_KEYS; or SELECT DECRYPTION_MODE FROM V$ENCRYPTION_THROUGHPUT; (Enabled via ALTER DATABASE ... ENCRYPTION ENABLE;).
  • PostgreSQL: No built-in TDE. Often achieved with extensions like pgcrypto for column-level or FDE for disk.
  • MySQL/MariaDB: SHOW VARIABLES LIKE 'innodb_encrypt%'; (Enabled via ALTER TABLE ... ENCRYPTION = 'Y'; or innodb_encrypt_tables=ON;).

Fix: Enable TDE for your database. For example, in SQL Server:

-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'VeryStrongPassword123!';

-- Create a certificate to encrypt the master key (or use a DPAPI/EFS key)
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate';

-- Create a symmetric key to encrypt the database master key
CREATE SYMMETRIC KEY TDE_Key WITH ALGORITHM = AES_256,
KEY_SOURCE = 'TDE_Cert';

-- Open the symmetric key
OPEN SYMMETRIC KEY TDE_Key DECRYPTION BY CERTIFICATE TDE_Cert;

-- Encrypt the database master key with the symmetric key
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

-- Enable TDE on the database
ALTER DATABASE YourDatabaseName SET ENCRYPTION ON;

This protects the data files on disk, meaning if someone steals the physical database files, they cannot be read without the database’s encryption keys.

Column-Level Encryption

This is more granular. You encrypt specific sensitive columns within your tables, rather than the entire database file or disk.

How it works: You use database functions or application logic to encrypt and decrypt data for individual columns. This often involves generating unique keys for each column or even each row, managed by the application or a dedicated key management service (KMS).

What it protects:

  • Specific sensitive data fields: If an attacker gains access to the database but only has read access to a table, they might see encrypted values in columns you’ve chosen to protect.
  • Breaches where only table data is exfiltrated: If a subset of data is stolen, the most sensitive fields might remain protected.

What it doesn’t protect:

  • Data that is decrypted for application use: Once the data is decrypted by the application or database for display or processing, it’s vulnerable.
  • Key management complexity: Managing keys for thousands of columns or rows can become incredibly complex. A compromise of the key management system is catastrophic.

Diagnosis: You look at your table schema and query your data.

  • SQL Server (using ENCRYPTBYPASSPHRASE or ENCRYPTBYKEY):
    SELECT customer_id, email,
       -- Example: encrypting credit_card_number with a passphrase
       ENCRYPTBYPASSPHRASE('MySecretPassphrase', CAST(credit_card_number AS VARBINARY(MAX))) AS encrypted_cc
FROM customers WHERE customer_id = 1;
-- To decrypt
SELECT customer_id, email,
       CAST(DECRYPTBYPASSPHRASE('MySecretPassphrase', encrypted_cc) AS VARCHAR(16)) AS decrypted_cc
FROM (SELECT customer_id, email, ENCRYPTBYPASSPHRASE('MySecretPassphrase', CAST(credit_card_number AS VARBINARY(MAX))) AS encrypted_cc FROM customers WHERE customer_id = 1) AS EncryptedData;
  • PostgreSQL (using pgcrypto extension):
    -- First, enable the extension: CREATE EXTENSION pgcrypto;
-- Then, encrypt a column (assuming 'credit_card_number' is already in the table)
UPDATE customers SET credit_card_number = pgp_sym_encrypt('1234567890123456', 'my_secret_key');
-- To decrypt
SELECT pgp_sym_decrypt(credit_card_number::bytea, 'my_secret_key') FROM customers WHERE customer_id = 1;

Fix: Implement encryption functions in your SQL queries or application code. For example, using pgcrypto in PostgreSQL to encrypt the credit_card_number column:

-- First, create the extension if it doesn't exist
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt the existing data (assuming you have a 'secret_key')
UPDATE customers
SET credit_card_number = pgp_sym_encrypt(credit_card_number, 'your_very_secret_key_here');

-- To decrypt when needed (e.g., in a query that requires the CC number)
SELECT
    customer_id,
    first_name,
    last_name,
    email,
    pgp_sym_decrypt(credit_card_number::bytea, 'your_very_secret_key_here') AS decrypted_credit_card_number
FROM customers
WHERE customer_id = 1;

This ensures that the credit_card_number field is unreadable in the database files if the database itself is compromised, unless the attacker also compromises the application or database process that holds the decryption key.

Application-Level Encryption

This is the most flexible but also the most complex. The application encrypts data before it’s sent to the database and decrypts it after retrieving it. The database only ever stores ciphertext for these fields.

How it works: The application logic calls encryption libraries (e.g., OpenSSL, AWS KMS SDK) to encrypt sensitive data. The encrypted data is then stored in the database. When the application needs the data, it retrieves the ciphertext and uses its keys to decrypt it.

What it protects:

  • Database-level compromises: If an attacker gains direct access to the database files or even obtains read access to the database server, the sensitive fields will be indecipherable ciphertext. This is because the encryption keys are held by the application, not the database.
  • Insider threats within the DBA team: Database administrators cannot see the plaintext sensitive data.

What it doesn’t protect:

  • Application vulnerabilities: If the application itself is compromised (e.g., via SQL injection that bypasses your application’s logic, or if the application server is taken over), the attacker can access the keys and decrypt the data.
  • Key management: The application becomes the sole guardian of the keys. If the application’s key management is weak, the entire system is vulnerable.

Diagnosis: You examine the application code and observe the data stored in the database. The database column will contain seemingly random strings of characters, not recognizable plaintext.

  • Example in Python (using cryptography library):
    from cryptography.fernet import Fernet

# Generate a key (should be stored securely, not hardcoded!)
key = Fernet.generate_key()
cipher_suite = Fernet(key)

sensitive_data = "1234567890123456" # Credit card number

# Encrypt before sending to DB
encrypted_data = cipher_suite.encrypt(sensitive_data.encode())
print(f"Encrypted: {encrypted_data.decode()}")
# Store encrypted_data in the database as a BLOB or VARCHAR

# Decrypt after retrieving from DB
decrypted_data = cipher_suite.decrypt(encrypted_data)
print(f"Decrypted: {decrypted_data.decode()}")
    In your SQL, you’d see something like:
    INSERT INTO customers (customer_id, first_name, last_name, email, credit_card_number)
VALUES (1, 'Alice', 'Smith', 'alice.smith@example.com', 'gAAAAABf...'); -- Stored ciphertext

Fix: Integrate encryption/decryption logic into your application’s data access layer. For instance, in a Java application using a KMS:

// Assuming you have configured AWS SDK and have a KMS key ARN
String sensitiveData = "1234567890123456";
String kmsKeyId = "arn:aws:kms:us-east-1:123456789012:key/your-kms-key-id";

// Encrypt data using KMS
KMSClient kmsClient = KMSClient.builder().region(Region.US_EAST_1).build();
EncryptRequest encryptRequest = EncryptRequest.builder()
    .keyId(kmsKeyId)
    .plaintext(SdkBytes.fromUtf8String(sensitiveData))
    .build();
EncryptResponse encryptResponse = kmsClient.encrypt(encryptRequest);
byte[] encryptedBytes = encryptResponse.ciphertextBlob().asByteArray();
String encryptedData = Base64.getEncoder().encodeToString(encryptedBytes);

// Store 'encryptedData' in the database

// Decrypt data when retrieved from the database
byte[] encryptedDataToDecrypt = Base64.getDecoder().decode(encryptedData);
DecryptRequest decryptRequest = DecryptRequest.builder()
    .ciphertextBlob(SdkBytes.fromByteArray(encryptedDataToDecrypt))
    .build();
DecryptResponse decryptResponse = kmsClient.decrypt(decryptRequest);
String decryptedData = new String(decryptResponse.plaintext().asByteArray(), StandardCharsets.UTF_8);

This approach provides the strongest protection against database-level breaches because the encryption keys are completely isolated from the database server.

The fundamental trade-off across all these methods is between security and performance/complexity. The more granular and application-centric your encryption, the more secure it is against certain threats, but the more development effort and potential performance overhead you introduce.

The next thing to consider is how to manage the keys for these different encryption methods, especially when dealing with multiple environments or teams.

Want structured learning?

Take the full Cryptography course →