Create a gist now

Instantly share code, notes, and snippets.

Find MySQL users with no privileges
-- Note: This only checks global permissions and database permissions, not table permissions.
-- Tested on MySQL 5.6 - other versions may have a different set of global permissions.
SELECT mysql.user.*
FROM mysql.user
LEFT JOIN mysql.db
ON mysql.db.Host = user.Host
AND mysql.db.User = user.User
WHERE mysql.db.Db IS NULL
AND mysql.user.Select_priv = FALSE
AND mysql.user.Insert_priv = FALSE
AND mysql.user.Update_priv = FALSE
AND mysql.user.Delete_priv = FALSE
AND mysql.user.Create_priv = FALSE
AND mysql.user.Drop_priv = FALSE
AND mysql.user.Reload_priv = FALSE
AND mysql.user.Shutdown_priv = FALSE
AND mysql.user.Process_priv = FALSE
AND mysql.user.File_priv = FALSE
AND mysql.user.Grant_priv = FALSE
AND mysql.user.References_priv = FALSE
AND mysql.user.Index_priv = FALSE
AND mysql.user.Alter_priv = FALSE
AND mysql.user.Show_db_priv = FALSE
AND mysql.user.Super_priv = FALSE
AND mysql.user.Create_tmp_table_priv = FALSE
AND mysql.user.Lock_tables_priv = FALSE
AND mysql.user.Execute_priv = FALSE
AND mysql.user.Repl_slave_priv = FALSE
AND mysql.user.Repl_client_priv = FALSE
AND mysql.user.Create_view_priv = FALSE
AND mysql.user.Show_view_priv = FALSE
AND mysql.user.Create_routine_priv = FALSE
AND mysql.user.Alter_routine_priv = FALSE
AND mysql.user.Create_user_priv = FALSE
AND mysql.user.Event_priv = FALSE
AND mysql.user.Trigger_priv = FALSE
AND mysql.user.Create_tablespace_priv = FALSE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment