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;
Because you granted it access without password...
If a user has no password, logging in with one will always fail.
Set the password for user, only then you'll be able to use -p
In addition, you might want to remove the Any
user.
Best Answer
phpMyAdmin
shows you the privileges of the users with regard to the underlyingMySQL
database instance to which you're connected. So, the answers don't come fromphpMyAdmin
, but fromMySQL
.You can have one user have certain privileges for a given database (that's the
database-specific
, while s/he has some others for all the databases (that's theglobal
). Someone withglobal
privileges can, for instance, create new databases. That's something that person won't be able to do it s/he has only database-specific privileges.In MySQL you can give different privileges to users based not only on the username, but also based on the
IP address
orDNS name
from which they connect. In principle,localhost
represents your local machine (or, in actuality, the machine wherephpMyAdmin
is running), accessed either via IP protocol version 4 or version 6,127.0.0.1
representslocalhost
through IPv4, and::1
represents your local machine via IPv6 (actually, they represent thevirtua loopback interface
, but don't bother with the subtle distinction). However,localhost
could be changed on thehosts
file of the machine to have a different meaning (don't do it or your machine might become inoperative!).You have the three different settings (I think that by default) because the client connecting to your database might be using either an IPv4 address, an IPv6 address, or an address that maps to the
localhost
name. This is done so that, no matter which is the connection method, the root user can access MySQL and setup and change the database(s).If you would like the user
root
to be able to connect from a remote computer (apart from changing some default configuration parameters), you would need to give him/her permissions to connect from a certain IP address (such as123.123.123.123
). Check theaccount names
documentation from MySQL for how to specify IP addresses, DNS names, or wildcards.