MySQL do not accepts wildcard for tables name level grant

Question

Trying to grant a user select privilege on multiple tables using wildcards as following:

GRANT SELECT ON 'DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password';

But keep giving the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password'' at line 1

Server version: 5.7.30-log MySQL Community Server (GPL)

I'm sure that the syntax of the query is correct, but for some reason it won't accept it, Any advise?

Answer 1

The MySQL 5.7 GRANT Syntax Documentation says something interesting about wildcards

Under the subheading Object Quoting Guidelines, Paragraph 1 says:

Several objects within GRANT statements are subject to quoting, although quoting is optional in many cases: Account, database, table, column, and routine names. For example, if a user_name or host_name value in an account name is legal as an unquoted identifier, you need not quote it. However, quotation marks are necessary to specify a user_name string containing special characters (such as -), or a host_name string containing special characters or wildcard characters such as % (for example, 'test-user'@'%.com'). Quote the user name and host name separately.

Paragraphs 3 and 4 say

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level (GRANT ... ON db_name.). This means, for example, that to use a _ character as part of a database name, specify it as _ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example, GRANT ... ON foo\_bar. TO ...).

When a database name not is used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine (for example, GRANT ... ON db_name.tbl_name), wildcard characters are treated as normal characters.

What about table privileges ? Please note the Docs on Table Privileges:

Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database. An error occurs if there is no default database.

The permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

Table-level privileges apply to base tables and views. They do not apply to tables created with CREATE TEMPORARY TABLE, even if the table names match. For information about TEMPORARY table privileges, see Section 13.1.18.2, “CREATE TEMPORARY TABLE Statement”.

MySQL stores table privileges in the mysql.tables_priv system table.

As shown from the MySQL 5.7 GRANTS Documentation

• Wildcards are applicable to hostname (which are bold italiczied)
• Wildcards are applicable to database name (which are bold italiczied)
• Nowhere do the docs specify wildcards for table names
• Wildcard characters are treated as normal characters for all other database objects

The workaround given in the comment is what I used to do when giving away SELECT on views.

Answer 2

you need to use skip special characters \ in your command

GRANT SELECT ON 'DBname'.'foo\_%' TO 'username'@'%' IDENTIFIED BY 'password';