Skip to content

# Dangers of skip-grant-tables

MySQL Configuration Security November 13, 2008December 13, 2008

When MySQL’s root password is lost and must be reset, there are two popular ways to create a new password. One of the options is far too popular, in my opinion.

The preferred way of setting a root’s password is by using an init-file. The process for doing this is well explained in MySQL’s manual. Using this method requires creating a simple text file, in which the required

`GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFY BY '****' WIth GRANT OPTION;`

(or, alternatively, `SET PASSWORD ...`) statement is written.

An entry must be written to my.cnf, or supplied via command line parameters:

`init-file=/tmp/my-init-file.sql`

MySQL must then be restarted. Upon restart, and before opening any outside connections, the init-file is executed. Once MySQL is up and running, the init-file entry should be dropped.

#### The bad way

For some reason, the following method seems to be far more popular: starting MySQL with `--skip-grant-tables`.

When MySQL is started with this parameter, it completely avoids checking its grant tables upon connection and upon query. This means anyone can log in from anywhere, and do anything on the database.

While the manual does mention this is a less preferred way of doing it, it does not elaborate. Starting MySQL with this parameter is a huge security breach. This is why one may wish to add the `--skip-networking` parameter, to only allow connection from the localhost (using Unix socket, for example).

Moreover, after MySQL starts, and the necessary `GRANT` or `CHANGE PASSWORD` take place, the server is still unsuitable for connections. This is why it needs to be restarted again, this time without `--skip-grant-tables`.

So, `init-file`: one restart; no security issues. `skip-grant-tables`: two restarts, security breach possible. We have a winner.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
6
Leave a Reply

6 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Scott

(psst…IDENTIFIED BY, not IDENTIFY BY)

The reason the latter approach is so popular is that everybody is able to make it work. Countless folks show up on #mysql on Freenode and say they’ve tried the init file and can’t make it work; we have them try the skip-grant-tables, and a minute later they’re in. I don’t know why the first method proves so difficult – maybe it’s because the manual shows it in 6 or 8 steps that scroll off the screen, but the skip-grant-tables is only 3 steps that all fit in one screen.

Admin
admin

Hi Scott,
I agree that the steps described in the manual seem somewhat overwhelming. I always found the init-file to be an easy and fast setup. The general practice, however, shows that people think otherwise.

Shlomi

VirtualMinds » Blog Archiv » Restaurar password de root en MySQL

[…] vía https://shlomi-noach.github.io/blog/?p=102 un par de formas de restaurar la contraseña de MySQL. Normalmente siempre se suele usar con la […]

Guest
Tansu CABACI

Nicely .

Guest
sylar

Nice post！
if you only use skip-grant-tables, you can execute “flush privileges” and remove the skip-grant-tables form my.cnf to bring back the privileges system(mysql 5.6)。

if skip-networking is added to my.cnf, you have to restart the mysqld server.

MySQL忘记root密码–不重启mysqd重置root密码 | DBA的罗浮宫

[…] Dangers of skip-grant-tables […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.