MySQL's privilege system is multi-layered, with different levels of access control. Before checking privileges, it's important to understand the hierarchy:

• Global privileges (across all databases)
• Database privileges (for specific databases)
• Table privileges (for specific tables)
• Column privileges (for specific columns)
• Procedure privileges (for stored procedures/functions)

For a quick overview of privileges granted to a user:

SHOW GRANTS FOR 'username'@'hostname';

Example output:

+----------------------------------------------------------------------------------+
| Grants for user1@localhost                                                       |
+----------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD 'xxx' |
| GRANT SELECT ON `testdb`.* TO 'user1'@'localhost'                               |
+----------------------------------------------------------------------------------+

For more detailed analysis, you can query MySQL's privilege tables:

SELECT * FROM mysql.user WHERE User='username' AND Host='hostname' \G

For database-specific privileges:

SELECT * FROM mysql.db WHERE User='username' AND Host='hostname';

For table-level privileges:

SELECT * FROM mysql.tables_priv WHERE User='username' AND Host='hostname';

To see the actual privileges available in the current session:

SHOW PRIVILEGES;

Or check if a specific privilege is available:

SELECT * FROM information_schema.user_privileges 
WHERE grantee LIKE "'username'@'hostname'";

For a comprehensive view of all privileges across all levels:

SELECT 
    u.user, u.host, 
    d.db, 
    t.table_name, 
    c.column_name, 
    p.routine_name,
    CONCAT_WS(',',
        IF(u.select_priv='Y','GLOBAL_SELECT',NULL),
        IF(d.select_priv='Y','DB_SELECT',NULL),
        IF(t.table_priv LIKE '%Select%','TABLE_SELECT',NULL),
        IF(c.column_priv LIKE '%Select%','COLUMN_SELECT',NULL)
    ) AS select_privileges
FROM 
    mysql.user u
    LEFT JOIN mysql.db d ON u.user = d.user AND u.host = d.host
    LEFT JOIN mysql.tables_priv t ON u.user = t.user AND u.host = t.host
    LEFT JOIN mysql.columns_priv c ON u.user = c.user AND u.host = c.host
    LEFT JOIN mysql.procs_priv p ON u.user = p.user AND u.host = p.host
WHERE 
    u.user = 'username';

Example 1: Check if a user has CREATE privilege on any database:

SELECT User, Host, Create_priv FROM mysql.user WHERE Create_priv = 'Y';

Example 2: List all users with table-level privileges:

SELECT DISTINCT User, Host FROM mysql.tables_priv;

Example 3: Check column-level SELECT privileges:

SELECT User, Host, Db, Table_name, Column_name 
FROM mysql.columns_priv 
WHERE Column_priv LIKE '%Select%';

• Privilege changes require FLUSH PRIVILEGES or server restart to take effect
• The SUPER privilege (if enabled) bypasses most access checks
• Some privileges might be granted via roles in MySQL 8.0+
• Always check with the specific user account you're troubleshooting

---

MySQL's privilege system controls user access to databases and tables. Privileges can be granted at different levels:

• Global privileges (server-wide)
• Database privileges
• Table privileges
• Column privileges
• Routine privileges

The primary commands to examine user privileges are:

-- Show privileges for current user
SHOW GRANTS;

-- Show privileges for specific user
SHOW GRANTS FOR 'username'@'hostname';

-- Alternative method using mysql database
SELECT * FROM mysql.user WHERE User='username' AND Host='hostname'\G

Let's examine some real-world scenarios:

-- Example 1: View privileges for user 'dev_user' connecting from localhost
SHOW GRANTS FOR 'dev_user'@'localhost';

-- Example 2: Check if user has specific privilege on database
SELECT * FROM mysql.db 
WHERE User='app_user' AND Host='%' AND Db='inventory_db';

-- Example 3: Detailed privilege analysis
SELECT 
    user, host, db, 
    table_name, column_name, 
    privilege_type 
FROM 
    mysql.columns_priv 
WHERE 
    user = 'report_user';

The output of SHOW GRANTS typically looks like:

GRANT SELECT, INSERT ON inventory_db.* TO 'dev_user'@'192.168.%' IDENTIFIED BY PASSWORD '*hashed_password'
GRANT CREATE TEMPORARY TABLES ON *.* TO 'dev_user'@'192.168.%'

Key elements in the output:

• GRANT keyword indicating privilege assignment
• Privilege types (SELECT, INSERT, etc.)
• Database/table specification (inventory_db.*)
• User/host combination ('dev_user'@'192.168.%')
• Authentication details (if shown)

For more comprehensive analysis:

-- Check all privilege tables
SELECT * FROM information_schema.user_privileges;
SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;
SELECT * FROM information_schema.column_privileges;

-- Find users with specific privileges
SELECT DISTINCT grantee 
FROM information_schema.user_privileges 
WHERE privilege_type = 'SUPER';

-- Check for anonymous users
SELECT User, Host FROM mysql.user WHERE User = '';

Frequently needed privilege checks:

-- Find all users with root-like privileges
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

-- Check who can create users
SELECT User, Host FROM mysql.user WHERE Create_user_priv = 'Y';

-- Find users with file system access
SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';

-- Check replication privileges
SELECT User, Host FROM mysql.user WHERE Repl_slave_priv = 'Y';

When privilege checks don't show expected results:

1. Verify you're checking the correct user@host combination
2. Check if privileges need to be flushed: FLUSH PRIVILEGES;
3. Confirm you have sufficient privileges to view other users' grants
4. Remember that host patterns (%, _) affect privilege matching

For comprehensive privilege auditing, consider using:

-- Create a complete privilege report
SELECT 
    u.User, u.Host, 
    db.Db, db.Select_priv, db.Insert_priv, 
    db.Update_priv, db.Delete_priv
FROM 
    mysql.user u
LEFT JOIN 
    mysql.db db ON u.User = db.User AND u.Host = db.Host
ORDER BY 
    u.User, u.Host, db.Db;