Mawg Mawg - 5 months ago 7
MySQL Question

Can I grant access to databases with prefix then wild card?

Can I

GRANT ALL PRIVILEGES ON a*.* TO 'my_user'@'%' WITH GRANT OPTION;
?

I just want the user to only have access to all databases beginning with "my_prefix_" but not, for instance, to "mysql".

Answer

Yes, you can.

This is what phpMyAdmin does when I make a user jaap and use the option Grant all privileges on wildcard name (username\_%):

GRANT ALL PRIVILEGES ON  `jaap\_%` . * TO  'jaap'@'localhost';

That's what you mean, right?

PS
As you can see, the _ is escaped, because in (My)SQL an unescaped _ means 'one character'. (Like . in regex.) In this case, you want the _ to be literal, so databases must start with jaap_