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;
Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.
So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)
See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.
You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.
Something like this should work :
GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';
Or you can allow that user to come in from any ip adress like this:
GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';
(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)
Best Answer
In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.
Unfortunately, mysql comes with full access to test databases. How can you find them ?
Run this query:
Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.
Why is this a problem ???
Try running this command:
You will have connected the test database without a password.
Now, create a table and load it with a row:
OK, big deal. Could you double this table in size 30 times ???
What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.
Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?
The best thing you can do under these circumstances is to run this query:
and restart mysql. Then, those three accounts will be properly rendered inoperative.
Give it a Try !!!
UPDATE 2011-09-12 10:00 EDT
This delete:
is just what you need for an initial installation. However, if you have users already established, You can run this instead:
This will remove the specific two DB permissions.
As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.