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 ««12

Encryption basics. Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:37 PM
Points: 33, Visits: 99
e4d4:

That is exactly what I have read as well. I have built a basic proof of concept form and it works for me, but I'm DBO. Today I test with a standard user and I expect it to fail as their database role does not, as of now, have those permissions. I will have our DBA add in the permissions one at a time and see what happens.

I have to admit to not being very comfortable giving Control permissions to an entire database role, but that's how we are handling permissions.

I will post results later today. Thanks again for your valuable help!

Kurt
Post #1410611
Posted Friday, January 25, 2013 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:37 PM
Points: 33, Visits: 99
Success!

Thanks to you both for your input. Giving the database role CONTROL permission on the cert, and REFERENCE permission on the key, worked perfectly. We are good to go.

But I'd like to throw out a high-level follow up question to everyone:

We encrypt data in a database so that if anyone should backup/copy/steal the table the data inside is unusable. But if that person has sufficient server rights to be able to run a backup, or to copy a table, wouldn't that mean they have a high enough permission set to decrypt the data using the cert and key?

I guess I cannot immediately think of a scenario where someone could get access to the entire table but not have enough permissions to decrypt the data. I suppose if a web user somehow knew the schema and submitted a SELECT * FROM tblTheTable they might get all the data, but we deny our web users any permissions they are not supposed to have.

Just wondering...

Post #1411762
Posted Monday, January 28, 2013 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:42 PM
Points: 224, Visits: 1,732
kpwimberger (1/25/2013)
Success!

Thanks to you both for your input. Giving the database role CONTROL permission on the cert, and REFERENCE permission on the key, worked perfectly. We are good to go.

But I'd like to throw out a high-level follow up question to everyone:

We encrypt data in a database so that if anyone should backup/copy/steal the table the data inside is unusable. But if that person has sufficient server rights to be able to run a backup, or to copy a table, wouldn't that mean they have a high enough permission set to decrypt the data using the cert and key?


If someone steals the backup file or mdf file, without Database Master Key he can't decrypt the data. You also should remember that if you want to restore the db on another instance you must restore DMK. And the DMK should be backuped and protected with a password and proper access to that file. If someone has select to a table where are encrypted columns, still he need permission to key to decrypt the data. You should thinking of both an encryption and permissions.

kpwimberger (1/25/2013)

I guess I cannot immediately think of a scenario where someone could get access to the entire table but not have enough permissions to decrypt the data. I suppose if a web user somehow knew the schema and submitted a SELECT * FROM tblTheTable they might get all the data, but we deny our web users any permissions they are not supposed to have.

Just wondering...



I don't know how big will be that web application but remember that encryption on sql level isn't easy scalable, with www maybe you should start thinking about encryption on another application tier.
Post #1412311
Posted Monday, January 28, 2013 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:37 PM
Points: 33, Visits: 99
Thanks e4d4:

What confuses me is - how likely is it that someone can get all the way into the server to create or steal a backup and NOT have access to the DMK? If someone is already in that deep, it seems we have much bigger issues on our hands. Honestly, in some ways encryption seems to be more trouble than it is worth.

Yes, we are going to have to backup the DMK, that's for certain!

Thanks

Kurt
Post #1412469
Posted Monday, January 28, 2013 4:20 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:56 AM
Points: 880, Visits: 2,435
kpwimberger (1/28/2013)
Thanks e4d4:
What confuses me is - how likely is it that someone can get all the way into the server to create or steal a backup and NOT have access to the DMK? If someone is already in that deep, it seems we have much bigger issues on our hands. Honestly, in some ways encryption seems to be more trouble than it is worth.


Well, perhaps they picked up the wrong (right) tapes from an offsite storage facility?

Maybe someone put backups in "the cloud" and someone else in "the cloud" made copies?

The trick in security is to make things harder for an adversary at every level.
Post #1412692
Posted Tuesday, January 29, 2013 8:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:37 PM
Points: 33, Visits: 99
That is exactly the answer I got from my contract specialist! He said "Noting is secure - we just need to keep making it harder for them to get anything."

And that's what keeps me going with it as well. Thanks again for the help, it is appreciated!
Post #1413107
Posted Thursday, January 31, 2013 3:44 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 3:39 AM
Points: 710, Visits: 242
nice aarticle’s guys about TDE

Thanks&Regards
AJAY REDDY.L
Post #1413967
Posted Thursday, January 31, 2013 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:37 PM
Points: 33, Visits: 99
Sorry Ajay, but I don't see any link to the article. Am I just missing it?

Kurt.
Post #1414167
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse