0

please help!...

I am facing a huge problem..i had access to mysql database through MySQL workbench but one of my coworkers accidentally went on to users and privileges and revoked something for something...he thought he was deleting another user.

Now I can't access my database on mysql workbench and all my privileges are gone....i am able to login to mysql on bash via mysql -h 127.0.0.1 -u root -p

but i can't seem to add or get my privileges back......i am logging through root on terminal...also my website is down because of this. test 1:

         mysql> show grants for 'root';
         ERROR 1141 (42000): There is no such grant defined for user 'root' 
         on host '%'
         mysql> SHOW GRANTS FOR 'root'@'localhost';
      +--------------------------------------------------------------+
      | Grants for root@localhost |
      +--------------------------------------------------------------+
      | GRANT USAGE ON . TO 'root'@'localhost' |
      | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
      +--------------------------------------------------------------+
      2 rows in set (0.00 sec)

**test 2:
mysql> select host, user from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'
ERROR 1142 (42000): SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFEREN CES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER command denied to user 'root' @'localhost' for table '*'**

also I have no privileges, it seems.

          mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
          ERROR 1227 (42000): Access denied; you need (at least one of) the 
          CREATE USER privilege(s) for this operation

also there doesn't seem to be any grant here

Please help, I have no access to my database and my website is down..

  • similar question on stackoverflow – user193597 Jun 21 '18 at 23:15
  • Hi @Wumpus,even after stopping mysql and restarting with skipping grant tables it still asks for password? also running the command show databases, i get only information schema and nothing else..all the other privileges are still; denied..please help...i'd really appreciate it. – jamen Jun 22 '18 at 15:55
0

Like mysql manual reset permissions(adjust URL to server version). Instead of ALTER USER / SET PASSWORD you'd need CREATE USER root@localhost IDENTIFIED BY "password"; GRANT ALL ON *.* to root@localhost WITH GRANT OPTION in the text file as your root user has been removed. Also recommend creating another user for your trusty co-workers to use instead of root.

| improve this answer | |

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.