Saving modular user permissions in a database.

Question

I have a PHP MySQL application which has a modular design. There are about 5 modules total now, and I don't really foresee there ever being more than 10.

I currently have 2 types of users - "User and Admin". These user types have the same perms except the Admins can edit users, whereas regular users can't.

I want to implement modular permssions, such that if you are an Admin you have permissions to make changes in ALL modules. If you are a regular User, there will be a way to set which modules that user has access to (ie a set of checkmarks).

My question is, what is the best way to store this in the database? I already have a Users table. I've come up with the following 3 solutions right off the top of my head, I'm wondering if there is a recommended way to do it or if there are other ways. I also need to keep in mind that when upgrading the application to the new version, I will have to set up perms for all users currently in the DB. I also have to be able to easily add a default perm for each user when a new module is integrated. I'm looking for something easy to maintain and implement, but flexible for adding a few more modules.

My brainstormed options so far:

1. Store perms in the Users table in a column with a bit-mask type of string: 10001 indicates they have permissions for "Module A" and "Module E" only.
2. Store perms in the Users table in a column with a delimited (comma or pipe) list of module names which the user has perms for: "ModuleA,ModuleE".
3. Create a separate table for user perms like so:

UserId  | Module  | Permission
1       | A       | 1
1       | B       | 0

What do you think?

Answer 1

Store perms in the Users table in a column with a bit-mask type of string: 10001 indicates they have permissions for "Module A" and "Module E" only.

You need to do a bitmask test to identify if a user has permissions in a particular module, and only allows two options (permitted/not permitted) so reducing your options to expand permissions subsequently.

Store perms in the Users table in a column with a delimited (comma or pipe) list of module names which the user has perms for: "ModuleA,ModuleE".

Working with separated lists in a VARCHAR always adds overhead to identify the permissions you want (there's countless questions about the topic here on SO)

Create a separate table for user perms like so:

the simplest approach. Easy SQL queries to extract the information, and scope to expand the range of permissions in the future

Answer 2

I do something similar to this for a desktop application that I work on. I think it's fine to store the user permissions in a table that would look something like this.

UserId | ModuleA | ModuleB| ... |ModuleN
1      |       1 |       0| ... |      0

I think this provides a little more clarity on what you are doing, granted if you are adding tons of of modules all the time you will need to update you table a lot, however doing the bit masking approach may not be a bad idea either.

Answer 3

It sounds like you are only tracking access as opposed to granular permissions which is fine.

Store perms in the Users table in a column with a bit-mask type of string: 10001 indicates they have permissions for "Module A" and "Module E" only.

I would highly recommend against this. Bit masks in databases are a maintenance nightmare. It is not even remotely obvious to another developer what the meaning of each of the bits represent.

Store perms in the Users table in a column with a delimited (comma or pipe) list of module names which the user has perms for: "ModuleA,ModuleE".

This is a variant on your first solution only using a delimiter. Again, I would highly recommend against it as it is a maintenance headache.

Create a separate table for user perms like so:

This is the solution I would recommend. The big reason is that it is more extensible than the others and is easier to maintain. If you wanted to make a maintenance screen to control permissions, it would be trivial using this approach. You can easily add modules, change the order of the modules and later expand on what is maintained in the table (e.g. you might later add "Read Only" as a column as different than the ability to simply access the module). In addition, this approach makes it easier to pull information from the table. "Who has access to module X?" is an easy query to write with this, it is harder using a delimited list or bit mask.

Answer 4

My company's application uses a slightly different method along the lines of the bit-wise solution--a user access value. It's a simple int field in the user table that tells that app what permissions the user gets access to (there is another table that holds simple definitions for the UI, but for all practical purposes the app only sees a number) In the example we used, a super admin is permission 9999, with a bottom-level member as a level 1000. Access levels are added at 100 number increments, so that if down the road another needs to be added there is no need to cascade update. In the user class, we simply ask if a user's access level is greater than a defined level for access to each individual module. so, for instance, if the lowest level user who can edit a document is a 3000 and someone is a super admin, the system will check to see if their permission is greater than 3000 (it is at 9999) and then will return true to allow permissions. Simple.

Sure, there are more modular systems out there, but when we carefully analyzed our product and our future plans, there was no need to add one-to-many type tables to handle our solution. While we do have quite a few different access levels, they're very well-defined and the business model is less than likely to add many new options. Really, it's important to ask yourself how far you really need to go....if it doesn't have to be super powerful with dozens or hundreds of different levels of permission, why add the overhead of extra tables, query, code, etc to your app? It sounds like what you're doing now is very straightforward.