How to grant access to a specific table across all databases in Mysql?

Question

I want to Grant access like so:

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

This is not possible per Mysql documentation on grant:

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level

And nothing is mentioned for table level access.

I found similar questions on stackexchange, but they have different use cases. There is Mysql Bug from 2017 that seems to suggest a fix for this specific use case:

Accept statements which have a wildcard in the database name and a fixed table name.

Is there a workaround?

• Akina: Is there a workaround? No. Grant each table separately (use stored procedure, iterate over metadata).
• Akina: And nothing is mentioned for table level access. Documentation claims: "Table privileges apply to all columns in a given table." ONE given table, not given tableS.
• rags: @Akina that's one solution, however, I would need this to apply to future databases as well.
• Akina: I would need this to apply to future databases as well. You must add according GRANT to future databases/tables creation code simply. Or to the code which performs this creation dynamically.
• Rick James: My condolences.

Answer 1

You will have to script it in a stored procedure.

here is such a script

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 ;

Here is a sample call

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');

Here was a sample test I did

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>

GIVE IT A TRY !!!

• rags: I'll mark this as accepted, although did not use this procedure, as I had my own migration script to do the same.