4

I have built a CMS system with a fairly typical user/group/permission system where users can be members of groups, and permissions can be applied to either the user directly, or to groups which users can be members of.

Permissions can also be 'wildcard' (e.g. apply to all objects) or apply to specific objects designated by a module name and a row id. Permissions can with be 'Allow' which grants access, or 'Deny' which specifically prevents access and overrides any 'Allow' permissions they have been granted elsewhere. Deny is stored in the userpermission/grouppermission table by creating an row with the 'allow' column set to 0.

The following query is currently used (and works) to list all users which have been granted a specific 'wildcard' permission (permissionid 123).

    SELECT
        `user`.*
    FROM
        (
            SELECT
                `user`.*,
                `userpermission`.`allow` AS `user_allow`,
                `userpermission`.`permissionid` AS `user_permissionid`,
                `grouppermission`.`allow` AS `group_allow`,
                `grouppermission`.`permissionid` AS `group_permissionid`

            FROM
                `user`

                LEFT JOIN `userpermission` ON
                    `user`.`userid` = `userpermission`.`userid`
                    AND `userpermission`.`module` = '*'
                    AND `userpermission`.`rowid` = '*'
                    AND `userpermission`.`permissionid` = 18

                LEFT JOIN `usergroup` ON 
                  `user`.`userid` = `usergroup`.`userid`

                LEFT JOIN `grouppermission` ON
                    `usergroup`.`groupid` = `grouppermission`.`groupid`
                    AND `grouppermission`.`module` = '*'
                    AND `grouppermission`.`rowid` = '*'
                    AND `grouppermission`.`permissionid` = 18

                WHERE
                    (
                        `grouppermission`.`allow` = 1
                        OR
                        `userpermission`.`allow` = 1
                    )

        ) AS `user` 

        LEFT JOIN `userpermission` ON
            `user`.`userid` = `userpermission`.`userid`
            AND `userpermission`.`permissionid` = `user`.`user_permissionid`
            AND `userpermission`.`allow` = 0
            AND `userpermission`.`module` = '*'
            AND `userpermission`.`rowid` = '*'

        LEFT JOIN `usergroup` ON 
          `user`.`userid` = `usergroup`.`userid`

        LEFT JOIN `grouppermission` ON
            `usergroup`.`groupid` = `grouppermission`.`groupid`
            AND `grouppermission`.`permissionid` = `user`.`group_permissionid`
            AND `grouppermission`.`allow` = 0
            AND `grouppermission`.`module` = '*'
            AND `grouppermission`.`rowid` = '*'

      GROUP BY `user`.`userid`

      HAVING
        COUNT(`userpermission`.`userpermissionid`) + COUNT(`grouppermission`.`grouppermissionid`) = 0

However it is very slow (~0.5 seconds, with ~3000 users, ~250 groups, ~10000 usergroup joins, ~30 permissions, ~150 grouppermissions and ~30 userpermissions).

permissionid as per the example above is just one permision. It may also be necessary to check multiple permissions e.g. IN(18,19,20) instead of = 18

Explain provides the following output - I think I've got the right columns indexed however I'm not sure about how (or if its possible) to index the derived table:

+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+
| id | select_type | table           | type | possible_keys              | key          | key_len | ref                            | rows | Extra                           |
+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+
|  1 | PRIMARY     | [derived2]      | ALL  | NULL                       | NULL         | NULL    | NULL                           |   62 | Using temporary; Using filesort |
|  1 | PRIMARY     | userpermission  | ref  | USERID,PERMISSIONID,ALLOW  | USERID       | 4       | user.userid                    |    2 |                                 |
|  1 | PRIMARY     | usergroup       | ref  | USERID                     | USERID       | 4       | user.userid                    |    4 |                                 |
|  1 | PRIMARY     | grouppermission | ref  | GROUPID,PERMISSIONID,ALLOW | PERMISSIONID | 4       | user.group_permissionid        |    3 |                                 |
|  2 | DERIVED     | user            | ALL  | NULL                       | NULL         | NULL    | NULL                           | 2985 |                                 |
|  2 | DERIVED     | userpermission  | ref  | USERID,PERMISSIONID        | PERMISSIONID | 4       |                                |    1 |                                 |
|  2 | DERIVED     | usergroup       | ref  | USERID                     | USERID       | 4       | [database].user.userid         |    4 |                                 |
|  2 | DERIVED     | grouppermission | ref  | GROUPID,PERMISSIONID       | PERMISSIONID | 4       |                                |    3 | Using where                     |
+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+

Is it possible to re-write the query without the sub-query so that it can be optimised, or optimise it as-is?

If the data structure needs changing that isn't a huge issue.

Improve this question
4
  • Sorry, I mis-copied the query - it does also have a 'Having' clause which I believe requires the Group By? Commented Nov 3, 2014 at 17:42
  • 1
    Ah, well that changes EVERYTHING!! Commented Nov 3, 2014 at 17:42
  • Can you set up a sqlfiddle or anything? That is an insane looking query. Just getting rid of the subquery will likely fix performance, since the subquery results will be unindexed. Commented Nov 6, 2014 at 16:26
  • Yes, getting rid of the sub-query would probably be the best solution but I can't wrap my head around how I'd do that. I should be able to setup an SQL fiddle tomorrow. Thanks for the feedback. Commented Nov 6, 2014 at 17:37

2 Answers 2

Reset to default
1
+100

