Sorry for sharing a confusing title
I have got a requirement to design a DB for a highly sensitive application.
There are virtually two set of tables. One is totally demographic information or its a single table. The other set carries the actual set of sensitive data which are spread across multiple tables.
So what my requirement is even the DBA when login to the DB, he cant even identify or make some report with details corresponding to a specific demography.
Here is how it looks actually
So when a search occurs for a specific user with a name, the search is happening on the Main table. Among them selecting the one we need. From that row there will be virtual hidden relation with the Key Table and Main table.
In my case I have designed (Should be a stupid way without understanding all solutions in SQL) it like this
--> Search for a user --> Locate the hash --> Match the hash in the Key Table --> Find the ID from the Key Table with above match --> Boom! We have got the records At the same time we are holding the Main table data in Application Session as well
How you rate this solution. Will it be cost effective or any better way with any latest builtin functionalities from SQL.
If my solution is Good, How can I implement that. I thought about a salt for each row which gets changing automatically and using that salt and did some MD5 hashing with the data in Main table.
Please suggest. I cant afford a data leak or world cant say these are the data of a specific demography or data from a specific state or city.
Adding a missing point. In the main table I have a Code_Red_Id column in the main table which is the actual ID from the key table, but encrypted using AES or SHA