DB schema for RBAC with multiple levels of roles

Question

I'm trying to come up with a DB schema for an RBAC, and I want to be able to create "departments" and "positions". Positions will extend the generic privileges of departments. Should I just create a single "roles" table, holding both the positions and departments? Or should I create 3 tables: positions, departments, and roles, with the positions and departments table having a foreign key to the roles table? Thx in advance for your help everyone! Cheers.

Answer 1

My experience while experimenting with a custom RBAC implementation is as follows:

1. You read a lot of the RBAC literature and think you understand it. Then you go ahead and try to implement it, just to realize you didn't really understand it at all. Eventually it will make sense as you move along in the project.

2. Based on your question, you already know the business domain to which you want to apply RBAC. But forget about the actual business objects for now. Your RBAC implementation should be generic, meaning that you have a DB schema consisting of Role, User, Permission, Operation, etc tables. Then you will have objects which map to such tables (one-to-one relation).

Once you have this RBAC implementation, it can then be modeled to practically any business domain, such as a 'Deparment' that you have mentioned.

Just remember that it's not all perfect... I've enhanced/modified/derived from the actual RBAC literature in order to add custom features, enhance performance, etc.

I haven't worked on this for a while, so I hope I'm correct in the following:

• User: Instances are created and saved to it's backing table.

• Role: Instances are created and saved to it's backing table. Roles will get assigned to users.

• Permission: A permission basically is a combination of an Operation on an Object. Permissions get assigned to roles.

• Operation: An operation is simply anything you want. It could be CRUD (create, read, update, delete) or it could also be 'print', 'search' or anything a human (or system) can perform on an object (or group of objects).

• Object: This is basically all your objects which make up your business domain.

For even more power, you could implement constraints to apply an enormous amount of various restrictions.

With this framework, you should be able to map:

• Who can assign users to a department
• Who can remove them from departments
• How many users can be in a department
• What kind of users (based on their assigned roles) can be in a department
• Which rolls can perform which operations to a department (create, read, update, delete them)
• Etc.