How to Check MySQL User Privileges and Access Rights for a Specific Database

When managing MySQL databases, it's crucial to understand which users have access to specific databases and their exact privileges. While phpMyAdmin provides a visual interface for this information, database administrators often need command-line solutions for scripting and automation.

The most direct way to check privileges for a specific database is querying the mysql.db table:

SELECT 
    User, 
    Host, 
    Db, 
    Select_priv, 
    Insert_priv, 
    Update_priv, 
    Delete_priv, 
    Create_priv, 
    Drop_priv,
    Grant_priv
FROM mysql.db 
WHERE Db = 'your_database_name';

For a more complete picture that includes both global and database-specific privileges:

SELECT 
    u.User, 
    u.Host, 
    IF(u.Select_priv='Y' OR d.Select_priv='Y', 'Y', 'N') AS Select_priv,
    IF(u.Insert_priv='Y' OR d.Insert_priv='Y', 'Y', 'N') AS Insert_priv,
    IF(u.Update_priv='Y' OR d.Update_priv='Y', 'Y', 'N') AS Update_priv,
    IF(u.Delete_priv='Y' OR d.Delete_priv='Y', 'Y', 'N') AS Delete_priv,
    IF(u.Create_priv='Y' OR d.Create_priv='Y', 'Y', 'N') AS Create_priv,
    IF(u.Drop_priv='Y' OR d.Drop_priv='Y', 'Y', 'N') AS Drop_priv,
    IF(u.Grant_priv='Y' OR d.Grant_priv='Y', 'Y', 'N') AS Grant_priv
FROM mysql.user u
LEFT JOIN mysql.db d ON u.User = d.User AND u.Host = d.Host AND d.Db = 'your_database_name'
WHERE (d.Db IS NOT NULL OR u.Select_priv = 'Y') 
ORDER BY u.User, u.Host;

To get output similar to phpMyAdmin's display:

SELECT 
    CONCAT(u.User, '@', u.Host) AS 'User',
    IF(d.Db IS NULL, 'global', 'database-specific') AS 'Type',
    CONCAT_WS(', ',
        IF(u.Select_priv='Y' OR d.Select_priv='Y', 'SELECT', NULL),
        IF(u.Insert_priv='Y' OR d.Insert_priv='Y', 'INSERT', NULL),
        IF(u.Update_priv='Y' OR d.Update_priv='Y', 'UPDATE', NULL),
        IF(u.Delete_priv='Y' OR d.Delete_priv='Y', 'DELETE', NULL),
        IF(u.Create_priv='Y' OR d.Create_priv='Y', 'CREATE', NULL),
        IF(u.Drop_priv='Y' OR d.Drop_priv='Y', 'DROP', NULL),
        IF(u.Grant_priv='Y' OR d.Grant_priv='Y', 'GRANT', NULL)
    ) AS 'Privileges',
    IF(u.Grant_priv='Y' OR d.Grant_priv='Y', 'Yes', 'No') AS 'Grant'
FROM mysql.user u
LEFT JOIN mysql.db d ON u.User = d.User AND u.Host = d.Host AND d.Db = 'your_database_name'
WHERE (d.Db IS NOT NULL OR u.Select_priv = 'Y')
ORDER BY u.User, u.Host;

To find users with write access (INSERT, UPDATE, DELETE) to a database:

SELECT DISTINCT u.User, u.Host
FROM mysql.user u
LEFT JOIN mysql.db d ON u.User = d.User AND u.Host = d.Host AND d.Db = 'your_database_name'
WHERE 
    (d.Insert_priv = 'Y' OR u.Insert_priv = 'Y') AND
    (d.Update_priv = 'Y' OR u.Update_priv = 'Y') AND
    (d.Delete_priv = 'Y' OR u.Delete_priv = 'Y');

For regular audits, you can create a stored procedure:

DELIMITER //
CREATE PROCEDURE audit_database_privileges(IN db_name VARCHAR(64))
BEGIN
    SELECT 
        u.User, 
        u.Host,
        IF(d.Db IS NULL, 'global', 'database-specific') AS Privilege_Type,
        GROUP_CONCAT(
            DISTINCT 
            CASE 
                WHEN u.Select_priv='Y' OR d.Select_priv='Y' THEN 'SELECT'
                WHEN u.Insert_priv='Y' OR d.Insert_priv='Y' THEN 'INSERT'
                WHEN u.Update_priv='Y' OR d.Update_priv='Y' THEN 'UPDATE'
                WHEN u.Delete_priv='Y' OR d.Delete_priv='Y' THEN 'DELETE'
                WHEN u.Create_priv='Y' OR d.Create_priv='Y' THEN 'CREATE'
                WHEN u.Drop_priv='Y' OR d.Drop_priv='Y' THEN 'DROP'
                WHEN u.Grant_priv='Y' OR d.Grant_priv='Y' THEN 'GRANT'
            END
            SEPARATOR ', '
        ) AS Privileges
    FROM mysql.user u
    LEFT JOIN mysql.db d ON u.User = d.User AND u.Host = d.Host AND d.Db = db_name
    WHERE (d.Db IS NOT NULL OR u.Select_priv = 'Y')
    GROUP BY u.User, u.Host, Privilege_Type;
