List all users with ALL PRIVILEGES in MySQL
15,147
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 :)
Related videos on Youtube
Author by
manifestor
Updated on September 18, 2022Comments
-
manifestor over 1 year
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 withGRANT 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?