Saturday, January 05, 2013

Using a password is insecure, but no password is OK?

I have been preaching since 2003 that the default deployment of MySQL (where root can access without password) should be changed to something more sicure.

Yet, MySQL 5.6 still uses the same defaults.

$ mysql --no-defaults -u root --port=5000 -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> set password=password('oh-come-on');
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
I have installed MySQL 5.6. Now I access as root without password. Not a word of complaint. Not a warning. Nothing.
But what happens when I set a password and use it?

$ mysql --no-defaults -u root --port=5000 -h 127.0.0.1 -poh-come-on
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.9-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> 

WTF? So a password is insecure, but no password is OK?
I know the risks of using a password at the command line, thanks for telling me. Now I don't want to see this message anymore.

I wonder how I can remove this warning. Scripted testing gets borked horribly with it.

10 comments:

Todd said...

The easiest solution is to store the password in an options file or use the mysql_config_editor to store the password.

water outbreaks said...

I'm sure you're aware of this, being the charmer of Data, but for completeness sake:

Many companies log bash history. So the one including the password would be insecure as the password would be stored plain text.

If you just left the -p blank, it prompts you with echo off to type the password. In which case with bash logging, your password would not be stored plain text.

I think its a legit warning. Of course, the first case without password is also insecure unless access is restricted in some other way outside of mysql.

Kristofer Pettersson said...

The warning is not really about how easy it is to break into your account. It is about involuntary exposure of your password. If you use a password on the command line you might think it is protected, but it isn't.

On the other hand, if you don't use a password, there is no reason for you to believe your password is protected hence no warning is necessary.

The reason you keep seeing this warning is that you persist in using an somewhat insecure practice as demonstrated by aggregated statistics by professionals in the field of breaking into stuff. ;-)

Giuseppe Maxia said...

Thanks to my former colleagues who volunteered explanations.
Just for the record, in the last 10 years, I have learned one or two things about MySQL passwords, and the meaning of this warning did not escape me.
What escapes me is why this warning is compulsory.

In a permanent environment, either production or development, I use options files.
But for the kind of testing that I do, where I create and destroy database servers on the spot, I need to use the --password option on the command line, at least once.

BTW, what screws up my tests is this line:

mysql --defaults-file=something.cnf --password="" --user=root < grants.sql

It's the statement that I use to create the password (which will be used from an option file from that moment on), and I want to make sure that no password is picked from anywhere. As you can see, there is NO PASSWORD, but I get the annoying warning all the same.

Therefore, my knowledgeable friends, how do I tell MySQL to stop nagging me?

Todd said...

Hi Giuseppe!

So, I'm assuming that you have a password defined in something.cnf that you want ignored when connecting the first time. One way to prevent using the password in the something.cnf file is to leverage the priority that .mylogin.cnf (the file produced by mysql_config_editor) has over normal configuration files, and create an entry with no password:

C:\mysql-5.6.8-rc-winx64>bin\mysql_config_editor.exe set --login-path=no_pass --password
Enter password:

C:\mysql-5.6.8-rc-winx64>bin\mysql_config_editor.exe print --all
[no_pass]
password = *****

Even though it might look like a password was entered, it was not - it was left blank.

Then, you can use this to connect to the server, while using all other connection attributes defined in something.cnf:

C:\mysql-5.6.8-rc-winx64>bin\mysql --login-path=no_pass --defaults-file=test.ini

Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Proving that the test.ini file has a password which would prevent access otherwise:

mysql> exit
Bye

C:\mysql-5.6.8-rc-winx64>bin\mysql --defaults-file=test.ini
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Hope that helps.

Giuseppe Maxia said...

Todd,
Thanks for the tip.
However, mysql_config_editor is not safe to use when installing multiple instances, as it always writes in $HOME/.mylogin.cnf.

What would help is the resolution of Bug#68034 which is an exasperation of what I am complaining about in this post.

Todd said...

Giuseppe,

Surely mysql_config_editor is "safe" to use if only being used to override a password with a blank password, no? As my example shows, you don't need to use MCE for any other configuration values, and a blank password is a blank password. Whether you feel MCE is safe for storing other connection attributes is a separate discussion.

Yes, I'm aware it's not exactly how you would like to see this behave - it's not an option that silences the warning. On the other hand, the warning is not likely to interfere with most use cases (and adds value for users who may not thought about security as much as you have), and MCE provides a meaningful (and preferred) workaround to supplying passwords in clear text as command-line arguments. In the same way you leveraged command-line arguments having priority over whatever is in something.cnf, MCE now allows you to do the same with .mylogin.cnf.

Tempor said...

I am coming too late into this conversation, but what if you used 2>/dev/null at the end of the line (if not concerned about errors or warning) or (better) 2>/path/to/some/logfile.log?

I have successfully used either with some automated bash scripts.

Giuseppe Maxia said...

@Tempor,
I can certainly redirect STDERR, but I get two problems with that:
1) existing scripts still need to be changed to apply this rule;
2) redirecting STDERR means that I won't see legitimate errors when they happen, and thus I will need to parse the log to detect the failure.

In short, it's a lot of effort for something that really should not have happened. What would make life easier is an option such as skip-command-line-password-warning, which could be conveniently placed in my.cnf

Tempor said...

Completely understandable. I faced this problem when I started writing scripts so the solution for me worked (no going back to change everything).

The frustration is still there though. Version 5.6.19 and still no option to bypass the warning!

You can always create an alias to bypass it (and therefore not change your scripts):

$ alias mysql='mysql 2>/dev/null'

Just tested it and it works.

Vote on Planet MySQL