MySQL Permissions – Using Wildcard in CREATE Grant

MySQLpermissions

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?

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

Related Solutions

Grant Table Privileges to a MySQL User Connecting from Any Host

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;

MySQL Mistake with GRANT OPTION

There is no "mass revoke" statement so you best option to change this is to update the users table:

UPDATE mysql.user SET Grant_priv = 'N' WHERE user != 'root';

Not that occasionally, there are users that should have grant privileges (and should have that) which are not named root. On my system, for example, I have debian-sys-maint (which I actually wonder if they should have grant privileges, but that's how it's installed).

Do not forget to reload the privileges after changing the user table:

FLUSH PRIVILEGES;

You can find more information on http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.

Best Answer

Related Solutions

Grant Table Privileges to a MySQL User Connecting from Any Host

MySQL Mistake with GRANT OPTION

Related Question