Searching an Encrypted Column

  • Comments posted to this topic are about the item Searching an Encrypted Column

  • 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?




  • 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?


  • 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.


  • 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.

  • Hi,

    I asked about equality search. First example in post.

    With dates i don't see a problem.

  • @mmartin1,

    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.

  • @etheral1980,

    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