Mysql: GRANT command denied to user ‘root’@’dhcp-x-x-x-x.‘ for table ‘testtable’

MySQL

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();

'root@dhcp-x-x-x-x.companyname.com', '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?

Best Answer

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'.

Related Topic