2

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?

7
  • 2
    Is there a workaround? No. Grant each table separately (use stored procedure, iterate over metadata). – Akina Feb 25 at 8:47
  • 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. – Akina Feb 25 at 8:49
  • @Akina that's one solution, however, I would need this to apply to future databases as well. – rags Feb 25 at 11:07
  • 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. – Akina Feb 25 at 11:48
  • 1
    My condolences. – Rick James Mar 1 at 17:06
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 !!!

1
  • I'll mark this as accepted, although did not use this procedure, as I had my own migration script to do the same. – rags Mar 2 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.