Wildcard in CREATE grant in MySQL

Question

MySQL allows the use of wildcards for database names, in order to allow an user to operate only on a subset of databases:

GRANT ALL PRIVILEGES ON `foobar%`.* TO 'user'@'%' IDENTIFIED BY 'somepassword';

Is there a way to do the same for the CREATE grant, to allow (cf. the example above) user to create only databases whose name starts with foobar?

Otherwise said: is the CREATE grant global (i.e. an user with this privilege is allowed to create any database, without limitations) or it can be limited in some way?

Answer 1

Yes. Just add the CREATE privilege:

GRANT CREATE ON `foobar%`.* TO 'foobaruser'@'%' IDENTIFIED BY 'foobarpass';

And just test it:

foobaruser$ mysql
mysql> create database `foobar_one`;
Query OK, 1 row affected (0.00 sec)

mysql> create database `barfoo_one`;
ERROR 1044 (42000): Access denied for user 'foobaruser'@'localhost' to database 'barfoo_one'

Be aware that you need to escape the _ (underscore), as it acts like one character in the pattern. So «`foobar_`» will match foobar1 or foobarZ.