MySQL SHOW GRANTS Permissions Mismatch Explained

In MySQL, the SHOW GRANTS command is used to display user privileges. However, in practice, we sometimes encounter situations where SHOW GRANTS indicates insufficient privileges, yet the user can perform operations; or it shows privileges, but executing an operation results in a permission error. This phenomenon primarily stems from MySQL's permissions being distributed across multiple system tables with a relatively complex matching mechanism.

1. Background

A client's production environment required security hardening, disallowing users with a host value of %. The client attempted to update the user's host field value directly, which led to inconsistencies between the permissions displayed by SHOW GRANTS and the actual effective permissions.

2. Scenario Reproduction

Create a database test, a table t1, and a user u01@'%'. Grant this user all privileges on test.* and create a view t_view. Subsequently, update the host for user u01 from % to 10.% directly in the mysql.user table.

After modification:

• SHOW GRANTS FOR 'u01'@'10.%' only shows USAGE privilege.
• The user can still perform INSERT and SELECT on test.t1.
• However, accessing test.t_view results in Access denied.

GRANT ALL PRIVILEGES ON test.* TO u01@'%' IDENTIFIED BY 'admin';
SHOW GRANTS FOR u01@'%';
+-------------------------------------------------+
| Grants for u01@%                                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'u01'@'%'                 |
| GRANT ALL PRIVILEGES ON `test`.* TO 'u01'@'%'   |
+-------------------------------------------------+

-- Log in as u01@'%' and create a view
CREATE VIEW t_view AS SELECT * FROM t1;

-- Update the host field directly
UPDATE mysql.user SET host='10.%' WHERE user='u01';
FLUSH PRIVILEGES;

SHOW GRANTS FOR u01@'10.%';
+------------------------------------------+
| Grants for u01@10.%                      |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'u01'@'10.%'       |
+------------------------------------------+

-- Validate permissions, log in as 'u01'@'10.%'
SHOW GRANTS;
+------------------------------------------+
| Grants for u01@10.%                      |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'u01'@'10.%'       |
+------------------------------------------+

-- Successfully insert data
INSERT INTO test.t1 VALUES (1);

-- Successfully query data
SELECT * FROM test.t1;

-- Querying the view fails with access denied
SELECT * FROM test.t_view;
ERROR 1045 (28000): Access denied for user 'u01'@'10.%' (using password: YES)

‍

3. Root Cause

​1. Distributed Permission Sources​
MySQL permissions are distributed across these system tables:

• mysql.user: Account basic information.
• mysql.db: Database-level privileges.
• mysql.tables_priv / columns_priv: Table and column-level privileges.
• mysql.procs_priv: Stored procedure privileges.

​2. SHOW GRANTS Limitations​
SHOW GRANTS FOR 'u01'@'10.%' primarily displays records from mysql.user where user='u01' AND host='10.%'. Actual permission checks are more flexible, matching host patterns at runtime from all privilege tables (e.g., a host value of '%' in mysql.db still matches 10.%). However, SHOW GRANTS does not display these matched permissions from other tables.

​3. Reason for View Access Failure​
The view was created with the default DEFINER='u01'@'%'. After the host was changed to 10.%, this user definition ('u01'@'%') no longer exists, causing the view's runtime permission check to fail.

4. Solutions

​Method 1: Recreate the User (Not recommended for production)​

DROP USER 'u01'@'10.%';
CREATE USER 'u01'@'10.%' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON test.* TO 'u01'@'10.%';

‍

Method 2: Use RENAME USER to change the host (Recommended)

RENAME USER 'u01'@'10.186.%' TO 'u01'@'10.%';

‍

Advantage: Migrates permissions associatively, avoiding omissions.

​Method 3: Manually update all relevant privilege tables​ (e.g., tables_priv, columns_priv)

UPDATE mysql.user SET host='10.186.%' WHERE user='u01';
UPDATE mysql.db SET host='10.186.%' WHERE user='u01';
UPDATE mysql.tables_priv SET host='10.186.%' WHERE user='u01';
UPDATE mysql.columns_priv SET host='10.186.%' WHERE user='u01';
FLUSH PRIVILEGES;

‍

Important Notes

• Avoid using % for host range authorization; specify explicit IP ranges based on business needs.
• After modifying a user's host, be sure to:
  • Update all relevant privilege tables (user, db, tables_priv, columns_priv) if not using RENAME USER.
  • Check if all DEFINER fields (for views, stored procedures, events) in information_schema remain valid.
  • Validate:
    • Whether the SHOW GRANTS output is reasonable.
    • Whether views/procedures are accessible.

5. Conclusion

MySQL's permission checking mechanism is more complex than what SHOW GRANTS displays. Actual permissions depend on runtime pattern matching across all privilege tables, while SHOW GRANTS relies heavily on the exact user@host definition.

​Recommendations:​​

• Use RENAME USER to modify the user host.
• Avoid manual direct table updates. If necessary, synchronize all related tables.
• Pay attention to the DEFINER field of privileged objects.

‍