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;
You could copy just the the "mysql" database away to another location and start another daemon on it. Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).
Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext. This should be trivial.
You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.
You can then make any modifications to permissions, create necessary schema and tables and flush privileges.
I'll leave the security implications of such practices up to you.
Best Answer
I did the following. I wrote a script (testfile) as below and it worked. The big difference AFAICS is that with my script I export my variables before trying to use them. BTW, I wouldn't call it $USER - I'd go with $MY_USER - there's already a Linux system variable called $USER. I also didn't have these lines
I just chmod'ed the file - $>
chmod 755 testfile
and ran it thus./testfile
.I have since added the line (to test the running of an sql script file following the user and database creation - with grants).
to the end of testfile. This has the advantage of not using root, but rather the user you have just created. It is always good practice to run commands as root as rarely as possible. Keeping the root password in a script file is rather insecure. You can keep it in your .bashrc - check out the MySQL environment variables here, in this case
$MYSQL_PWD
.[EDIT] - here is an alternative method. The -e stands for execute (mysql --help) - and is rather more conventional than the echo construct that you used.
(This worked on 5.6 - the previous example worked on a milestone release).
You can see the result below. Don't worry about the
./bin/mysql
, nor the-S ./mysql.sock
- I have a non-standard source install. For a normal install, just usemysql
.You can get rid of the annoying "password ... insecure" warnings by issuing the command this way
./testfile &>/dev/null