1

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?

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@%.

  • I agree with this because if you do not have WITH GRANT OPTION with the CURRENT_USER() you are using, you do not have the right to give away your privileges no matter what level the privieges are (global, db, table, or column). +1 for thourough explanation. – RolandoMySQLDBA May 24 '14 at 18:24
  • After connecting directly to the local host, I could issue the Grant command successfully. Thanks for your detailed explanation. – user3439138 May 27 '14 at 17:30
2

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

  • Although your answer is not very explanatory, it should solve the problem as presented in the question. +1 for coming to point with a solution and suggesting the actual DNS name for root as a more narrow beam to authenticating with proper GRANT OPTION. Sometimes, aquick-and-dirty answer that can actually do something can suffice. – RolandoMySQLDBA May 24 '14 at 18:27
  • Thanks! Will keep in mind next time I run into something similar. – user3439138 May 27 '14 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.