Query to list all the user and their privileges in mariadb/mysql
SELECT
CONCAT("`",gcl.Db,"`") AS 'Database(s) Affected', CONCAT("`",gcl.Table_name,"`") AS 'Table(s) Affected', gcl.User AS 'User-Account(s) Affected', IF(gcl.Host='%','ALL',gcl.Host) AS 'Remote-IP(s) Affected', CONCAT("GRANT ",UPPER(gcl.Column_priv)," (",GROUP_CONCAT(gcl.Column_name),") ", "ON `",gcl.Db,"`.`",gcl.Table_name,"` ", "TO '",gcl.User,"'@'",gcl.Host,"';") AS 'GRANT Statement (Reconstructed)'FROM mysql.columns_priv gclGROUP BY CONCAT(gcl.Db,gcl.Table_name,gcl.User,gcl.Host)/* SELECT * FROM mysql.columns_priv */UNION/* [Database.Table]-Specific Grants */SELECT CONCAT("`",gtb.Db,"`") AS 'Database(s) Affected', CONCAT("`",gtb.Table_name,"`") AS 'Table(s) Affected', gtb.User AS 'User-Account(s) Affected', IF(gtb.Host='%','ALL',gtb.Host) AS 'Remote-IP(s) Affected', CONCAT( "GRANT ",UPPER(gtb.Table_priv)," ", "ON `",gtb.Db,"`.`",gtb.Table_name,"` ", "TO '",gtb.User,"'@'",gtb.Host,"';" ) AS 'GRANT Statement (Reconstructed)'FROM mysql.tables_priv gtbWHERE gtb.Table_priv!=''/* SELECT * FROM mysql.tables_priv */UNION/* Database-Specific Grants */SELECT CONCAT("`",gdb.Db,"`") AS 'Database(s) Affected', "ALL" AS 'Table(s) Affected', gdb.User AS 'User-Account(s) Affected', IF(gdb.Host='%','ALL',gdb.Host) AS 'Remote-IP(s) Affected', CONCAT( 'GRANT ', CONCAT_WS(',', IF(gdb.Select_priv='Y','SELECT',NULL), IF(gdb.Insert_priv='Y','INSERT',NULL), IF(gdb.Update_priv='Y','UPDATE',NULL), IF(gdb.Delete_priv='Y','DELETE',NULL), IF(gdb.Create_priv='Y','CREATE',NULL), IF(gdb.Drop_priv='Y','DROP',NULL), IF(gdb.Grant_priv='Y','GRANT',NULL), IF(gdb.References_priv='Y','REFERENCES',NULL), IF(gdb.Index_priv='Y','INDEX',NULL), IF(gdb.Alter_priv='Y','ALTER',NULL), IF(gdb.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL), IF(gdb.Lock_tables_priv='Y','LOCK TABLES',NULL), IF(gdb.Create_view_priv='Y','CREATE VIEW',NULL), IF(gdb.Show_view_priv='Y','SHOW VIEW',NULL), IF(gdb.Create_routine_priv='Y','CREATE ROUTINE',NULL), IF(gdb.Alter_routine_priv='Y','ALTER ROUTINE',NULL), IF(gdb.Execute_priv='Y','EXECUTE',NULL), IF(gdb.Event_priv='Y','EVENT',NULL), IF(gdb.Trigger_priv='Y','TRIGGER',NULL) ), " ON `",gdb.Db,"`.* TO '",gdb.User,"'@'",gdb.Host,"';" ) AS 'GRANT Statement (Reconstructed)'FROM mysql.db gdbWHERE gdb.Db != ''/* SELECT * FROM mysql.db */UNION/* User-Specific Grants */SELECT "ALL" AS 'Database(s) Affected', "ALL" AS 'Table(s) Affected', gus.User AS 'User-Account(s) Affected', IF(gus.Host='%','ALL',gus.Host) AS 'Remote-IP(s) Affected', CONCAT( "GRANT ", IF((gus.Select_priv='N')&(gus.Insert_priv='N')&(gus.Update_priv='N')&(gus.Delete_priv='N')&(gus.Create_priv='N')&(gus.Drop_priv='N')&(gus.Reload_priv='N')&(gus.Shutdown_priv='N')&(gus.Process_priv='N')&(gus.File_priv='N')&(gus.References_priv='N')&(gus.Index_priv='N')&(gus.Alter_priv='N')&(gus.Show_db_priv='N')&(gus.Super_priv='N')&(gus.Create_tmp_table_priv='N')&(gus.Lock_tables_priv='N')&(gus.Execute_priv='N')&(gus.Repl_slave_priv='N')&(gus.Repl_client_priv='N')&(gus.Create_view_priv='N')&(gus.Show_view_priv='N')&(gus.Create_routine_priv='N')&(gus.Alter_routine_priv='N')&(gus.Create_user_priv='N')&(gus.Event_priv='N')&(gus.Trigger_priv='N')&(gus.Create_tablespace_priv='N')&(gus.Grant_priv='N'), "USAGE", IF((gus.Select_priv='Y')&(gus.Insert_priv='Y')&(gus.Update_priv='Y')&(gus.Delete_priv='Y')&(gus.Create_priv='Y')&(gus.Drop_priv='Y')&(gus.Reload_priv='Y')&(gus.Shutdown_priv='Y')&(gus.Process_priv='Y')&(gus.File_priv='Y')&(gus.References_priv='Y')&(gus.Index_priv='Y')&(gus.Alter_priv='Y')&(gus.Show_db_priv='Y')&(gus.Super_priv='Y')&(gus.Create_tmp_table_priv='Y')&(gus.Lock_tables_priv='Y')&(gus.Execute_priv='Y')&(gus.Repl_slave_priv='Y')&(gus.Repl_client_priv='Y')&(gus.Create_view_priv='Y')&(gus.Show_view_priv='Y')&(gus.Create_routine_priv='Y')&(gus.Alter_routine_priv='Y')&(gus.Create_user_priv='Y')&(gus.Event_priv='Y')&(gus.Trigger_priv='Y')&(gus.Create_tablespace_priv='Y')&(gus.Grant_priv='Y'), "ALL PRIVILEGES", CONCAT_WS(',', IF(gus.Select_priv='Y','SELECT',NULL), IF(gus.Insert_priv='Y','INSERT',NULL), IF(gus.Update_priv='Y','UPDATE',NULL), IF(gus.Delete_priv='Y','DELETE',NULL), IF(gus.Create_priv='Y','CREATE',NULL), IF(gus.Drop_priv='Y','DROP',NULL), IF(gus.Reload_priv='Y','RELOAD',NULL), IF(gus.Shutdown_priv='Y','SHUTDOWN',NULL), IF(gus.Process_priv='Y','PROCESS',NULL), IF(gus.File_priv='Y','FILE',NULL), IF(gus.References_priv='Y','REFERENCES',NULL), IF(gus.Index_priv='Y','INDEX',NULL), IF(gus.Alter_priv='Y','ALTER',NULL), IF(gus.Show_db_priv='Y','SHOW DATABASES',NULL), IF(gus.Super_priv='Y','SUPER',NULL), IF(gus.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL), IF(gus.Lock_tables_priv='Y','LOCK TABLES',NULL), IF(gus.Execute_priv='Y','EXECUTE',NULL), IF(gus.Repl_slave_priv='Y','REPLICATION SLAVE',NULL), IF(gus.Repl_client_priv='Y','REPLICATION CLIENT',NULL), IF(gus.Create_view_priv='Y','CREATE VIEW',NULL), IF(gus.Show_view_priv='Y','SHOW VIEW',NULL), IF(gus.Create_routine_priv='Y','CREATE ROUTINE',NULL), IF(gus.Alter_routine_priv='Y','ALTER ROUTINE',NULL), IF(gus.Create_user_priv='Y','CREATE USER',NULL), IF(gus.Event_priv='Y','EVENT',NULL), IF(gus.Trigger_priv='Y','TRIGGER',NULL), IF(gus.Create_tablespace_priv='Y','CREATE TABLESPACE',NULL) ) ) ), " ON *.* TO '",gus.User,"'@'",gus.Host,"' REQUIRE ", CASE gus.ssl_type WHEN 'ANY' THEN "SSL " WHEN 'X509' THEN "X509 " WHEN 'SPECIFIED' THEN CONCAT_WS("AND ", IF((LENGTH(gus.ssl_cipher)>0),CONCAT("CIPHER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL), IF((LENGTH(gus.x509_issuer)>0),CONCAT("ISSUER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL), IF((LENGTH(gus.x509_subject)>0),CONCAT("SUBJECT '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL) ) ELSE "NONE " END, "WITH ", IF(gus.Grant_priv='Y',"GRANT OPTION ",""), "MAX_QUERIES_PER_HOUR ",gus.max_questions," ", "MAX_CONNECTIONS_PER_HOUR ",gus.max_connections," ", "MAX_UPDATES_PER_HOUR ",gus.max_updates," ", "MAX_USER_CONNECTIONS ",gus.max_user_connections, ";" ) AS 'GRANT Statement (Reconstructed)'FROM mysql.user gus;
