Exercise 2: Fixing Plaintext Credit Card Columns — Possible Solution
====================================================================

THE FIX: column-level encryption on the card number field specifically,
e.g.:

UPDATE customers
SET card_number_encrypted = AES_ENCRYPT(card_number, @encryption_key);

-- reading it back requires the SAME key, held only by authorized
-- application code, not by every account with SELECT on the table
SELECT AES_DECRYPT(card_number_encrypted, @encryption_key) FROM customers;

WHY THIS FIXES THE PROBLEM: TDE (what the team already has) decrypts
automatically for ANY authenticated query — including this developer's
legitimate, properly-scoped SELECT access, which is exactly why they
can still read plaintext card numbers despite doing nothing wrong from
a least-privilege standpoint. Column-level encryption is the one
technique from this chapter that changes this: the card number stored
in the table becomes ciphertext to EVERY query, including ones from
accounts with entirely legitimate table access — only code that
separately holds the encryption key (kept apart from the database
account's own privileges, per this chapter's key management section)
can ever decrypt it back to a real number.

THE TRADE-OFF: the card_number column can no longer be efficiently
searched, filtered, or sorted on directly by the database — a query
like "find the customer with card number ending in 1234" can no longer
be answered with a simple indexed WHERE clause on the encrypted column,
since the stored value bears no readable relationship to the real
number. Any functionality that depended on querying by that column
directly needs to be redesigned around this limitation (e.g. storing a
separate, non-reversible hash of just the last four digits for lookup
purposes, if that specific functionality is truly needed).