MySQL 5.7 Security in Detroit

July 13, 2016

I will be speaking at the Converge Security Conference on July 14th on MySQL 5.7 security. There were many changes in 5.7 and it has been very hard to get the majority of those changes in a forty minute presentation. And is with just scratching the surface with the new encryption for InnoDB. If you are are attending please say ‘hi’!


Log Changes with MySQL 5.7

June 30, 2014

Most MySQL-ers quickly learn to move logs out of the data directory. Hopefully the logs are being written to a different disk, on a different controller than where the data is being kept. The horror of finding you database server dead to the world because the single partition used for everything was filled up by the error log should be a thing of the past. MySQL 5.7 will give DBAs better control of log files,

As of 5.7.2, we have gained the ability to control the verbosity of error messages with log_error_verbosity. This system variable controls verbosity in writing error, warning, and note messages to the error log. A value of 1 provides errors only, 2 adds warnings, and 3 adds notes. The default value is 3. And with that with level 3, aborted connections and access-denied errors for new connection attempts are written to the error log.

The good ol’ log_warnings is being deprecated in favor of the added flexibility from log_error_verbosity.

And with MySQL 5.7.2, the log_timestamps system variable lets you set the timestamp time zone of messages written to the error log, the general query log, and slow query log files. The choices are UTC (the default) and SYSTEM (local system time zone). Previously messages use the local system time zone.

Please note that as of MySQL 5.7.2, the ID included in error log messages is that of the thread within mysqld responsible for writing the message. This indicates which part of the server produced the message, and is consistent with general query log and slow query log messages, which include the connection thread ID. Earlier releases use the ID of the mysqld process in error log message.


MySQL 5.7 user table: password_last_changed & password_lifetime

April 14, 2014

MySQL 5.7.4 has added two fields to the mysql.user table — password_last_changed, a timestamp and password_lifetime, a small but unsigned integer. Several blogs ago I started to cobble together a password expiration tracking script before these two columns were added. But I could see three ways of tracking expired passwords but none of them were palatable. Todd Farmer was working on a similar idea.

So when you run mysql_upgrade after upgrading to 5.7.4, you will find these two new columns. The password_last_changed will be set to the time you ran the upgrade and password_lifetime will be set to null.

You can set global password lifetime policy in the options file.
[mysqld]
default_password_lifetime=180

So 180 is about six months and zero would set a never expire policy.

ALTER USER 'dave'@localhost' PASSWORD EXPIRE INTERVAL 90 DAYS;
ALTER USER 'john'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jane'@'localhost' PASSWORD EXPIRE DEFAULT;


How to get MySQL Critical Patch Updates and Security Alerts notices

January 22, 2014

Beware of bugs in the above code; I have only proved it correct, not tried it.
Donald Knuth

Bugs in software are a fact of life. MySQL, as part of Oracle, issues of Critical Patch Updates and Security Alerts notices. You may have seen Daniel van Eeden‘s blog on the January announcement.

Daniel’s summary:

For MySQL 5.6 you should upgrade to 5.6.15
For MySQL 5.5 you should upgrade to 5.5.35
For MySQL 5.1 you should upgrade to 5.1.73

But you probably missed the executive summary.

But how do YOU get this information when it become available? Subscribe here for Critical Patch Update Alert E-mails. You will need an Oracle Technology Network account (free) and please note that there are more than just MySQL information in the alerts as it covers all Oracle products.

Example of subscribing for alerts

Example of subscribing for alerts

It will take you just a few moments to sign up.