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

Question

I am considering the best way to develop a permission system for the admin web application. There will probably be many users in the application, each of which may be assigned a specific role; some of these users may be allowed to perform certain tasks outside the role.

I can think of two ways to create this: one, with a “permissions” table with a row for each user and boolean columns, one for each task that assigns them permissions to perform these 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 was to use a bitmask to store these user permissions. This would limit the number of tasks that could be controlled to 31 for the 32-bit signed integer, but in practice we are unlikely to have more than 31 specific tasks that the user could perform. Thus, the database schema will be simpler, and we won’t have to change the table structure every time we add a new task that will require access control. Like this:

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

What mechanisms do people usually use, and why?

Thank!

Answer 1

I think this is a general rule to stay away from the mystical bistrons that encode the meaning of the universe.

Although perhaps more clunkier, having a table of possible permissions, a user table and a table of links between them is the best and clearest way to organize this. It also simplifies your requests and maintenance (especially for a new guy).

Answer 2

How to create a Permission table and then a UserPermission table to store relationships?

You no longer have to change the structure, and you have the opportunity to add as many permissions as you wish.

Answer 3

I have done it both ways. But I no longer use bit masks. A separate table will be useful, as you can use it as a cross-reference, given the user ID or group ID as a foreign key.

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

Thus, it would be easier to maintain and add further.

I would also use a separate table for managing permissions.

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

Answer 4

Usually I have a Users table, a Roles table, and a UserRoles table. Thus, you can have an unlimited number of roles without changing the structure of your db, and users can be in several roles.

I force the application to allow only roles (never to users). Note that the id column in the role table is not an identity column. This is because you may need to control the identifiers that will be placed in this table, because your application will look for specific identifiers.

The structure is as follows:

 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 ) 

Answer 5

I suggest abstracting the permissions of your web application with the concept of a role provider. Starting with version 2.0, this is provided for you in .NET as System.Web.Security.RoleProvider .

The main idea is that you use the existing infrastructure by writing your checks for compliance with the structure, and not with the specific storage mechanism. You can then attach any storage engine, be it an XML file, a database, or even an authorization store using the Windows Authorization Manager software (which allows you to easily bind your user permissions to LDAP, as one example - no code that needs to be configured) .

If you decide to use the database as a storage engine, several databases are supported to automatically create the underlying tables that the infrastructure needs. This includes running .NET on Mono and using the role provider model on top of MySQL.

For more information, see Implementing a Role Provider . It is possible that in other languages ​​/ environments there are also libraries that you could use to implement this concept - it would be interesting to study.

EDIT . I also have to specify how your web application is configured in the storage engine through the web.config file and does not require code changes. I found this very useful for testing the production version of the codebase on my local machine, using an XML file to simulate permissions instead of the usual database provider - all by changing two lines in web.config.

Another thing that I forgot to mention is that you can connect your own custom providers by expanding the base classes, allowing you to use the permission model, but use a patented storage system (for example, bitmasks if you really wanted to).

Answer 6

You can use Active Directory or another implementation of LDAP if you are in a managed environment. In this way, the security groups that define permissions can be controlled by first-line support using technology that they are most likely familiar with.

If your application is compressed, then +1 for Levi Rosol’s proposal to normalize the database so you can have an extensible data model in your application.

Answer 7

I saw several few limited permission systems similar to what you offer, as well as some really terrible systems. In some simple situations, they may be acceptable if the application does not become more complex. However, in many cases, they become more complex, and systems must be rewritten to provide the required functionality.

If you think that someday expressiveness is needed, I would go with a full access control list (ACL) with users and groups (or roles). That is, every thing controlled by permissions (for example, "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 corresponding ACLs or added to a group that is already a member of the ACL.

Although the ACL offers a list implementation, you will be better off with a table; this answer is a good way.

Answer 8

Permissions are usually keywords with 1, 0, or null (indicating inheritance). With a bit system, you probably cannot create indexes on the user ID and permission keyword; instead, you will need to scan each entry to get the resolution value.

I would say for the first option. It seems to me the best 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` ) 

Answer 9

I can’t comment, because I'm new to SO, but with respect to the accepted answer - the huge advantage that comes with this solution is the ability to handle permissions universally, and not just operators everywhere in the code, as well as special features, for example, permission of temporary permissions (permits expiring)