Encryption¶

Planning Column Encryption¶

When planning to protect data using encryption, take in mind the following considerations:

Data will be encrypted at rest.
Only the authorized db users defined in parameter ENCRYPTUSERALLOWED and [DECRYPTUSERALLOWED will be able to see data in clear or execute direct queries that can involve the protected column.]{.underline}
[The time to complete the encryption of a column depends of the number of rows and, the batch size (LIVE_BATCH_SIZE) and the assigned RAM to the Tomcat.]{.underline}

Note

Initial encryption is indeed a massive field update, which means the encryption occurs in-place.

Column Encryption Design (aligned to BIP SQL Proxy)¶

The BIP SQL Proxy applies policy-driven transforms on traffic (no app changes). Use these rules when encrypting columns that are PK/FK/join participants.

What the proxy guarantees¶

Transparency: intercepts SELECT/INSERT/UPDATE and transforms only the columns in TABLEFIELDSTOPROTECT.
Deterministic options: ENCRYPT_MODE=FPE (FF1/Card10) preserves length/format for equality joins.
Key custody: crypto keys live in CipherTrust Manager / Luna HSM; the proxy never persists keys.
Role-based cleartext: ENCRYPTUSERALLOWED / DECRYPTUSERALLOWED gate who can write/read cleartext.

Do / Don’t for PKs, composite keys, and FKs¶

Primary Keys (PK)

✅ Prefer surrogate PK (e.g., BIGINT IDENTITY or UUID) left in clear; encrypt the sensitive business field as a separate column.
✅ If a business field must be the PK: use deterministic FPE (same policy/key/alphabet/tweak), and revalidate uniqueness post-encryption.
❌ Don’t use non-deterministic modes for PKs (will break equality/joins).

Composite PKs

✅ Encrypt only the sensitive component(s), deterministically.
✅ Check index size and collation after encryption (FPE keeps length but not ordering semantics).
❌ Don’t mix different policies/alphabets across components of the same composite key.

Foreign Keys (FK)

✅ Both sides (referenced & referencing) must use the same policy: same ENCRYPT_MODE (FPE), same alphabet, same provider key ID, same tweak (if used).
✅ If you cannot encrypt both sides: use a token table (deterministic token → FK-safe) managed by policy - this is beyond of BIP scope.
❌ Don’t encrypt only one side with a different policy (FK validations and joins will fail).

Joins / Lookups

✅ Use deterministic FPE for columns used in equality joins.
❌ Avoid LIKE, BETWEEN, ORDER BY on ciphertext; add auxiliary clear/hash/sort columns if needed.

Metrics for initial encryption¶

In an environment of 16 GB RAM and 20 vCPU, and a latency \< 5ms, the BIP SQL Proxy was able to execute the below metrics

Test Metrics (40,000 updates)¶

Batch size (rows)	Time (min)	Time (s)	Throughput (upd/s)	ms/update
1,000	23	1,380	28.99	34.50
5,000	10	600	66.67	15.00
10,000	5	300	133.33	7.50

Calculations: throughput = 40,000 / seconds and ms/update = 1000 / throughput.

Estimate for 10,000,000 updates (same logic/environment)¶

Scenario (batch)	Throughput (upd/s)	Estimated time (hh:mm)	Hours
1,000	28.99	95:50	95.83
5,000	66.67	41:40	41.67
10,000	133.33	20:50	20.83

Queries Samples¶

Once the columns in tables are encrypted, the SQL queries should be the same, just make sure the db user is the one permitted.

Select queries sample¶

Select Query for multiple columns¶

SELECT TOP (1000) [BusinessEntityID]
      ,[PhoneNumber]
      ,[PhoneNumberTypeID]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[Person].[PersonPhone]

Watch the demo

Click to play → open on YouTube

Select Query for direct queries -equality match on encrypted column-using the encrypted column (no changes needed in query form)¶

SELECT TOP (5000) [id]
      ,[tarjeta]
      ,[telefono]
      ,[email]
      ,[documentoID]
      ,[nombre]
      ,[apellido]
      ,[frase_favorita]
      ,[direccion]
  FROM [AdventureWorks2019].[dbo].[DataGente] where documentoID = '0801735533'

Watch the demo

Click to play → open on YouTube

In INNER JOIN queries over encrypted columns, the BIP SQL Proxy supports joins as long as both sides use deterministic encryption¶

Encrypted FKs: temporarily disable/enable constraints

When foreign key columns are encrypted, the DBA should temporarily disable FK checks, perform the encryption, and then re-enable and re-validate them. Because the proxy uses deterministic encryption, equality is preserved and referential integrity remains valid once constraints are rechecked.

SQL Server (per table)

-- Disable FK checks during the encryption window
ALTER TABLE dbo.ChildTable NOCHECK CONSTRAINT ALL;

-- ... run your encryption job ...

-- Re-enable and re-validate constraints (trusted)
ALTER TABLE dbo.ChildTable WITH CHECK CHECK CONSTRAINT ALL;

Tips: run this in a maintenance window, re-check FK violations, and refresh stats/indexes if large volumes were updated.

-- Inner join on columns encrypted _documentID_
SELECT u.documentID,  u.name, u.lastname, COUNT(*) visits FROM 
dbo.Users u JOIN dbo.Visits v ON v.documentID=u.documentID 
GROUP BY u.name,u.lastname, u.documentID ORDER BY visits DESC;

-- Also is possible to do Inner joins using match values on encrypted columsn
SELECT u.documentID,  u.name, u.lastname, COUNT(*) visits FROM 
dbo.Users u JOIN dbo.Visits v ON v.documentID=u.documentID 
where u.documentID ='615909989027' 
GROUP BY u.name,u.lastname, u.documentID ORDER BY visits DESC;

Watch the demo

Click to play → open on YouTube