You're left joining and afterwards you count the non-null rows and check if there exist none. I hope you realize from my wording, that this is more complicated than it needs to be. You can simply rewrite the whole query like this:

SELECT
    `user`.*
FROM
    `user`
    LEFT JOIN `userpermission` ON
        `user`.`userid` = `userpermission`.`userid`
        AND `userpermission`.`module` = '*'
        AND `userpermission`.`rowid` = '*'
        AND `userpermission`.`permissionid` = 18
    LEFT JOIN `usergroup` ON `user`.`userid` = `usergroup`.`userid`
    LEFT JOIN `grouppermission` ON
        `usergroup`.`groupid` = `grouppermission`.`groupid`
        AND `grouppermission`.`module` = '*'
        AND `grouppermission`.`rowid` = '*'
        AND `grouppermission`.`permissionid` = 18
    WHERE
        (`grouppermission`.`allow` = 1 OR `userpermission`.`allow` = 1)
        AND NOT EXISTS (SELECT 1 FROM `userpermission` WHERE `user`.`userid` = `userpermission`.`userid`
                        AND `userpermission`.`allow` = 0
                        AND `userpermission`.`module` = '*'
                        AND `userpermission`.`rowid` = '*'
                        AND `userpermission`.`permissionid` = 18
                       )
        AND NOT EXISTS (SELECT 1 FROM `grouppermission` WHERE `usergroup`.`groupid` = `grouppermission`.`groupid`
                        AND `grouppermission`.`allow` = 0
                        AND `grouppermission`.`module` = '*'
                        AND `grouppermission`.`rowid` = '*'
                        AND `grouppermission`.`permissionid` = 18
                       );

What's also great about EXISTS(), is, that it stops as soon as an entry was found. You don't have to get all rows and check afterwards if there was none.

Another way to write the query would be this:

SELECT
    `user`.*
FROM
    `user`
    LEFT JOIN `userpermission` ON
        `user`.`userid` = `userpermission`.`userid`
        AND `userpermission`.`module` = '*'
        AND `userpermission`.`rowid` = '*'
        AND `userpermission`.`permissionid` = 18
    LEFT JOIN `usergroup` ON `user`.`userid` = `usergroup`.`userid`
    LEFT JOIN `grouppermission` ON
        `usergroup`.`groupid` = `grouppermission`.`groupid`
        AND `grouppermission`.`module` = '*'
        AND `grouppermission`.`rowid` = '*'
        AND `grouppermission`.`permissionid` = 18
    LEFT JOIN `userpermission` up ON `user`.`userid` = `up`.`userid`
                                AND up.`allow` = 0
                                AND up.`module` = '*'
                                AND up.`rowid` = '*'
                                AND up.`permissionid` = 18
    LEFT JOIN grouppermission gp ON `usergroup`.`groupid` = `gp`.`groupid`
                                AND gp.`allow` = 0
                                AND gp.`module` = '*'
                                AND gp.`rowid` = '*'
                                AND gp.`permissionid` = 18
    WHERE
        (`grouppermission`.`allow` = 1 OR `userpermission`.`allow` = 1)
        AND up.userid IS NULL
        AND gp.groupid IS NULL;

Have a try which one works better for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi, Thanks for taking the time to answer this. I've tried your SQL and it returns zero results. I believe this is because a user can be granted a permission from either a grouppermission or userpermission. Using INNER joins means that they would have to have the same permission from in userpermission and grouppermission...
Okay, that was just an assumption. Rewrote my answer again to use left joins. Please check again, if it yields correct result and has better performance.
Ok - I've updated your query so it produces the expected results (there is a comma after user.* which causes problems, and I added the 'permissionid' = 18 check to the NOT EXISTS checks... however it takes around 0.6 seconds to execute, which is about the same as my original query. I'm going to see if I can add some indexes to optimise it...
@ChrisWheeler So, how's it going? Feel free to share explain plan and table structures, if I shall have a look on it.
@ChrisWheeler I edited my answer to include another possibility to write the query. Have a try if it works better for you.
|
0

Another way to do this without any sub-queries is:

SELECT
    `user`.*,
     SUM(CASE WHEN `userpermission`.`allow` = 1 THEN 1 ELSE 0 END) as user_allow,
     SUM(CASE WHEN `grouppermission`.`allow` = 1 THEN 1 ELSE 0 END) as group_allow,
     SUM(CASE WHEN `userpermission`.`allow` = 0 THEN 1 ELSE 0 END) as user_deny,
     SUM(CASE WHEN `grouppermission`.`allow` = 0 THEN 1 ELSE 0 END) as group_deny
FROM
    `user`
    LEFT JOIN `userpermission` ON
        `user`.`userid` = `userpermission`.`userid`
        AND `userpermission`.`module` = '*'
        AND `userpermission`.`rowid` = '*'
        AND `userpermission`.`permissionid` = 18
    LEFT JOIN `usergroup` ON `user`.`userid` = `usergroup`.`userid`
    LEFT JOIN `grouppermission` ON
        `usergroup`.`groupid` = `grouppermission`.`groupid`
        AND `grouppermission`.`module` = '*'
        AND `grouppermission`.`rowid` = '*'
        AND `grouppermission`.`permissionid` = 18
    GROUP BY 
        `user`.`userid`
    HAVING
        (
            `user_allow` > 0
            OR
            `group_allow` > 0
        ) 
        AND 
            `user_deny` = 0
        AND
            `group_deny` = 0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.