Comment by littlecranky67
4 years ago
Authorization & access restrictions. Yes, you can go quite far with table/row/column permissions, but a lot of business logic cannot be modeled using just those (i.e. "user cannot place orders if total outstanding invoice payments surpass value $X").
The combination of DB permissions, DB constraints, and simple (SQL, not procedural language) triggers gets you a lot, including the ability to enforce rules like the one you mention.
Yes, you can enforce a lot through SQL triggers/stored procedures etc. But you often end up abusing your DB/SQL as a business logic layer, where your business logic is encoded in a huge set of row/column permission and custom SQL triggers . This tightly couples your database into your whole business application stack.
Especially in Oracle PL/SQL, I've seen this often abused to an extend where no one ever understood the whole business logic anymore (as logic was spread out in frontend, middle-layer services, and DB mumbojumo), and the database became a fragile core-piece (with a significant vendor login) and hindered all sort of future development.
Seriously, your business logic should be modelled in code, ideally in some sort of service layer (which does not necessarily mean microservices!).
> But you often end up abusing your DB/SQL as a business logic layer, where your business logic is encoded in a huge set of row/column permission and custom SQL triggers.
That's not “abuse”. Admittedly, it's no longer an essential best practice for most systems, the way it used to be viewed, because it's more common to have a single application which fully owns the database and not to (at least in idealized theory, though very some ops staff still end up with direct access to the prod DB) allow access by other means, so in theory it doesn't tend to be necessary to avoid either circumvention of rules or (inevitably inconsistent, as well as expensive to maintain) duplication of logic.
Then why aren't we working on improving the databases to allow for such complex rules, and instead wrap it in another layer (often multiple) to do all this stuff there?
because a database should not hold your business logic. It should hold your data, and that it can do well. See also my other post on parent for more reasoning.
I beg to differ in many interesting cases, we put at least some parts of the business logic into the database. The table design is a direct consequence of the business logic, the same is true for constraints and triggers.
Why shouldn't it? As noted above, database servers already have most of this (security) logic in them - likely tested much better than whatever you can write on top of the database yourself. And given how many apps are basically just CRUD, why reinvent the wheel every time?
3 replies →