Access Control in ASP.NET MVC using [Flags] based Enum to int permissions management in SQL

Question

This question is inspired by this SO question regarding Access Control in ASP.NET MVC. Here I'm trying to bring the accepted answer into a tangible solution.

The answer mentions using FileSystemSecurity as an inspiration to managing permissions. Here I'm also using an enum with Flags attribute to define the ACL for all my objects. Additionally each member of my objects will be stored in a column within SQL. Assume a simplified Linq2SQL, EF, or nHibernate ORM mapping.

Edit: Added the following benefit / rationale for this approach

This security model was inspired by the FileSystemRights, the .NET approach to managing file level permissions.

One of the main reasons I like this approach is so I can easily create a summary of all the permissions by OR'ing all the individual ACLs together. I also like that I can add a DENY ACL to remove an inherited permission.

List<myObject> PermissionsList  = GetACLForObject(ItemID, UserID);
foreach (var acl in PermissionsList)
{
   // The following enum has the [FlagsAttribute] applied so the .ToString() is pretty 
   PermissionWithFlagsEnum sampleForSO = (PermissionWithFlagsEnum )acl.Permission;

   Console.Writeline ("Setting " + sampleForSO.ToString() + " permission for group: " + acl.ACLName);

   ResultingPermission = resultPermission |  acl.Permission ; 
}
public int ResultingPermission {get;set;}

/End Edit

Then it occurred to me that I could compare less privileged users by numeric value of the enum from more privileged users's enum.

I'm thinking that this definition would allow for quick and easy identification of users within the SQL database, without having to parse the enum on the backend. ( Find unprivileged users via select users from permissions where security < DBAceessRights.DetailRead )

Here is how I defined the flags (lowest value has the least permission)

[Flags]
public enum DBAccessRights
{
    DenyAll  =1 << 1,

    WikiMode = 1 << 4,

    AppearInListing = 1 << 8,

    DetailRead = 1 << 12,

    CreateNew = 1 << 18,

    DetailEdit = 1 << 22,

    DeleteChild = 1 << 26,

    DeleteThis = 1 << 30,

    EditPermissions = 1 << 31,

}

I have a permission table that joins a user ID to an object specific ACE. This should reduce the need for concurrent updates on a particular row.

Question

• Is this a good idea?

• Has someone done it before (better than this)? (Edit: It is the accepted answer here )

• If this is a standard way of implementing permissions, what is it called?

Answer 1

I'd say no.

1. You're assuming you'll need no more than 32 (or 64 if you use bigint) privileges. Sounds like an arbitrary limit to me. Moving to a varbinary in the database can overcome this, but then your enum is up the creek (can't create enums on byte[])! And you won't be able to do numeric comparisons.

2. You're assuming that privilege 0x1 will always be logically less than 0x2 and 0x80 and so on. That's rarely the case in my experience. More usually, privileges are quite independent of each other (ie: having the "add user" privilege has nothing to do with the "upload image" privilege; one group of users (admins) will have the former while others (content publishers) have the latter. This means your numeric comparison isn't as helpful as you first thought.

3. What you're doing may yield a performance benefit, but you haven't demonstrated there's a performance problem yet! Most of my privilege systems work with one database record per user to grant or deny each privilege. Fetching 100 records doesn't tax my database. You'd do better simply caching each user's permissions between requests than using a bitmask.

4. Regarding update performance: how often do user permissions change anyway? Once a system is in place they tend to be pretty static in my experience.

I've found bitmasks most useful when trying to pack alot of data into a small space. But often my point 1 comes back to bite me when I end up with more than 64 things.

Note that I have used this technique when recording statistics of user actions (tracking what items users find in searches, what entities they view, etc). My reason was purely to make sure database record lengths were fixed and small so inserts were fast. And I wasn't doing numeric comparisons. (And, to be fair, I never tested to see if there was any difference between an int column and several bit columns).

EDIT A basic alternative (which I'm using): an M:N relationship between Users and Privileges (I call them Rights).

(Sorry about those Micky Mouse ears on my user!)

The presence of a record in UserRight indicates the right is granted to that user. The absence indicates no right. This query gives you all the rights assigned to a user.

SELECT [dbo].[User].Username, [dbo].[Right].Id, [dbo].[Right].Name
FROM [dbo].[Right]
INNER JOIN [dbo].[UserRight] ON [dbo].[Right].Id = [dbo].[UserRight].RightId
INNER JOIN [dbo].[User] ON [dbo].[User].Id = [dbo].[UserRight].UserId
WHERE [dbo].[User].Id = @pUserId

Then, in code to assert a user has a right:

var grantedRights = RunTheAboveQuery(currentUser.Id);
if (grantedRights.Any(r => r.Id == requiredRight))
    // User has the right.
else
    // User does not have the right. 

Obviously, you can scale this to check a user has several rights in one query.

This doesn't artificially limit how many Privileges your system supports. And it doesn't assume any relationships between Privileges (so you can't do your numeric comparisons; everything is done by IEnumerable<Right> in my system). And, if you're really keen on bitmasks, you could create a Dictionary<User, BitArray> in a cache!

I also have the concept of a Role which provides a logical group of rights for users. It's just another M:N table. That's an exercise for the reader!

Answer 2

In general putting multiple values (flags) into a single field is a bad idea.

If you're planning on auditing this data its a really bad idea since it because its hard to efficiently tease out which flags changed from update to update.

Problems you may encounter even if you don't do auditing

• Many simple queries (what users the DeleteThis authroization) aren't SARGable because you'll need to perform a bitwise operation before a comparison.

• Also select users from permissions where security < DBAceessRights.DetailRead may not return the right results because AppearInListing & CreateNew is greater than DetailRead but doesn't have the DetailRead turned on. So the benefit you hoped for you may not get

• Managing concurrency (multiple writers to ACL) is more difficult since mutating one "logical value" is actually mutating all the values.