mysql.user


Every MySQL DBA has at least peeked at a mysql.user table. But with the latest versions come some changes that many may have not noticed. The last three of the forty three columns — plugin, authentication_string, and password_expired — fields deserve a closer look.

First off, lets look at the entire table that is the output of DESC mysq.user run in MySQL Workbench and only the last few lines are shown for the sake of clarity.
mysqluser01

The password_expired field is simply set to ‘N’ if the password is expired.
ALTER USER 'joeuser'@'localhost' PASSWORD EXPIRE;
The use will receive a message that their password has expired and they need to set a new one IF their client supports resetting password. The account is is “sandbox” mode where the use has only the privileges needed to reset the password. Using SET PASSWORD will turn off the password expired flag. See http://dev.mysql.com/doc/refman/5.6/en/password-expiration.html

What if the client being used can not handle “sandbox mode”? That depends on your setting for disconnect_on_expired_password. By default this setting is on and the server will reject the connection with an ER_MUST_CHANGE_PASSWORD error. See http://dev.mysql.com/doc/refman/5.6/en/password-expiration.html

The authentication_string and plugin settings work together for Authentication Plugins (http://dev.mysql.com/doc/refman/5.6/en/authentication-plugins.html). The plugin named is then used to authenticate the user by communicating with a plugin on the server. The authentication_string provides information on how to pass information to the server side plugin. This allows DBAs to let users authentication via LDAP, PAM, Windows auth service, or a custom written plugin.

And it lets DBAs use proxies for authentication and I will go into that in a future posting.

About these ads

1 Comment

Filed under MySQL

One response to “mysql.user

  1. Please see Bug 70742, 70744 and 70745. These all are related to the updated mysql.user table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s