CODEWITHSUNDEEP
mysqldatabasesql-grant
a989898
a989898

Reputation: 13

All users are having NO Privileges in phpMyAdmin

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.

Upvotes: 1

Views: 4419

Answers (1)

Leandro Papasidero
Leandro Papasidero

Reputation: 3738

This is for XAMPP

Log in as root, then run the following MySQL commands:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

If you get an error please try this:

  1. Stop mysqld

enter image description here

  1. Restart mysql with the --skip-grant-tables option.

    • Add the two options in the mysqld section of my.ini:

my.ini

[mysqld]
skip-grant-tables
skip-networking

and then run Open command prompt windows >> Command prompt

net stop mysql (wait 10 seconds)
net start mysql

  1. Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).

    • Open command prompt windows >> Command prompt
    • Type cd C:\xampp\mysql\bin
    • Type mysql

Issue the following commands in the mysql client:

insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) 
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

Reference: How can I restore the MySQL root user’s full privileges?

Upvotes: 1

Related Questions