Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Encryption noob with a table design general question Expand / Collapse
Author
Message
Posted Friday, February 1, 2013 6:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
I need to use the encryption feature for the first time. I'm reading some how-to's now but I have a table design question.

When using encryption do people encrypt individual columns in a table as needed. Or is it a better practice to split the table vertically into two tables, one with the non encrypted data and the other with the encrypted data. That would create an extra table but it might be better from an organizational perspective.

Thanks,
Bill, Charlotte NC
Post #1414637
Posted Friday, February 1, 2013 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,901, Visits: 32,135
the purpose of encryption is to not store the data unencrypted at all, storing it both ways makes sense during the testing/development phase, but not after you've proved the encryption works.


so my answer is no; the right thing to do is NOT to split the table,and to only store the encrypted values. so i'd have both enc/-un-enc columns in development, in the same table, during testing, but only there.

once i got encryption where i was confident, i would drop the un-encrypted columns and promote to QA/Production.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1414644
Posted Friday, February 1, 2013 6:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
Sorry if I was not clear. My data is such that I will have encrypted and un encrypted columns for a given table, both in development and production.

Our apps will use the un encrypted data more frequently. So does it make any sense to split the table vertically and put all the encrypted columns into a separate table so we don't incur any decryption overhead until that data is retrieved ?
Post #1414646
Posted Friday, February 1, 2013 6:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 39,960, Visits: 36,315
Since you'd only decrypt and fetch the columns when you need to decrypt then, no it doesn't make any sense to do that.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414648
Posted Friday, February 1, 2013 6:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
ok, I think I understand that. So if fetching the 'common' un enc data just make sure I use the appropriate field list in the query. And if I need all the data just go for it.

So how do others do this out there in the world ? Do people commonly encrypt individual columns in a table or just say the heck with it and encrypt the entire table ?
Post #1414650
Posted Friday, February 1, 2013 6:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,901, Visits: 32,135
ahh sorry i misunderstood.

for me, encrypted or not, it just goes to whatever normalization is proper for the data;

Basic normalization considerations for me would be:
data is 1:1 and rarely null, same table probably, especially if it will be typically queried witht eh main data.
data is 1:1 with lots of nulls , a separate table for optional data
data is 1:M, then separate table, obviously

HOW it is stored , ie encrypted or not,isn't going to affect that decision, but i can understand how you'd think there is a logical reason to store encrypted data together.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1414655
Posted Friday, February 1, 2013 6:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:50 AM
Points: 263, Visits: 345
I follow you. If not for the encryption this data would be in a single table and I would not even consider splitting it vertically into two tables. I just didn't know if implementing encryption would have any significant performance impact such that I would want to separate out the encrypted columns.

btw, I'm assuming I can have a mixture of encrypted and un encrypted columns in a table. Is that correct ?

thanks.
Post #1414657
Posted Friday, February 1, 2013 7:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502
William Plourde (2/1/2013)
ok, I think I understand that. So if fetching the 'common' un enc data just make sure I use the appropriate field list in the query. And if I need all the data just go for it.

So how do others do this out there in the world ? Do people commonly encrypt individual columns in a table or just say the heck with it and encrypt the entire table ?


You will always have some unencrypted columns is a table, like the primary key and any indexed columns. Indexes on an indexed column are worthless.

Encrypt only data that needs to be encrypted.

Post #1414681
Posted Friday, February 1, 2013 7:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
Michael Valentine Jones (2/1/2013)
William Plourde (2/1/2013)
ok, I think I understand that. So if fetching the 'common' un enc data just make sure I use the appropriate field list in the query. And if I need all the data just go for it.

So how do others do this out there in the world ? Do people commonly encrypt individual columns in a table or just say the heck with it and encrypt the entire table ?


You will always have some unencrypted columns is a table, like the primary key and any indexed columns. Indexes on an indexed column are worthless.

Encrypt only data that needs to be encrypted.



BWAAA-HAAAA!!!!! You just gave me a brilliant idea to have fun with the auditors next year!!! I'm going to make a table of all encrypted columns and call it something like "CustomerPrivate". I'll encrypt each column using a different salt and datatype and it'll be filled with nothing but random data. I'll use the old Col01-Col99 style of columns names and when the ask me what's in it, I'll tell them what they've told me so many times... "You don't have the clearance or the need to know".

Notice that it wouldn't be a real good idea to pluralize this particular table name.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1414893
Posted Saturday, February 2, 2013 6:02 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502
Jeff Moden (2/1/2013)
Michael Valentine Jones (2/1/2013)
William Plourde (2/1/2013)
ok, I think I understand that. So if fetching the 'common' un enc data just make sure I use the appropriate field list in the query. And if I need all the data just go for it.

So how do others do this out there in the world ? Do people commonly encrypt individual columns in a table or just say the heck with it and encrypt the entire table ?


You will always have some unencrypted columns is a table, like the primary key and any indexed columns. Indexes on an indexed column are worthless.

Encrypt only data that needs to be encrypted.



BWAAA-HAAAA!!!!! You just gave me a brilliant idea to have fun with the auditors next year!!! I'm going to make a table of all encrypted columns and call it something like "CustomerPrivate". I'll encrypt each column using a different salt and datatype and it'll be filled with nothing but random data. I'll use the old Col01-Col99 style of columns names and when the ask me what's in it, I'll tell them what they've told me so many times... "You don't have the clearance or the need to know".

Notice that it wouldn't be a real good idea to pluralize this particular table name.



Call it something that will really get them worked up, like AuditorInformation, and use column names like wife, children, parents, financial profile, photo, personality profile, academic record, military service, medical reports, surveillance reports, criminal record, known associates, etc. with all columns containing what might be encrypted documents.



Post #1414974
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse