Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:
>> Whoever has access to the decryption key has access to the data. <<
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>> Hi,
>> I am writing an .NET 2.0 app that has different users logging in who
>> have different access levels in the app. One of the user roles is "HR".
>> When the user logs in with these credentials, they have a whole heap of
>> buttons/links/pages related to HR stuff that normal team members don't.
>> One of the sensitive information that needs to be displayed and more
>> importantly, stored in the database is the employee performance reviews
>> (HR access only).
>> I am wondering how I would go about storing/maintaining this
>> information as even the DBA's are not supposed to have access to this
>> information, but should still be able to administer it and/or retrieve
>> the encryption keys if the HR personnel forget their passwords etc.
>> The simplest way I can think of is to issue all the HR people with one
>> password, encrypt the data in the DB layer (in code) and then store it
>> in a table in the database. This way, anyone with access to the
>> Database won't necessarily have access to the data. The problem with
>> this one is that all the HR personnel share one password, which doesn't
>> seem like a nice (secure) solution.
>> The other option was to store the encryption key in the database and
>> encrypt the key itself with the password provided by the individual
>> user (HR person), which will be stored against that user's record. This
>> way, whenever a data request is made, the database sends the encrypted
>> performance review data and the password encrypted key to the user. The
>> user will then decrypt the key using their password, and then decrypt
>> the data using the key. This way, if the user ever forgets their
>> password, all the DBA (and/or App with owner acess) has to do is
>> reencrypt the key with a new user password and store it against the
>> user's record. The problem with this is storing the key in the database
>> and yet restricting access to the DBA. They should not have raw access
>> to the key (which essentially means they can see the data in the
>> database), but still should have enough access that if a user forgets
>> their password, the DBA can reset the password without losing all the
>> existing data.
>> I was thinking of storing the key on a key server somewhere with
>> different access rights etc. but that means the system starts to get
>> complex.
>> How would you guys go about building such a system? Does MS Sql Server
>> 2005 provide any mechanisms for such functionality? Plus, as a design
>> issue, is it better to let the database handle the encryption, key
>> management, roles/privileges or is it better to do it in the
>> application itself? One of the advantages I can think of for doing it
>> in the application is that it gives a bit of database independence, i
>> can change the underlying database easily without having to rewrite a
>> whole heap of functionality. The other reason is that this way, i won't
>> have unencrypted data or keys on wire.
>> Looking forward to some expert opinions and discussions regarding this
>> problem.
>> Thanks in advance.
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
> >> Whoever has access to the decryption key has access to the data. <<
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
> > Hello? Anyone? Surely this is not such a hard problem, or particularly
> > unusual. I would guess there are many times when you would want to
> > encrypt the data in a database, so as to make it inaccessible to the
> > DBA's, but still give them some administrative privileges. One of the
> > constraints is that the user with access to the data doesn't
> > necessarily "own" the data, so that if they forget the password (i.e
> > lose the encryption key), all the data is not lost.
> > On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> >> Hi,
> >> I am writing an .NET 2.0 app that has different users logging in who
> >> have different access levels in the app. One of the user roles is "HR".
> >> When the user logs in with these credentials, they have a whole heap of
> >> buttons/links/pages related to HR stuff that normal team members don't.
> >> One of the sensitive information that needs to be displayed and more
> >> importantly, stored in the database is the employee performance reviews
> >> (HR access only).
> >> I am wondering how I would go about storing/maintaining this
> >> information as even the DBA's are not supposed to have access to this
> >> information, but should still be able to administer it and/or retrieve
> >> the encryption keys if the HR personnel forget their passwords etc.
> >> The simplest way I can think of is to issue all the HR people with one
> >> password, encrypt the data in the DB layer (in code) and then store it
> >> in a table in the database. This way, anyone with access to the
> >> Database won't necessarily have access to the data. The problem with
> >> this one is that all the HR personnel share one password, which doesn't
> >> seem like a nice (secure) solution.
> >> The other option was to store the encryption key in the database and
> >> encrypt the key itself with the password provided by the individual
> >> user (HR person), which will be stored against that user's record. This
> >> way, whenever a data request is made, the database sends the encrypted
> >> performance review data and the password encrypted key to the user. The
> >> user will then decrypt the key using their password, and then decrypt
> >> the data using the key. This way, if the user ever forgets their
> >> password, all the DBA (and/or App with owner acess) has to do is
> >> reencrypt the key with a new user password and store it against the
> >> user's record. The problem with this is storing the key in the database
> >> and yet restricting access to the DBA. They should not have raw access
> >> to the key (which essentially means they can see the data in the
> >> database), but still should have enough access that if a user forgets
> >> their password, the DBA can reset the password without losing all the
> >> existing data.
> >> I was thinking of storing the key on a key server somewhere with
> >> different access rights etc. but that means the system starts to get
> >> complex.
> >> How would you guys go about building such a system? Does MS Sql Server
> >> 2005 provide any mechanisms for such functionality? Plus, as a design
> >> issue, is it better to let the database handle the encryption, key
> >> management, roles/privileges or is it better to do it in the
> >> application itself? One of the advantages I can think of for doing it
> >> in the application is that it gives a bit of database independence, i
> >> can change the underlying database easily without having to rewrite a
> >> whole heap of functionality. The other reason is that this way, i won't
> >> have unencrypted data or keys on wire.
> >> Looking forward to some expert opinions and discussions regarding this
> >> problem.
> >> Thanks in advance.--
> -Dave Markle
> http://www.markleconsulting.com/blog
This worked for me!
![Paradise [ip]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_u1bY7YDYdf70hdJCAb2gitF4r3xxYNYjqQgFE-Qp_U28RLqpG7-OpnCPC2U8YCNrBAiTkvCVVP1PpMXox6jJ3XoEiQV6IbZciUFXQxlqxSRd9TKzLU6Xo=s0-d)
jennifer7290 wrote: