Why SHOW GRANTS Misleads After Changing MySQL User Host and How to Fix It

1. Problem Background

In a production environment the client forbids users with host '%'. Updating the host field of a user leads to SHOW GRANTS displaying permissions that do not match the actual ability to operate.

2. Reproducing the Issue

Create a database test, a table t1 and a user u01@'%' with all privileges on test.*. Create a view t_view. Then change the host in mysql.user to 10.%.

After the change: SHOW GRANTS FOR 'u01'@'10.%' shows only USAGE.

The user can still INSERT and SELECT on test.t1.

Access to test.t_view fails with “Access denied”.

grant all privileges on test.* to u01@'%' identified by 'admin';
show grants for 'u01'@'%';
-- after host update
update mysql.user set host='10.%' where user='u01';
flush privileges;
show grants for 'u01'@'10.%';

3. Root Cause

MySQL stores privileges in several system tables: mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv, and mysql.procs_priv. SHOW GRANTS only reports rows from mysql.user, while the runtime permission check matches patterns across all tables. The view was created with DEFINER='u01'@'%'; after the host change the definer no longer exists, causing the view to fail.

4. Solutions

Method 1: Drop and 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 (recommended): Use RENAME USER to change the host.

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

Method 3: Manually update all privilege tables ( mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv) and flush privileges.

5. Precautions

Avoid using ‘%’ for host ranges; specify explicit IP ranges.

After changing a user’s host, update every privilege table and verify that all DEFINER fields in information_schema (views, procedures, events) remain valid.

6. Conclusion

The actual MySQL permission evaluation is more complex than the output of SHOW GRANTS. Use RENAME USER for host changes, avoid manual table edits when possible, and always check objects that store a definer.