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

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.