我想像這樣授予訪問權限：

> GRANT INSERT, UPDATE ON `%`.my_table TO 'user'@'%';
ERROR 1146 (42S02): Table '%.my_table' doesn't exist

根據有關grant的 Mysql 文件，這是不可能的：

在授予數據庫級別權限的 GRANT 語句中指定數據庫名稱時，允許使用 _ 和 % 萬用字元

並且沒有提到表級訪問。

我在 stackexchange 上發現了類似的問題，但它們有不同的案例。2017 年的Mysql Bug似乎建議修復此特定案例：

接受在數據庫名稱和固定表名稱中具有萬用字元的語句。

有解決方法嗎？

您必須在儲存過程中編寫腳本。

這是這樣一個腳本

CREATE DATABASE IF NOT EXISTS grantor;
USE grantor
DELIMITER $$
DROP PROCEDURE IF EXISTS grant_table $$
CREATE PROCEDURE grant_table
(
   tb VARCHAR(64)
  ,userhost VARCHAR(128)
  ,grantlist VARCHAR(255)
)
BEGIN
   DROP TABLE IF EXISTS DBLIST;
   CREATE TABLE DBLIST
   (
       id INT NOT NULL AUTO_INCREMENT,
       db VARCHAR(64),
       PRIMARY KEY (id)
   ) ENGINE=MEMORY;
   INSERT INTO DBLIST (db)
   SELECT table_schema FROM information_schema.tables WHERE
   table_schema NOT IN
   ('information_schema','performance_schema','mysql','sys')
   AND table_name = tb;
   SELECT MAX(id) INTO @rcount FROM DBLIST;
   SELECT @rcount;
   SET @x = 0;
   WHILE @x < @rcount DO
       SET @x = @x + 1;
       SELECT CONCAT('GRANT ',grantlist,' ON `',db,'`.',tb,' TO ',userhost)
       INTO @sql
       FROM DBLIST WHERE id = @x;
       SELECT @sql;
       PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
   END WHILE;    

END $$
DELIMITER ;

這是一個範例呼叫

CREATE TABLE tryout.thistable (id INT NOT NULL PRIMARY KEY,name VARCHAR(20));
CREATE TABLE DMP492.thistable LIKE tryout.thistable;
CREATE TABLE DMP551.thistable LIKE tryout.thistable;
CREATE TABLE DMP579.thistable LIKE tryout.thistable;
CREATE TABLE DMP756.thistable LIKE tryout.thistable;
CALL grantor.grant_table('thistable','ptqd@''%''','INSERT,SELECT');

這是我做的一個樣本測試

MySQL://localhost/root/mysqld.sock/grantor> CREATE DATABASE IF NOT EXISTS grantor;
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> USE grantor
Database changed
MySQL://localhost/root/mysqld.sock/grantor> DELIMITER $$
MySQL://localhost/root/mysqld.sock/grantor> DROP PROCEDURE IF EXISTS grant_table $$
Query OK, 0 rows affected (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> CREATE PROCEDURE grant_table
   -> (
   ->     tb VARCHAR(64)
   ->    ,userhost VARCHAR(128)
   ->    ,grantlist VARCHAR(255)
   -> )
   -> BEGIN
   ->     DROP TABLE IF EXISTS DBLIST;
   ->     CREATE TABLE DBLIST
   ->     (
   ->         id INT NOT NULL AUTO_INCREMENT,
   ->         db VARCHAR(64),
   ->         PRIMARY KEY (id)
   ->     ) ENGINE=MEMORY;
   ->     INSERT INTO DBLIST (db)
   ->     SELECT table_schema FROM information_schema.tables WHERE
   ->     table_schema NOT IN
   ->     ('information_schema','performance_schema','mysql','sys')
   ->     AND table_name = tb;
   ->     SELECT MAX(id) INTO @rcount FROM DBLIST;
   ->     SELECT @rcount;
   ->     SET @x = 0;
   ->     WHILE @x < @rcount DO
   ->         SET @x = @x + 1;
   ->         SELECT CONCAT('GRANT ',grantlist,' ON `',db,'`.',tb,' TO ',userhost)
   ->         INTO @sql
   ->         FROM DBLIST WHERE id = @x;
   ->         SELECT @sql;
   ->         PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
   ->     END WHILE;
   ->
   -> END $$
Query OK, 0 rows affected (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> DELIMITER ;
MySQL://localhost/root/mysqld.sock/grantor> CALL grantor.grant_table('thistable','ptqd@''%''','INSERT,SELECT');
+---------+
| @rcount |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP492`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.02 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP551`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.02 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP579`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP756`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `tryout`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

MySQL://localhost/root/mysqld.sock/grantor> show grants for ptqd@'%';
+------------------------------------------------------------+
| Grants for ptqd@%                                          |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ptqd'@'%'                           |
| GRANT SELECT, INSERT ON `DMP492`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `tryout`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP756`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP551`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP579`.`thistable` TO 'ptqd'@'%' |
+------------------------------------------------------------+
6 rows in set (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor>

試試看 ！！！

引用自：https://dba.stackexchange.com/questions/286007