User permissions from user own permission and group permission

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

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

---

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.

链接地址: http://www.djcxy.com/p/73402.html

上一篇: 基于Django组的用户访问控制

下一篇: 用户自己的权限和组权限的用户权限