Michał Skóra Michał Skóra - 4 months ago 13
SQL Question

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.

Answer

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.