What is the best way to manage permissions for a web application - bitmask or database table?

Question

I'm considering the best way to design a permissions system for an "admin" web application. The application is likely to have many users, each of whom could be assigned a certain role; some of these users could be permitted to perform specific tasks outside the role.

I can think of two ways to design this: one, with a "permissions" table with a row for every user, and boolean columns, one for each task, that assign them permissions to perform those tasks. Like this:

User ID          Manage Users     Manage Products     Manage Promotions     Manage Orders
1                true             true                true                  true
2                false            true                true                  true
3                false            false               false                 true

Another way I thought of was to use a bit mask to store these user permissions. This would limit the number of tasks that could be managed to 31 for a 32-bit signed integer, but in practice we're unlikely to have more than 31 specific tasks that a user could perform. This way, the database schema would be simpler, and we wouldn't have to change the table structure every time we added a new task that would need access control. Like this:

User ID          Permissions (8-bit mask), would be ints in table
1                00001111
2                00000111
3                00000001

What mechanisms have people here typically used, and why?

Thanks!

Answer 1

I think it's a general rule of thumb to stay away from mystical bitstrings that encode the meaning of the universe.

While perhaps clunkier, having a table of possible permissions, a table of users, and a link table between them is the best and clearest way to organize this. It also makes your queries and maintenance (especially for the new guy) a lot easier.

Answer 2

how about creating a Permission table, then a UserPermission table to store the relationships?

You'll never have to modify the structure again, and you have the ability to add as many permissionss as you wish.

Answer 3

I've done it both ways. But I don't use bit masks much anymore. A separate table would be fine that you can use as a cross reference, given a user id or a group id as a foreign key.

UserID | Permission
===================
1      | 1              1 representing manage users
1      | 2              2 being manger products
2      | 3 

This way would be easier to maintain and add on to later on.

I'd also use a separate table to manage what the permissions are.

PermissionID | Description
==========================
1            | Manage Users
2            | Manager Products