3

One of the common things seen people setup is database users (for use by an actual application) that only have SELECT/UPDATE/INSERT/DELETE permissions. A separate user (that additionally has CREATE/ALTER/DROP/etc) is used to actually deploy schema updates (either an admin account, or a user dedicated for that purpose).

I am wondering what the rational behind this is.

There are two situations I am looking at specifically, both in a continuous deployment environment (so the application is automatically built and deployed):

  1. A separate deployment system does schema upgrades while deploying the app itself.
  2. The application does its own schema upgrades on initial startup.

Scenario 2 is often necessary when there are many environments being deployed to on separate networks (eg separate AWS VPC's).

In the case of Scenario 1, it's not a huge deal to have a separate user account with schema privileges, but in the case of 2, it is.

The only rationale I've ever heard is the cop-out statement "less privileges are better for security". It's not that I disagree with this statement, but I'd like to understand why it is better for security.

I think this boils down to the question: If someone compromises the credentials... what can they do with schema privileges that they can't do with only SELECT/UPDATE/DELETE?

  • "Destroy" the database? DELETE is quite capable of that. UPDATE/INSERT is arguably even more dangerous as an attacker could subtly modify existing data in a way that won't cause an obvious catastrophic failure like dropping a table or deleting all rows would
  • Extract data? SELECT does that.
  • Modify the schema? Uh.. to what end? If done in a way that breaks the app, it's obvious, and either way, may be removed by the next app deployment.

Am I missing something else?

Improve this question

3 Answers 3

Reset to default
2

I'm not sure that this is always done for security from external users. For instance - you might have a DBA who wants developers to be able to Select/Update/Delete records, but not start rolling their own schema changes. So they setup an account to be used by developers while keeping the more powerful accounts for themselves.

In the case of an external user gaining access to your database through SQLi or something like that, I agree that they can wreak havoc with basic select/update/delete operations, but there are others things that they could do if they have the ability to say drop tables. For instance - if the main page of an application queries a specific table and that table has been dropped, the attacker can essentially prevent the site from coming up. Granted - if your db has been compromised you probably want to take the site offline, but ideally you decide when that happens and not the attacker.

This might be far fetched, but an attacker could also start tracking information that they might find useful by logging information to a table they have created using triggers or something like that. If the database doesn't get a lot of attention this might go un-noticed and they could check back and use this information for more sophisticated attacks.

Those are some specific scenarios that come to mind. I disagree with your assertion that creating accounts with the least privileges they need to function is a "cop-out". Depending on how creative an attacker is, i'm sure there are other ways they could exploit these extra privileges to their own nefarious ends. In addition I can't think of a reason to give accounts more power than they need.

Improve this answer
1
  • Sorry I didn't mean it's a cop-out to have least permissions (I agree with that part), I meant it's a cop-out statement to justify it as "more secure". In this case, I have a need for the permissions (deployment) and so I'm asking what's the danger in having those permissions. Or to put it another way, is there a justification for spending more time and building something more complex to keep the deployment and regular use accounts separated. At the end of the day, if someone has somehow got your credentials in the first place AND can access your database server, you're pretty screwed anyway.
    – gregmac
    Feb 18, 2016 at 21:56
1

The ability to change the schema is among the most power you can get in a database. Select/Insert/Update/Delete only allows you to change the data. Updating schema allows you to change all objects. This includes extremely powerful things like:

  • Changing stored procedures
  • Adding triggers
  • (possibly) Change scheduled tasks (depending on how your DB is configured)

The top two allow code level access, which is far more than simply being able to view the data. An attacker could easily gain far more control over your application with code level access.

In any case, the ability to change the schema is extremely powerful, and can't be ignored. It's also essentially costless because few, if any applications need this level of access once they're running.

If you need this level of access for CI, I'd suggest designing for the scenario where a higher level of access is needed to deploy the application than to simply run it.

Improve this answer
2
  • I'm still not convinced here. I get what you're saying, but an attacker could modify the database in really bad ways with just UPDATE access. UPDATE userAccounts SET balance = 10000000 WHERE userId = 56022. Or if they want to maliciously break things, UPDATE userAccounts SET balance = balance * (0.5 + RAND()). Put another way, I wouldn't feel any safer after an account was compromised by anyone saying "Well, they only had data access, at least they couldn't do schema changes". The database must be considered as broken regardless.
    – gregmac
    Feb 18, 2016 at 22:03
  • 2
    @gregmac "bad" isn't binary. You're talking about one specific set of attacks on a site that are possible with update/delete access. The point is that MORE attacks are possible with access to changing the schema. Things can always get worse. Remember, this is general advice. We can't comment on specifics here, but in general it's much worse to have schema level access. Hence "best practice".
    – Steve Sether
    Feb 18, 2016 at 22:29
0

You often don't have to give out DELETE permissions. Depending on the application, you'll probably find most tables will never get a DELETE (especially audit trail and financial transactions). There's probably another subset of table that don't need UPDATE as well, where you can limit updates to a subset of columns. You can also have some tables in the same schema totally unavailable to one application, but maintainable from a separate application that might have more secure access (eg VPN to a company intranet).

Some DBMS do have capabilities to limit or audit exceptional activity even from the table owner, but separation via access rights is pretty much universal.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .