sql - values - why use multiple tables in a database




What is the best way to handle multiple permission types? (4)

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.

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[email protected]SectionID
  AND (emp.UserID[email protected]UserID OR dept.DeptID[email protected]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?


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.


Well, if I wanted to implement permissions in my application, I can do this 3 ways (I can think of on the top of my head):

  1. On the users table (and record if you are using a orm) put a level int. So for example a level 1 user can only view forms, level 2 can post, level 3 is admin and can edit, etc. Then in the views you just check to see what level the user is and echo the form is they can edit etc. (Based on your question I assume this is what you are already doing)

    Pros:

    • Easy to set up
    • Lets you group permissions together.

    Cons:

    • If you need a level between 1 and 2 you have to do a lot of work. So, it's harder to add levels in between afterward.
  2. Join a permissions table with the users table. Basically what the other answerer described in this thread.

    Pros:

    • You aren't confined to levels, you can add as many levels in between as you would like.
    • You can fine tune what you want each user to be able to do.

    Cons:

    • You can't group permissions, but I suppose you could just assign them all at once if you wanted.
    • It's more work to tell a user what s/he is because you can only list all the permissions or make a massive switch to determine if they are an admin or not. (Ex: am I a regular user? well I can edit forms, but not search?)
  3. Combine these, so basically on the user table you could have a string that had values like "admin", and on each page just look if they are an admin or not.

    Pros:

    • You can add as many times as you want without throwing anything out of wak. Types like "super admin" or "somewhat less admin"

    Cons:

    • If you are fine-tuning every user, you would end up with a lot of new user types fast.
    • Adding new user type strings may be difficult, unless user type string table was joined tabled to permissions, but now we are getting pretty complicated.

How to structure multiple users with multiple permission levels?

Using a second table for options is a good idea. Some forum frameworks use this method.
Each of your users are given a UserGroupId which is usually an Int since they are easiest to work with. UserGroupId of 1 for instance, could be an admin, 2 could be a teacher (depends on your organization).

Then you have a table called Permissions, on this table you include all options as Columns, something like this.

UserGroupID --|-- SearchEnabled --|-- CanOrder
             1                   0           1
             2                   1           1

Using a simple binary system, 1 enabled, 0 disabled, you can control options for each user group. This allows you to get all permissions with a single query, while still offering a very large area for customization.

You don't have to use binary numbers though. For instance you could use values 1,2,3 where 1 is full permission, 2 is partial, and 3 is zero. It depends on how specific your regulations need to be.

Now before you allow a user to perform an option you do a simple check on the users permissions (which you should store in an array or a class for quick access). For a function that enables search you would use a condition such as

If ($user['SearchEnabled')
{
   $generate->SearchOptions();
}
else 
{
   $generate->Error('NoSearchPermissions');
}

Using binary numbers has the obvious benefit of simply check if TRUE or FALSE. If you use a different numbering system it would require a bit more work, checking the specific value
If ($user['SearchEnabled'] == 2 || $user['SearchEnabled'] == 1)





permissions