Searching an Encrypted Column

Question

Post reply

Searching an Encrypted Column

David Lafayette

SSCommitted

Points: 1559
More actions
December 15, 2023 at 12:00 am

#4338034

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

etheral1980 Old Hand Points: 303 More actions · Answer 1

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?

etheral1980 Old Hand Points: 303 More actions · Answer 2

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?

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

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.

----------------------------------------------------

David Lafayette SSCommitted Points: 1559 More actions · Answer 4

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.

etheral1980 Old Hand Points: 303 More actions · Answer 5

Hi,

I asked about equality search. First example in post.

With dates i don't see a problem.

David Lafayette SSCommitted Points: 1559 More actions · Answer 6

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

David Lafayette SSCommitted Points: 1559 More actions · Answer 7

@etheral1980,

Sorry about that... yes, in the example of the "equality" search, you would need an index on the encrypted column.