Encryption noob with a table design general question

Question

Encryption noob with a table design general question

William Plourde

SSCrazy

Points: 2035
More actions
February 1, 2013 at 6:25 am

#288779

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

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

William Plourde SSCrazy Points: 2035 More actions · Answer 2

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

William Plourde SSCrazy Points: 2035 More actions · Answer 4

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 ?

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

William Plourde SSCrazy Points: 2035 More actions · Answer 6

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

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. :hehe:

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

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. :hehe:

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.