Storing menu items with user permissions

Question

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.

Answer 1

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.

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.

Answer 2

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.

Answer 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.