0

I accidentally removed all privileges for root user in mysql database user table.

By helping best at MySQL Error: : 'Access denied for user 'root'@'localhost' I can now access database but I cannot grant all privileges to my root user at localhost.

Via PHPMyAdmin interface when I go and try to add, I get this error:

#1290 - The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

But I cannot access database and phpmyadmin without skip-grant-tables option.

This is the query runs but returns error:

GRANT ALL PRIVILEGES ON  *.* TO 'root'@'localhost' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 2147483647 MAX_CONNECTIONS_PER_HOUR 2147483647 MAX_UPDATES_PER_HOUR 2147483647 MAX_USER_CONNECTIONS 2147483647;

What should I do?

Saeed
  • 3,255
  • 4
  • 17
  • 36
  • Any reason for withholding the exact error message you received? – Shadow Dec 15 '21 at 09:47
  • Error message shows that you use MariaDB, not MySQL. This is another DBMS, similar, of course, but different nevertheless. – Akina Dec 15 '21 at 09:47
  • @Shadow this is the exact and the only error I got – Saeed Dec 15 '21 at 09:48
  • @Akina yes that's mariadb. But not sure if they differ because syntax is the same as I know – Saeed Dec 15 '21 at 09:49
  • Find another MariaDB of the same version. Execute`SHOW GRANTS FOR root@localhost;` on it. Copy the returned GRANT statements completely. Execute them on your server while `--skip-grant-tables`, then `FLUSH PRIVILEGES;` and restart server without the option `--skip-grant-tables`. – Akina Dec 15 '21 at 09:50
  • @Akina `MariaDB [(none)]> show grants for root@localhost;` is the command, and `ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement` is the error in terminal – Saeed Dec 15 '21 at 09:53
  • @Akina also `flush privileges; Query OK, 0 rows affected (0.025 sec)` – Saeed Dec 15 '21 at 09:54
  • :facepalm: One more time: *Find **another** MariaDB of the same version.* Non-broken MariaDB server. – Akina Dec 15 '21 at 09:54
  • @Akina what do you mean? – Saeed Dec 15 '21 at 09:55
  • Either find another server (of the same or maximally close but not below version) and look what privileges set must be assigned or install another MariaDB instance (in VM, for example) and copy needed privileges set. – Akina Dec 15 '21 at 09:56
  • @Akina the issue is I cannot grant privileges:( – Saeed Dec 15 '21 at 09:57
  • 1
    When you start the server with `--skip-grant-tables` it disables account-management statements. You need to `FLUSH PRIVILEGES` before account management statements will work. https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html#resetting-permissions-generic – user1191247 Dec 15 '21 at 10:26
  • @nnichols I cannot login to `mysql` without `--skip-grant-tables` options – Saeed Dec 15 '21 at 10:33
  • 1
    I didn't say to start mysqld without `--skip-grant-tables`? – user1191247 Dec 15 '21 at 10:41
  • @nnichols sorry I did not understand your meaning. Should I start `mysql` without `--skip-grant-tables`? – Saeed Dec 15 '21 at 11:28
  • Did you manage to run all the GRANTS for root@localhost? – user1191247 Dec 15 '21 at 12:13
  • @nnichols yes I did – Saeed Dec 15 '21 at 12:51

0 Answers0