Implementing permissions based on reputation

php mysql sql permissions

11,882

Solution 1

user_table
id, etc

permission table
id, user_id, permission_type

with this structure, each user could have several permission types associated with their account, one for each set of features they could have access to. you would never need to change the table structure in order to add new types of permissions.

to take this a step further, you could make each type of permission a binary number. this way you could make a set of permissions be represented by one integer by using bitwise operators.

for instance if you had the constants

PERMISSION_CHANGE_PERMISSIONS = bindec('001') = 1
PERMISSION_MAKE_CHANGES = bindec('010') = 2
PERMISSION_ACCEPT_CHANGES = bindec('100') = 4

you could combine these values into one integer using a bitwise operator "|"

(PERMISSION_CHANGE_PERMISSIONS | PERMISSION_MAKE_CHANGES) = bindec('011') = 3 = $users_combined_permissions

then to check if they have a specific permission, use the bitwise operator "&"

($users_combined_permissions & PERMISSION_MAKE_CHANGES) = true

if you did that, you would only need one db record for each set of permissions.

Solution 2

I have used Zend_Acl in the past for this. I can recommend it. A tried and tested library that is quite easy to implement and can be used stand-alone. This option will scale well if you have different permission schemes to add afterwards.

11,882

Ry-

If you don’t use code formatting for emphasis, we can probably be friends.

Updated on January 17, 2020

Comments

Ry- over 4 years
I'm creating a website in which there are projects, users, and permissions for each user or groups of users. What this is is a community collaboration tool, and I have 4 different permissions:
- Creator - make changes, accept changes, change permissions
- Accept changes
- Make changes
- View
How could I implement, in a database, this kind of permission system, for groups of users?

Edit: Groups/permissions are defined by reputation, like on StackOverflow.

Edit 2 - more in detail: Each file needs to have a permission, projects need default permissions for newly created files, and I also need to set up MySQL database permissions.
- wilbbe01 almost 13 years
  
  Do you have to use php? I know Django has great permission support out of the box. I'm guessing any given MVC (or similar) framework out there probably gives you similar permissions ease as well. Django is also really easy to get started on and learn.
- Flipper almost 13 years
  
  @wilbee01 If he is doing website development then Django would not work since that is Python and he needs either PHP or ASP.net for web development.
- Ry- almost 13 years
  
  No, I can't, unfortunately :(
- Denis de Bernardy almost 13 years
  
  You might find this thread interesting: stackoverflow.com/questions/5875646/database-schema-for-acl/‌…
Ry- almost 13 years

The problem I have, though, is groups of users (moderators, trusted, etc. like on Stack Overflow).
Flipper almost 13 years

@minitech What do you mean? You would make a rank called Moderators and then set everybody's rank id to that specific rank. Next just do an SQL query to get all of the users in that group.
dqhendricks almost 13 years

@Flipper forward thinking, you would have to add a new DB column everytime a new type of permission is needed.
Flipper almost 13 years

@dqhendricks Yes you would, but there really is not another way that would be as efficient.
dqhendricks almost 13 years

@Flipper that's a pretty bold statement for any peice of code.
Flipper almost 13 years

That is an interesting way of doing it, but i think that method would only be suitable for a system that would need to be complex and have a lot of different permissions.
dqhendricks almost 13 years

@Flipper I guess. It never hurts to think about the future however. Making your code agile can save a lot of time and headache at a later date.
Ry- almost 13 years

This is going to be a pretty complex system eventually, so it might come in handy - thanks!
Ry- almost 13 years

Well, the problem is that the rankings are reputation-based. Ideally, I'd like a structure similar to a hash or dictionary, but with ranges, then with special exceptions like Administrators and the creator of the project, particular user ids, etc.
Flipper almost 13 years

@minitech I am sorry, but I am just not understanding your question. Maybe somebody else could explain what you mean better because it just does not seem to relate to your original question completely.
Ry- almost 13 years

I've edited my question, sorry - I didn't completely explain that last part. I basically want what SO has.
Ry- almost 13 years

I finally ended up going with this. Thanks everybody.
Jaylen over 9 years

@dqhendricks, I like this idea for my system; but I have a question, what if I have multiple section system. For example, I want to grant user "XYZ" to have only PERMISSION_CHANGE_PERMISSIONS to the "news" section, and I also want to grant "XYZ" PERMISSION_MAKE_CHANGES, PERMISSION_CHANGE_PERMISSIONS, and PERMISSION_ACCEPT_CHANGES to the blog section? How can I differentiate the section? my first thought will be add a new column in the permission table with the section name but I am wondering if there is a better approach to this neat idea?
dqhendricks over 9 years

@Mike Well you could just use a naming convention and keep it all in the same column, like NEWS_PERMISSION_CHANGE_PERMISSIONS and BLOG_PERMISSION_CHANGE_PERMISSIONS. Two columns is equally viable.