MySQL 5.7 – More security for your users and passwords

MySQL 5.7 reaches the RC (release candidate) stage. This means it’s time to start thinking about retiring the good, but not old, MySQL 5.6.

But before migrating, it’s interesting to take a look at the new way MySQL manages passwords. For those familiar with MySQL since the long-gone versions 3 and 4, you’ll notice some good changes.

Some things have always bothered me about users and privileges. For example, what if I want to grant access to a specific user (say, a temporary developer) to my database for only 90 days? I don’t even remember what I just had for lunch, so how would I remember after 90 days to drop that user? Sure, I could create a script… No! Enough of leaving garbage all over our servers. MySQL had to solve this—and now it has. This is just one of several new features.

Let’s look at some changes implemented in user management.

For those experienced, the way to “initialize” the MySQL datadir (data directory) will feel strange right from the start. Up to version 5.6, the script mysql_install_db was used for this purpose. From version 5.7 onwards, the mysqld itself is used:

shellCopyEditmysqld --initialize

And here’s a sweet little novelty. Simple, but useful. Remember that in versions 5.6 and earlier, when the database was initialized, the root user had a blank password, and there was an anonymous user with no password either.

Now, when you initialize the datadir, you get a gift—a password! Woo-hoo!!

pgsqlCopyEditA temporary password is generated for root@localhost: wnWdAjS9=yD%

Note that this password grants root access from the local machine (localhost). That’s already great.

Let’s get our hands dirty and create a user:

sqlCopyEditmysql> CREATE USER alexandre@localhost;
mysql> SELECT user, host, password_expired, password_last_changed, password_lifetime, account_locked, authentication_string FROM mysql.user WHERE user = 'alexandre'\G

*************************** 1. row ***************************
user: alexandre
host: localhost
password_expired:
password_last_changed: 2015-05-20 20:09:21
password_lifetime:
account_locked: N
authentication_string:

The <user> and <host> columns are old friends: storing the username and the host where access and privileges apply. Notice one column is missing in this SELECT. Also, the <password> column no longer exists in the <mysql>.<user> table. It disappeared. So, where are passwords stored now? Calm down! I’ll explain.

• <password_expired> is a new column in our yard. ‘Y’ means YES, the password is expired and cannot be used to authenticate the user. ‘N’ or blank means the password is valid.
• <password_last_changed> shows the date and time of the last password change.
• <password_lifetime> is the password’s lifespan, measured in DAYS.
• <account_locked> ‘Y’ means the account is locked and can’t be used. ‘N’ or blank means the account is valid.
• <authentication_string> — what a fancy name! This column replaces <password>. It serves the same purpose as before: storing the encrypted login password.

You can create a global password expiration policy by setting this variable (in my.cnf or my.ini):

iniCopyEdit[mysqld]
default_password_lifetime=180

Of course, you can also apply this via SET GLOBAL or SET SESSION.

Now, let’s update my user “alexandre” so that his password expires in 10 days:

sqlCopyEditmysql> ALTER USER 'alexandre'@'localhost' PASSWORD EXPIRE INTERVAL 10 DAY;

Let’s check it out:

sqlCopyEditmysql> SELECT user, host, password_expired, password_last_changed, password_lifetime, account_locked, authentication_string FROM mysql.user WHERE user = 'alexandre'\G

*************************** 1. row ***************************
user: alexandre
host: localhost
password_expired: N
password_last_changed: 2015-05-20 20:09:21
password_lifetime: 10
account_locked: N
authentication_string:

Notice that <authentication_string> is still empty. Let’s fill it — the right way! No more directly updating MySQL system tables.

sqlCopyEditmysql> SET PASSWORD FOR alexandre@localhost = PASSWORD('VaiQueColaEstaSenha@@@');

Query OK, 0 rows affected, 1 warning (0.00 sec)

Oops, a warning? You must be kidding! The syntax is perfect, the password is good—what happened?

sqlCopyEditmysql> SHOW WARNINGS;

pgsqlCopyEditWarning | 1287 | 'SET PASSWORD FOR <user> = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR <user> = '<plaintext_password>' instead

Ah… okay. So the PASSWORD() function is no longer valid? Sort of. With SET PASSWORD you don’t need it anymore:

sqlCopyEditmysql> SET PASSWORD FOR alexandre@localhost = 'VaiQueColaEstaSenha@@@';

Query OK, 0 rows affected (0.00 sec)

Awesome! But did MySQL fill the column correctly? Let’s see:

sqlCopyEditmysql> SELECT user, host, password_expired, password_last_changed, password_lifetime, account_locked, authentication_string FROM mysql.user WHERE user = 'alexandre'\G

*************************** 1. row ***************************
user: alexandre
host: localhost
password_expired: N
password_last_changed: 2015-05-20 20:15:14
password_lifetime: 10
account_locked: N
authentication_string: *70CCAFC73D595949FFC1425DF697124855ABDF5F

Note that now the password is stored correctly (without using the PASSWORD() function), and the last password change timestamp was updated.

In summary: the <mysql>.<user> table structure changed by removing one column and adding five others. You now have the ability to control password expiration and lock accounts.

We will explore more MySQL 5.7 user management features in the next article.

Schedule a meeting here

Visit our Blog

Learn more about databases

Learn about monitoring with advanced tools

Have questions about our services? Visit our FAQ

Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!

Discover the History of HTI Tecnologia