User Permission System

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

My current, self-inflicted, project requires a user permission system to determines admin access, guest access and other permissions.

I've found this from a google search but was wondering if there is any other solution that would work, maybe even better. I'm thinking of adding the ability to create groups with their own permissions from the admin like phpBB has...


My current idea is to have each permission thing on a MySQL table... something like...
Code: [ Select ]
groupID | userID | canPostComment | canPostTopic | canReplyComment | canReplyTopic | canPostBlog | canCommentBlog
------------------------------------------------------------------------------------------------------------------
  1   |   0  |     1    |     1   |      1    |     1   |     1   |     1
  1. groupID | userID | canPostComment | canPostTopic | canReplyComment | canReplyTopic | canPostBlog | canCommentBlog
  2. ------------------------------------------------------------------------------------------------------------------
  3.   1   |   0  |     1    |     1   |      1    |     1   |     1   |     1

so on and so forth... but I'm not sure if that's the best way that I can do... I mean the link from the google I posted here works just the same, it's just at the end it would be like 20 characters long :lol: ... and the MySQL example would look like 10111111 and then some for other things...

Any ideas?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Split each permission into its own row.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Something like:

Code: [ Select ]
permissionID.|.permissionName.|.groupID.|.userID.|
--------------------------------------------------
.....1.......|.canPostComment.|....1....|....0...|
.....2.......|.canPostTopic...|....1....|....0...|
.....3.......|canReplyComment.|....1....|....0...|
.....4.......|.canReplyTopic..|...8,2...|.1,3,5,8|
.....6.......|.canCommentBlog.|..1,3,4..|....0...|
  1. permissionID.|.permissionName.|.groupID.|.userID.|
  2. --------------------------------------------------
  3. .....1.......|.canPostComment.|....1....|....0...|
  4. .....2.......|.canPostTopic...|....1....|....0...|
  5. .....3.......|canReplyComment.|....1....|....0...|
  6. .....4.......|.canReplyTopic..|...8,2...|.1,3,5,8|
  7. .....6.......|.canCommentBlog.|..1,3,4..|....0...|

?

I think that will work quite a bit better... I don't know why I haven't thought of that...

Am I missing anything there? Do I need another column or something?
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Only put a single value in any given field.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

So if I want 2 different groups to have the same permission, I need to have that permission listed twice in there?
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Yep
Code: [ Select ]
table: permissionNames
--- ID
--- name

table: permissions
--- ID
--- permID
--- isGroup
--- reference

-OR-

table: permissions
--- ID
--- permID
--- groupID
--- userID
  1. table: permissionNames
  2. --- ID
  3. --- name
  4. table: permissions
  5. --- ID
  6. --- permID
  7. --- isGroup
  8. --- reference
  9. -OR-
  10. table: permissions
  11. --- ID
  12. --- permID
  13. --- groupID
  14. --- userID


For the first table isGroup is a boolean value, it tells you if reference is pointing to a group or a user. if isGroup is true, reference is a groupID, if its false then its a userID.

With the permissionName table you can list out all your permissions once, then link to them in the permissions table.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

The later table might be better because it would allow you to search the table with both the userID and the groupID, telling it to return all permissions where either is set to some value, then return all unique permID's that way you don't get duplicates.

Just a added note, for the later table at least one field out of groupID and userID should be set to 0. Your not defining if a user is in a group here. Thats one reasons why I did the first permissions table the way I did.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Here is an example on how to use the first table:

SQL Code: [ Select ]
SELECT DISTINCT permissionName.name
FROM permission
JOIN permissionName ON permissionName.ID = permission.permID
WHERE (
  permission.reference = %userID%
  AND permission.isGroup = 0
)
OR (
  permission.reference = %groupID%
  AND permission.isGroup = 1
)
GROUP BY permission.permID
  1. SELECT DISTINCT permissionName.name
  2. FROM permission
  3. JOIN permissionName ON permissionName.ID = permission.permID
  4. WHERE (
  5.   permission.reference = %userID%
  6.   AND permission.isGroup = 0
  7. )
  8. OR (
  9.   permission.reference = %groupID%
  10.   AND permission.isGroup = 1
  11. )
  12. GROUP BY permission.permID


This will return a unique list of all permissions by their name that a particular user has, along with a list of permissions that a particular group has. This allows you to select all permissions for the group that the user is in along with his/her individual permissions. If a user is in more than one group you can modify it to do a OR on the group part.

You could even throw another join in there so you only have to supply an ID, then the query will look up the userID and find out what group he/she is in and select permissions.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Thank you... I think that would work beautifully... the first table that is :D

I'm going to spend some time now setting the permissions up with my system and making it configurable
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

I finally created the group system and the permission system :D Thank you SpooF

Post Information

  • Total Posts in this topic: 10 posts
  • Users browsing this forum: No registered users and 60 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.