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.
Creation of symmetric keys which are encrypted by the certificates which were
created from the above statements using the Triple_DES encryption algorithm.
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