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):
- A separate deployment system does schema upgrades while deploying the app itself.
- 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?