Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

According to the MySQL documentation, you can harden a MySQL server by adding passwords, or removing the anonymous accounts.

If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts.

Before hardening, my users table looked like this.

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX |
| root             | gitlab    |                                           |
| root             | 127.0.0.1 |                                           |
| root             | ::1       |                                           |
|                  | localhost |                                           |
|                  | gitlab    |                                           |
| debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX |
| myuser           | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX |
+------------------+-----------+-------------------------------------------+

I've remove all anonymous accounts, so that the user table now looks like this. (I'm using puppet to manage the users, but puppet effectively performs a DROP USER command).

mysql> select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *F3A2A51A9B0F2BE246XXXXXXXXXXXXXXXXXXXXXX |
| debian-sys-maint | localhost | *95C1BF709B26A5BAXXXXXXXXXXXXXXXXXXXXXXXX |
| myuser           | localhost | *6C8989366EAF75BB6XXXXXXXXXXXXXXXXXXXXXXX |
+------------------+-----------+-------------------------------------------+

Why is it that I am still able to login to my test system without a username or a password?
What do I need to do to prevent any unwanted users from logging in?

root@gitlab:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)
....
mysql> 

Update: I've also just discovered that I can login as root, without entering a password.

Update2: I found this question which has some good information, but does not solve the issue.

There are no anonymous users.

mysql> select user,host,password from mysql.user where user='';
Empty set (0.00 sec)

I login as root@localhost.

mysql> select USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+ 

I do not have a default password, or skip-grant-tables defined in my.cnf

root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'skip-grant-tables'|wc -l
0
root@gitlab:~# cat /etc/mysql/my.cnf |grep -i 'pass'|wc -l
0

Update3:

I have tried performing these steps with puppet, (which should perform a flush privileges automatically). I have also manually flushed privileges and also tried restarting mysql.

Update4:
I've also tried changing the mysql root password and flushed privileges. No luck, I can still log in as any user without a password.

share|improve this question
    
Also see stackoverflow.com/q/7179894/632951 – Pacerier Jan 23 '15 at 14:05
up vote 4 down vote accepted

Figured it out!

While /etc/mysql/my.cnf did not have a password keypair stored, There was a password stored in

/root/.my.cnf.

As soon as I commented out the password in /root/.my.cnf I was blocked from logging in without a password. (Which is what I expected)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.