13

In the app I'm writing I separated the user and main databases a long time ago for "security reasons". However its getting harder and harder to justify the overhead and the difficulty of managing such a setup.

For some quick detail this is a PHP web app. There are 2 separate mysqli connections, one for each database. Each database has its own user with its own credentials. This means you can't get to the main database with the user database credentials

In theory this prevents SQL injection damage, separation of concerns, security through obscurity, etc. However in a costs vs rewards I loose easy interoperability with the user table and the rest of the database, eg joins.

Are there more concrete reasons that such a database setup makes sense? Is this even common?

Rory Alsop
  • 61,367
  • 12
  • 115
  • 320
TheLQ
  • 1,239
  • 1
  • 12
  • 21
  • 5
    This is not "(in)security though obscurity", which is always a vulnerability. Its security though isolation, which is a defense in depth security measure. – rook Jan 02 '12 at 22:16
  • @Rook, please explain why "security though obscurity" is "always a vulnerability" – 700 Software Jan 03 '12 at 20:04
  • @George Bailey To be honest in my carrier I have never heard of it being refereed to as a good idea, in fact if it actually worked then it wouldn't be called "security though obscurity", it would just be called security. [wikipeida has a good discussion on this topic](http://en.wikipedia.org/wiki/Security_through_obscurity#Arguments_against). – rook Jan 03 '12 at 20:09

3 Answers3

15

Rather than two separate connections working together, you can use separate user accounts with access to specific columns. Most of your statements can use the user account that has access only to the username (not password) column of the users table, and the less important data from your main db.

In MySQL, you can do this with nice granularity. This will allow you to show everyone's usernames, and only access passwords in login pages, and only access sessions in session management, only access email addresses in notifiers, etc.

With this model, SQL Injections will generally be limited to the public info, or the single section that they reach.

Of course, what is public, how your users are set up, how many, what they get is all specific to your use case.

Check the GRANT documentation for your version of MySQL.

Disclaimer: This is not a fix to SQL Injection, only an effort to help minimize the impact.

700 Software
  • 13,807
  • 3
  • 52
  • 82
  • This is... impressive. I had no idea I could do this. I will be sure to look into it. – TheLQ Jan 02 '12 at 23:10
  • 7
    @TheLQ You can also limit the risk of disclosure by never allowing the password hash to be read. Use stored procedures to create setHash() and verifyHash() functions to inform your application whether the password matches. – Jeff Ferland Jan 05 '12 at 08:50
3

if the app accessing the two different DB is the same, the separation of the DBs is not so useful. It complicates EVERY programming phase and DATA related question. I'd prefer a logical separation instead of physical separation of the data.

It's the same with two safes: if you have the 2 keys in the same place, how much security is for the two safes?

The "usability vs security" balance here looses much more usability respect to security.

robob
  • 243
  • 2
  • 8
1

Depends.....

For example bank store PINs in their own database, on their own server. The server also auto deletes the data if anyone tries to remove it from the rack.

One good reason for a separate database is if you wish to be able to get your customers to send you there database when they hit a bug. If the database does not contain any password there may be fewer issues.

I think a separate database only make sense if it allows you to manage the two databases in a different way.

Ian Ringrose
  • 641
  • 1
  • 4
  • 9