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.