karim karim - 29 days ago 8
MySQL Question

User permissions from user own permission and group permission

i'm developing a user permission system, here is my eer

enter image description here

each user can have permission if:


  • he is a member of a group and that group have permission

  • he have record in
    user_permission
    table



so to get all permission that a user have, i must get a union of group permission that the user belong to, and the permission the user have in
user_permission
table

that the sql statement that I found as solution, but I don't know if it is the good one, (can I have the result using only one SELECT statement)

select statement:

SELECT p.name FROM permission p
JOIN group_permission as gp ON gp.permission_id = p.id
JOIN `group` as g ON g.id = gp.group_id
JOIN `user_group` as ug ON ug.group_id = g.id
where ug.user_id = 5

UNION

SELECT p.name FROM permission p
JOIN `user_permission` as up ON up.permission_id = p.id
where up.user_id = 5


here is the mysql dump of my database:

https://pastebin.com/2Kaq8fVs

Answer Source

The 3rd line is not necessary. You can join gp and ug via group_id. Keep your group table for securing foreign keys, but you don't need it for this kind of query. Everything else is OK for your requirement as you described it.

Note: Reading all permissions without regularly reloading them causes changed permissions not to be recognized by your application. If this is a problem, you could additionally constrain your query with a specific permission.