3 ms·
> But, you do have to enable it for each table plus you need to commit to using database roles as a main security mechanism. That last part is the barrier but
by davidw 11y ago
> But, you do have to enable it for each table plus you need to commit to using database roles as a main security mechanism. That last part is the barrier but also the reason to use such a feature.
That's kind of a sticking point for web apps. Wonder if there's a way around that.
- rch 11y agoIt can be really nice for enterprise web apps. One might have thousands of users organized into a handful of departments with different authorizations, some of which have their own DBAs and developers. Solid testing on the application side is important, of course.
- rst 11y agoIf the database roles correspond to departments, and authorizations at a department level, this sort of thing can be quite useful. (e.g., in a hospital, designating certain rows as visible only to the psychiatry department, in a way that developers within other departments can't screw it up.) But establishing a DB role for every distinct person within a department gets awkward very fast, particularly if permissioning requirements have a bit of business logic associated with them (e.g., "this row can be updated by the user designated in the row itself as "owner", and is visible to both them and anyone designated in this other table over here as their assistant...") So, you'll often still need additional permissioning logic within the webapp regardless.
- rch 11y agoYes, exactly. Roles would generally be by department, or in an application/department matrix. Your hospital example is spot on, and in finance a department might be a trading group.
- elchief 11y agoIt's pretty easy to set the user's role after connecting: https://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database/19602050#19602050 https://stackoverflow.com/questions/2998597/switch-role-afte...