| Author |
Topic |
|
iminore
Posting Yak Master
141 Posts |
 Posted - 2007-10-07 : 12:56:02
|
| Suppose I encrypt columns in a table using my own code. The big downside is I cannot sort or search those columns any more.Is this the same in SQL 2005 if using native encryption? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-07 : 13:50:40
|
Why can't you sort them?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2007-10-07 : 13:55:56
|
| Well if in 2 records col1 contains 'A1' and 'A2' and this gets encryted to 'K8' and 'GB' then sequencing the records by col1 will give record 2 then 1. Not 1 then 2. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-07 : 21:04:08
|
| May need decrypt before sorting and searching, or combine encript algorithm in your select statement. |
|
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2007-10-08 : 04:14:38
|
| is there anyone out there who has done encryption and solved these issues? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 04:47:39
|
| What sort of application is this?Just a single Credit Card Number column (for example), or something broader than that?Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-08 : 04:59:27
|
There is a reason for ENCRYPTION to scramble the orignal value as much as possible.If I understand you correctly, you want to encrypt a column, but still be able to both sort and search by orignal value?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2007-10-09 : 04:22:00
|
| Yes, this is more than encrypting CCNs, logins and passwords.If our clients want us to encrypt everything I'm trying to find out what the downside is.The issue seems to be the type of encryption. If, for a particular password/key, you always get the same result, so 'orange' always becomes 'h5kn33q' then you can search for the encrypted value in a column just as you would for the original value. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-09 : 04:26:25
|
Or, you can use the PK for the column (add addition to other password) as a password-key to that particular record? Then "orange" not always becomes "h5kn33q".But why should you?Who has access to the table anyway? You and which else?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 04:34:12
|
"If our clients want us to encrypt everything I'm trying to find out what the downside is."Makes it very hard for the DBA to perform a useful service!"so 'orange' always becomes 'h5kn33q' then you can search for the encrypted value in a column"... but you can only do = 'h5kn33q' and not LIKE '%h5kn33q%'if the access to the database is tied down why worry about encrypting the data?I can see a benefit in Belt&Braces for CCN/password, but I'm really struggling with the whole database. yes no one can steal your data, but I reckon you'll be up-a-gum-tree faster than you can say jack-rabbit  Kristen |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-09 : 07:15:53
|
| >If our clients want us to encrypt everythingIndexes on encrypted columns become uselessEncrypted Columns and SQL Server Performancehttp://www.sqlsolutions.com/articles/articles/Encrypted_Columns_and_SQL_Server_Performance.htm |
|
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2007-10-11 : 11:54:22
|
| Thanks all for your input on this.Some (government) clients want to encrypt all data.Not sure I've ever said 'jack-rabbit'. Curses, I said it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 15:39:18
|
| Are you "you up-a-gum-tree" yet then? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-10-11 : 17:56:31
|
| If you encrypt a column, it is usually not possible to create a useful index on the encrypted column. For example, AES encryption creates a different cipher text each time you encrypt it, even with the same clear text value and encryption key.You may also have to create an index using a hash to be able to do lookups. For example, create a hash of the original value, store that in another column, and create an index on that column. To do a lookup for a particular value, you would hash the value, and select on the hashed column by that value. Or instead of that, you could store a checksum of the hashed value in an integer column to reduce the size of the column to an integer.CODO ERGO SUM |
|
|
|
iminore
Posting Yak Master
141 Posts |
Posted - 2007-10-12 : 03:35:46
|
| When I get down from this gum-tree I'll have more to say. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 03:54:13
|
| |
|
|
|
|
Encrypt implications