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;
If you installed MySQL 5.6 from the command line, the easiest way to find the temporary password is to go to the Linux command line do the following:
cd
cat `ls -la | grep mysql | grep secret | awk '{print $9}'` | awk '{print $18}'
This will quickly print the temporary password.
Try to login using that password
cd
OLDPASSFILE=`ls -la | grep mysql | grep secret | awk '{print $9}'`
PASSWORD=`cat ${OLDPASSFILE} | awk '{print $18}'
mysql -uroot -p${PASSWORD}
If the temporary password is no longer available, then you will have do the following:
NEWPASS="whateveriwant"
service mysql stop
service mysql start --skip-grant-tables
mysql -ANe"update mysql.user set password=PASSWORD('${NEWPASS}') where user='root'"
service mysql restart
Give it a Try !!!
Best Answer
There is not a wildcard facility of that sort.
Don't prefix columns with the table name. If you need qualification, do
tablename
.columnname
.Don't prefix tables with the table name. If you need qualification, do
dbname
.tablename
.If necessary, move all the examples into a separate db and
GRANT
suitable permissions to suitable users for that database.