In response to Steve Atkins <steve@xxxxxxxxxxx>: > > On Apr 16, 2009, at 12:40 PM, Bill Moran wrote: > > (This is the traditional "you're asking the wrong question" response). > > > > > What are folks doing to protect sensitive data in their databases? > > I don't think that's a useful way to look at it. Protecting sensitive > data in the entire system, where the database is just one > part of that system is likely to lead to a much better answer. <snip> I disagree. We're already addressing the issues of security on the application level through extensive testing, data validation out the wazoo (to prevent SQL Injection and other application breaches). All our servers are in highly secure data centers. We have VPNs and access restrictions at the IP and the user level to the 9s. It's still not enough. My task here is to develop a system to protect the data in the event that all of those fail. As a result, I'm looking for general advice. I already have a system in place. This is apparently another part that I should have described in more detail. So, here goes: To draw a parallel example on the application: Imagine that you're an employee in a business. When you're hired, you enter your SSN into the company database. Now, your department manager needs to have access to your SSN for various reasons, so the system grants access to your encryption key to the department manager. Based on system policy, the division manager has access to all the data in the department, and the company head has access to all divisions. As a result, the company head can get your SSN out of the database using the passphrase for his key. However, Joe, over in IT can not access your SSN. Even though he's the DBA and can pull a full text dump of the database at will, he can not decrypt your SSN unless he has a passphrase to one of the keys that can decrypt it. All that is pretty standard PKI stuff, and I've created the tables and the functions that implement it. The problem comes when the company head wants to search through the database to find out which employee has a specific SSN. He should be able to do so, since he has access to everything, but the logistics of doing so in a reasonable amount of time are rather complex and very time consuming. On a million rows with the SSN unencrypted, such a query would take less than a second with an appropriate index, but pulling those million rows into the application in order to decrypt each one and see if it matches can easily take a half hour or longer. That's where we're having difficulty. Our requirements are that the data must be strongly protected, but the appropriate people must be able to do (often complex) searches on it that complete in record time. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
