Encrypting PII at Rest Under a Deadline
A company must encrypt customer PII (names, addresses, national ID numbers) at rest before an audit in 30 days. The database team proposes using AES-256-GCM with a single key stored in an environment variable on each app server. The security team prefers envelope encryption with a KMS-managed master key and per-record data keys. The application also needs to search by national ID for customer support, and the team is considering deterministic encryption for that field. There are 12 million records and a legacy batch job that reads the database directly. The audit requires evidence of key management and access control.
1. Compare the two key-management proposals and recommend one, with reasoning.
Read the worked answer
A single key in an environment variable is easy to leak through logs, process listings, backups, or misconfigured deployments, and rotation requires re-encrypting everything. It also gives every app server the same key with no audit trail. Envelope encryption with a KMS master key and per-record data keys limits exposure: data keys can be rotated, access is logged and policy-controlled, and compromise of one data key does not expose all records. The KMS approach also satisfies audit evidence requirements for key management. The recommendation is envelope encryption. The legacy batch job must be brought into the same key-access model or given a scoped, audited role; direct database reads of ciphertext are useless without decryption, so its access pattern must be redesigned.
2. Is deterministic encryption appropriate for the national ID search field? Explain the trade-offs and alternatives.
Read the worked answer
Deterministic encryption allows equality search because the same plaintext yields the same ciphertext, but it leaks equality patterns: an attacker with database access can see which records share an ID and can mount frequency analysis, especially for structured IDs. Given national ID numbers have limited entropy and predictable formats, this is risky. Alternatives include a blind index: store a keyed HMAC of the normalized ID in a separate column for exact-match lookup, while storing the ID itself with randomized encryption. The HMAC key must be protected like the encryption key. Another option is tokenization with a vault. If deterministic encryption is used, scope it to the single field, document the leakage, and restrict access. The blind index is generally preferable because it separates search from decryption.