MySQL login security

[Mark Knecht]

It appears that my current MySQL setup is allowing logins too easily. For instance, if I try to log in as 'evelyn' from within my shell it's not allowed: (GOOD!)

mark@c2RAID6 ~ $ mysql -u evelyn
ERROR 1045 (28000): Access denied for user 'evelyn'@'localhost' (using password: YES)
mark@c2RAID6 ~ $ mysql -u evelyn -p
Enter password:
ERROR 1045 (28000): Access denied for user 'evelyn'@'localhost' (using password: YES)
mark@c2RAID6 ~ $

However if I ssh into the machine as evelyn I can log into MySQL:

mark@c2RAID6 ~ $ ssh evelyn@localhost
The authenticity of host 'localhost (127.0.0.1)' can't be established.
ECDSA key fingerprint is ba:23:c1:07:7b:25:98:66:0e:d8:90:13:08:5b:5b:ae.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'localhost' (ECDSA) to the list of known hosts.
Password:
evelyn@c2RAID6 ~ $ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 170
Server version: 5.1.70-log Gentoo Linux mysql-5.1.70

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye
evelyn@c2RAID6 ~ $


I suspect this might be due to sort of 'anonymous' accounts existing in MySQL? I.e.:
(NOTE: I have a .my.cnf file in my account with my login info...)

mark@c2RAID6 ~ $ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 172
Server version: 5.1.70-log Gentoo Linux mysql-5.1.70

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT host,user,password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |
| c2RAID6 | root | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |
| 127.0.0.1 | root | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |
| localhost | | |
| c2RAID6 | | |
| localhost | mark | *E8F1FB8D711EFE40BEE77B5E9EC940D7C1740E15 |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql>


What's the proper way to configure MySQL so that all logins at least require a real user and password?

Thanks,
Mark

[Mark Knecht]

Following on, but not sure if I'm looking in the right places:


mysql> SHOW GRANTS FOR 'mark';
ERROR 1141 (42000): There is no such grant defined for user 'mark' on host '%'
mysql> SHOW GRANTS FOR 'mark'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for mark@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mark'@'localhost' IDENTIFIED BY PASSWORD '*E8F1FB8D711EFE40BEE77B5E9EC940D7C1740E15' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'evelyn'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'evelyn' on host 'localhost'
mysql> SHOW GRANTS FOR ''@'localhost';
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

mysql>

So it seems that 'evelyn' is not a user, but I do grant 'USAGE' to anonymous users @ localhost?

Is this the thing I want to solve? (I think it is but want to tread carefully...)

Thanks in advance!

[Mark Knecht]

So it seems I could drop the anonymous login WRT localhost using

mysql> drop user ""@localhost;


which now leaves me with

mysql> SELECT host,user,password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |
| c2RAID6 | root | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |
| 127.0.0.1 | root | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |
| c2RAID6 | | |
| localhost | mark | *E8F1FB8D711EFE40BEE77B5E9EC940D7C1740E15 |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> quit
Bye
mark@c2RAID6 ~ $ ssh evelyn@localhost
Password:
evelyn@c2RAID6 ~ $ mysql
ERROR 1045 (28000): Access denied for user 'evelyn'@'localhost' (using password: NO)
evelyn@c2RAID6 ~ $ mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user 'evelyn'@'localhost' (using password: YES)
evelyn@c2RAID6 ~


Not sure about the c2RAID6 version but I don't seem to be able to remove that one...