12

Anyone with a brain cell knows that using a user that isn't root/dbo/etc adds a lot to security and how effective SQL injection attacks can be. I'm wondering if taking that idea a step further is a good idea.

The basic idea is simple. For guest-like actions (viewing) use a 'guest' user on the database which only has the permissions to select things. For user-like actions (adding/editing), have a 'user' user, which only has the permissions to run insert and update queries. As well as that, also include a third user with 'admin' like actions, for delete abilities.

NB: This has been simplified to just cover basic CRUD abilities

Pros:

  • Theoretically added security through permission limits.
  • Forces the CMS to be secure to prevent impersonation/replay attacks.

Cons:

  • Complicated code within a CMS (which is what I'm thinking about using this feature in).
  • Forces the CMS to be secure (which will not always be achievable)

My question is this:
Is there any security benefit to separating out roles and permissions this way, and if so, do the benefits of doing this justify more complicated code to force a user switch depending on an action?

For example's sake, let's just say that the check to see if I switch users is only an if-statement away, and if that statement fails, then the user isn't allowed to do whatever action anyways so the switch doesn't happen in the first place.

Mike S
  • 404
  • 2
  • 11
  • Hi @Mike, this isnt really just about MySQL, right? It seems the question could apply equally to any RDBMS... – AviD Apr 27 '11 at 23:34
  • This is very true. Since my case involves MySQL that's where the question originated. It can, at least in my head, be translated to other RDBMSes. – Mike S Apr 28 '11 at 03:51
  • 4
    If you want to see a _great_ example of how to implement PLoP with unix facilities (pipes, users, changing eid, etc) take a look at [Postfix's design](http://blog.schmichael.com/wp-content/uploads/2006/12/mail-server-diagram.pdf). Every little piece of functionality is compartmentalized, runs at lowest possible privileges to do the one task that's intended for it, and communicates with other programs in a very tightly defined manner. It takes a lot of forethought to make such designs, but once you do, the security will largely take care of itself. – Marcin Apr 29 '11 at 13:48
  • Oh! Thanks for the pointer! I'll definitely be looking into that to see if I can apply it to my application. Thanks again! – Mike S Apr 29 '11 at 14:02

3 Answers3

6

In theory this kind of thing should always help (principle of least privilege) and can't really hurt, but if in practice you wind up with a CMS middle tier holding all the credentials anyway, then it may not help much or at all.

An alarm bell is that you are thinking only in terms of SQL injection. The principle of least privilege is so fundamental it should be buying much more protection than that, it should be covering off entire avenues of attack and whole classes of vulnerability, not just one.

Whether or not there is any real benefit I think depends on whether you can (for example) also point at a machine and say 'this machine only ever has 'guest' privilege', etc. In other words, when you look beyond the database, is the argument for the contribution of this to the whole system's security equally simple?

If the result is just additional complexity of the CMS then it might actually be counterproductive (i.e. introduction of complexity, tends to result in more bugs, some of which will tend to be security holes in their own right especially if the bug is in juggling permissions). So another alarm bell is that you seem to foresee a tangle of code in the CMS. This may or may not be necessary however - can you do it more simply?

frankodwyer
  • 1,907
  • 12
  • 13
  • 2
    So you're saying that the concern should be placed more on the _implementation_ of the idea, as the idea is apparently rather solid? Hmm. You bring up good points. Thanks for the answer! – Mike S Apr 27 '11 at 20:16
  • more the design than the implementation - you want to make sure that the rest of the system has an equally simple argument as to why it has the same simple privilege separation that you're putting in the DB. Basing it all on CRUD semantics seems like it could lead to something elegant - for example Rails tries to map CRUD onto HTTP verbs. – frankodwyer Apr 27 '11 at 20:33
1

Making something more complex is never good for security but on the other hand giving someone more privileges the what they need may also compromise security. So its a balance between complexity whit additional security features or simplicity whiteout the additional features. What you are suggesting sounds interesting. However my suggestion is to focus your attention on creating a strong data sanitizing filter. Preventing the SQL injection to even reach the database.

KilledKenny
  • 1,662
  • 4
  • 19
  • 28
  • The sanitizer is on my list, and currently stands up to basic testing. Still looking in more places for new attack vectors to test, so that when it goes to production it'll at least be semi prepared to take on the real world. Thanks for the answer! – Mike S Apr 27 '11 at 19:32
  • This completely ignores the principle of defense in depth... – AviD Apr 30 '11 at 22:13
  • @AviD I know... I like the idea of one strong line of defense instead of multiple small ones. Don't expect it to brake make shore it doesn't instead :p But i must say that its a intriguing question – KilledKenny Apr 30 '11 at 22:24
  • But no matter what, it *will* break, eventually. Better to be robust an resilient in face of the unexpected. – AviD Apr 30 '11 at 22:31
0

Use SSL/TLS client certificates to a SSL/TLS enabled MySQL instance for each app. There are a million things to do with MySQL security and it deserves a separate set of answers.

Perhaps this question is the right place -- MySQL Server Hardening.

Community
  • 1
atdre
  • 18,885
  • 6
  • 58
  • 107
  • We're generally assuming that the secure stuff should already be happening. The question was not about general MySQL Security, but more or less the benefits of implementing the principle of least privilege on the database vs the cost of slightly more complicated code in the app level. – Mike S Apr 28 '11 at 14:07
  • @ Mike: Yes, and not only did I answer what I think needs to be done regarding least privilege in MySQL but also hinted that much more needs to be done. – atdre Apr 28 '11 at 15:16
  • @ atdre: You did? Where? I don't see it. All you said was to use secure connections, mentioned that there's a bunch of other things about security, and then pointed me to a question that has no relation to what my question was about outside of mysql security. Sure the chosen answer there says something about separation of privileges, but it's not nearly as focused or in depth as a proper answer for what I was looking for. Seriously, your answer would have been better as a comment. – Mike S Apr 29 '11 at 02:54
  • 1
    @Mike S: Client-side certificates can replace users as far as connection credentials are concerned – atdre Apr 29 '11 at 05:51
  • @atdre I +1'd your latest comment: perhaps you should incorporate a description of how client-side certs integrate with MySQL authentication in your answer. –  Apr 29 '11 at 09:34
  • @Graham That won't make his answer any more relevant to the question I was asking... Though this is getting useless to argue, so I'm just gonna leave it be. – Mike S Apr 29 '11 at 14:05