SQL语句中`%`.*与'db_user'@'%'里%的含义及权限问题咨询

Let’s break down each of your questions clearly, as someone who’s worked with MySQL privileges extensively:

1. Is % a wildcard in your REVOKE statement? #

Absolutely—% acts as a wildcard in MySQL privilege statements, but its meaning shifts based on context:

• In %.*, the % is a wildcard for all schemas (databases) on the server.
• In 'db_user'@'%', the % is a wildcard for any client host (meaning this user can connect from any IP address or hostname).

2. What does %.* represent? #

Your understanding is spot-on:

• The % before the dot refers to all available schemas (databases) on the MySQL server.
• The * after the dot refers to all tables within each of those schemas.
  So REVOKE SELECT ON %.* FROM 'db_user'@'%'; removes the user’s ability to run SELECT queries on any table in any database.

3. Why does GRANT INSERT, UPDATE ON %.tablename TO 'db_user'@'%'; throw Error 1146? #

MySQL doesn’t support using the % wildcard for schemas when you specify a specific table name. The % wildcard for schemas only works when paired with * (all tables).

Here’s the root cause: When you write %.tablename, MySQL interprets this as looking for a schema literally named % (not a wildcard), which doesn’t exist—hence the "Table '%.tablename' doesn't exist" error.

If you want to grant those privileges to the same table name across all schemas, you have two practical options:

• Run separate GRANT statements for each schema (e.g., GRANT INSERT, UPDATE ON schema1.tablename TO 'db_user'@'%';, GRANT INSERT, UPDATE ON schema2.tablename TO 'db_user'@'%';, etc.).
• If you’re using MySQL 8.0.16 or later, create a role with the privilege on each schema’s target table, then assign the role to the user.

Alternatively, if you meant to grant access to all tables in a specific schema, use schema_name.* instead of %.tablename.

4. What’s the difference between 'db_user'@'%' and 'db_user'@'localhost'? #

These are two distinct user accounts in MySQL—even though they share the same username:

• 'db_user'@'%': This user can connect to the MySQL server from any remote host (any IP address or hostname outside the server itself). Depending on your config, it might also allow local connections via TCP/IP.
• 'db_user'@'localhost': This user can only connect from the local machine (typically via the Unix socket on Linux/macOS, or named pipes/shared memory on Windows). Note that connecting via 127.0.0.1 might use a separate 'db_user'@'127.0.0.1' account unless your config maps localhost to TCP/IP.

Critical note: These two users have entirely separate privilege sets—granting a privilege to one won’t affect the other.

内容的提问来源于stack exchange，提问作者f0rfun