0

I need to find a way to list all database users that have ALL PRIVILILEGES on *.* (all databases and tables). Additionally, it would be great if it could also list only those with GRANT OPTION.

My initial thought was to select User from mysql.user where each privilege field is ="Y". That would result in a very long query. Is there another, more convenient way?

1

On MariaDB we have grants: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE. In sum, we have 28 available grants. So to show users with all grants we can do:

select count(*) as grants, grantee from information_schema.user_privileges group_by grantee having grants >= 28;

To check if it's true, you can check with result from

select * from information_schema.user_privileges;

and compare :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.