January 11, 2006 at 5:52 pm
Dangerous newbie question...
I'm looking at using encryption in SQL Server 2005 for an OLTP application. Some of the columns that should be encrypted are searchable by the application (such as SSN). Simply building an index on the ciphertext is almost worthless from a searchability standpoint. Making the application aware of columns containing partial cleartext data that -- is indexed -- is something I'd like to avoid.
Is there a way from a trigger or instead-of trigger to generate an index on the cleartext?
Tom
January 12, 2006 at 7:22 am
Not without revealing some sort of information, no. If you were wanting to do a sort order, that's one thing, but if you want to be able to search for specific records or a range of records, no.
K. Brian Kelley
@kbriankelley
January 12, 2006 at 9:54 am
Yes, we want to search for specific records and a range of records. It sounds like some information would need to be revealed in the column of a table, then indexed. Right?
The solution need only provide low levels of security, if we can keep the cleartext confined to the indexes, that would be "good enough".
A co-worker of mine mentioned that another DB they worked with a long time ago provided the capability to effectively insert/update a user-defined value in an index and then use the query language to search for records using the values in that index. I didn't see anything like that described in the BOL or SQL Server Reference Library...
Tom
January 12, 2006 at 10:49 am
Keep in mind the index builds off a column or columns. So you're basically creating a column of user-defined values and indexing that column. In the SQL Server 6.5 and 7.0 days, this wasn't unusual. For instance, the Lawson Insight suite (ERP package) sometime wanted data sorted in descending order. However, descending indexes weren't available. So what Lawson did was create an additional column and wrote values in it that went opposite to how the data was actually arranged. So if you had 100 values, value 1 was written in this second column as 100, value 2 was 99, etc. When you put an index on this column, you gained your descending order sort.
Something like that will work. The catch is not to reveal the sensitive information on those additional columns on which you build your indexes.
K. Brian Kelley
@kbriankelley
January 12, 2006 at 3:27 pm
I think I can run with that! Thanks.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply