Database Design – How to Design Role-Based Access Control

access-controlauthorizationdatabase-designpermissionsroles

I am trying to follow the role bases access control model to restrict what users can or cannot do in my system.

So far I have the following entities:

users – People who will use the system. Here I have usernames and passwords.
roles – Collection of roles that users can have. Stuff like manager, admin, etc.
resources – Things that users can manipulate. Like contracts, users, contract drafts, etc.
operations – Things that users can do with the resources. Like create, read, update or delete.

Now, my doubt rises here in the diagram where I have a relation like this:

operations (0..*) are executed upon resources (0..*)
which generates a table I called permissions and that will store the operation and the resource.

The permissions table will look like this (one row of it):
ID: 1, operation: create, resource: contract.

Which means a permission to create a contract.

I have done it this way because I feel some resources may not have all kinds of operations . For instance, for registering contracts, users can upload files, but this operation is not available for registering a provider.

So now when the administrator will be giving permissions to a role, he will not have list of resources with every single operation registered in the system.

I think each resource has its own collection of operations that can be executed upon him.

I can clarify if something is not understandable.

Is this the correct way to implement the rbac?

EDIT

What I mean is that by having a permissions table which has operation and resource, I have TWO extra tables because I want to associate resources with operations. I could have also just done resources have permissions where the permissions table would store the permissions.

But then what would have happened is that some permissions which do not even exist for some resources would have appeared when the admin would be assigning them.

So I want to know from a database design point of view if this table permission which has one column operation and another resource is correct? Will I run into problems if it stays like this?

Best Answer

Your design seems pretty close to me. Just a couple suggestions.

users - People who will use the system. Here I have usernames and passwords.

Fine

roles - Collection of roles that users can have. Stuff like manager, admin, etc.

Fine too. But you will also need a "UserRoles" entity/table that will tell you which users have which roles. It is likely that a given user may have two or more roles.

resources - Things that users can manipulate. Like contracts, users, contract drafts, etc.

Might be just a question of semantics. I don't think users directly manipulate resources; roles do. Thus it goes user -> user role -> role -> operation -> resource

operations - Things that users can do with the resources. Like create, read, update or delete.

yep, except "roles" instead of "users"

The permissions table will look like this (one row of it): ID: 1, operation: create, resource: contract. Which means a permission to create a contract.

Hmmm. There are two ways to go with this. You could have the permissions table you describe, but you would also need an additional RolePermissions table/entity that tells you which role has which permission. But I am not sure that is necessary.

A simpler way to do it is a permissions table/entity with these columns/attributes: Role ID, Operation ID, ResourceID. That way, operations x resource combinations are assigned directly to a role, rather than loaded into a permission that is assigned to a role. It eliminates one entity. There really isn't need for a separate role-agnostic permissions table, unless you wish to predefine what permissions combinations are allowed and which ones are not.

Related Topic