There is no "mass revoke" statement so you best option to change this is to update the users table:
UPDATE mysql.user SET Grant_priv = 'N' WHERE user != 'root';
Not that occasionally, there are users that should have grant privileges (and should have that) which are not named root
. On my system, for example, I have debian-sys-maint
(which I actually wonder if they should have grant privileges, but that's how it's installed).
Do not forget to reload the privileges after changing the user table:
FLUSH PRIVILEGES;
You can find more information on http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.
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 !!!
Best Answer
I have discussed situations like this before in these posts:
May 10, 2013
: In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user tableSep 22, 2013
: How to create root user after running mysql_install_db?In your case do this
STEP #1
Add these lines just under the
[mysqld]
group header inmy.ini
STEP 2
Open Windows Command Line as Administrator, and reboot MySQL Service
STEP 3
You can login to MySQL
STEP 4
From the mysql prompt, run this
STEP 5
Remove
skip-grant-tables
andskip-networking
frommy.ini
STEP 6
Restart MySQL
STEP 7
Try logging into MySQL
GIVE IT A TRY !!!