10

I'm creating a menu system in PHP and MySQL. I will have several different menus and each menu will have a set of menu items connected to it.

On the site, I also have different user permissions, some users can see all menu items and some items are hidden from some users. I'm curious on how I could handle the permissions in a clean way that will allow for more types of users in the future to be easily added.

What I have thus far is something like this:

-------------------
|Menus
-------------------
|id| |display name|
-------------------

----------------------------------------------------------
|Menu items
----------------------------------------------------------
|id| |menu_id| |label| |link| |parent| |sort| |permission|
----------------------------------------------------------

I'm thinking that the permission column could either be a comma separated string that I can match against the current user's permission id. It could also be a reference to some other table that defines all possible combinations of the currently existing permissions.

One solution could also be to simply store multiple menu items where the only difference is the permission although this would lead to duplicate storage and perhaps a pain to administer.

I'd love to hear some thought on how to structure this and what could be considered clean, dynamic and crappy.

Thanks.

  • Do users have something in common? Typically you'd group menu items into functional groups and assign users to those groups (for example - Admin users, DB users, traders etc). Then you just manage the groupings, depending on technology choice - this can be managed by using something like Active Directory. – Michael Jul 29 '13 at 13:49
  • 1
    You're getting a lot of good answers here, but what you might want to read about is ACL. en.wikipedia.org/wiki/Access_control_list – Reactgular Jul 29 '13 at 14:02
15

I will model it using an ER diagram.

  • A PERMISSION is the access granted to a ROLE on a given MENU_ITEM.
  • A ROLE is a set of pre-defined permissions which is given a name
  • A USER can have many ROLEs granted to it.
  • Having permissions assigned to roles instead of users, makes the administration of permissions much easier.

enter image description here

Then you can create a view so you don't have to write the joins every time:

create or replace view v_user_permissions
select
    distinct mi.id, mi.menu_id. mi.label. mi.link, mi.parent, mi.sort, u.user_id
from
    user u
    join user_role ur on (u.user_id = ur.user_id)
    join role ro on (ur.role_id = role.role_id)
    join permission per on (ro.role_id = per.role_id)
    join menu_item mi on (per.metu_item_id = mi.metu_item_id)

Then each time you want to know what menu items a user have access to, you can query it:

select * from v_user_permissions up where up.user_id = 12736 order by up.sort

EDIT:

Since a user can have multiple roles granted, roles' permissions can overlap, i.e. two distinct roles can have access to the same menu item. When you define a role you don't know beforehand whether it will have some permissions in common with other roles. But since it's about the union of sets, it only matters whether or not a given permission is part of the set, not how many times it appears, hence the distinct clause in the view.

  • Awesome, thanks. Can't see why I just couldn't draw that up myself. Guess I was blocked and non experienced :) – span Jul 29 '13 at 14:07
  • Ack, I thought I understood but obviously didn't. How can I have multiple permissions for a single menu item? – span Jul 29 '13 at 17:24
  • 1
    @span Because a user can have multiple roles granted and roles' permissions can overlap, i.e. two distinct roles can have access to the same menu item. When you define a role you don't know beforehand if the role will be granted in along with other that have some permissions in common with it. But since this problem is about the union of sets, it only matters whether a given permission is part of the set or not, not how many times it appears. – Tulains Córdova Jul 29 '13 at 17:59
  • Thanks, I'll keep reading your answer until I get it ;). I think my mistake was in thinking that a single permission could be used together with the role to separate the menu items. It seems I need a permission for each 'type' of menu item. Once again, thanks for your help! I'll draw some Venn diagrams and see if I can get my head around it properly \o/ – span Jul 29 '13 at 19:56
3

That classical approach to this is User -> UserGroup and then associated a Menu -> MenuItem -> UserGroup. Using an integer value to weigh the permission level.

-------------------
|Menu
-------------------
|id| |display name|
-------------------

