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?
On MariaDB there are a total of 28 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.
To show all users with all grants:
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 :)
