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 :
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.