Mysql: GRANT command denied to user 'root'@'dhcp-x-x-x-x.<company>' for table 'testtable'

Question

I am new to MySQL admin duties, and am trying to unsuccessfully grant SELECT Permissions to a limited number of columns in a table, using MySQL Workbench 5.2.44:

GRANT SELECT (col1, col2, col3) ON mysqldb.testtable TO 'testuser'@'%'

I get the following error:

Error Code: 1142. GRANT command denied to user 'root'@'dhcp-x-x-x-x.companyname.com' for table 'testtable'

I have gone through similar posts in here and have done the following tests:

1) SHOW GRANTS FOR 'root'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'xxxxxxxx' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

2) SHOW GRANTS FOR 'root'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxxx'
GRANT ALL PRIVILEGES ON `mysqldb`.* TO 'root'@'%'

3) SHOW GRANTS FOR CURRENT_USER();

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxxx'
GRANT ALL PRIVILEGES ON `mysqldb`.* TO 'root'@'%'

4) SELECT USER(), CURRENT_USER();

'[email protected]', 'root@%'

5) select host,user,select_priv, Create_user_priv from mysql.user;

localhost       root  Y      Y
hostname.companyname.com  root  Y   Y
::1 root    Y   Y           
%   root    Y   Y

Am I getting the 'GRANT command denied' error because even though I am root, CURRENT_USER() is identified as 'root'@'%' and GRANT ALL PRIVILEGES 'WITH GRANT OPTION' is only given to 'root'@'localhost' and therefore, 'root'@'%' is not evaluating to be the equivalent of 'root'@'localhost'? (I am connecting to the hostmachine via MySQL WorkBench)

Is there anyway to solve this problem without changing the root password using the --skip-grant-tables option?

Answer 1

First, you will need to connect to mysql locally, as stated. Try to do the GRANT for 'root'@'%' as suggested, byut if time is of the essense, a quick dirty way to accomplish this (yes, sadly, I have had to do this) is to specifically grant privileges to the fully qualified name 'root'@'dhcp-123.123.123.123.companyname.com'.

Answer 2

Am I getting the 'GRANT command denied' error because even though I am root, CURRENT_USER() is identified as 'root'@'%' and GRANT ALL PRIVILEGES 'WITH GRANT OPTION' is only given to 'root'@'localhost' and therefore, 'root'@'%' is not evaluating to be the equivalent of 'root'@'localhost'?

Yes, you are correct. Snippet from mysql documentation

To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT OPTION privilege because two users with different privileges may be able to combine privileges!

The solution would be connect mysql from localhost with root user and change the privilege of root@%.