December 23, 2008 at 3:38 pm
I am new to the encryption game, so any assistance would be appreciated...
I have created a table with ID, FirstName, LastName, EMail, etc1, etc2, etc3
Firstname, Lastname and email are encrypted.
I have created a view with those columns decrypted.
The Firstname, lastname, and email columns all need to be UNIQUE.
Is there any way I can put a unique constraint on firstname, lastname, and email?
I was told that I cannot put a unique constraint on the table because it's encrypted. Is that correct?
I tried to put a unique index on the view but got the following error:
"Cannot create index on view 'vwPerson' because the view is not schema bound." I looked up that error, but was confused even further.
December 24, 2008 at 5:09 am
Ok - a few things to go over.
If you have to encrypt the columns, why is it ok to have them decrypted in a view? Didn't that just remove the benefit of having the columns encrypted?
As far as uniqueness, how are you encrypting the information? The problem with a unique index on an encrypted column is that the same value properly encrypted twice is stored as two different encrypted values. Ensuring the encrypted values are unique (which is all a unique index or constraint can do) will not ensure the decrypted values are unique.
If you have a small enough number of records, you could use a check constraint and test new values against existing values but decrypt them first. The problem is that you will not have an index to help and performance will quickly suffer because every value in the table needs to be checked.
Another solution would be to create an algorithm that generates a value that is unique to the original value, always the same, and not reversable. If you did this, you could store this value in another column and give it a unique constraint. This is sometimes a solution for searching for encrypted values, so you may want to search google a bit for searching efficiently in encrypted data.
December 24, 2008 at 2:29 pm
(If you have to encrypt the columns, why is it ok to have them decrypted in a view? Didn't that just remove the benefit of having the columns encrypted?) Good question, and I'm not sure I have a satisfactory answer. I asked that when I first came here, but I'm not sure I can recall the answer correctly. Unfortunately, the person I would ask is out on vacation. I believe it was because some IT people who shouldn't see the clear text data will access the database directly. Those that need to see the clear data will use an application that has permissions on the view.
(As far as uniqueness, how are you encrypting the information?) hmmm...Can we make this a multiple choice question? We use a symmetric key with the AES_256 algorithm, if that's what you're asking. Me being the encryption expert that I am, I just followed the documentation they used on other tables.
(If you have a small enough number of records, you could use a check constraint ...and performance will quickly suffer ...) Yes, that was one of the concerns. They really don't know how big this will grow, but I doubt it will get past 100,000 records.
(Another solution would be to create an algorithm that generates a value that is unique to the original value, always the same, and not reversable. ...) Do you mean a hash key? I found a good hash key article on Simple-Talk (http://www.simple-talk.com/sql/t-sql-programming/intelligent-database-design-using-hash-keys/). If I head down this path, I could create a hash column of the encrypted columns. I could check against the hash for uniqueness, and also put an index on the column...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply