Mysql query to find a user with all of the given permissions

Question

Hi All,

I want to find the users(userid) from a permissions table who have all of the given permissions. Something like :

select userid, permission from permissions where all_of permissions in ('view', 'delete', 'add', 'edit');

Note: this query is not to do with mysql permissions. It is a generic question, assuming that I have a user_permissions table which has the following fields & data:


userid | permission
1 | view
1 | add
2 | view
2 | delete
2 | add
2 | edit
The query I'm asking should return

userid
2

Please let me know if this is not clear.

Thanks in advance

Answer 1

Look into the

SELECT * FROM information_schema.user_privileges
WHERE grantee = '\'root\'@\'localhost\''

OR

SHOW GRANTS FOR 'root'@'localhost';

Answer 2

select userid, GROUP_CONCAT(DISTINCT permissions ORDER BY permissions DESC) as permissions_grouped from permissions where permissions in ('view', 'delete', 'add', 'edit') GROUP BY userid HAVING permissions_grouped = "view,edit,delete,add";

thise will first get all the users who have any of those permissions, and then concat all of their permissions to an ordered string, then the having will only select rows with the right string.

edit: formatting