Permissions Table Structure

[baudchaser]

I am designing a company intranet, have two working prototypes for the permissions structure, and am wondering which one is more efficient (or more accurately, if one or the other is just going to be too slow).

Method 1:
There is a basic users table with userid as the primary key.  Webpages are each assigned a groupid, which is essentially the permissions group to which it belongs.  For instance, each department will have its own groupid, and every subpage in that department typically has the same groupid.

I also have a "perms" table with userid as the primary key, and read, write, delete, etc. as fields.  Each of these fields consists of a comma separated string containing the groupids to which that user has permission.

For example:
IT has a groupid of 1, and HR has a groupid of 2.  If I want userid 1 to have read access to both, the "read" field in the perms table has the string "1,2".

When the user logs in, I explode the comma separated string for each permission type (read, write, modify, delete, etc.) into an array, and store as a session variable.  When that user tries to access a webpage, I use the "in_array" function to determine if that page's groupid exists in the session variable.

Method 2:
I have the same users table, and the same groupids.  But the permissions table has one record for each user for each groupid they have access to.

For example:
Userid needs read access to both IT (groupid = 1) and HR (groupid = 2), but write access only to IT.  The permissions table will look like this:

userid    group    read    write
-----------------------------
1          1          1        1
1          2          1        0


Method 1 seems to emphasize using the code to do most of the work, where Method 2 seems to emphasize using the database to do most of the work.  I guess my dilemma is that I do not know whether the code in method 1 is outperforming the bigger query in method 2.  I imagine as the number of users/groupids increases, the table size will increase exponentially in method 2.  Is method 2 preferrable at smaller table sizes and method 1 preferrable at larger sizes?  When will CPU utilization become the bottleneck?

I guess it is possible that both methods are equally viable given a reasonable number of records (2000 users, 50 groups) and also possible that I am approaching this at the completely wrong angle.  Thoughts?

[effigy]

