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.
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.