-------------------------------------------------------------
|Menu Item
-------------------------------------------------------------
|id| |menu_id| |label| |link| |parent| |sort| | min_option1
-------------------------------------------------------------

-------------------------------------------
| User
-------------------------------------------
|id| | username | password | user_group_id
-------------------------------------------

-------------------------------------------
| User Group
-------------------------------------------
|id| option1 | option2 | option2
-------------------------------------------

When you need to display a menu for the current user. You can query the database like this.

SELECT * FROM `MenuItem`
    LEFT JOIN `UserGroup` ON (`MenuItem`.`user_group_id` = `UserGroup`.`id`)
    LEFT JOIN `User` ON (`UserGroup`.`id` = `User`.`user_group_id` AND `User`.`id` = $current_user_id)
        WHERE `MenuItem`.`menu_id` = $menu_id AND `UserGroup`.`option1` >= `MenuItem`.`min_option1`;

That will only select menus that are visible to the current user based upon the condition for option1.

Alternatively, if you store the current user's group details in the current session, then no join is required.

SELECT * FROM `MenuItem` WHERE `MenuItem`.`menu_id` = $menu_id AND `MenuItem`.`min_option1` >= $user_group_option1;

When you talk about wanting to store multiple permissions per menu item. I'd be careful not to confuse user roles and business logic.

  • 2
    This design would only allow each MenuItem to be tied to a single UserGroup, meaning either limited menus or data duplication. In reality you want a link table, ideally. Also, your choice of DB table naming as plurals makes me sad ;) – Ed James Jul 29 '13 at 13:52
  • @EdWoodcock oh very good point. I should have gone with a permission level (int) and then compared that to the user's group level. I'll change that. Note, plural names habit caused by my use of CakePHP. Which is strange, cause the framework uses singular aliases for tables in queries. – Reactgular Jul 29 '13 at 13:56
  • @MatthewFoscarini No worries, I'm not really bothered as long as a codebase is consistent ;) – Ed James Jul 29 '13 at 14:01
  • 1
    Awesome answer. I will keep this in mind next time I do something likt this. For now I think user61852 solution will suit best since it doesn't require as many changes to the existing code. Thanks! – span Jul 29 '13 at 14:06
5

Having a comma separated list means doing a substring comparison each time you do a query against the menu. This is less than ideal.

You need to normalize the table:

---------------------------------------------
|Menu items
---------------------------------------------
|id| |menu_id| |label| |link| |parent| |sort|
---------------------------------------------

+---------------------------+
| menu_permission           |
|---------------------------|
| |menu_permission_id| (pk) |
| |menu_id| (fk)            |
| |permission|              |
+---------------------------+

If you still want the comma separated list (for some reason), you can pull it out with things such as group_concat in mysql wm_concat in oracle or similar functions in other languages.

The advantage for this is multi-fold.

First, there's the practicality of the call. Doing a substring against an arbitrarily large string (if you fix size it, you can have problems later on with filling up the string so you start getting permissions like a instead of another_permission) means scanning the string on each row. This isn't something that databases are optmized for.

Second, the query that you write becomes much simpler. To determine if the permission 'foo' exists in a comma separated list, you need to check for 'foo'.

... permission like "%foo%" ...

However, this will give a false positive if you also have the permission 'foobar'. So now you need to have a test like

... permission like "%,foo,%" ...

but that will give a false negative if 'foo' is at the start or end of the string. That leads to something like

... (permission like "foo,%" 
  or permission like "%,foo,%" 
  or permission like "%,foo" ) ...

You will note that it is quite likely that you will need to do multiple scans of the string. This way leads to madness.

Note with all of these you lack the practical ability to do parameter binding (still possible, just gets even more ugly).

Normalizing the field gives you much greater flexibility and legibility in your database. You won't regret it.

  • Thank you for your great answer, it has given me more knowledge and I'm greatful for that although I think user61852 solution will suit best for now. – span Jul 29 '13 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.