Permissions done properly!

[KieronWiltshire]

So here is a concept of a permission system that I haven't really seen any where else. Now usually conventional permissions are usually stored in columns whilst the record specifies a bit which is then used to determine if the record has access to that permission.

Below is my concept of how permissions should be done, I'm looking for someone who can help me create the system in a way which would be easily implementable by other applications such as MyBB. In my case I have multiple game servers, and most of my players have accounts created on my forums which are powered by MyBB, I'm currently in the process of integrating their MyBB accounts across all my related game servers, but one thing I've noticed is that I have multiple permission systems created for all of my servers so right now I'm also trying to integrate all of them into one system so please try and understand that I designed this system in a way that could be used by multiple applications. 

 

Any constructive criticism is accepted.

 
 
So the idea is that you have 1 table, I'll just list it here to make it easier to follow:

- uniperms_nodes

 
Now the uniperms_nodes table will contain the following columns:

- key (Int, Not Null, Primary Key, Auto Increment)
- type (Enum('USER', 'GROUP'), Not Null)
- id (Int, Not Null)
- permission (Varchar(255), Not Null)
- description (Text)

Now here is how it works,
a record is inserted into the uniperms_nodes table containing the necessary information. Here is an example:

INSERT INTO `uniperms_nodes` (`type`, `id`, `permission`, `description`) 
VALUES ('GROUP', '1', 'my.test.node', 'A simple permission');

 
With this information inserted, I can simply use the following query in order to get all of the permissions related to the GROUP with the ID of 1.

SELECT `permission` FROM `uniperms_nodes`
WHERE type='GROUP' AND id=1;

With this array of permission nodes I can simply just check if the array has 'my.test.node', if the array contains 'my.test.node' then that means that the group I queried has access to that permission.

 

Now the reason I have the types USER and GROUP is because maybe you would like to give permissions to individual users, but maybe you wouldn't necessarily want to create a new group.

 

 

 

 

 

^{I'm currently looking for someone to help me create a lovely interface for this system, so that it's easier for the user to modify a group/user's permissions. If you're interested feel free to message me here on the forums or via email.}

^{kieron.wiltshire@outlook.com}

Edited November 19, 2014 by KieronWiltshire

[gizmola]

From a conceptual standpoint, you are showing us one table, when in fact there are several different related tables.  It seems your main interest is not having to determine whether the permissions are stored for a group or a user.  What you have solves that problem but you will still be differentiating those in code, so I'm not sure what the win is, as you've described your use case.

 

What you have done is similar to acl's systems you can find around. The problem with all these systems is that inevitably you have the following scalability conundrum.

 

 

Does this user have the ability to "EDIT" this object?

 

If you have thousands of users who each could have hundreds of objects, pretty soon you have a table with lots of these rows, and that eventually becomes a scalability limitation.

 

The alternative answer is that you have some code that does a series of checks.

 

 

-Does this user have a ROLE that gives them super powers?

-Is this user in a GROUP that has a ROLE that gives them super powers?

-Is this user the OWNER of this item

and possibly

-has this user been DELEGATED OWNERSHIP of the item, so that they have similar powers?

 

 

This can get as complex and nuanced as you want it to, depending on your application.  Utlimately, it comes down to running some code that looks at a number of different possibilities, each possibility needing to be resolved in a minimum amount of time.

 

This article talks about this conundrum and the confusion surrounding permission systems, acl and how people might solve the problem.  It discusses this in the context of the symfony framework, but presents the way that symfony has an "acl" solution not too far from the one you're tinkering with as it happens (you would have to reverse the actual tables the symfony acl system creates, but they take a somewhat similar approach to yours), and then goes on to explain why acls are typically not the solution to the problems people are trying to solve.  Instead, symfony offers the alternative of "voters" which is an engine allowing you to write simple rules for making these types of permission decisions.  Article is here:  http://knpuniversity.com/screencast/question-answer-day/symfony2-users-menu-cms

[Jacques1]

Before you revolutionize permission management, you should read up on database normalization.

• You repeat all data of a permission for every assigned entity. This means you may get conflicting information. Even worse, there's no definite set of permissions: You may accidentally end up with different spellings of the same permission, or the assigned permission may be plain garbage data which isn't implemented anywhere in the application.
• The ID can point to two entirely different entities, which means you cannot check if it actually makes sense at database level (unless you fumble with complicated workarounds).

If you fix this, you'll actually end up with three tables: one for the permissions, one for assigning permissions to users, and one for assigning permissions to groups. That's already way more complex than the “classical” approach.

 

Yes, your model makes it easy to add/delete a permission or select all permissions of a user. But if you want to check if a user has a specific permission, things get cumbersome: Instead of a plain SELECT some_permission FROM users WHERE user_id = ..., you now need something like SELECT COUNT(*) > 0 FROM user_permissions WHERE permission = 'some_permission' AND user_id = .... More complex checks may also involve joins between the various tables.

 

The question is what exactly you want to achieve. What's the concrete benefit of your model for your application?

[KieronWiltshire]

Before you revolutionize permission management, you should read up on database normalization.

• You repeat all data of a permission for every assigned entity. This means you may get conflicting information. Even worse, there's no definite set of permissions: You may accidentally end up with different spellings of the same permission, or the assigned permission may be plain garbage data which isn't implemented anywhere in the application.
• The ID can point to two entirely different entities, which means you cannot check if it actually makes sense at database level (unless you fumble with complicated workarounds). (BTW: This is what the 'type' column is for, to check the entity type.)

 

If you fix this, you'll actually end up with three tables: one for the permissions, one for assigning permissions to users, and one for assigning permissions to groups. That's already way more complex than the “classical” approach.

 

Yes, your model makes it easy to add/delete a permission or select all permissions of a user. But if you want to check if a user has a specific permission, things get cumbersome: Instead of a plain SELECT some_permission FROM users WHERE user_id = ..., you now need something like SELECT COUNT(*) > 0 FROM user_permissions WHERE permission = 'some_permission' AND user_id = .... More complex checks may also involve joins between the various tables.

 

what's wrong with just getting all of the permissions relating to the user, storing them in an array and check if the array contains said permission that way you're only using one query and the rest is done in code.

 

The question is what exactly you want to achieve. What's the concrete benefit of your model for your application?

 

My replies are in bold found within the quote, so please read them first.

 

I really appreciate the feedback, and I agree that maybe another table where the 'nodes' are only stored once and then have them relate to the uni_perms 'permission' column would be much better. Thanks!

Edited November 20, 2014 by KieronWiltshire