Jim
In the case of our encrypted data no user, application or script is given
access to the tables in question. Access is only granted via a couple of stored
procedures and to be honest if you didn't know which ones you would have a hard
job finding them as we have hundreds.
Problem with keeping any part of the key in a place other than the mysql server
is you add complexity and give yourself a whole bunch of new security concerns
as you then have to transmit the 'key part' to the mysql server over a network.
For someone to take complete control of our mysql server and compromise our
data they would need to guess a username and password for the box in under 3
attempts, then guess the root password and then guess a valid mysql username
and password.
The biggest headache for us, and one which is often overlooked is 'How do we
keep our backups secure'
-----Original Message-----
From: Jim <j...@lowcarbfriends.com>
Sent: Friday, March 19, 2010 4:22 PM
To: John Daisley <daisleyj...@googlemail.com>; mysql@lists.mysql.com
Subject: Re: MySQL Encryption
Thanks for the reply, John.
What you are describing seems to be the approach I've seen on the few
places I've seen this topic discussed.
I've been considering something along those lines, essentially a two
part key.
Part one of the key is made from some data that is in the record I want
to protect and it is different for each record, very much like you suggest.
Part two of the key is some constant key value I store somewhere.
The full key is created based on some defined manipulation of the two
parts, much like you suggest I believe.
But, then the issue comes of where to store part two of the key.
In your case, you are storing it in a stored procedure and I assume that
stored procedure resides on the same mysql server that holds the data
you want to protect.
That's where I start questioning the security of that approach. The
assumption being if someone got full control of that mysql box then
essentially all your eggs are in one basket.
I was thinking in terms of a most secure solution, you could have a
separate server (perhaps a mysql server) that for the purpose of this
example only serves part two of the key. That server is well protected
and non-public as is the mysql server that stores the data.
This way, two servers have to be compromised in order to gain all the
parts of the key and data. But, of course, that's kind of a waste of a
server and can you afford that and the extra resources that go along
with maintaining another server.
So, I was thinking, is it really so bad to store only one part of the
key in source code. That source code resides on a separate server from
the mysql server. Yes, the server that stores the source code is a
public server, but at least it's two servers that have to be compromised
to give up all the components needed to gain access to the encrypted data.
I suppose maybe if I ask you to expand on what you mean by the following
that would be helpful to further understand your approach:
"I then store the logic in a database stored procedure and use database
security to prevent unauthorised access".
Thanks,
Jim
On 3/19/2010 6:39 AM, John Daisley wrote:
> Jim,
>
> I tend to derive a key based on a separate character string and the
> contents of the data in the same or a related table. This means each row
> has a unique encryption key and you never have to have the whole key
> stored somewhere (you don't even know it :p ). Biggest advantage to this
> is should someone get hold of your data they have to work out your
> character string and the logic for deriving the key or attempt to hack
> each and every individual row of the table because no two rows will ever
> have the same key.
>
> For example, in a table with the columns `username`, `email_address`,
> `password`, `jointime` (where password is encrypted with AES_ENCRYPT) I
> may Use a charcter string of "awfully_complex_char_string-" and derive
> the key like so
>
> CONCAT("awfully_complex_char_string-",SUBSTRING(`email_address`,1,LOCATE("@",`email_address`)-1),CAST(`jointime`
> AS CHAR))
>
> I then store the logic in a database stored procedure and use database
> security to prevent unauthorised access. At no point do I have this
> logic outside the database in any external application or script! That
> would be silly :)
>
> Regards
>
> John Daisley
>
> On Thu, Mar 18, 2010 at 7:26 PM, Jim <j...@lowcarbfriends.com
> <mailto:j...@lowcarbfriends.com>> wrote:
>
> In terms of encryption functions AES_DECRYPT and AES_ENCRYPT, can
> anyone point to any good links or offer any suggestions in terms of
> best practices on storage of the associated symmetric key? I've
> found very little information on this when searching.
>
> Does MySQL offer any asymmetric encryption capabilities?
>
> What are people using in terms of a good solution for encrypting
> specific columns of table data while providing protection of the key?
>
> Thanks,
> Jim
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
