I have a table where all columns need to be encrypted (aside from the PK). This will be achieved using AES at the application level. However there is one particular field that needs to remain searchable.
For example a Message table has:
- MessageTitle (Encrypted)
- MessageBody (Encrypted)
- SenderID (Encrypted but matchable)
Given a plainText SenderID I should be able to select all the related messages.
A few constraints / decisions:
- I do need to encrypt the data at rest, including this column. I have no choice.
- The SenderID is of a fixed format, so if I hashed the value it wouldn't be hard to brute force
- I have chosen to encrypt specific fields rather than the whole database (TDE) to mitigate threats other than just the disks being stolen.
Currently my preferred solution is to encrypt the SenderID (and only the SenderID) with a fixed IV. This way the data remains encrypted but we can still match on it in queries.
A downside to this approach is that plaintext attacks could occur on the data; DBAs could create records with different SenderIDs and try to find a matching record. However I feel this can be mitigated at the Encryption Service with suitable access control and monitoring.
Does this seem like a valid solution?