Sql 2005 - how to allow users to decrypt table data using a database certificate ??
- From: "frostbb" <frostbb@xxxxxxxxxxxxxxxxx>
- Date: Tue, 11 Jul 2006 15:57:08 -0700
Ok, I've spent the last day or so reading up on Sql Server 2005 data
encryption methods. i.e. Encryption newbie.
We have a couple of columns in a couple of tables that need to be encrypted
for privacy purposes.
We're using Win2003 Enterprise & Sql Server 2005
I think I understand the basic Sql Server 2005 encryption hierachy i.e.
(01) Win Data Protection API =>
(02) Searvice Master Key (backed up) =>
(03) Database Master Key (created for db's w/ sensitive data & backed up)
=>
(04) Certificates (using Database Master Key) => etc.
Ok ... so I've got a certificate in place and as 'SA' I can encrypt data to
a table column and decrypt data from a table column. So far so good.
Now I need to allow a certain set of Win Domain users to encrypt and decypt
the data to and from the secure table columns using the EncryptByCert and
DecryptByCert.
THE QUESTION: How do I as the 'dba' assign the ability to use a Certificate
to encrypt and decript data to an Sql Server 2005 database >> roll << that
represents a set of Win Domain users ??
The documents I've found on the web do a pretty good job of explaining how
to setup the encryption tools but I've yet to find a good explanation of how
to allow my users to access the encrypted data. Data security does us no
good if the people that NEED to access the data can't.
For instance ...
I have a set of Win Domain Users that belong to the Win Domain Group
"License Mgrs"
The Sql Server 2005 roll "License Managers" maps to the Win Domain
Group "License Mgrs" and has R/W permissions to the Database Tables with the
encrypted columns. The members of the "License Manager" roll can Read and
Write to the non-encrypted data within the target database tables with no
problems. Works fine.
So the question boils down to "What permissions do I grant to the "License
Managers" roll that will allow the members to use the EncryptByCert and
DecryptByCert statements" ???
Thanks in advance !!
Barry
in Oregon
P.S. Insert, Update & Delete access to the tables is via stored procs, the
domain users are granted access to the appropriate stored procs via database
roll.
Select privledges are granted directly to the target database tables again
via "read-only" rolls.
.
- Follow-Ups:
- Prev by Date: Newbie needs help
- Next by Date: Re: GRANT CREATE DATABASE TO a domain user account or group in SQL 2005
- Previous by thread: Newbie needs help
- Next by thread: RE: Sql 2005 - how to allow users to decrypt table data using a database certificate ??
- Index(es):
Relevant Pages
|
