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?

  • If you are building foobar1, foobar2, etc., don't. Bad schema design. – Rick James May 2 '16 at 0:55

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.

  • Note it does not work at table level, GRANT CREATE ON 'foo'.'bar%' TO .. won't work – fraff Feb 10 '20 at 9:48
  • @fraff it should. But you've used single quotes ' instead of backtick `. You can even go down to column privileges (see dev.mysql.com/doc/refman/8.0/en/…) – Yvan Feb 10 '20 at 12:23
  • I tried, with backticks (I don't know how to add backticks in stackexchange comments without ruining indentation) on mysql5.7 but it didn't work. If you manage to do it please let me know, I'm still interested. – fraff Feb 10 '20 at 13:30
  • @fraff it should be like this: GRANT CREATE ON `foo`.`bar%` TO .... To add a backtick you should prefix it by an antislash: `\`` – Yvan Feb 11 '20 at 14:04
  • grant is created, I can see it with show grants for .. but user still can't create or frop table matching wildcard. I'm using mysql 5.7 – fraff Feb 11 '20 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.