I have a large database (millions of rows in hundreds of tables) that contains sensitive data (Think SSNs, account numbers, etc). I am tasked with safeguarding the data and have chosen to encrypt only sensitive data and not every field. This encryption is handled by the application, and not the database server, so all solutions require that the encryption process stays in the application.
Because there are existing JOINs in the application, I can't just encrypt every value separately because 2 identical values will yield different encrypted strings (and thus won't JOIN). I've chosen to key the data (a hash of the value, so the key will always be the same for each unique value) so that I can store the key in place of the original data and use it to look up the encrypted data when needed.
So I need to go through the database, row-by-row, and encrypt certain fields, storing the encryption in a separate table, and then storing the key in place of the original data.
My problem is that looping over the data is very slow. 10-15 minutes per 1000 values. I've optimized the task by grouping the values so that I only have to encrypt each unique value 1 time and then update all instances of that value with the key in 1 UPDATE.
Is there a different approach (keeping all the encryption in the Application layer) that can speed up my task of encrypting the sensitive fields?
Best Answer
You're paying the extra price of network IO and there's not a whole lot you can do if you need to remove the encryption (really the key storage/management) from the database. If your MySQL instance is on a Unix host, you can try connecting with a Unix socket file instead of TCP/IP. Just host the app on the same machine during the encryption process, finish the DB then move the app back to wherever it should be.
If your MySQL DB is running on Windows then you're out of luck. You could still put the app on the same machine or isolate them from the rest of the network so you minimize impact from network congestion or switch latency but those are likely to yield limited improvement unless your network was really, really busy or if you were using some old 100Mbps switch.