mysql - 获取给定用户的权限
问题描述
我编写了一个大型 MySQL 查询来检索给定用户的所有权限,最近发现由于 ONLY_FULL_GROUP_BY 模式,这在较新版本的 MySQL 中不起作用。
检索给定用户的所有授予权限的查询是:
SELECT
`name`,
`display_name`,
`description`
FROM(
SELECT * FROM (
SELECT
`p`.`id`,
`p`.`name`,
`p`.`display_name`,
`p`.`description`,
MAX(`r`.`priority`),
IFNULL(`up`.`is_granted`, `rp`.`is_granted`) AS `is_granted`
FROM `permissions` AS `p`
LEFT JOIN `user_permissions` AS `up`
ON `up`.`permission` = `p`.`id`
AND `up`.`user` = 1
LEFT JOIN `role_permissions` `rp`
ON `rp`.`permission` = `p`.`id`
AND `rp`.`role` IN(
SELECT
`ur`.`role`
FROM `user_roles` AS `ur`
WHERE `ur`.`user` = 1
)
LEFT JOIN `roles` AS `r`
ON `r`.`id` = `rp`.`role`
GROUP BY `r`.`priority` DESC, `rp`.`permission`, `up`.`permission`
) AS `res`
GROUP BY `res`.`id` ASC
) AS `res`
WHERE `res`.`is_granted` = 1
这会在启用标志 ONLY_FULL_GROUP_BY 的较新 MySQL 安装上产生以下错误(默认情况下启用)SQL Error (1055): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'res.MAX(r.priority)' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
:.
我试图完全重新编写查询以提高效率并启用标志,但是它并没有完全实现与第一个查询相同的目标。
SELECT
u.id AS user_id,
u.email AS user_email,
up.permission AS user_permission_id,
up.is_granted AS user_permission_is_granted,
upp.name AS user_permission_name,
upp.display_name AS user_permission_display_name,
upp.description AS user_permission_description,
ur.role AS role_id,
urr.name AS role_name,
urr.display_name AS role_display_name,
urr.priority AS role_priority,
urp.permission AS role_permission_id,
urp.is_granted AS role_permission_is_granted,
urpp.name AS role_permission_name,
urpp.display_name AS role_permission_display_name,
urpp.description AS role_permission_description
FROM users u
LEFT JOIN user_permissions up
ON up.user = u.id
LEFT JOIN permissions upp
ON upp.id = up.permission
LEFT JOIN user_roles ur
ON ur.user = u.id
LEFT JOIN roles urr
ON urr.id = ur.role
LEFT JOIN role_permissions urp
ON urp.role = ur.role
LEFT JOIN permissions urpp
ON urpp.id = urp.permission
WHERE u.id = 1
我该怎么做?我已经给了它几次尝试,并不能真正看到一种只返回具有最高优先级的行而不禁用 ONLY_FULL_GROUP_BY 模式的方法。
我是否应该只使用较新的查询并在应用程序代码中而不是在 SQL 查询中应用角色优先级?
鉴于上述 DBFiddle 中的数据,我希望结果集如下所示:
| user_id | user_email | permission_id | permission_name | permission_display_name | permission_description | is_granted |
| ------- | ----------------- | ------------- | --------------- | ----------------------- | -------------------------------------------------- | ---------- |
| 1 | user1@example.com | 1 | test1 | Test permission 1 | Role permission, should ONLY be granted to user 1 | 1 |
| 1 | user1@example.com | 2 | test2 | Test permission 2 | Role permission, should ONLY be granted to user 1 | 1 |
| 1 | user1@example.com | 3 | test3 | Test permission 3 | Role permission, should ONLY be granted to user 2 | 0 |
| 2 | user2@example.com | 3 | test3 | Test permission 3 | Role permission, should ONLY be granted to user 2 | 1 |
| 1 | user1@example.com | 4 | test4 | Test permission 4 | Role permission, should be granted to user 1 and 2 | 1 |
| 2 | user2@example.com | 4 | test4 | Test permission 4 | Role permission, should be granted to user 1 and 2 | 1 |
| 1 | user1@example.com | 5 | test5 | Test permission 5 | User permission, granted to user 1 | 1 |
| 2 | user2@example.com | 6 | test6 | Test permission 6 | User permission, granted to user 2 | 1 |
我希望权限 ID 3 返回is_granted = 0
而不是出现在结果集中的原因是因为用户最初被授予权限,但后来没有从具有更高优先级的角色 ID 2 授予它。
这个问题不是SQL select only rows with max value on a column的重复,因为它特别涉及我应该如何修改我的查询以解决该ONLY_FULL_GROUP_BY
模式。
解决方案
通过仔细查看重复的问题并了解该问题的答案究竟是如何工作来解决的。
我必须创建一个视图来将role_permissions
表与表中的priority
列连接在一起roles
。然后我必须为每个用户获取所有最高优先级的角色权限。最后,我必须获取所有不会覆盖用户的任何角色权限的用户权限,并使用UNION
.
这是我的解决方案:
SELECT
u.id AS user_id,
u.email AS user_email,
p.id AS permission_id,
p.name AS permission_name,
p.display_name AS permission_display_name,
p.description AS permission_description,
IFNULL(up.is_granted, IFNULL(rp2.is_granted, rp1.is_granted)) AS is_granted
FROM users u
LEFT JOIN role_permissions_withpriority rp1
ON rp1.role IN(SELECT role FROM user_roles WHERE user = u.id)
LEFT OUTER JOIN role_permissions_withpriority rp2
ON rp2.role IN(SELECT role FROM user_roles WHERE user = u.id) AND rp2.permission = rp1.permission AND rp2.priority > rp1.priority
LEFT OUTER JOIN user_permissions up
ON up.user = u.id AND up.permission = rp1.permission
LEFT JOIN permissions p
ON p.id = rp1.permission
GROUP BY user_id, permission_id, is_granted
UNION
SELECT
u.id AS user_id,
u.email AS user_email,
p.id AS permission_id,
p.name AS permission_name,
p.display_name AS permission_display_name,
p.description AS permission_description,
up.is_granted AS is_granted
FROM users u
LEFT JOIN user_permissions up
ON up.user = u.id
LEFT JOIN permissions p
ON p.id = up.permission
WHERE p.id IS NOT NULL
GROUP BY user_id, permission_id, is_granted
推荐阅读
- jquery - 更改树视图控件的呈现 html 以创建标签而不是跨度
- php - Facebook Marketing api“读取”功能弃用问题
- html - 在 div 内时,离子头和离子页脚的离子 4 高度错误
- python - 为什么我写的程序会导致 JupyterLab 中出现“IOPub data rate exceeded”问题
- python - 如何在不下载视频内容的情况下从 Kwik 网站获取 POST 标头?
- python - 当我使用 concurrent.futures 时,无法在函数中使用一对项目
- c# - 如何模拟类变量
- apache-nifi - Nifi多部分形式
- ibm-watson - 在我的 Watson 知识目录和项目中找不到目录 ID/产品 ID
- html - 如何自动调整行大小以适应 CSS 中的容器?