Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 anonomous 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 log in as root, without entering a password.

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

Theere are no anonymous users

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

I log in 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
    
Did you do a FLUSH PRIVILEGES; or restart? –  Phil Oct 14 '13 at 0:31
    
@Phil Thanks for bringing that up. I updated the question. Yes I have restarted mysql and also flushed privileges. –  spuder Oct 14 '13 at 0:34

1 Answer 1

up vote 2 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
    
You should accept you answer, it helps with search results. –  booyaa Dec 13 '13 at 9:01
1  
Thanks for the reminder booyaa, forgot about this one. –  spuder Dec 13 '13 at 23:48

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.