END //
DELIMITER ;

-- Usage:
CALL audit_database_privileges('your_database_name');

MySQL's privilege system is hierarchical and stored across several system tables in the mysql database. When you need to check which users have access to a particular database, you'll need to query these tables directly.

The most direct way to find database-specific privileges is by querying the mysql.db table:

SELECT * FROM mysql.db WHERE Db = 'your_database_name';

This will return all rows where privileges are granted specifically for your target database. The output includes columns for each privilege type (Select_priv, Insert_priv, etc.) with 'Y' or 'N' values.

For a cleaner presentation similar to phpMyAdmin's output, use this query:

SELECT 
    User, 
    Host, 
    'database-specific' AS Type,
    CONCAT_WS(', ',
        IF(Select_priv='Y','SELECT',NULL),
        IF(Insert_priv='Y','INSERT',NULL),
        IF(Update_priv='Y','UPDATE',NULL),
        IF(Delete_priv='Y','DELETE',NULL),
        IF(Create_priv='Y','CREATE',NULL),
        IF(Drop_priv='Y','DROP',NULL),
        IF(Grant_priv='Y','GRANT',NULL),
        IF(References_priv='Y','REFERENCES',NULL),
        IF(Index_priv='Y','INDEX',NULL),
        IF(Alter_priv='Y','ALTER',NULL),
        IF(Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
        IF(Lock_tables_priv='Y','LOCK TABLES',NULL),
        IF(Create_view_priv='Y','CREATE VIEW',NULL),
        IF(Show_view_priv='Y','SHOW VIEW',NULL),
        IF(Create_routine_priv='Y','CREATE ROUTINE',NULL),
        IF(Alter_routine_priv='Y','ALTER ROUTINE',NULL),
        IF(Execute_priv='Y','EXECUTE',NULL),
        IF(Event_priv='Y','EVENT',NULL),
        IF(Trigger_priv='Y','TRIGGER',NULL)
    ) AS Privileges,
    IF(Grant_priv='Y','Yes','No') AS Grant
FROM mysql.db 
WHERE Db = 'your_database_name';

Users with global privileges (like root) may have access to all databases. To include these users in your results:

SELECT 
    u.User, 
    u.Host, 
    'global' AS Type,
    'ALL PRIVILEGES' AS Privileges,
    'Yes' AS Grant
FROM mysql.user u
WHERE u.Select_priv = 'Y' 
AND u.Insert_priv = 'Y'
AND u.Update_priv = 'Y'
AND u.Delete_priv = 'Y'
/* Add checks for other privileges as needed */
UNION ALL
SELECT 
    d.User, 
    d.Host, 
    'database-specific' AS Type,
    CONCAT_WS(', ',
        IF(d.Select_priv='Y','SELECT',NULL),
        /* Include other privileges as shown in previous query */
    ) AS Privileges,
    IF(d.Grant_priv='Y','Yes','No') AS Grant
FROM mysql.db d
WHERE d.Db = 'your_database_name';

For frequent use, create a stored procedure:

DELIMITER //
CREATE PROCEDURE show_db_privileges(IN db_name VARCHAR(64))
BEGIN
    SELECT 
        u.User, 
        u.Host, 
        'global' AS Type,
        'ALL PRIVILEGES' AS Privileges,
        'Yes' AS Grant
    FROM mysql.user u
    WHERE u.Select_priv = 'Y' 
    AND u.Insert_priv = 'Y'
    AND u.Update_priv = 'Y'
    AND u.Delete_priv = 'Y'
    UNION ALL
    SELECT 
        d.User, 
        d.Host, 
        'database-specific' AS Type,
        CONCAT_WS(', ',
            IF(d.Select_priv='Y','SELECT',NULL),
            /* Include other privileges */
        ) AS Privileges,
        IF(d.Grant_priv='Y','Yes','No') AS Grant
    FROM mysql.db d
    WHERE d.Db = db_name;
END //
DELIMITER ;

-- Usage:
CALL show_db_privileges('your_database_name');

From the Linux command line without entering MySQL interactively:

mysql -u root -p -e "SELECT User, Host FROM mysql.db WHERE Db = 'your_database_name'"

For a more complete output similar to the earlier queries, you can save the complex query to a file and execute it:

mysql -u root -p < query.sql > output.txt