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

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!

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.

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.

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

Usually I have a Users table, a Roles table, and a UserRoles table. This way you can have an unlimited amount of roles without changing your db structure and users can be in multiple roles.

I force the application to only authorize against roles (never users). Notice how the "id" column in the roles table is not an identity column. This is because you may need to control the IDs which get put in this table because your application is going to have to look for specific IDs.

The structure looks like this:

create table Users (
 id int identity not null,
 loginId varchar(30) not null,
 firstName varchar(50) not null,
 etc...
)

create table Roles (
 id int not null,
 name varchar(50) not null
)

create table UserRoles (
 userId int not null,
 roleId int not null
)

You could use Active Directory or another LDAP implementation if you're in a managed environment. That way the security groups, which determine permissions can be managed by first line support, using a technology they're most likely already familiar with.

If your app is shrink wrapped then +1 for Levi Rosol's suggestion of normalising the database so that you can have an extensible data model in your app.

I'd suggest abstracting your web application permissions with the concept of a Role Provider. As of version 2.0, this is provided for you in .NET as System.Web.Security.RoleProvider.

The basic idea is that you leverage an existing framework by writing your permission checks against the framework rather than a specific storage mechanism. You can then plug-in whatever storage mechanism is available, whether it's an XML file, a database, or even an authorization store using the Windows software Authorization Manager (which lets you seamlessly tie in your custom permissions to LDAP, as one example - no code required to configure).

If you decide to use a database as a storage mechanism, several databases are supported for the automatic creation of the underlying tables that the framework needs. This includes running .NET on Mono and using the role provider model on top of MySQL.

See Implementing a Role Provider for more information. It is entirely possible that other languages/environments also have libraries you could leverage to implement this concept - it would be worth looking into.

EDIT: I should also point out the configuration of how your web application ties in to the storage mechanism is done through a web.config file, and doesn't require code changes. I have found this very useful to test a production version of a codebase on my local machine, using an XML file to mimic permissions instead of the normal database provider - all by modifying two lines in web.config.

The other thing I forgot to mention is that you can plug-in your own custom providers by extending the base classes, allowing you to leverage the permission model but still use a proprietary storage system (eg. bit masks, if you really wanted to).

I've seen a number of somewhat limited permissions systems similar to what you're suggesting -- as well as some truly terrible systems. In some simple situations they can be acceptable, as long as the application doesn't get more complex. However, in so many cases, they do get more complicated, and the systems have to be rewritten to accommodate the required functionality.

If you think you might someday need the expressiveness, I'd go with a full ACL (access control list) system with users and groups (or roles). That is, each thing governed by permissions (e.g. "manage users", "manage products") has an ACL, which is a list of all users and groups that have access to it. Then users are either added directly to the relevant ACLs, or added to a group that's already a member of an ACL.

Although ACL suggests a list implementation, you'd be better off with a table; this answer is a good way.

Can't comment because I am new to SO, but in regards to the accepted answer - a huge advantage that comes with this solution is the ability to universally handle permissions, instead of just if statements everywhere in the code, as well as special abilities such as allowing temporary permissions (permissions that have an expiration date)

Permissions are usually key words with a 1, 0 or null (indicating inherit). With an bit system, you probably cannot create indexes on the user id and permission keyword; instead, you would have to scan every record to get the permission value.

I would say go for the first option. It seems to me the better solution:

create table permissions (
    user_id INT NOT Null,
    permission VARCHAR(255) NOT NULL,
    value TINYINT(1) NULL
)
alter table `permissions` ADD PRIMARY KEY ( `user_id` , `permission` )