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 advan开发者_运维问答ce

---

Look into the

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

OR

SHOW GRANTS FOR 'root'@'localhost';

---

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

---

You can also do like this:

    SHOW GRANTS FOR CURRENT_USER;