The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Sensitive data in DB's

I'm working on the architecture for a high-volume web application.  A question has arisen in our discussion, and I'd like more input on it.

We will be storing confidential information in a database, along with non-sensitive data.  Would we gain anything (aside from additional complexity) in have two databases, one with the mundane information, and the other with the sensitive info?

Data access would look like this:
Application -> Mundane DB -> Sensitive DB

The app would not be able to access the sensitive DB directly, only the mundane DB would be able to access it.  Does this make a system more secure, or just more obfuscated?
nathan Send private email
Wednesday, December 13, 2006
Once I have access to the Mundane DB doesn't that automatically mean I have access to the Sensitive DB?
Almost H. Anonymous Send private email
Wednesday, December 13, 2006
Just adds needless complexity.  There are sufficient methods available to secure data within a single database.
Mike S Send private email
Wednesday, December 13, 2006
Can you move this data to distinct tables?  In mysql, you can limit which database users have a access to specific tables, so that may be a strategy...
KC Send private email
Wednesday, December 13, 2006
You can set up different views/stored procs that access sensitive parts and normal parts then set different permissions on them.
Wednesday, December 13, 2006
Obviously it adds complexity, whether it's worth it depends on how sensitive the data is.

For example, a bank in Switzerland has strict confidentiality requirements and would not store client details in the same database as their portfolio data.

Probably the portfolios would be identified by an anonymous PortfolioId, and the client database would contain an encrypted PortfolioID.  So you would need to access both databases (or their backups) and the decryption key to be able to associate a client with his portfolios.
Wednesday, December 13, 2006
Nathan, most security measures only make sense in the context of a sensible threat model. So first figure out where the real threats are, and only then work on minimising those threats.
Mark Pearce Send private email
Wednesday, December 13, 2006
Sounds like you need something more along these lines (Translucent Databases book):
Wednesday, December 13, 2006
We came up against something like this at work a few years ago (I work for a government agency).

We paid Oracle big money for their RDBMS/J2EE stack and they were keen to see us move to a single database for all our data, of which there is a fair amount. They pointed out that Oracle only use something like three database instances for all their company data (disclaimer: I don't know if that's true or a sales pitch). They said that we could use various security features to effectively partition the single database instance so it would act like multiple ones. Anyway, it turned out that we couldn't do it for statutory reasons, rather than technical.
John Topley Send private email
Wednesday, December 13, 2006
A nice security model in Oracle is to do this:

i) Put data in a schema with minimum privileges, removing connect privilege also.

ii) Grant only the required I/U/D privileges on those tables to a second schema, in which the application code is held -- naturally PL/SQL is the coding environment so you have to go out of your way to suffer from SQL injection.

iii) Grant execute access on the procedures to users as appropriate.
David Aldridge Send private email
Wednesday, December 13, 2006
I don't think you can accurately figure out which way to do this without determining how much traffic goes between the mundane and the secure.

If you are returning/transferring a small amount of data there are a ton of things you can do.

One of the other posts mentioned "if I have access to the mundane, can't I have access to the secure?". I think the way you are connecting the two will make a big difference.

For example, huge difference between the following:
-> Dynamic SQL
-> Stored Procs
-> Endpoints (sql2005)
D In PHX Send private email
Thursday, December 14, 2006
Don't forget encryption as a possible strategy
dot for this one
Thursday, December 14, 2006
How much 'sensitive data' do you have?

At a company I used to work for we adopted the policy of
no sensitive data in unencrypted form in the database ever. We used GPG to encrypt all this information with a public key upon entry, and a small cluster of boxes handled decryption / consumption of that data.

For example, if this is billing data:

* Any box can enter new billing data or remove/replace billing data, but upon doing so it encrypts with the billing systems Public Key.

* Only the billing system itself can access the data needed (to run credit cards for instance) and decrypts with its private key.

Obviously if both your billing server and your dbs are comprimised you're semi screwed, but at that point you've got pretty big problems anyway. Also, this won't work a large subset of all your data is 'sensitive'
Andrew Murray Send private email
Thursday, December 14, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz