Right now we're implementing a (honestly overly complex) role-based permissions system where users will be assigned to roles and those roles will be granted permissions on some combination of resources. Fairly traditional, and I think fairly straightforward to implement database-wise by linking a table of users with a table of roles linked to a table of permissions, which have a reference to the resource type they're a permission for and a simple bitmask identifying the CRUD permissions they've been granted.

That said, we've got two edge cases involved:

  • First, there are administrator users who have unique permissions which can't be granted to users. Some of these are specific features which don't really map nicely with database tables and resources and are specifically available only to administrator users
  • Second, since we're a multi-tenant system we have a group of in-house users who can access any database. While the tenant's users all sit within the same database as the tenant's data, these support users all sit in a separate database; otherwise they act as administrator users, occasionally with additional permissions specifically available only to them (again)

Normally the level of specificity required for these users makes me want to move the roles into the application code, but I can't do that because tenants need to be able to manage and assign their own roles, up to and including the creation of new administrators. What this boils down to is that users will likely end up having some combination of roles and attribute-based access control in place: roles for most users, attributes lookup for special cases. So basically hybridizing ARO and attribute-based access control.

My alternative solution here is to create hardcoded roles in the database, potentially reserving the first ~10 or so IDs in my roles table for "special" roles with hardcoded behaviour. Which also feels nasty.

Which approach should I take? Or better yet, is there an alternative approach I haven't thought of as yet?

2 Answers 2


Preferable any type of user action should be controlled in the same way. Administrators as just users (don't tell them I said that :-)), just with a set different set of permissions.

Perhaps these permissions do not fit in the current permissions model because the model is only applied to basic database actions.

A permission system involves more. Any action that is not allowed by all users should be regulated by the permission system. That includes actions like being able to view or export data.

And if some user users may circumvent the application and directly access the database, any important change or access should be captured in audit data that cannot be modified by the same person.

A more complex system also includes the contents of the data. Sometimes a user may only perform a function on part of the data.

Storing permissions directly in the code seems a bad idea to me. It's not possible to change at runtime and you need extra care when making code changes to make sure the permissions are enforced in all situation.

I suggest looking at a permission system that covers all actions of all types of users in the same way.

That may involve having to change the system that needs to be controlled.

  • That would be really nice to have, yeah; as for the contents of the data, do you mean field-based or row-based permissions? Because I actually also need to implement both of those features too and would love some reference implementations to look at because I'm honestly a little stumped on how to deal with database design for something like that.
    – moberemk
    Aug 25, 2015 at 0:29
  • I meant row-based permissions. But depending on database structure the access to information can be split over rows and/or fields. You could investigate 'identity and access management' systems. I think most of the times companies buy such systems, not develop their own. But it might help to get better informed.
    – Kwebble
    Aug 25, 2015 at 21:58
  • I have been yeah, it's just hard to find much on systems that have the necessary level of feature complexity to match what I need to build--most frameworks I can compare against stick to very simple and general permission setups, which makes this product feel excessive by comparison. Back to the books it is!
    – moberemk
    Aug 26, 2015 at 2:15

In this case I would go with the hybrid solution.

Like you say, the other options are essentially hardcoding the special permissions in one way or another.

In this case I'd look at the security scheme set up in most DBMS systems:

  • Users can get specific permissions on tables in specific databases
  • Users can get specific permissions on the system as a whole
  • Users can get the right to grant specific permissions to other users (This may or may not be useful for your specific system.)

Most relational DBMS systems use system tables that store all the information on user accounts and permissions, so it would be easy enough to take a look at and learn from.

  • Something to note, this doesn't quite address the implementation details of row-level access control, which does get into much trickier territory (and I've been realizing belongs more in the realm of attribute-based authorization anyways).
    – moberemk
    Mar 26, 2016 at 0:53

Your Answer

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

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