Search code examples
mysqljdbcshowprivileges

Show users with any privileges to database. MySQL

i have to select all users with any privileges to database (e.g. database 'mysql'). Any suggestions? Thanks.

Solution

  • Look the in mysql database (an actual db named mysql inside the mysql server, just to be clear). There's three tables (db, tables_priv, and columns_priv) where the db/table/column privs are stored:

    SELECT 'db', User, Host
FROM db
WHERE Db='mydatabase'

UNION

SELECT 'table', User, Host
FROM tables_priv
WHERE Db='mydatabase'

UNION

SELECT 'col', User, Host
FROM columns_priv
WHERE Db='mydatabase'

    should show you what you need.