Column-level encryption is a database security technique that encrypts the values of selected columns, such as national identity numbers, card numbers or medical notes, so that they are stored as ciphertext while the rest of the table stays in plaintext. Only users or applications holding the right decryption key can read the protected values, even if they can query the table itself.
In databases, column-level encryption sits between whole-database encryption at rest and access controls. Unlike transparent data encryption, it keeps data encrypted in memory, in query results, in backups and in logs until a key holder decrypts it, which is why it is used to protect the most sensitive fields against administrators, attackers with SQL access and leaked dumps.
How column-level encryption protects data
Each protected value is encrypted with a symmetric algorithm, today typically AES with 256-bit keys in an authenticated mode such as GCM, before it is written to the column. The stored value combines the ciphertext with an initialization vector and often an authentication tag, so the column type is usually binary or encoded text, and stored values grow in size.
Keys are organized in a key hierarchy. A data or column encryption key encrypts the values, and a master key encrypts that data key. The master key lives outside the database in a key management service or hardware security module. This pattern, called envelope encryption, allows master keys to be rotated without re-encrypting every row, and it keeps the key that unlocks everything away from the data it protects.
Where encryption and decryption happen
The most important design decision is where plaintext and keys exist. If the database server decrypts values, a database administrator or an attacker on the server may still see them; if only the client decrypts, the server never holds plaintext.
| Approach | Who holds the key | Typical implementations |
|---|---|---|
| Database functions | Database server during the query | pgcrypto in PostgreSQL, symmetric keys in SQL Server |
| Built-in column encryption | Database engine with a keystore | Oracle TDE column encryption |
| Client-side driver encryption | Client driver only | SQL Server Always Encrypted, MongoDB client-side field level encryption |
| Application-level encryption | Application code and a KMS | Encryption libraries before the INSERT |
Client-side encryption offers the strongest separation, because the server stores and returns only ciphertext. Its cost is functional: the database cannot compute on values it cannot read, so filtering, sorting and aggregation on encrypted columns are restricted or impossible without special schemes.
Deterministic and randomized encryption
Encrypted columns use one of two schemes, and the choice decides what queries remain possible and how much information leaks.
| Scheme | Behavior | Queries supported | Leakage |
|---|---|---|---|
| Randomized encryption | Same plaintext gives different ciphertext each time | None on the encrypted value | Minimal |
| Deterministic encryption | Same plaintext always gives the same ciphertext | Equality lookups, joins, grouping | Reveals equal values and frequencies |
| Blind index | Keyed hash stored next to randomized ciphertext | Equality search via the hash | Reveals equal values |
| Queryable or enclave-based encryption | Special encrypted structures or secure enclaves | Equality, sometimes range | Depends on the design |
Deterministic encryption is risky for low-cardinality columns. Encrypting a gender or blood group field deterministically produces only a few distinct ciphertexts, and an attacker who knows the population distribution can often infer the values by frequency analysis. Randomized encryption is the default unless equality search is genuinely required.
Column-level encryption compared with related controls
Column-level encryption is often confused with other database security controls. They address different threats and are usually combined rather than chosen exclusively.
| Control | What it protects against | Data visible to privileged users |
|---|---|---|
| Column-level encryption | SQL access without keys, leaked dumps | No, unless they hold the key |
| Transparent data encryption | Stolen disks, files and backups | Yes, decrypted on read |
| Column-level security | Unauthorized queries on columns | Yes for administrators |
| Dynamic data masking | Casual exposure in query results | Yes, masking is easily bypassed by privileged users |
| Row-level security | Access to other tenants’ or users’ rows | Yes for administrators |
A common architecture applies transparent data encryption to everything, column-level encryption to a handful of high-risk fields, and column-level security or roles to limit who can query the rest. Regulations and standards such as PCI DSS for card data and data protection laws for personal data frequently motivate this layered approach.
Performance, key management and common pitfalls
The CPU cost of AES is small on modern processors with hardware acceleration, but indirect costs dominate: indexes on randomized columns are useless, range queries require decrypting many rows, and storage per value increases. Encrypting only the columns that truly need it keeps these costs manageable.
- Keys stored in the same database as the data, which defeats the purpose if the database is stolen.
- Keys passed as literals in SQL, where they can appear in query logs, statement monitoring and error messages.
- No rotation plan: changing a data key requires re-encrypting every affected row, which must be scheduled in batches.
- Plaintext leaking elsewhere, for example in application logs, caches, search indexes or analytics exports.
- Losing the master key, which makes the encrypted data permanently unreadable without a tested key backup.
In simple terms
Column-level encryption locks individual fields in a database rather than the whole database. Imagine a filing cabinet where most documents are in open folders, but the folder with passport numbers is inside a locked envelope. People who can open the cabinet still cannot read the envelope without its separate key.
This means that even someone with wide access, or a thief with a copy of the data, sees only scrambled text in those fields. The trade-off is convenience: locked fields are harder to search and sort, so only the truly sensitive ones are usually encrypted.
A practical example
A health insurance company stores member records in a SQL Server database used by several internal teams. Its compliance team requires that national identity numbers and diagnosis codes be unreadable to database administrators and to anyone who obtains a backup. The engineers apply client-side column encryption to both columns, with master keys held in a hardware-backed key vault.
Identity numbers use deterministic encryption so that the claims system can still look up a member by exact number, while diagnosis codes use randomized encryption because they are never searched directly. Reporting teams receive pseudonymized extracts instead. During a later audit, a copied backup file is shown to reveal only ciphertext in the protected columns, and the company passes the review without changes to its applications’ business logic.