Have you considered using bits to handle the permissions? See [url=http://www.phpfreaks.com/forums/index.php/topic,113143.0.html]this[/url] topic.

[baudchaser]

[quote author=effigy link=topic=123697.msg511626#msg511626 date=1169580733]
Have you considered using bits to handle the permissions? See [url=http://www.phpfreaks.com/forums/index.php/topic,113143.0.html]this[/url] topic.
[/quote]

Ahh...I was not aware that PHP had built-in capability for binary/hex like this.  Very enlightening read, thanks!

But I think I may still be struggling a bit with the implementation.  This would allow me to cut the number of fields down from 1 per permission type (I had 7 fields - read, upload, create, modify, delete..etc) to having just one field containing the binary/hex string.

But how would I link the perms to certain pages, but not others?  For example, I might want a user to be able to upload, but only to certain pages.  I imagine the only way I could do this would be to have a table where each user had a record for each groupid they have access to

Just making up the hex for permissions here, but:

userid  groupid  hex
1        1          0x00000001
1        2          0x00000003
1        3          0x00000006
1        4          0x00000001
1        5          0x00000001
2        2          0x00000001
2        7          0x00000004
....

Is this right, or am I missing something?  This goes back to my initial concern - the permissions table growing to a rather unwieldy size quickly as the number of users and groups increases.  Let me give a real world example.  We might have 50 unique "groups" that need their own specific permissions.  We hire an executive who wants read access to all, and additional access to some.  Would that necessitate 50 records for that one new exec???

[effigy]

I would only associate users to groups and groups to permissions, not users to groups [i]and[/i] permissions.

[Daniel0]

Also read my tutorial on this: http://www.phpfreaks.com/tutorials/151/0.php
You can probably skip page one and two.

[steelmanronald06]

I think your approaching the permissions the wrong way. Have you considered basing your permissions off of an ACL structure?  I have written a great tutorial for it: http://lampgeekz.netgeekz.net/tutorials/index.php?cmd=tutorial&id=13&page=0

[448191]

Using ACL is definately flexible...

If you're looking for an example ACL implementation in PHP: http://phpgacl.sourceforge.net/demo/phpgacl/docs/manual.html

You could try and get the best of both worlds: imagine a basic ARO tree, use bits to store user permissions... I.e.:

[quote=phpGACL Manual]Millennium Falcon Passengers
├─Crew [ALLOW: ALL]
│ ├─Han
│ └─Chewie [DENY: Engines]
└─Passengers [ALLOW: Lounge]
├─Obi-wan
├─Luke [ALLOW: Guns]
├─R2D2 [ALLOW: Engines]
└─C3PO[/quote]
[code]
<?php
/* ... client code ... */
$acl->set('Chewie','Crew',array('DENY'=>'Engines'));

//Generated query:
$db->query('INSERT INTO user (user_name, perm)
VALUES
("Chewie",
(SELECT bits FROM usergroup WHERE usergroup_name = "Crew" ^ SELECT bits FROM aco WHERE aco_name = "Engines")
)');

//Chewie request access to Engines:
$acl->request('Chewie','Engines');

//Compare Chewies permissions to aco 'Engines' (untested, but should work I guess):
$res = $db->query('SELECT IF ((SELECT perm FROM user WHERE user_name = "Chewie") &
(SELECT bits FROM aco WHERE aco_name = "Engines"), 1, 0)');

if($db->fetchFirst($res)){
echo 'Welcome to the engine room Chewie!';
}
?>[/code]

Just a thought, I don't have any experience with implementing ACL whatsoever.. :P Things could get a lot more complicated if you use nested groups or AXO's...

For all I know phpGACL translates a fictional ARO tree to bitwise comparisons too, I don't know..

[448191]

I made a boo boo... ::)

[b]^[/b] should be [b]& ~[/b]..

Sorry... (damn regex.. :P)

For completeness, 'ALLOW' would translate to [b]|[/b].

[448191]

Also, adding a 'parent_id' to the group table (referring to a group in the same table) might just solve the nested groups issue.

When adding a nested group, one could easily inherit the bits of the parent, and add or remove any bits specific for the nested group. Nothing would change when adding a new ARO (user).

I haven't worked out using AXO's yet...

[b]Edit:[/b] when implementing this it is probably a good idea to create a script that can render a visual represenation of the ARO tree.

[448191]

I must say I am intrigued by using bitwise ACL..

It definately has potential.

Consider the following client code to write permissions:

[code]<?php
$acl->newAroGroup($name, $parent, array(
'ALLOW'=>array('some aco not allowed by this group\'s parent','another aco previously not allowed'),
'DENY'=>array('something that is allowed by the parent group, but not in this one')));
$acl->newAro($name, $aroGroup, $customPermissionArrayIdenticalInFormToAboveExample);
$acl->newAxo($name, $axoGroup, $customizeAllowedAros);
?>[/code]

The beauty of this is that while you have highly customized permissions, checking them can be made astonishly easy:

[code]<?php
$acl->request($aro, $aco, $axo);
?>[/code]

[attachment deleted by admin]

[448191]

I am trying to figure out a way to allow/dissallow certain ACOs on AXOs, not just AROs like in the above implementation...

Any help would be greatly appreciated.

[448191]

Only way I think it is possible: create a new ACL (ARO tree) for a child AXO (group) if it needs to be different from the parent's ACL.

E.g. define the difference, create new ACL, assign new ACL id to AXO (group).

[code]<?php
$acl->newAxo($name, $axoGroup, array(
'ALLOW'=>array(
'ARO'=>'Fred',
'ACO'=>'Read'))
);
?>[/code]

ERM:

[attachment deleted by admin]

[448191]

Ouch. Mulitple ACL's require multiple ARO lists.. Say I have 2000 users and 100 AXOs (articles - sections - whatever), that would result in 200000 rows in the ARO table.  What a pain...

Alternatively I could change table 'acl' to hold serialized arrays with only the difference between the specific ACLs...

I'm struggling, help me out here!

[448191]

Can't figure it out.

Funny thing is that it is essentially the same question as what started this topic (if you read well).

[448191]

OK. Consider this:

Create a table to map AROs to ACLs, but let ACL inherit from eachother everything that hasn't been redefined by the AXO node. AXOs bind to ACLs. The ACL table does nothing more then define the hierarchy between lists. The ARO map doesn't repeat ARO data that was defined by the parent ACL.

I think I got it now. Phewey... Is this what giving birth is like? :P

[attachment deleted by admin]

[448191]

I must say I'm pretty pleased with this.  :)

Thanks for pointing out ACL Ronald! I'm using this for future projects.

[redbullmarky]

dude i wish i could help out but looks like you're having fun with this :) i'll stay by the sidelines though for now and just learn learn learn. ACL is a term that came up a long time ago for me that i've just not gotten around to looking into properly, so cheers for the tutorial!

[448191]

[quote author=redbullmarky link=topic=123697.msg513544#msg513544 date=1169761815]
dude i wish i could help out but looks like you're having fun with this :)
[/quote]

Yep, it was fun.  I know, I should get a life, LOL... :P

[quote author=redbullmarky link=topic=123697.msg513544#msg513544 date=1169761815]
i'll stay by the sidelines though for now and just learn learn learn. ACL is a term that came up a long time ago for me that i've just not gotten around to looking into properly, so cheers for the tutorial!
[/quote]

I pretty much figured out how to implement a 3-dim ACL structure, if you'd like I could elaborate?

[redbullmarky]

elaborate? by all means, dude - go for it. it's not completely off the OP's topic so why not.

[baudchaser]

I'm still here and reading - so much going through my mind right now (and that I've been testing) that I haven't been able to take the time to reply to each of your suggestions - don't think I don't appreciate the suggestions.  :)

Bitwise has allowed me to cut down from 10 fields (read, add, modify, delete, etc.) into one field for perms, which is cool.  ACL I'm still researching and trying to figure out.

I think 448191 and I are thinking along the same lines - even with the concepts mentioned here, I'm ending up with at least one large table.  If I only needed to assign permissions once per user accross the entire site - this would be easy!  But I need to be able to manage the permissions for each "group" of pages, which means an additional record in the table for each group!

I have some number of permissions (read, add, modify, delete, grant, etc being cumulatively stored as a single hex number), which I need to be able to assign to a page (actually a group of pages since I'm grouping up similar pages using parentid's).  I also need to specify which user (or group of users) needs access to that group of pages.

Let's say I want to grant a user read access to both IT and HR groups.  It would require 2 entries in the linking table, one for each department.  The more groups I want to give a user access to, the more entries there are for that one user.  This is my fundamental problem - or maybe it's not a problem and just the way it is...

groupid = all pages in a certain group, 1=IT, 2=HR, etc..

userid  groupid  perms
1        1          0x00000001
1        2          0x00000001

[448191]

Ok, for anyone reading this in the future; the sum-up:

AROs: Things requesting access
ACOs: Actions that are requested
AXOs: Things to control access on
ARO (ACL) Tree: List mapping AROs to ACOs
AXO Tree: List mapping AXOs to both ACOs and AROs (3-dim structure)

Example AXO tree (or 'map' rather):

[code]System [DENY ALL, Administrators => ALLOW ALL ]
|
|___ Admin Section [448191 DENY Delete]
|
|___ Public Section [Users ALLOW View]
      |___ Articles [Moderators ALLOW Edit]
            |____ Article132 [Harry ALLOW Edit, Delete][/code]

This is all purely left to the power of imagination though. This structure doesn't really exist, except for in your mind.

The problem I encountered when trying to implement the fictional AXO tree is that it is impossible to directly map sets of ACOs and AROs to an AXO without an extra table that maps the relation between these three 'dimensions'.

Initially I tried to solve that by creating seperate ARO trees, and bind them to an AXO node...

ARO (ACL list) tree:

[code]Root [DENY ALL]
|___ Administrators [ALLOW ALL]
|    |___ 448191 [DENY Delete]
|___ Users [ALLOW View][/code]

I attempted to solve the problem of repeating data by having the ACL lists inherit ARO / ACO bitvalue pairs from parent lists.

[img]http://home.orange.nl/lekkage/img/acl6.png[/img]

While that would work, further tought reveals there is no need for a seperate ACL table, no need to translate to an ARO tree first.

[img]http://home.orange.nl/lekkage/img/acl7.png[/img]

The concept of inheritance natural to ACL can be found in the aro and axo tables: each entry after the root entry has a parent_id. This is comparable to inheritance in OOP terms, the child element is accessed, the top element found and loaded, and on the way back down any properties redefined get overridden. The map table allows for per-axo setting, yet only ARO / ACO bitvalue pairs which differ from the AXOs parent need to be redefined.

Definition of AXOs as groups is not nessecary: any element with children is a group. Same goes for AROs.

       

[baudchaser]

[quote author=448191 link=topic=123697.msg513247#msg513247 date=1169741445]
Ouch. Mulitple ACL's require multiple ARO lists.. Say I have 2000 users and 100 AXOs (articles - sections - whatever), that would result in 200000 rows in the ARO table.  What a pain...
[/quote]

Yes!  This is what I was describing, only not exacly in ACL/ARO terms.  If I have 2000 users and 100 "groups" (marketing group, IT group, a group for a "contracts" page - basically defined as any "section" of the Intranet that needs to have its permissions handled separately) then I would end up with 20000 records if the average person ended up needing at least read access to at least 10 of those groups.

[quote author=448191 link=topic=123697.msg513247#msg513247 date=1169741445]
Alternatively I could change table 'acl' to hold serialized arrays with only the difference between the specific ACLs...
[/quote]

Not sure what you mean by serialized arrays, but in my first post I was trying to resolve the monster table "problem" by using a comma separated string to store values that would otherwise end up as separate records.  To extract the data, I'd be parsing the comma strings into an array and checking perms for each group against that array - in effect, I was transferring database load to CPU/code load.

[448191]

Forget about all that, I was still struggling to find a solution when I posted that.

Read my previous post, things will make sense.  :)

[448191]

P.S. I wouldn't recommend serializing arrays and inserting them into a table unless you're incredibly desperate as in 'ready to jump off a bridge'... ;)

I was just thinking aloud.

[Jenk]

Have you tried using a redundant override?

Maintain groups, but only use individuals when the need arises, otherwise they remain null.

Table for users (user details, foreign key groupid), table for permission groups (id, groupid, group details) table for permissions (permission details [including value], permission id, foreign key group id) table for indiviual overrides (foreign key userid, foreign key permissionid, override value)