Mysql – Grant table privileges to a user connecting from any host

authenticationMySQLmysql-5.5

I have a user account – let's call it 'wordpress' – that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host masks it's allowed to connect from: 'localhost', the IP address of the web server, and the hostname of the web server. No problems there.

The 'wordpress' user also has full access to its own database, granted via the Schema Privileges section in MySQL Workbench. Here, it shows the host is '%', which is what I want, since I don't want to manage three duplicate sets of privileges for the same user. If I look in mysql.db, I see these privileges, with '%' in the Host column.

So now I want to grant SELECT permission on a handful of tables in another database – let's call it 'store'. So I try this:

GRANT SELECT ON store.catalog TO 'wordpress'@'%';

And I get 'Can't find any matching row in the user table', for the obvious reason that '%' isn't a host mask I've explicitly allowed a connection from for this particular user. So what's the proper syntax to grant a table privilege to a user from any of its allowed host masks? How is MySQL Workbench getting away with it for schema privileges? I don't have to manually insert rows into mysql.tables_priv, do I?

UPDATE: To clarify, here's what the current user/grant tables look like. I've anonymized some names, obviously. Note that the host in the schema privilege table is '%', but there aren't any users with that host. How do I get MySQL to let me do that with schema object grants? Preferably without mucking around directly in mysql.tables_priv, but I'll do it if it comes down to it.

mysql> SELECT user, host FROM mysql.user WHERE user = 'wordpress';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| wordpress | 10.0.0.22 |
| wordpress | webserver |
| wordpress | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT user, host, db, select_priv FROM mysql.db WHERE User = 'wordpress';
+-----------+------+----------------+-------------+
| user      | host | db             | select_priv |
+-----------+------+----------------+-------------+
| wordpress | %    | wordpress      | Y           |
| wordpress | %    | wordpress_test | Y           |
+-----------+------+----------------+-------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'wordpress'@'localhost';
+---------------------------------------------------------------------------+
| Grants for wordpress@localhost                                            |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'localhost' IDENTIFIED BY PASSWORD '--' |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'wordpress'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'wordpress' on host '%'

Is MySQL Workbench doing something horribly undocumented with schema/object privileges? Just for kicks, I granted some table privileges to one of the specific user@host combinations, then updated mysql.tables_priv to change the host to '%'. After running FLUSH PRIVILEGES, it worked perfectly. Weird.

Best Answer

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;
Related Question