MediTrace: ECB, one key everywhere, and a nine-hour re-encryption
MediTrace stores 12 million patient records in MySQL. The application encrypts the SSN, date of birth and diagnosis columns with AES-256 in ECB mode using a single key held in an environment variable - the same key in dev, staging and production. Where a mode would require an IV, the primary key (a sequential bigint) is used. Two years ago the team handed an analytics vendor a full dump encrypted under that same key.
Password columns hold SHA-256(password + one global salt). A new partner integration requires TLS, but the partner's load balancer only supports TLS 1.0 with 3DES cipher suites. The DBA estimates a full re-encryption needs nine hours of exclusive table locking; the SLA permits 15 minutes of read-only degradation but no downtime. Regulators require encryption at rest and documented key rotation.
1. Rank the cryptographic weaknesses by risk, explain the concrete attack each enables, and identify which are compliance failures with lower practical risk.
Read the worked answer
1. One key across all environments plus a copy at a vendor - highest. Anyone with dev access, CI logs, a heap dump, or the vendor's archive decrypts production PHI without ever touching production. You cannot revoke the vendor's copy without rotating everything. This is the only item that is a complete compromise.
2. SHA-256 with a single global salt for passwords - high. A fast hash runs at billions of guesses per second on a GPU, and one global salt means one precomputed table covers every user and identical passwords produce identical digests. Any database read becomes an offline cracking exercise, and password reuse spreads the damage to other services.
3. AES-256-ECB on SSN and date of birth - high for SSN. ECB is deterministic: identical plaintext blocks produce identical ciphertext. SSNs have low entropy, so an attacker can encrypt a candidate list and match, or do frequency analysis; block splicing moves values between rows. Note the nuance the IV question is testing: ECB ignores the IV entirely, so using the primary key as IV is not an active vulnerability today - but it proves the mode is wrong and becomes a predictable-IV flaw the moment someone switches to CBC.
4. TLS 1.0 with 3DES - medium. BEAST, POODLE and SWEET32 require a network-position attacker, but this is a hard compliance failure.
5. No documented rotation - compliance failure, medium practical risk until a key leaks, at which point it becomes item 1.
2. Design a zero-downtime migration to AES-256-GCM with envelope encryption and per-tenant keys. How do you handle the nine-hour lock and the need to look patients up by SSN?
Read the worked answer
Schema first. Add ssn_enc_v2 VARBINARY, key_id, nonce and enc_version columns, all nullable. No locking, instantly reversible.
Dual write. New writes populate v2 and leave v1 populated. Reads prefer v2 and fall back to v1. Ship this before any backfill so nothing is missed.
Backfill in batches, not one transaction. Walk primary key ranges of about 5,000 rows, commit per batch, throttle during off-peak, make each batch idempotent and resumable with a progress table. This keeps lock duration per batch in milliseconds and stays well inside the read-only degradation window. Verify by row counts per version, then decrypt a random sample of 10,000 rows and compare to v1.
Cutover. Flip reads to v2 only, stop writing v1, keep the legacy columns for a rollback window, then drop them in a later release.
Envelope encryption. A KMS master key wraps per-tenant data keys; the row stores key_id plus the wrapped DEK, or you keep a wrapped-DEK cache per tenant. Rotation then means rewrapping DEKs - no data rewrite, which is what makes the regulator's rotation requirement affordable.
GCM specifics. A random 96-bit nonce per encryption from a CSPRNG, stored alongside the ciphertext, never reused under the same key. Set AAD to tenant_id plus row id plus column name so ciphertext cannot be swapped between rows or tenants. Use a random 96-bit nonce, store it with the ciphertext, and never reuse it under the same key.
Lookup by SSN. Do not use deterministic encryption. Add a blind index: HMAC-SHA256(normalized_ssn, separate_search_key) in an indexed column. Equality search works; ECB-style leakage does not.
3. How do you migrate the password hashes, and what do you do about the TLS 1.0 partner?
Read the worked answer
Passwords - you cannot rehash what you cannot see. Migrate opportunistically: on next successful login, verify against the old SHA-256 scheme, then immediately compute Argon2id (64 MB memory, 3 iterations, parallelism 4) or bcrypt at cost 12 where Argon2 is unavailable, and update the row with an algorithm identifier. Users who never log in get a forced reset after a defined window - 90 days is typical. Never email passwords or temporary plaintext.
Details that matter. If you chain into bcrypt, pre-hash with HMAC-SHA-256 before bcrypt because bcrypt silently truncates at 72 bytes, and base64 the digest to avoid null bytes. Add a pepper held in the KMS, applied outside the database, so a database-only compromise is not enough to crack offline. After migration, identical passwords no longer collide, and you can finally screen against breached-password lists.
TLS 1.0 partner. Do not enable a deprecated protocol globally to accommodate one counterparty - you would downgrade every other connection. Options in order of preference: terminate a dedicated listener or a separate hostname for that partner with mutual TLS and a documented exception expiry date; move to an SFTP batch drop if the data is file-based; or require the partner to upgrade, with a dated exception approved by the risk owner and tracked in the exception register. If the partner carries PHI, add a compensating control - IP allowlisting plus payload encryption at the application layer for that channel - so the transport weakness does not mean plaintext PHI on the wire. Record the residual risk and the review date; a permanent exception without an owner is how TLS 1.0 survives for a decade.