I'm designing an RBAC model (role base access control) and have a couple of questions.

I have a permissions table where you can add a object (forum, profile etc), group, and the permission
Example:
Forum object has admin group with permissions can_post, can_view, can_edit.
Forum object has user group with permissions can_post only.
These are represented as rows.

The problem is that this feels like an EAV model. I think permissions should actually be columns...not rows.

Thus, the forum_permissions table could instead be...
CREATE TABLE forum_permissions
(
forum_name VARCHAR(25)
group_name VARCHAR(25)
can_view_topics CHAR(1)
can_view_posts CHAR(1),
can_create_post CHAR(1),
can_edit_post CHAR(1),
can_edit_any_post CHAR(1),
can_create_poll CHAR(1),
can_edit_poll CHAR(1),
can_edit_any_poll CHAR(1),
can_vote_poll CHAR(1),
can_delete_post CHAR(1),
can_delete_topic CHAR(1),
can_delete_any_post CHAR(1),
can_delete_any_topic CHAR(1)
can_delete_poll CHAR(1),
can_delete_any_poll CHAR(1),
can_move_topic CHAR(1),
can_move_post CHAR(1)
PRIMARY KEY (forum_name, group_name)
)

Now the problem is that it could contain too many columns once the permission set gets large. Also, what would happen if the client wanted to add a new permission in production? With the EAV model, it would be as simple as inserting a row into the permissions table.

Any suggestions?

A common approach for role based security this is to have a Roles table defining a role id and description, then a user/role table and a group/role table for granting roles to specific users or groups. You could borrow from this approach and create a ForumRoles which would include a forum id for each role assignment:

ForumPermissions
----------------
ForumPermissionId
Description (view_post, delete_post, etc)

ForumUserPermissions
-------------
ForumId
ForumPermissionId
UserId

ForumGroupPermissions
-------------
ForumId
ForumPermissionId
GroupId


Implementation would involve exposing some methods on your BLL/DAL to ask whether the current user of interest (or any groups said user belongs to) have been granted the appropriate ForumPermission to perform the requested action on the target ForumId.

Teddy's design looks more practical to me than using one column for each permission.

I don't think this has much to do with an EAV model because it's not a question of cramming multiple attributes and types into one column. It's more a case of whether to represent the data as one attribute or a collection of boolean attributes. No right or wrong answer I suppose but it seems likely that Teddy's model will be easier to maintain.

Thank you both for the responses.

One disadvantage with Teddy's approach is that you have zero compile support in the application layer. If you were to ever change a permission attribute name (create_topic to post_topic)...you would have to manually find every reference and change it. Also, typos could even cause bugs.

In the one column for each permission attribute approach, you get compiler support in the application layer (ForumPermission.PostTopic etc) and re-factoring is easier.

There's nothing stopping you from using an Enum for which the integral value maps to the appropriate RoleId in your database. Your actual role class could then have a "RoleEnum" as a property along with any ancillory information you'd like to make available regarding your roles.

At that point you're trading intellisense and automagic refactoring stuff for possible inconsistency and additional maintenance points. For example, should you ever delete and re-create a role, it is very highly likely that this role would have a different RoleId on creation assuming RoleId was an identity/auto-increment field. This would invalidate the enum value and you'd have to make sure you remember to go in to the code and update your enum to reflect the new id. You'll have the same issue with your whole-bunch-of-bools-on-a-dedicated-class method. You still have to handle the fact that the app doesn't know about any changes to the database schema/roles until you specifically teach it.

Depending on your language, you could get really rambunctious and dynamically compile your enum on the fly. I wouldn't recommend doing that, but you could...


One of the major strengths I like about the model is you can very easily provide more comprehensive functionality such as CreateRole, DeleteRole, etc... from the application. You can't really do that if you have a class with members that represent all available roles. End users tend to like the idea of not requiring a software developer in order to add a role...