Understanding MySQL Permission Changes and Their Effectiveness in Uproxy

Uproxy is a high‑performance read/write middleware for the CloudTree® DMP product, maintaining a connection pool to a MySQL backend.

Background

Customers reported that after revoking a global privilege with REVOKE *.* the user could still see and operate on databases, and FLUSH PRIVILEGES seemed ineffective, raising the question of a MySQL bug.

MySQL permission change methods

There are two ways to modify privileges:

1. Directly edit the mysql.user table (INSERT/UPDATE/DELETE) – not recommended.

update mysql.user set Select_priv='N' where user='ouyanghan' and host='%';

2. Use GRANT/REVOKE statements – recommended.

GRANT priv_type [(column_list)] ... ON object_type priv_level TO user ...;

When using the first method you must run FLUSH PRIVILEGES ; the second method reloads privileges automatically. However, privilege changes take effect differently depending on scope:

Table‑ and column‑level changes ( db_name.table_name ) become effective on the next client request (effectively immediate).

Database‑level changes ( db_name.* ) require the client to execute USE db_name after enabling the query cache ( query_cache_type ) to take effect.

Global changes ( *.* ) affect only new sessions; existing connections remain unchanged.

Verification

Created a user ouyanghan with only USAGE privilege, granted SELECT on demp.* , and observed that the database‑level privilege became visible immediately without switching databases.

# root creates user
CREATE USER ouyanghan IDENTIFIED BY 'oyh123';
# grant SELECT on demp.*
GRANT SELECT ON demp.* TO ouyanghan;
# ouyanghan shows grants
SHOW GRANTS;
# shows SELECT privilege

After enabling the MySQL query cache:

SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 1000000;

The user could see tables in db1 after switching databases, confirming the cache‑related rule.

Summary

The effective rules are:

Table/column privileges take effect immediately on the next request.

Database privileges require USE db_name (and query cache enabled in older MySQL versions).

Global privileges affect only new connections.

To resolve the original issue, refresh the Uproxy connection pool after privilege changes.