2

Whenever I try to Grant Privileges as root@localhost, it says permission denied (using password=Yes).

My root user is the one that I am logged in as. I verify this by select user();

It responds with 'root'@'localhost'.

I have also checked out this stack overflow post which seems to be the same issue:

https://stackoverflow.com/questions/28796510/cannot-grant-permissions-in-mysql-using-root-or-any-account-access-denied-f

and this one

https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

I am having no luck. If I Show GRANTS;, It lists all GRANT privileges: SELECT, Insert, Update, DELETE, CREATE, DROP, etc.

  1. How do I make sure that ROOT has GRANT option itself?

  2. Why would my root user not have all privileges? I am using mysql version: 5.5.38-0+wheezy1 on Debian.

migrated from stackoverflow.com Apr 9 '15 at 16:45

This question came from our site for professional and enthusiast programmers.

  • A) What command are you running? B) Are you having trouble connecting to the MySQL server or running the command? – tadman Apr 9 '15 at 16:21
  • I am running: GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION; I am not having trouble connecting to mysql -u root -pMyPassword – user1075722 Apr 9 '15 at 16:53
  • Please run the following query : select count(1) UserTableColumnCount from information_schema.columns where table_schema='mysql' and table_name='user';. What number does it report ??? – RolandoMySQLDBA Apr 9 '15 at 16:54
  • The output of that command is 39. – user1075722 Apr 9 '15 at 16:55
  • 1
    Ah ha. I know the problem. Please stand by ... – RolandoMySQLDBA Apr 9 '15 at 16:55
3

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user 
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!

  • The --upgrade-system-tables option was used, databases won't be touched. FATAL ERROR: Upgrade failed – user1075722 Apr 9 '15 at 17:02
  • Maybe I should downgrade to 5.1. This is a test server. – user1075722 Apr 9 '15 at 17:03
  • You could add the missing columns manually to mysql.user – RolandoMySQLDBA Apr 9 '15 at 17:05
  • Go to my post dba.stackexchange.com/questions/16397/… and see all the columns each version of MySQL has for mysql.user – RolandoMySQLDBA Apr 9 '15 at 17:07
  • I just added a manual fix to the mysql.user table. – RolandoMySQLDBA Apr 9 '15 at 17:27
0
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; 
  • Is this not risky? What if it allows me to revoke all fine but still won't allow me to put them back? Is that possible? – user1075722 Apr 9 '15 at 16:56
  • No. Is not. This will grant ALL. – Florin Apr 9 '15 at 17:10
  • The privileges will not be activated until you FLUSH PRIVILEGES;. – Anders Feb 11 '16 at 16:24

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.