December 15, 2023 at 12:00 am
Comments posted to this topic are about the item Searching an Encrypted Column
December 25, 2023 at 7:29 am
Hi,
Interesting article, though i have a question.
Equality search method you described will be scan and not search. Or you imply there is an index on varbinary(8000) ? Or am i missing something?
December 25, 2023 at 7:33 am
Hi,
Interesting article, but i have a question.
In equality search method we will have scan and not search or you imply having index on varbinary(8000)?
Or am i missing something?
January 2, 2024 at 4:02 am
Having that extra lookup table with unecrypted data will no doubt mean it will require extra column level security at the least. Preventing unauthorized access will be the primary importance.
----------------------------------------------------
January 2, 2024 at 6:57 pm
Hi @etheral1980,
There is no need for an index on the encrypted column. The index seek would be on Birthdate in the dbo.Customer_Birthdate table (yes, you'd want an index on that). That returns a set of primary key IDs for the dbo.Customer table.
January 2, 2024 at 7:01 pm
Hi,
I asked about equality search. First example in post.
With dates i don't see a problem.
January 2, 2024 at 7:05 pm
In the example I gave, the "sensitive date" has no meaning if not associated to the Customer (in this case); they are just dates, so they would not require column level encryption. Of course if you were storing something that could identify a person when unencrypted, such as an SSN or DOL number, this would not be a good idea. But in those cases, it's rare for you to want to return a range, you'd simply look for the specific number like I noted.
Other examples where this might be useful could be storing the city on lives in. If there was a need to encrypt that, you could use this method to easily return all Customer who lived in "Vancouver". The unencrypted data would just be a list of lots of cities.
January 2, 2024 at 7:08 pm
Sorry about that... yes, in the example of the "equality" search, you would need an index on the encrypted column.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy