1

By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one database. But I have nearly 25 databases created with 'root' user. If I want to access all other databases I need to GRANT Privileges for the root user again but all other users are not permitted to execute GRANT statement. So is there any way to grant permissions for the root user again. The all other Databases contained in my DB Server are very much important. So Please help in resolving this issue.

  • QUESTIONS : Are you willing to restart mysql twice ??? What version of MySQL are you using ??? – RolandoMySQLDBA Feb 27 '15 at 20:35
  • I want to grant permissions to the root user Sir. All the permissions are removed by mistake. I am running on localhost, and with the OS Windows. Can you please guide me in resolving this Sir – a989898 Feb 27 '15 at 21:14
  • So MySQL is running in Windows ??? – RolandoMySQLDBA Feb 27 '15 at 21:18
  • yes sir................ – a989898 Feb 27 '15 at 21:19
  • Do you have my.ini ? What version of MySQL are you running (5.1, 5.5, 5.6) ? – RolandoMySQLDBA Feb 27 '15 at 21:44
1

I have discussed situations like this before in these posts:

In your case do this

STEP #1

Add these lines just under the [mysqld] group header in my.ini

[mysqld]
skip-grant-tables
skip-networking

STEP 2

Open Windows Command Line as Administrator, and reboot MySQL Service

C:\> net stop mysql
C:\> net start mysql

STEP 3

You can login to MySQL

C:\> mysql <Hit Enter>

STEP 4

From the mysql prompt, run this

SET @PasswordHash = PASSWORD('whateverpasswordiwant');
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = @PasswordHash,
             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',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = '127.0.0.1',
              Password = @PasswordHash,
             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',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;
exit

STEP 5

Remove skip-grant-tables and skip-networking from my.ini

STEP 6

Restart MySQL

C:\> net stop mysql
C:\> net start mysql

STEP 7

Try logging into MySQL

GIVE IT A TRY !!!

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.