Finding Users of a Given Database or Table

SELECT Db FROM db WHERE User = 'user' AND Host = 'host'; SELECT Table_name FROM tables_priv WHERE User = 'user' AND Host = 'host' AND Db = 'db'; 

To find out which users have access to a given database, query the db table using the User and Host columns. The Db column contains the database name.

To find out specific tables to which a user has access, query the tables_priv table. The phrase shown in the phrasebox assumes you will supply the User, Host, and Db values. Alternatively, the following example finds the database and table names to which a user has access:

SELECT Db, Table_name FROM tables_priv WHERE User = 'zak' AND Host = 'localhost'; 

To find the specific privileges that a user has, use the SHOW GRANTS command, discussed in the "Viewing a User's Privileges" phrase later in this chapter.