Multiple permission types (roles) stored in database as single decimal

Question

I was going to ask a question here about whether or not my design for some users/roles database tables was acceptable, but after some research I came across this question:

What is the best way to handle multiple permission types?

It sounds like an innovative approach, so instead of a many-to-many relationship users_to_roles table, I have multiple permissions defined as a single decimal (int data type I presume). That means all permissions for a single user are in one row. It probably won't make sense until you read the other question and answer

I can't get my brain around this one. Can someone please explain the conversion process? It sounds "right", but I'm just not getting how I convert the roles to a decimal before it goes in the db, and how it gets converted back when it comes out of the db. I'm using Java, but if you stubbed it out, that would be cool as well.

Here is the original answer in the off chance the other question gets deleted:

"Personally, I sometimes use a flagged enumeration of permissions. This way you can use AND, OR, NOT and XOR bitwise operations on the enumeration's items.

[Flags]
public enum Permission
{
    VIEWUSERS = 1, // 2^0 // 0000 0001
    EDITUSERS = 2, // 2^1 // 0000 0010
    VIEWPRODUCTS = 4, // 2^2 // 0000 0100
    EDITPRODUCTS = 8, // 2^3 // 0000 1000
    VIEWCLIENTS = 16, // 2^4 // 0001 0000
    EDITCLIENTS = 32, // 2^5 // 0010 0000
    DELETECLIENTS = 64, // 2^6 // 0100 0000
}

Then, you can combine several permissions using the AND bitwise operator.

For example, if a user can view & edit users, the binary result of the operation is 0000 0011 which converted to decimal is 3. You can then store the permission of one user into a single column of your DataBase (in our case it would be 3).

Inside your application, you just need another bitwise operation (OR) to verify if a user has a particular permission or not."

Answer 1

Actually, this is how we determine authority within a fairly large web application that I'm the DBA for.

If you are going to do something like this, you'll really benefit from having a numbers table. It will make your calculations much faster.

The basic setup includes the following tables:

1. Groups - for doing many to many of users and security points
2. Security points - which contain a value for anonymous authorization and one for authenticated users who are not part of a separate group
3. Group security point join table
4. A special BitMask numbers table that contains entries for the ^2 values. Thus there is one entry for 2 (2) and two entries for three (2 and 1). This keeps us from having to calculate values each time.

First we determine if the user is logged in. If they aren't we return the anonymous authorization for the security point.

Next we determine if the user is a member of any groups associated with the security point through a simple EXISTS using a JOIN. If they aren't we return the value associated with authenticated user. Most of the anonymous and authenticated defaults are set to 1 on our system because we require you to belong to specific groups.

Note: If an anonymous user gets a no access, the interface throws them over to a log in box to allow them to log in and try again.

If the user is a member of one or more groups, then we select distinct values from the BitMask table for each of the values defined for the groups. For example, if you belonged to three groups and had one authorization level of 8, one with 12 and the last with 36, our select against the Bit Mask table would return 8, 8 and 4, and 4 and 32 respectively. By doing a distinct we get the number 4, 8 and 32 which correctly bit masks to 101100.

That value is returned as the users authorization level and processed by the web site.

Make sense?

Answer 2

You use bitwise operations. The pseudo-code would be something like:

bool HasPermission(User user, Permission permission) {
    return (user.Permission & permission) != 0;
}

void SetPermission(User user, Permission permission) {
    user.Permission |= permission;
}

void ClearPermission(User user, Permission permission) {
    user.Permission &= ~permission;
}

Permission is the enum type defined in your post, though whatever type it is needs to be based on an integer-like type. The same applies to the User.Permission field.

If those operators (&, |=, and &=) don't make sense to you, then read up on bitwise operations (bitwise AND and bitwise OR).