3

I wrote a large MySQL query to retrieve all permissions for a given user, and recently found that this does not work in newer versions of MySQL due to the ONLY_FULL_GROUP_BY mode.

The query to retrieve all granted permissions for a given user is:

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

This produces the following error on newer MySQL installations with the flag ONLY_FULL_GROUP_BY enabled (it is enabled by default): 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.

I tried to completely re-wrote the query to be more efficient and work with the flag enabled, however it does not quite achieve the same goal as the first query.

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

How should I do this? I have given it a couple tries and cannot really see a way to only return rows with the highest priority without disabling the ONLY_FULL_GROUP_BY mode.

Should I resort to just using the newer query and applying the role priority in application code rather than in the SQL query?

Here is a DBFiddle with database structure and query.

Given the data from the DBFiddle above, I want the result set to look like the following:

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

The reason I want permission ID 3 to return is_granted = 0 instead of not appearing in the result set is because the user is initially granted the permission, but then not granted it from role ID 2, which has a higher priority.

This question is not a duplicate of SQL select only rows with max value on a column because it specifically pertains to how I should modify my query to work around the ONLY_FULL_GROUP_BY mode.

Alexander Sagen
  • 146
  • 7
  • Why do you have the column `r.priority` in your `GROUP BY` clause, but also have `MAX(r.priority)` in your `SELECT` clause? – Progman Dec 17 '18 at 10:20
  • Please edit your question to include example data of your tables, what the content of the result set should be and the result set you are currently getting instead (see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query). – Progman Dec 17 '18 at 10:21
  • Thanks for the reply @Progman, I have updated the question with a DBFiddle and wanted result set. I believe the result set should be achievable using the current data structure, only modifying the query. – Alexander Sagen Dec 17 '18 at 13:50

1 Answers1

0

Solved by looking a bit closer at the duplicate question and understanding how exactly the answers on that question works.

I had to create a view to join together the role_permissions table with the priority column from the roles table. Then I had to get all the highest priority role permissions for each user. Lastly I had to get all the user permissions which are not overriding any role permissions for the user and join this together with the role permissions using a UNION.

Here is my solution:

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

Here is an updated DBFiddle which demonstrates that the solution is working.

Alexander Sagen
  • 146
  • 7