html

Understanding MySQL User Access Control

As a database administrator or developer, knowing exactly which users have access to your MySQL databases is crucial for security and maintenance. Here's a comprehensive approach to retrieve this information.

The first place to look is the mysql.user table, which stores all user accounts and their global privileges:

SELECT User, Host FROM mysql.user;

To see which users have access to specific databases, query the mysql.db table:

SELECT User, Host, Db FROM mysql.db 
WHERE Db = 'your_database_name';

For a complete view of privileges granted to users on particular databases:

SELECT * FROM mysql.db 
WHERE Db = 'your_database_name'\G

MySQL 5.7.6+ introduced the SHOW GRANTS command which displays effective privileges:

SHOW GRANTS FOR 'username'@'host';

Here's a complete script to audit database access:

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

Remember that wildcard hosts ('%') and anonymous users (empty username) can affect your security posture. Always review these carefully.

For MySQL 8.0+, consider using the new roles-based privilege system and check the mysql.role_edges table for additional complexity in role assignments.

Understanding MySQL User Privileges

When administering MySQL databases, one of the fundamental security tasks is auditing which users have access to specific databases. MySQL stores privilege information in several system tables within the mysql database.


The key tables involved in storing access permissions are:

    
mysql.user - Global privileges
    
mysql.db - Database-level privileges
    
mysql.tables_priv - Table-level privileges
    
mysql.columns_priv - Column-level privileges



To find all users with access to a specific database (let's say 'inventory_db'), use:

SELECT DISTINCT User, Host 
FROM mysql.db 
WHERE Db = 'inventory_db'
UNION
SELECT User, Host 
FROM mysql.user 
WHERE Select_priv = 'Y' 
   OR Insert_priv = 'Y'
   OR Update_priv = 'Y'
   OR Delete_priv = 'Y'
   OR Create_priv = 'Y'
   OR Drop_priv = 'Y'
   OR Grant_priv = 'Y'
   OR References_priv = 'Y'
   OR Index_priv = 'Y'
   OR Alter_priv = 'Y';

For a more granular view of user permissions on a database:
SELECT 
    u.User, 
    u.Host, 
    db.Db,
    db.Select_priv,
    db.Insert_priv,
    db.Update_priv,
    db.Delete_priv,
    db.Create_priv,
    db.Drop_priv,
    db.Grant_priv,
    db.References_priv,
    db.Index_priv,
    db.Alter_priv,
    db.Create_tmp_table_priv,
    db.Lock_tables_priv,
    db.Create_view_priv,
    db.Show_view_priv,
    db.Create_routine_priv,
    db.Alter_routine_priv,
    db.Execute_priv,
    db.Event_priv,
    db.Trigger_priv
FROM 
    mysql.user u
LEFT JOIN 
    mysql.db db ON u.User = db.User AND u.Host = db.Host
WHERE 
    db.Db = 'inventory_db' OR 
    (u.Select_priv = 'Y' OR u.Insert_priv = 'Y' OR u.Update_priv = 'Y' OR u.Delete_priv = 'Y');

To see the effective privileges for a specific user (considering all privilege levels):
SHOW GRANTS FOR 'username'@'host';

Example output might look like:
GRANT USAGE ON *.* TO 'app_user'@'%' 
GRANT SELECT, INSERT, UPDATE ON inventory_db.* TO 'app_user'@'%'
GRANT EXECUTE ON PROCEDURE inventory_db.update_stock TO 'app_user'@'%'

For regular security audits, you might want to create a stored procedure:
DELIMITER //
CREATE PROCEDURE audit_database_access(IN db_name VARCHAR(64))
BEGIN
    SELECT 
        u.User, 
        u.Host,
        IFNULL(GROUP_CONCAT(DISTINCT priv.PRIVILEGE_TYPE), 'Global Privileges') AS Privileges
    FROM 
        mysql.user u
    LEFT JOIN 
        information_schema.USER_PRIVILEGES priv ON 
            CONCAT('\'', u.User, '\'@\'', u.Host, '\'') = priv.GRANTEE
    LEFT JOIN 
        mysql.db db ON u.User = db.User AND u.Host = db.Host
    WHERE 
        db.Db = db_name OR
        (priv.PRIVILEGE_TYPE IS NOT NULL AND priv.IS_GRANTABLE = 'YES')
    GROUP BY 
        u.User, u.Host;
END //
DELIMITER ;

-- Usage:
CALL audit_database_access('inventory_db');


Always review privileges after MySQL upgrades
Monitor for anonymous users (empty username)
Check for wildcard hosts (%)
Regularly review application-specific accounts