Use wildcard to grant rights to table with MySQL and MariaDB

The attribution of right in MySQL and MariaDB is sometimes a puzzle especially if one wishes to have a control by table for a user. It is possible to use a wildcard in the GRANT command, but its syntax is not very explicit.

Let's imagine that for my blog I work with an SEO team. This team needs access to the BDD to manage their data and to analyze stats. The team is not composed of developers, I do not want to grant them too much rights on tables.

All tables they need are prefixed with "seo_". The command that comes to mind is:

GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.seo_* TO 'seo_team'@'localhost';

You guessed it, it's too easy, it does not work. MySQL and MariaDB do not accept the notation "seo_ *" as the table name. So I could do one command per table by explicitly indicating the table, but it implies to maintain the rights as more tables are added or removed.

It is possible to work around the problem, MySQL and MariaDB accepts a wildcard (only the char %) on the first part of the table expression (before the point). The syntax to use becomes:

GRANT SELECT, INSERT, UPDATE, DELETE ON `blog.seo_%`.* TO 'seo_team'@'localhost';

Easy to use, but not the solution it comes to mind.

There is 4 comments.

Commented by VinLor on Jan 18, 2024

I'm running MySQL v5.7.42, the feature must have been introduced in newer versions.

Commented by VinLor on Jan 17, 2024

Does not work. Having twice the dot is just weird and mysql uses the whole first part `blog.seo_%` as DB name which is not what we want. BTW '_' is also a wildcard and must be backquoted.

Replied of ulrich on Jan 17, 2024

I just tried with MySQL v8.0.36 and this syntax is working. Which MySQL version are you using?

Commented by Herschelle42 on Nov 27, 2022

merci beaucoup. exactly what I was looking for. :)

Add a comment