3 ms·

HTTP doesn't preclude robust security at all. But how is a database-direct API layer going to enforce Role Based Access Control? Or really any access control mo

HTTP doesn't preclude robust security at all. But how is a database-direct API layer going to enforce Role Based Access Control? Or really any access control model? Database security is at best per-command and per-table, when true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row. Example: user x has access to change payment details (all in the same table of course), but not those for user y. A straight-up HTTP API layer won't have the flexibility to enforce that, or it will be entirely too complex in order to support every possible business rule set. As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in? Sure, you can validate that on the client side, but I can just as easily use anything that will make an http request to make that same http request without doing any business rules validation. Heck, I can alter javascript while it's running just to circumvent business logic if I so desire.

imanaccount247 12y ago
>Or really any access control model? The same way you already do? >Database security is at best per-command and per-table, when true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row. Stored procedures already solve all the problems you are imagining. Sane people already use them anyways. >As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in? The same way it already does? If your database is not enforcing those rules then you are already doing it very wrong.
dragonwriter 12y ago
> Database security is at best per-command and per-table per command and per relation is more common (though many databases, including Postgres, actually also include column-level permissions, as well), but relations aren't restricted to base tables. > true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row. The effects of per column and per row security can (even if per-column permissions aren't directly available), in most DB engines, be managed using either views, stored procs, or both. > As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in? A few options: (1) Order submission is a call to a stored proc which performs the validation then does any necessary table updates, or (2) Order submission is through insert to a table (or view) which has an appropriate BEFORE INSERT trigger that performs the validation, (3) Order submission is an uncondition insert to a table, but that table is just an "incoming_orders" table that isn't particularly actionable -- the actionable views on that table (executable_orders and orders_with_payment_errors, say) are views that based on joins that validate the payment information, including gift cards. There's probably other database-side ways of dealing with that problem, as well.

imanaccount247 12y ago

>Or really any access control model? The same way you already do? >Database security is at best per-command and per-table, when true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row. Stored procedures already solve all the problems you are imagining. Sane people already use them anyways. >As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in? The same way it already does? If your database is not enforcing those rules then you are already doing it very wrong.

dragonwriter 12y ago

> Database security is at best per-command and per-table per command and per relation is more common (though many databases, including Postgres, actually also include column-level permissions, as well), but relations aren't restricted to base tables. > true app security often needs to be per-row, or even per-column, perhaps depending on data in the other columns of the same row. The effects of per column and per row security can (even if per-column permissions aren't directly available), in most DB engines, be managed using either views, stored procs, or both. > As far as business logic, how is an RDBMS going to make sure that your remaining balance on your gift card is higher than that order you just put in? A few options: (1) Order submission is a call to a stored proc which performs the validation then does any necessary table updates, or (2) Order submission is through insert to a table (or view) which has an appropriate BEFORE INSERT trigger that performs the validation, (3) Order submission is an uncondition insert to a table, but that table is just an "incoming_orders" table that isn't particularly actionable -- the actionable views on that table (executable_orders and orders_with_payment_errors, say) are views that based on joins that validate the payment information, including gift cards. There's probably other database-side ways of dealing with that problem, as well.