0

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

enter image description here

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

0
1

I don't believe you'll be able to hide the relation between tables themselves other than obfuscating the names of the related columns (even then, someone with access to the data can always guess how tables relate). Although the relationships aren't explicitly visible on the server either unless you define foreign key relationships or create database diagrams. Keeping that in mind, you're likely looking for something to prevent unwanted data access instead. Here are a few possible options:

  1. Row-Level Security - A data driven / functional solution to filtering data from being accessible by users who don't meet the criteria you define, no matter where and how that data is consumed.

  2. Always Encrypted - A feature for separating data access concerns with sensitive information between different types of users, via actual persisted data encryption.

  3. Transparent Data Encryption (TDE) - Another data encryption methodology built into SQL Server, that is key based as opposed to user based. Anyone without the key can't access the data. (Though depending on your DBA's normal functions, this might inhibit them in unwanted ways.)

While these may not exclusively prevent access or be completely applicable to your needs, they are relevant honorable mentions for this type of question:

  1. Dynamic Data Masking - Used to obfuscate sensitive pieces of data in SQL Server to unprivileged users, defined at the column level, applied at runtime.

  2. Static Data Masking - Similar feature to the one above (new to SQL Server 2019 only I believe), except it actually persists a copy of the data to the server, masked, as opposed to applying the mask whenever the data is queried like Dynamic Data Masking does.

1

Your approach introduces unnecessary complexity to the application without solving the problem. Even if you don't formally define foreign keys, a DBA will be able to see what queries you are executing and in what sequence, including plain-text parameter values -- you have to decrypt Code_Red_Id in order to find the actual ID value, and you will send that decrypted ID with the query.

How you rate this solution

0/10


Somewhat related question.

6
  • I am not using any straight queries in the SQL for these tables. Everything being handled in the application since its an EF 5 based Web API. Also Code_Red_Id, its an encrypted column where master key in Azure Key Vault – Sandeep Thomas Jun 9 at 11:59
  • And how, in your view, "EF 5" communicates with the database, if not by sending SQL queries? The fact they are not "straight" doesn't make them any less readable. – mustaccio Jun 9 at 12:03
  • Sorry, its in App queries.. So its not visible for those with DB alone – Sandeep Thomas Jun 9 at 12:05
  • 3
    I'm afraid you misunderstand how databases work. – mustaccio Jun 9 at 12:08
  • @SandeepThomas To elaborate on Mustaccio's point, every request made to a SQL Server instance, to interact with the data (regardless if it's for reading or writing purposes), are submitted as queries. It doesn't matter where they come from, whether it's via Entity Framework from your application, or directly as raw queries, it will always be converted to an actual query sent to the SQL Server (which gets compiled to an execution plan, and then executed). Mustaccio is correct that a DBA with the appropriate access, can see the queries (without the data) currently being executed on the server. – J.D. Jun 9 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.