Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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;
share|improve this answer
    
I actually only want the wordpress user to connect from those specific hosts. What's confusing is that the mysql.db table does have 'wordpress'@'%' entries for the schema privileges. MySQL Workbench created those, and I can't figure out what GRANT syntax allows that. I'd like to do the same for object privileges, if possible, so I don't need to maintain three identical sets of privileges. –  db2 Jan 20 '12 at 18:25
    
You may have maintain identical wordpress users since (host,user) is primary key of mysql.user. That table is always double checked before checking entries into mysql.db. After all, wordpress@'%' is separate and distinct from worpress@localhost. By design, they do not share privileges. Check out my post on how MySQL User Authentication is loaded and maintained : dba.stackexchange.com/a/10897/877 –  RolandoMySQLDBA Jan 20 '12 at 18:32
    
See, that's the weird thing. It seems to be working perfectly fine like this. The wordpress user has full access to the wordpress database, despite the host being '%' in mysql.db, and individual host names in mysql.user. This is 5.5.11-log Win64, if that makes a difference. –  db2 Jan 20 '12 at 18:40
    
When you connect to mysql as wordpress, run this command SELECT USER(),CURRENT_USER(); and tell me what you see –  RolandoMySQLDBA Jan 20 '12 at 18:41
    
Shows it as wordpress@localhost for both, and I can fully access the wordpress database (but not other databases), based on the wordpress@% grant entry. –  db2 Jan 20 '12 at 18:46
show 7 more comments

This worked for me using the command line (not workbench):

mysql> GRANT SELECT ON foo.bar TO 'wordpress'@'%';
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW GRANTS FOR wordpress@'%';
+------------------------------------------------+
| Grants for wordpress@%                         |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'%'          |
| GRANT SELECT ON `foo`.`bar` TO 'wordpress'@'%' |
+------------------------------------------------+
2 rows in set (0.05 sec)

I poked around and found this bug report. What version is your Workbench? It seems they have a fix, but not being a user of Workbench, I don't know their versioning (current version should be fixed).

Even if it's not, solution is to run it through command line!

share|improve this answer
    
+1 for going CSI:Miami on the bug report for Workbench –  RolandoMySQLDBA Jan 20 '12 at 16:45
    
Actually, I am just doing it from the command line (well, through MySQL Query Browser, but same thing). I'll add some more details as to what the current user/grant tables look like. It's... strange. –  db2 Jan 20 '12 at 18:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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