The Davinci code ( SQL Cryptology)

 

 

Late night, Sitting under the A/C duct relaxing I could hear a grunted words from a near cubicle

About a strange requirement which requested by a customer. Keeping my curiosity at bay I thought its not my business …

 

It dint stop there ! I could here a disappointed words from another person the same bay .

Yet, I said to myself NO ! Let me not involve, but being a CONSULTING detective I couldn't leave with my curiosity and made a decision to look furtively and pretend

not being interested in the ongoing case.

 

I went to the last dark cubicle in the bay where I was hearing the sounds from and stood next to

Mr.SAM who was stuck to his laptop and dint seem to welcome the detective (you got it :) , who else but me).

He looked up to me and said " Hello Mr. MJ. The Sherlock homes of SQL Server "

 

Even tough it was sarcastic I stood there to see what's going on ?

And asked Mr. SAM about the other person who made a disappointed sound in the bay.

He said its Mr.IG the new guy from the Old Trafford.

 

Wasting no time, me and Mr.SAM went to Mr.IG bay to look what's the disappointed all about.

I said "Howdy ! What keeps you in bother ?" . All I could get was a grumpy face !

Hmmmm…..this sign is surely not welcoming !

 

Me : So, tell me about the problem you are facing folks !

Mr. SAM: Me and IG are working on a project which has requirement that the user should be able to see only the

data which he is eligible for , sort of an role based

Me: hmmmm.. ( not jumping into any conclusion) , so what difficulty are you facing in that ?

Mr.IG: We wanted to do this using @system_user function but the solution was rejected as they wanted to

Make it more secure !"

Mr.SAM : Why is it so hard to find a solution for a simple problem ?

Me : OK ! Have you guys tested using symmetric keys to encrypt the data to make it secure ?

Mr.SAM : " Hmmm…..But It will encrypt all the data in the column how can it be encrypted for a user ?"

Mr.IG :"Yes !

Me : Quoting the Sherlock homes " I am fairly familiar with all forms of secret writing, and am  myself the author of a trifling monograph upon the subject, in which I analyse one hundred  and sixty separate ciphers "

Mr.SAM :" Please, stop bragging and show us the solution"

Me : OK .

For readers : Symmetric keys are used to encrypt and as well decrypt the data.\

 

 Step 1 :

 

Creation of 2 logins which will simulate the 2 consultants ! One for samish and other for the new guy from old trafford IG.

Step 2 :

Creation of a database and table which will hold the encrypted data.

 Step 4 :

 

Creation of certificates one for each user which will be owned by the user which is done using the

 

Authorization keyword which are encrypted by the database master key.

 

Step 5:

 

Creation of symmetric keys which are encrypted by the certificates which were

 

created from the above statements using the Triple_DES encryption algorithm.

 

Step 6:

 

 Insert the data into the table encrypted the symmetric keys which are owned by the user. Close all the symmetric keys after

 

Inserting the data.

 

Lets repeat the same for sam .

 

 

Step 6 : The Judgment step

 

 

 

As you can see , Mission Accomplished and case solved !

  [Update] : 3/8/2013