The password features in PHP aren't exactly new, but I see lots of applications from "before" which aren't being migrated to better practices. I have some strategies for doing these migrations so I thought I'd share my main approach, plus a similar-but-different one I saw in the wild (OK it was in CakePHP, so not too wild!).

The examples here assume that you currently have either unsalted or all-with-the-same-salt passwords stored in your database, hashed with md5 or sha1 or something. This is a Very Bad Idea (TM) since it's trivial to recover unsalted passwords and not all that hard to figure out same-salted ones.

Enter the password_hash() and password_verify() functions which were included by default in PHP 5.5 but are also available for PHP 5.3.9+ via a userland implementation (see https://github.com/ircmaxell/password_compat). …

There are many thing changed in MySQL 5.6 which are related to passwords:

• There is a new password hash algorithm (SHA-256)
• There is obfuscation for passwords with the .mylogin.cnf file.
• The option to store slave passwords in a database table.
• It's now possible to supply a password to START SLAVE.

But that's not what this blog post is about.

This blog post is a great new feature: Hiding passwords from your log files, automatically.

MySQL 5.6 will by default hide passwords from the general log. This is not just obfuscation as only the one-way hash will be put in the log files. By setting log-raw=OFF you can disable password hiding for the general log. The log-raw setting will only influence the general log, so the passwords in the slow query log and the binary logs will still be hidden.

With MySQL 5.5 this could be done manually by …

What happens when you use the PASSWORD() function to insert a password hash into a table?

• The hash will be written to the table
• The password might be written in clear text to the binlog
• The password might be written in clear text to the general log
• The password might be written in clear text to the slow query log

The query

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));
Query OK, 1 row affected (0.00 sec)

The General log

130128 16:04:41     1 Query     INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))

The Slow query log

# Time: 130128 16:04:41
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.004887  Lock_time: 0.001043 Rows_sent: 0  Rows_examined: 0
SET …

In case you missed it, MySQL 5.6.6, also known as Milestone 9, was recently released. I have yet to install this, however just one part of the MySQL 5.6.6 Release Notes makes placing installing and testing high on my TODO list.

Updated 20 Sep, 2012. Be sure to also read Todd’s post Understanding mysql_config_editor’s security aspects about a more in-depth and accurate description of this new feature. In summary, “It makes secure access via MySQL client applications easier to use”.

That is the reported improvements in password management. From the release notes:

Security Improvements

These security improvements were implemented:

MySQL now provides a method for …

Lenz Grimmer recently wrote two blogs about password security on MySQL. Both are worth reading in detail. You’ll find them in Basic MySQL Security: Providing passwords on the command line and More on MySQL password security.

Although I wrote a comment on the latter one, there is one point I thought was worth its own blog.

GRANT … IDENTIFIED BY PASSWORD…

You can work around having to specify the password in the open following these steps:

1. Use a local or non-public instance (for example using MySQL Sandbox) to define the user / password combination you need:

   CREATE USER 'name'@'host' IDENTIFIED BY 'secret';

2. Use SHOW GRANTS to …

I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is “no”. The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.

From http://forums.mysql.com/read.php?146,169265,169700

The clear-text password is _never_ transfered in the authentication phase.

On the network we have:
* client connects to server (no data)
* server sends a seed (40 char, one-time, random)
* client sends 40 char hash of (seed + PASSWORD(clear-text-password))
* server compares against the hash(seed + SELECT password FROM mysql.user WHERE username = )

That way we never have the password as clear-text on the wire. (only in SET PASSWORD or GRANT statements).

As a rule I always execute the following commands on a fresh database installation. Then for each user that is granted privileges, of course they are given a password and the host permissions are locked down as much as possible for their needs, alternately table and column privs as well. I’m not going to get into the parts the manual covers, but rather mention a couple of things on my mind.

First the initial commands:


mysql> use mysql
mysql> delete from user where User='';
mysql> delete from db where User='';
mysql> update user set Password=password('password_here') where User=’root’;
mysql> flush privileges;


However, one thing I’ve noticed is that when you hand over a server to someone that doesn’t necessarily follow your same understanding or regard to user privilege security, bad things can happen. Such as users created without a password. …

I do realize that for most of you, there may be nothing new about the dbms_sys_sql package ? knowledge of it has been floating around for quite a while. I myself discovered this package a couple of years ago while playing around with HTMLDB’s (now APEX’s) internals. I’m posting this as a response to a [...]