What is the best way to handle multiple permission types?

Question

I often encounter the following scenario where I need to offer many different types of permissions. I primarily use ASP.NET / VB.NET with SQL Server 2000.

Scenario

I want to offer a dynamic permission system that can work on different parameters. Let's say that I want to give either a department or just a specific person access to an application. And pretend that we have a number of applications that keeps growing.

In the past, I have chosen one of the following two ways that I know to do this.

1. Use a single permission table with special columns that are used for determining a how to apply the parameters. The special columns in this example are TypeID and TypeAuxID. The SQL would look something like this.

   SELECT COUNT(PermissionID) FROM application_permissions WHERE (TypeID = 1 AND TypeAuxID = @UserID) OR (TypeID = 2 AND TypeAuxID = @DepartmentID) AND ApplicationID = 1

2. Use a mapping table for each type of permission, then joining them all together.

   SELECT COUNT(perm.PermissionID) FROM application_permissions perm LEFT JOIN application_UserPermissions emp ON perm.ApplicationID = emp.ApplicationID LEFT JOIN application_DepartmentPermissions dept ON perm.ApplicationID = dept.ApplicationID WHERE q.SectionID=@SectionID AND (emp.UserID=@UserID OR dept.DeptID=@DeptID OR (emp.UserID IS NULL AND dept.DeptID IS NULL)) AND ApplicationID = 1 ORDER BY q.QID ASC

My Thoughts

I hope that the examples make sense. I cobbled them together.

The first example requires less work, but neither of them feel like the best answer. Is there a better way to handle this?

Answer 1

The way I typically go about coding permission systems is having 6 tables.

• Users - this is pretty straight forward it is your typical users table
• Groups - this would be synonymous to your departments
• Roles - this is a table with all permissions generally also including a human readable name and a description
• Users_have_Groups - this is a many-to-many table of what groups a user belongs to
• Users_have_Roles - another many-to-many table of what roles are assigned to an individual user
• Groups_have_Roles - the final many-to-many table of what roles each group has

At the beginning of a users session you would run some logic that pulls out every role they have assigned, either directory or through a group. Then you code against those roles as your security permissions.

Like I said this is what I typically do but your millage may vary.

Answer 2

An approach I've used in various applications is to have a generic PermissionToken class which has a changeable Value property. Then you query the requested application, it tells you which PermissionTokens are needed in order to use it.

For example, the Shipping application might tell you it needs:

new PermissionToken()
{
    Target = PermissionTokenTarget.Application,
    Action = PermissionTokenAction.View,
    Value = "ShippingApp"
};

This can obviously be extended to Create, Edit, Delete etc and, because of the custom Value property, any application, module or widget can define its own required permissions. YMMV, but this has always been an efficient method for me which I have found to scale well.

Answer 3

I agree with John Downey.

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 4

In addition to John Downey and jdecuyper's solutions, I've also added an "Explicit Deny" bit at the end/beginning of the bitfield, so that you can perform additive permissions by group, role membership, and then subtract permissions based upon explicit deny entries, much like NTFS works, permission-wise.

Answer 5

Honestly the ASP.NET Membership / Roles features would work perfectly for the scenario you described. Writing your own tables / procs / classes is a great exercise and you can get very nice control over minute details, but after doing this myself I've concluded it's better to just use the built in .NET stuff. A lot of existing code is designed to work around it which is nice at well. Writing from scratch took me about 2 weeks and it was no where near as robust as .NETs. You have to code so much crap (password recovery, auto lockout, encryption, roles, a permission interface, tons of procs, etc) and the time could be better spent elsewhere.

Sorry if I didn't answer your question, I'm like the guy who says to learn c# when someone asks a vb question.