CODEWITHSUNDEEP
mysqlprivileges
Steven
Steven

Reputation: 25284

How to get all privileges back to the root user in MySQL?

I have logged in to MySQL with the --skip-grant-tables option. But I don't know how to get all privileges back to the root user.

I tried:

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

MySQL said:

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

Trying:

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

MySQL said:

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

Upvotes: 14

Views: 94922

Answers (6)

John
John

Reputation: 7826

For mysql 8.0.12+
I've tried the documentation (not working) I've tried various other options, all failed.

PASSWORD() is deprecated, SET PASSWORD is disabled, ALTER USER is disabled as well.

  1. stop mysql service
  2. Start it this way: /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
  3. Login (mysql) and exec: use mysql; update user set authentication_string='' where User='root';
  4. killall mysqld
  5. Start service again

Now you can use set password or alter user after logging in without a password

Upvotes: 25

Mansur Ul Hasan
Mansur Ul Hasan

Reputation: 3606

First, stop the MySQL server and then start it with the --skip-grant-tables option.

[root@backups1 mysql5.7]# /etc/init.d/mysqld stop
[root@backups1 mysql5.7]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql

Then, connect to your instance without a password:

[root@backups1 mysql5.7]# mysql -u root

Then, reset the password for the root user.

mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;

Switch to normal mode of MySQL then connect without password.

Upvotes: 7

user18099
user18099

Reputation: 663

If the goal of your --skip-grant-tables is insert a new Grant, you can do so by inserting a line in mysql.user (see other answers)

If your challenge is in giving this new Grant all the required privileges (many Y/N colums), then you can copy an existing root grant. And adjust only what you require.

CREATE TEMPORARY TABLE mysql.tmpUser SELECT * FROM mysql.user WHERE host="old" and user="root";
UPDATE mysql.tmpUser SET host="new" WHERE user = "root";
INSERT INTO mysql.user SELECT * FROM mysql.tmpUser;

Upvotes: 1

cyb0k
cyb0k

Reputation: 2718

If you cant acces to mysql server as a root you should delete or cannot restore all root records in mysql.user table, delete all of them and add a new record.

You should use mysql PASSWORD() function to hash your cleartext password. check for more information http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

First stop mysql server and launch mysqld with --skip-grant-tables.

[root@mysql ~]# service mysqld stop; mysqld_safe --skip-grant-tables &

Hash your cleartext password.

mysql> select PASSWORD('CLEARTEXT_PASSWORD');
+-------------------------------------------+
| PASSWORD('CLEARTEXT_PASSWORD')            |
+-------------------------------------------+
| *1EADAEB11872E413816FE51216C9134766DF39F9 |
+-------------------------------------------+
1 row in set (0.00 sec)

Use mysql database to apply changes.

mysql> use mysql;

Delete all root records

mysql> delete from user where User='root';

Add new record that root user can access with all privileges from your ip adress.

mysql> insert into `user` VALUES('YOUR_IP_ADDRESS','root','*1EADAEB11872E413816FE51216C9134766DF39F9','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','Y','Y','Y','','','','',0,0,0,0,'','');

Flush changes.

mysql> FLUSH PRIVILEGES;

Exit from mysql command line .

mysql> exit;

Restart mysqld service.

[root@mysql ~]# /etc/init.d/mysqld restart

if you run this mysql commands/ queries you will get a new access to mysql server.

Upvotes: 8

RageZ
RageZ

Reputation: 27313

When you run mysql using --skip-grant-tables mysql won't check any permissions. So basically you can do anything.

To get back the root privileges you would need to run a query in mysql DB like this

select * from user where user = 'root'

just to check if the root user is still there if ok:

UPDATE user SET Grant_priv = 1, Super_priv = 1 WHERE user = 'root'

after you can restart mysql without the --skip-grant-tables and the root user should be able to do some grant so your query should work

Upvotes: 5

Alex Martelli
Alex Martelli

Reputation: 881555

To run a GRANT query, you just don't run mysql with skip-grant-tables -- what's complicated about that...?

Upvotes: -10

Related Questions