User permission table structure

Question

Hi,

I have a PHP script with many restricted areas. In each of these areas I have a function that checks if the user have access to the current area by checking the "usergroup" table. That problem is I have over 100 columns now so I'm not sure if that's a correct database design.

Please advise. Thanks.

Answer 1

I think this might have been the incorrect approach.

You should have gone for something like

UserGroups

• UserGroupID
• UserGroupDescription

UserGroupRules

• RuleID
• RuleSection
• RuleSubSection

UserGroupRuleLinks

• UserGroupID
• RuleID

Then could have simply checked if the group has the appropriate rule associated.

Answer 2

There are some people who will tell you to go with a roles based permission structure but I prefer binary permissions myself. Way back in the day I would use an int field which would give me 32 distinct flags that I could set. The permissions table would contain the name and value of each flag and a permits table would hold all the permissions applicable to each user. I also implemented a groups structure and split the permissions fields into allow and deny which gave me a great deal of flexibility. Essentially the permissions would be calculated like so:

AllowMask = userPermit.AllowPermissions;
DenyMask = userPermit.DenyPermissions;
foreach(groupPermit in groups.UserMemberOf(UserID))
{
   AllowMask = AllowMask | groupPermit.AllowPermissions;
   DenyMask = DenyMask | groupPermit.DenyPermissions;
}
Permissions = AllowMask & ~DenyMask

From there it was a simple matter of getting the flag value and checking Permissions & FlagValue > 0;

As you've noted in your question however it's possible that 32 flags aren't enough. I ran into the same issue and began working with varchar fields which held base64 encoded numbers. Since base64 characters hold 6 bits I would simply make sure that the character lengths were some multiple of four since 4x6 = 24 / 8 = 3. This gave me enough space to convert 4 char chunks into ints and run the above function on them. If a flag was greater than 2^24 I'd just shear off 4 characters and work with a smaller number.

Hope that makes sense. It's kind of a confusing system to put in place but once it's running it makes permissions handling a dream.

Answer 3

This is a classic problem as systems grow over time. The model begins with a half dozen permissions, and over time it grows to many time that, at which point it becomes ugly and unmanageable.

I would look into role based access control. You define a series of roles which can be assigned to your users. The permissions are then assigned to the role, not the user. This makes user management very easy, even for people with little understanding of the system -- rather than having to select from hundreds of permissions, they are choosing from a small number of roles. Whenever you need more granularity, simple create new roles.

It may look intimidating at first, but you're actually looking at just a few tables:

• user_role_assn
• role
• role_permission_assn
• permission
• permission_object (lookup)
• permission_operation (lookup)

I implemented the basic RBAC spec a few months ago, and the initial revision only took 3-4 days to build and implement.