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?

  • please don't cross-post: stackoverflow.com/questions/75717870/… "Cross-posting is frowned upon as it leads to fragmented answers splattered all over the network..."
    – gnat
    Mar 13, 2023 at 4:56
  • @gnat I wasn't sure which site the question was more appropriate for. On which site do you suggest that I delete the question from?
    – Daniel Gee
    Mar 13, 2023 at 5:01
  • @DanielGee: conceptual questions about permissions and application design might be a good fit here, IMHO better than on Stackoverflow. Unfortunately, I think, your question in the current form is quite unclear and would need a rewrite for having a chance to survive. For example, it is unclear why you think the solution to a long permission string depends on the answer to the question asked in the last sentence. ...
    – Doc Brown
    Mar 13, 2023 at 7:18
  • 2
    Do I understand it correctly that your problem is not so much the granularity of the permission system, but rather how it is stored who has which permission? Mar 13, 2023 at 7:55
  • 2
    @DanielGee: I tried to make a start by improving the title of your question according to your comment above. Still I think it is unclear to me what you are after. For example, is each of your categories associated with a fixed set of permissions, or is this different per user? Permissions are granted/revoked by some operator or admin, I guess? Individually, or are admins granting/revoking hole categories of permissions? And user-specific permissions cannot be stored in the (code of) the application, they must go into the database, so what exactly do you mean by "storing in the application"?
    – Doc Brown
    Mar 13, 2023 at 9:43

1 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 
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.

  • 1
    I think that you are correct in saying that I need to create roles. I'm still unsure how to store the permissions for those roles. Should I keep the permission string? Thanks so much for your detailed answer!
    – Daniel Gee
    Mar 13, 2023 at 9:37
  • 1
    @Daniel Gee: As an intermediary step (In part because a full transition might take longer than ideal), putting the permissions strings into the roles location may be the fastest way to migrate to roles out of the gate and get a feel for how many *distinct" roles there are. Long-term, I'd recommend trying to take the permissions and assign them directly to the individual files themselves, and use Role_ID values, not the permission strings, but that's more into "How I would've done that if I had built this from the ground-up" as a starting position to move towards. Mar 13, 2023 at 9:44
  • 1
    @DanielGee: For example, one thing I noticed now that I'm looking at it again, I actually pulled away from is making the Maintenance/Reporting/etc. Access_Type listing not tied to the role, but listed as separate columns for the individual files - based on given roles., rather than "Accessible_User_Roles" combining both of those. That might still depend on if Maintenance/Reporting/etc. permissions tend to be a small subset of combinations per file, or if there's a lot of different ways that roles can be combined. Mar 13, 2023 at 9:58
  • 2

Not the answer you're looking for? Browse other questions tagged or ask your own question.