1
  • I wrote a Java web service using Spring that handles REST calls from the Internet.
  • The web service uses a Postgres database underneath to store, modify and read data.
  • The Postgres database is used exclusively by this web service, no other program accesses the database.

The web service uses a database user that has all rights in the database schema (dropping tables, modifying tables, etc.).

Would there be any tangible benefit in using a database user for this web service, that only has rights to modify table entries (select, insert, update, etc.), but no rights to execute DDL statements?

Or would this be over engineered?

Improve this question
4
  • What would be the worst-case outcome (business wise), if a bad actor gets hold of that account used by the web service and destroys the database (drops all tables and locks out everyone from the database)? Keep in mind that the credentials for this account are, for all practical purposes, stored in plain text on the server hosting the web service.
    – Bart van Ingen Schenau
    Sep 2, 2021 at 8:37
  • 2
    Following the principle of least authority is a security best practice. Denying DDL rights to the service account could make recovery easier after an attack, but nuking the entire DB and restoring from a backup seems better anyway. In the grand scheme of things, following PoLA here is not that urgent. Other security measures (proper firewall rules, preventing SQL injection, having a backup and recovery plan) should probably be prioritized.
    – amon
    Sep 2, 2021 at 12:33
  • @BartvanIngenSchenau is the db destruction really the only worst case? What if the attacker uses the credential to covertly host an illegal dataset at one’s expense? What would be the reputational damages if law enforcement cracks down and seize the servers a couple of days because of prohibited criminal activities running under the name of your company in its databases? And what if the control over the db content is only one step in a larger and more complex attack we cannot imagine yet?
    – Christophe
    Sep 3, 2021 at 14:14
  • 1
    @christophe, that was just the first one I came up with. Your scenarios are equally valid.
    – Bart van Ingen Schenau
    Sep 3, 2021 at 16:37

3 Answers 3

Reset to default
5

The security argument against using DDLs is neither completely wrong nor completely right.

If a web service is compromised, and an attacker manages to issue arbitrary select, insert, update or delete statements, the damage they can cause is not really different from the damage they can cause by a drop table or modify table statement. Ok, when you system allows installing of new stored procedures or other forms of executable code by DDLs, an attacker might cause more damage than just by destroying or extracting some data, and it is probably a good idea to forbid such DDLs.

But with or without DDL access rights, the countermeasures to make a system robust against such attacks are 100% the same:

  • be extremely careful with dynamic SQL, and make sure only statements prepared by you can be executed, with properly validated input parameters

  • have regular backups in place

  • keep your system up-to-date with regular updates

  • organizational measures

When the system was successfully attacked, you will usually have to make a full restore of the system, including data and DB schema. If an attacker had no execution rights for DDL statements, I don't think any professional DB admin will try only to restore the data based on that information, they will usually restore the whole database anyway.

Improve this answer
3
  • 2
    I think this is the more precise answer.
    – T. Sar
    Sep 2, 2021 at 15:46
  • DDL isn't just about tables - it can create functions, load extensions, add tablespaces and so on. With another vulnerability, maybe you could use it to read a local file or get a shell. From a cost-benefit point of view you might reasonably decide not to do it, especially if combined with other security measures or if your app needs DDL. But there's at least some level of security benefit to use a database user with permissions limited to what you need.
    – Alex Hayward
    Sep 3, 2021 at 14:26
  • @AlexHayward: you have a point, I will come back and adapt my answer later.
    – Doc Brown
    Sep 3, 2021 at 15:21
3

Are you absolutely, completely, 100% sure that there are no vulnerabilities in your code and all the libraries you're using to access the database that might allow SQL injection or the like?

If you are sure, then there's no benefit. If you're not, then the benefit is obvious.

(PS: you're not 100% sure)

Improve this answer
3
  • 2
    I had intended to post a similar answer, but then it occurred to me that it's not so obvious. Whether an adversary drops a table or deletes all rows doesn't really make a big difference. Most harm is probably caused by extracting sensitive data or manipulating data in ways that are not immediately obvious, and using a restricted user doesn't prevent that.
    – Hans-Martin Mosner
    Sep 2, 2021 at 8:55
  • 3
    They could be 100% sure. It's easy being sure when there's no requirement to be sure and right about it.
    – Joachim Sauer
    Sep 2, 2021 at 9:14
  • @Hans-MartinMosner Maybe slightly oversimplified, but it certainly reduces your exposure. And makes it easier to lock the permissions down tighter if you find that becomes necessary.
    – Philip Kendall
    Sep 2, 2021 at 9:20
2

No, it’s not ok. Someone claimed:

security is like onions: the more layers you peal, the more you cry.

This refers to the concept of layered security. There are many ways your system could be attacked: from the outside by compromising your public service; but also from the inside via another service or system: this is called a “lateral move” by the security experts.

This is why every layer needs its own security. And granting db users with rights limited to necessary privileges (more generally called the principle of least privilege), is cheap, offers limited protection, but contributes to this overall security concept. Use it!

What’s the additional risk with DDL here? It’s not only about destroying a db, since there are other means to ruin it, as Dr.Brown explained very well. But it’s also about misusing your db for example for hosting an illegal database, or for covertly exfiltrating data of another system in your datacenter (moreover by making the trafic appear legitimate). Don’t underestimate the creativity of the dark side to exploit flaws at your expense.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.