Permissions and encryption

Question

Post reply

Permissions and encryption

sqlsc-1053844

SSC Enthusiast

Points: 179
More actions
November 5, 2013 at 1:55 pm

#285822

Is there any way to restrict permissions so that a user can read from a database (and even decrypt data) but not write/update/delete? I assume this is possible. If someone could point me in the right direction where I can read up on it. I haven't been able to come up with anything thus far. Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

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

david.john.gilligan Default port Points: 1471 More actions · Answer 1

Have you looked into TDE: http://www.infotechguyz.com/SQLServer2012/TransparentEncryptioninSQLServer2012.html

george sibbald SSC Guru Points: 104210 More actions · Answer 2

this would all be controlled by the permissions you grant within the database.

---------------------------------------------------------------------

Dave Vroman SSC Eights! Points: 821 More actions · Answer 3

What you're looking for is "Permissions (Database Engine)". The operative word is GRANT permissions. You can "not" grant permissions to do just about anything. You can grant permission to SELECT and deny the permission to INSERT or UPDATE. There is a whole granularity thing going on here. In the past, the secure way of doing this was to deny any direct access to the tables and only grant access through programmables (procedures, functions,...). I don't know what the latest thoughts are on this subject.

sqlsc-1053844 SSC Enthusiast Points: 179 More actions · Answer 4

Thank you for the replies. TDE is not an option at this point. The application has been in service for many years. We need a solution to where users can be given read access to any table for troubleshooting purposes. This includes decrypting data. However, these users should not be able to affect any change to the existing data.

There were a few comments that have given me new areas to search. Hopefully, I will find a resolution to this issue soon.

Thank you again.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

sounds like these users just need adding to the db_datareader role and no other, plus use the decrypting functions to read the data.

the other way would be to provide read only stored procedures and give then execute rights on those.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

There are no grants for encrypting/decrypting data. This is controlled when a user can open a key that performs the function. This could be in a stored proc or with batch code.

The read but not write has to do with permissions as noted above.

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

Normal SQL permissions (GRANT and DENY and the database roles) apply for reading and writing data.

TDE has no effect.

Your cryptographic approach applies for decrypting and encryption - if you use symmetric encryption, then if they have the key to encrypt, they have the key to decrypt - you'd have to move to asymmetric/public key cryptography to enforce this at a cryptographic level.