Storing a large number of user permissions [closed]

Question

I am refactoring an old application which has about 200 user permissions.

Based on access permissions for the user, menu items for webpages will be Enabled or not. Permissions are also checked on the Load of a webpage to see if the particular user should have access or not.

At the moment there are a few "category" permissions such as Maintenance, Processing, Reporting. In the database each "category" has a string and each position in the string refers to a page - [user_id,permission_type,permission_string]

So, for example, the string for Maintenance would be "10001110000" which would mean that the user has permissions to access pages 1, 6, 7 and 8. When the user wants to access page 1, a check is done to see if Mid(maintenance_string, 1, 1); is 1 or 0 and if it is 1, the user has access to the page (or menu item.)

The issue at the moment is that the Reporting string is over 100 characters long and it's becoming dificult to manage.

How should the permissions be stored a.) in the database b.) in the application?

Answer 1

In my experience, User Groups/Roles separated from User IDs is generally the preferred way to go with this, and have users assigned to specific roles, not assigned to specific permissions.

It sounds like, based on how you described it, the SQL database for user permissions looks like this:

User_ID / Permission Type/ Page Permissions

1.    Maintenance    10001110000
2.    Maintenance    10100000010
1.    Reporting      1010000001010101010101010....
3.    Maintenance    10100000010
4.    Maintenance    10100010010

Or something along those lines - if there's repeat permissions like with users 2, 3, and 4, it might be worth grouping their alike permissions together, into a role, and just assign them all the same role value, like so:

User_Group_ID /User_Group /       Permission Type / Pages 

1             Base_Maintenance   Maintenance     10100000010
2.            Unique_Maintenance  Maintenance    10001110000
3.            Custom_maintenance  Maintenance    10100010010
4.            Default_Reporting   Reporting      1010000001010101010101010....
....

This would allow you to assign most of your users to Base_Maintenance as a role, and be able to get the final string of the roles, for a basic user.

While it does sound like the granular nature of the permissions might still be overcome by attempting to group users together, having a reference that could be assigned to a textual description of what they're supposed to be able to do can help ensure they only access what they can access - and if you need to make a large scale change, you can do it in one place.

You could also go a bit more granular there with simpling &'ing strings of permissions, so that Custom_Maintenance's string instead becomes "00000010000" - anded with Base_Maintenance, you'd get the same result.

That said, this is focusing on the Page format string, and I'd be less focused on doing it that specific way.

Instead, you have these user groups, and have users given IDs that correspond to individual User_Group_IDs, like so:

User_Group_ID /User_Group      / Access_Type

1             Base_Maintenance    Maintenance
2.            Unique_Maintenance  Maintenance 
3.            Custom_maintenance  Maintenance 
4.            Default_Reporting   Reporting
....

User_ID /User_Role_IDs

1             1,2,4   
2.            1
3.            1,3,4 
4.            1,   
....

Then, for the specific pages themselves, you could indicate permissions based on the User_Role_IDs assigned to the user:

Page_ID / Accessible_User_Role

1        1,4      
2.       1,4 
3.           
4.            
....
7.       2,3

There's other ways to split up how Permission_Type and Roles are separated, and perhaps indicated per-page specific items, but this would help decrease the load on where to look in a single string to determine who had access to what; you can simply look at "Are they assigned a role that is granted that permission to that particular page?"

The latter example I mentioned here does involve a lot of re-engineering of the solution, so the role and page permissions by number value may be the better choice in the short-term to get to a form more like where the strings in questions are abstracted out to indicate actual sortability into what they can access.