CODEWITHSUNDEEP
mysql
CodeTweetie
CodeTweetie

Reputation: 6361

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

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

Upvotes: 1

Views: 4163

Answers (3)

Harpreet
Harpreet

Reputation: 719

You can also do like this:

    SHOW GRANTS FOR CURRENT_USER;

Upvotes: 0

Mike Sherov
Mike Sherov

Reputation: 13427

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

Upvotes: 0

Yada
Yada

Reputation: 31225

Look into the 

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

OR

SHOW GRANTS FOR 'root'@'localhost';

Upvotes: 1

Related Questions