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

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.

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/1451/" >What is the best way to handle multiple permission types?< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.