Password Expiration for MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Authentication for MariaDB Xpand
Topics on this page:
Overview
MariaDB Xpand supports enabling password expiration with global and user-level settings.
Compatibility
Xpand Series | First Supported Version |
|---|---|
5.3 | N/A |
6 | 6.0.4 |
Password Expiration
The password expiration settings for a user are set when the user is created and can be changed when the user is altered. There is also a global default expiration value in the default_password_lifetime global variable that controls any user that is set to honor the global expiration default.
If a user has expiration enabled and the expiration period elapses before a new password is set, the user will be disconnected when trying to login. When a user's password is changed, the expiration countdown clock is reset and a new countdown begins.
The root user and certain internal/replication users are set to use PASSWORD EXPIRE NEVER. It is best to leave these unchanged to avoid losing access to your database or breaking replication.
By default, regular users get the PASSWORD EXPIRE DEFAULT setting and are governed by the default expiration settings.
Enable Password Expiration
Password expiration values can be set individually for each user, globally with the default expiration value, or with a combination of the two.
The current implementation of password expiry does not include sandbox support.
Individual Expiration
When a user is created, an optional expiration clause can be specified:
Clause | Effect |
|---|---|
| The numeric value |
| The user's password never expires, overriding the global default |
| The user's expiration settings are controlled by the |
If the PASSWORD EXPIRE clause is omitted, the user is created with an implied PASSWORD EXPIRE DEFAULT setting.
The expire interval is always expressed in days, and no other interval unit is accepted in this context.
CREATE USER 'joe'@'localhost' PASSWORD EXPIRE 42 DAY;
Password expiration settings can be changed using an ALTER USER command and the PASSWORD EXPIRE clause choices shown above.
ALTER USER 'joe'@'localhost' PASSWORD EXPIRE NEVER;
The root user and certain internal/replication users are set to use PASSWORD EXPIRE NEVER. It is best to leave these unchanged to avoid losing access to your database or breaking replication.
Regular users get the PASSWORD EXPIRE DEFAULT setting by default and are governed by the default expiration settings.
Default Expiration
Any user that has PASSWORD EXPIRE DEFAULT set is controlled using the global value of the default_password_lifetime variable, which defaults to 0. When set to a positive value, all default-expiration users are configured to expire a user's passwords after that count of days has elapsed without a new password being set. Setting the value to 0 indicates the default is for default-expiration users to never expire their passwords.
The default_password_lifetime value can be set with a SET statement as long as the user has the SUPER privilege:
SET GLOBAL default_password_lifetime = 30;
Note that the password-related variables don't have a separate session value in addition to the global value, so changing a global password variable causes the new value to be applied to all sessions.
Show Expiration Settings
To see the expiration settings for a user, use the SHOW CREATE USER command for individual users or run a select system.users to see them all. For example, a new installation typically starts with an xpand user with an empty password:
SHOW CREATE USER 'xpand'@'localhost';
+-----------------------------------------------------------------------------------------------------+
| CREATE USER 'xpand'@'localhost' |
+-----------------------------------------------------------------------------------------------------+
| CREATE USER 'xpand'@'localhost' IDENTIFIED WITH mysql_native_password AS '' PASSWORD EXPIRE DEFAULT |
+-----------------------------------------------------------------------------------------------------+
While two new columns in the system.users table contain information on expiration settings:
SELECT username, host, pw_last_changed, pw_lifetime FROM system.users;
+------------------+-----------+----------------------------+-------------+
| username | host | pw_last_changed | pw_lifetime |
+------------------+-----------+----------------------------+-------------+
| root | 127.0.0.1 | NULL | 0 |
| mysql_slave | | NULL | 0 |
| clx_maint | 127.0.0.1 | NULL | 0 |
| clx_view_definer | 127.0.0.1 | NULL | 0 |
| xpand | localhost | 2022-05-11 19:11:41.869268 | -1 |
| xpandm | localhost | 2022-05-11 19:11:42.141268 | -1 |
| tester | % | 2022-05-11 19:47:40.043196 | 90 |
+------------------+-----------+----------------------------+-------------+
In the output above, a pw_lifetime value has these meanings:
| Meaning |
|---|---|
-1 |
|
0 |
|
n |
|