Rechercher dans le manuel MySQL
6.2.15 Password Management
MySQL supports these password-management capabilities:
Password expiration, to require passwords to be changed periodically.
Password reuse restrictions, to prevent old passwords from being chosen again.
Password verification, to require that password changes also specify the current password to be replaced.
Dual passwords, to enable clients to connect using either a primary or secondary password.
Password strength assessment, to require strong passwords.
The following sections these capabilities, except password
strength assessment, which is implemented using the
validate_password
plugin and is described in
Section 6.4.3, “The Password Validation Component”.
MySQL implements password-management capabilities using tables
in the mysql
system database. If you upgrade
MySQL from an earlier version, your system tables might not be
up to date. In that case, the server writes messages similar to
these to the error log during the startup process (the exact
numbers may vary):
[ERROR] Column count of mysql.user is wrong. Expected
49, found 47. The table is probably corrupted
[Warning] ACL table mysql.password_history missing.
Some operations may fail.
To correct the issue, perform the MySQL upgrade procedure. See Section 2.11, “Upgrading MySQL”. Until this is done, password changes are not possible.
The password-management capabilities described here apply only
to accounts that store credentials internally in the
mysql.user
system table
(mysql_native_password
,
sha256_password
, or
caching_sha2_password
). For accounts that use
plugins that perform authentication against an external
credential system, password management must be handled
externally against that system as well.
Password Expiration Policy
MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. Expiration policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.
To expire an account password manually, use the
ALTER USER
statement:
This operation marks the password expired in the corresponding
row in the mysql.user
system table.
Password expiration according to policy is automatic and is
based on password age, which for a given account is assessed
from the date and time of its most recent password change. The
mysql.user
system table indicates for each
account when its password was last changed, and the server
automatically treats the password as expired at client
connection time if its age is greater than its permitted
lifetime. This works with no explicit manual password
expiration.
To establish automatic password-expiration policy globally, use
the default_password_lifetime
system variable. Its default value is 0, which disables
automatic password expiration. If the value of
default_password_lifetime
is a
positive integer N
, it indicates the
permitted password lifetime, such that passwords must be changed
every N
days.
Examples:
To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in a server
my.cnf
file:[mysqld] default_password_lifetime=180
To establish a global policy such that passwords never expire, set
default_password_lifetime
to 0:[mysqld] default_password_lifetime=0
default_password_lifetime
can also be set and persisted at runtime:SET PERSIST
sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change the value for the running MySQL instance without saving it for subsequent restarts, use theGLOBAL
keyword rather thanPERSIST
.
The global password-expiration policy applies to all accounts
that have not been set to override it. To establish policy for
individual accounts, use the PASSWORD EXPIRE
option of the CREATE USER
and
ALTER USER
statements. See
Section 13.7.1.3, “CREATE USER Syntax”, and Section 13.7.1.1, “ALTER USER Syntax”.
Example account-specific statements:
Require the password to be changed every 90 days:
This expiration option overrides the global policy for all accounts named by the statement.
Disable password expiration:
This expiration option overrides the global policy for all accounts named by the statement.
Defer to the global expiration policy for all accounts named by the statement:
When a client successfully connects, the server determines whether the account password has expired:
The server checks whether the password has been manually expired.
Otherwise, the server checks whether the password age is greater than its permitted lifetime according to the automatic password expiration policy. If so, the server considers the password expired.
If the password is expired (whether manually or automatically), the server either disconnects the client or restricts the operations permitted to it (see Section 6.2.16, “Server Handling of Expired Passwords”). Operations performed by a restricted client result in an error until the user establishes a new account password:
- statement before executing this statement.
- Query OK, 0 rows affected (0.01 sec)
- +---+
- | 1 |
- +---+
- | 1 |
- +---+
After the client resets the password, the server restores normal access for the session, as well as for subsequent connections that use the account. It is also possible for an administrative user to reset the account password, but any existing restricted sessions for that account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.
Although it is possible to “reset” an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password. DBAs can enforce non-reuse by establishing an appropriate password-reuse policy. See Password Reuse Policy.
MySQL enables restrictions to be placed on reuse of previous passwords. Reuse restrictions can be established based on number of password changes, time elapsed, or both. Reuse policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.
The password history for an account consists of passwords it has been assigned in the past. MySQL can restrict new passwords from being chosen from this history:
If an account is restricted on the basis of number of password changes, a new password cannot be chosen from a specified number of the most recent passwords. For example, if the minimum number of password changes is set to 3, a new password cannot be the same as any of the most recent 3 passwords.
If an account is restricted based on time elapsed, a new password cannot be chosen from passwords in the history that are newer than a specified number of days. For example, if the password reuse interval is set to 60, a new password must not be among those previously chosen within the last 60 days.
The empty password does not count in the password history and is subject to reuse at any time.
To establish password-reuse policy globally, use the
password_history
and
password_reuse_interval
system
variables.
Examples:
To prohibit reusing any of the last 6 passwords or passwords newer than 365 days, put these lines in the server
my.cnf
file:[mysqld] password_history=6 password_reuse_interval=365
To set and persist the variables at runtime, use statements like this:
SET PERSIST password_history = 6; SET PERSIST password_reuse_interval = 365;
SET PERSIST
sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change the value for the running MySQL instance without saving it for subsequent restarts, use theGLOBAL
keyword rather thanPERSIST
.
The global password-reuse policy applies to all accounts that
have not been set to override it. To establish policy for
individual accounts, use the PASSWORD HISTORY
and PASSWORD REUSE INTERVAL
options of the
CREATE USER
and
ALTER USER
statements. See
Section 13.7.1.3, “CREATE USER Syntax”, and Section 13.7.1.1, “ALTER USER Syntax”.
Example account-specific statements:
Require a minimum of 5 password changes before permitting reuse:
This history-length option overrides the global policy for all accounts named by the statement.
Require a minimum of 365 days elapsed before permitting reuse:
This time-elapsed option overrides the global policy for all accounts named by the statement.
To combine both types of reuse restrictions, use
PASSWORD HISTORY
andPASSWORD REUSE INTERVAL
together:These options override both global policy reuse restrictions for all accounts named by the statement.
Defer to the global policy for both types of reuse restrictions:
As of MySQL 8.0.13, it is possible to require that attempts to change an account password be verified by specifying the current password to be replaced. This enables DBAs to prevent users from changing a password without proving that they know the current password. Such changes could otherwise occur, for example, if one user walks away from a terminal session temporarily without logging out, and a malicious user uses the session to change the original user's MySQL password. This can have unfortunate consequences:
The original user becomes unable to access MySQL until the account password is reset by an administrator.
Until the password reset occurs, the malicious user can access MySQL with the benign user's changed credentials.
Password-verification policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.
For each account, its mysql.user
row
indicates whether there is an account-specific setting requiring
verification of the current password for password change
attempts. The setting is established by the PASSWORD
REQUIRE
option of the CREATE
USER
and ALTER USER
statements:
If the account setting is
PASSWORD REQUIRE CURRENT
, password changes must specify the current password.If the account setting is
PASSWORD REQUIRE CURRENT OPTIONAL
, password changes may but need not specify the current password.If the account setting is
PASSWORD REQUIRE CURRENT DEFAULT
, thepassword_require_current
system variable determines the verification-required policy for the account:If
password_require_current
is enabled, password changes must specify the current password.If
password_require_current
is disabled, password changes may but need not specify the current password.
In other words, if the account setting is not PASSWORD
REQUIRE CURRENT DEFAULT
, the account setting takes
precedence over the global policy established by the
password_require_current
system
variable. Otherwise, the account defers to the
password_require_current
setting.
By default, password verification is optional:
password_require_current
is
disabled and accounts created with no PASSWORD
REQUIRE
option default to PASSWORD REQUIRE
CURRENT DEFAULT
.
The following table shows how per-account settings interact with
password_require_current
system
variable values to determine account password
verification-required policy.
Table 6.10 Password-Verification Policy
Per-Account Setting | password_require_current System Variable | Password Changes Require Current Password? |
---|---|---|
PASSWORD REQUIRE CURRENT |
OFF |
Yes |
PASSWORD REQUIRE CURRENT |
ON |
Yes |
PASSWORD REQUIRE CURRENT OPTIONAL |
OFF |
No |
PASSWORD REQUIRE CURRENT OPTIONAL |
ON |
No |
PASSWORD REQUIRE CURRENT DEFAULT |
OFF |
No |
PASSWORD REQUIRE CURRENT DEFAULT |
ON |
Yes |
Privileged users can change any account password without
specifying the current password, regardless of the
verification-required policy. A privileged user is one who has
the global CREATE USER
privilege or the UPDATE
privilege for the mysql
system database.
To establish password-verification policy globally, use the
password_require_current
system
variable. Its default value is OFF
, so it is
not required that account password changes specify the current
password.
Examples:
To establish a global policy that password changes must specify the current password, start the server with these lines in a server
my.cnf
file:[mysqld] password_require_current=ON
To set and persist
password_require_current
at runtime, use a statement such as one of these:SET PERSIST
sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change the value for the running MySQL instance without saving it for subsequent restarts, use theGLOBAL
keyword rather thanPERSIST
.
The global password verification-required policy applies to all
accounts that have not been set to override it. To establish
policy for individual accounts, use the PASSWORD
REQUIRE
options of the CREATE
USER
and ALTER USER
statements. See Section 13.7.1.3, “CREATE USER Syntax”, and
Section 13.7.1.1, “ALTER USER Syntax”.
Example account-specific statements:
Require that password changes specify the current password:
This verification option overrides the global policy for all accounts named by the statement.
Do not require that password changes specify the current password (the current password may but need not be given):
This verification option overrides the global policy for all accounts named by the statement.
Defer to the global password verification-required policy for all accounts named by the statement:
Verification of the current password comes into play when a user
changes a password using the ALTER
USER
or SET PASSWORD
statement. The examples use ALTER
USER
, which is preferred over SET
PASSWORD
, but the principles described here are the
same for both statements.
In password-change statements, a REPLACE
clause specifies the current password to be replaced. Examples:
Change the current user's password:
Change a named user's password:
Change a named user's authentication plugin and password:
The REPLACE
clause works like this:
REPLACE
must be given if password changes for the account are required to specify the current password, as verification that the user attempting to make the change actually knows the current password.REPLACE
is optional if password changes for the account may but need not specify the current password.If
REPLACE
is specified, it must specify the correct current password, or an error occurs. This is true even ifREPLACE
is optional.REPLACE
can be specified only when changing the account password for the current user. (This means that in the examples just shown, the statements that explicitly name the account forjeffrey
fail unless the current user isjeffrey
.) This is true even if the change is attempted for another user by a privileged user; however, such a user can change any password without specifyingREPLACE
.REPLACE
is omitted from the binary log to avoid writing cleartext passwords to it.
As of MySQL 8.0.14, user accounts are permitted to have dual passwords, designated as primary and secondary passwords. Dual-password capability makes it possible to seamlessly perform credential changes in scenarios like this:
A system has a large number of MySQL servers, possibly involving replication.
Multiple applications connect to different MySQL servers.
Periodic credential changes must be made to the account or accounts used by the applications to connect to the servers.
Consider how a credential change must be performed in the preceding type of scenario when an account is permitted only a single password. In this case, there must be close cooperation in the timing of when the account password change is made and propagated throughout all servers, and when all applications that use the account are updated to use the new password. This process may involve downtime during which servers or applications are unavailable.
With dual passwords, credential changes can be made more easily, in phases, without requiring close cooperation, and without downtime:
For each affected account, establish a new primary password on the servers, retaining the current password as the secondary password. This enables servers to recognize either the primary or secondary password for each account, while applications can continue to connect to the servers using the same password as previously (which is now the secondary password).
After the password change has propagated to all servers, modify applications that use any affected account to connect using the account primary password.
After all applications have been migrated from the secondary passwords to the primary passwords, the secondary passwords are no longer needed and can be discarded. After this change has propagated to all servers, only the primary password for each account can be used to connect. The credential change is now complete.
MySQL implements dual-password capability with syntax that saves and discards secondary passwords:
The
RETAIN CURRENT PASSWORD
clause for theALTER USER
andSET PASSWORD
statements saves an account current password as its secondary password when you assign a new primary password.The
DISCARD OLD PASSWORD
clause forALTER USER
discards an account secondary password, leaving only the primary password.
Suppose that, for the previously described credential-change
scenario, an account named
'appuser1'@'host1.example.com'
is used by
applications to connect to servers, and that the account
password is to be changed from
'
to
password_a
''
.
password_b
'
To perform this change of credentials, use ALTER
USER
as follows:
On each server that is not a replication slave, establish
'
as the newpassword_b
'appuser1
primary password, retaining the current password as the secondary password:Wait for the password change to replicate throughout the system to all slave servers.
Modify each application that uses the
appuser1
account so that it connects to the servers using a password of'
rather thanpassword_b
''
.password_a
'At this point, the secondary password is no longer needed. On each server that is not a replication slave, discard the secondary password:
After the discard-password change has replicated to all slave servers, the credential change is complete.
The RETAIN CURRENT PASSWORD
and
DISCARD OLD PASSWORD
clauses have the
following effects:
RETAIN CURRENT PASSWORD
retains an account current password as its secondary password, replacing any existing secondary password. The new password becomes the primary password, but clients can use the account to connect to the server using either the primary or secondary password. (Exception: If the new password specified by theALTER USER
orSET PASSWORD
statement is empty, the secondary password becomes empty as well, even ifRETAIN CURRENT PASSWORD
is given.)If you specify
RETAIN CURRENT PASSWORD
for an account that has an empty primary password, the statement fails.If an account has a secondary password and you change its primary password without specifying
RETAIN CURRENT PASSWORD
, the secondary password remains unchanged.For
ALTER USER
, if you change the authentication plugin assigned to the account, the secondary password is discarded. If you change the authentication plugin and also specifyRETAIN CURRENT PASSWORD
, the statement fails.For
ALTER USER
,DISCARD OLD PASSWORD
discards the secondary password, if one exists. The account retains only its primary password, and clients can use the account to connect to the server only with the primary password.
Statements that modify secondary passwords require these privileges:
The
APPLICATION_PASSWORD_ADMIN
privilege is required to use theRETAIN CURRENT PASSWORD
orDISCARD OLD PASSWORD
clause forALTER USER
andSET PASSWORD
statements that apply to your own account. The privilege is required to manipulate your own secondary password because most users require only one password.If an account is to be permitted to manipulate secondary passwords for all accounts, it should be granted the
CREATE USER
privilege rather thanAPPLICATION_PASSWORD_ADMIN
.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-password-management.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.