Movatterモバイル変換

• Authentication: The default authentication plugin (determined as described inThe Default Authentication Plugin), and empty credentials

• Default role:NONE

• SSL/TLS:NONE

• Resource limits: Unlimited

• Password management:PASSWORD EXPIRE DEFAULT PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; failed-login tracking and temporary account locking are disabled

• Account locking:ACCOUNT UNLOCK

• Connections must be made using a valid X.509 certificate.

• Up to 60 queries per hour are permitted.

• Password changes cannot reuse any of the five most recent passwords.

• The account is locked initially, so effectively it is a placeholder and cannot be used until an administrator unlocks it.

MySQL 9.4 supports multifactor authentication (MFA), such that accounts can have up to three authentication methods. That is, accounts can use two-factor authentication (2FA) or three-factor authentication (3FA). The syntax and semantics ofauth_option remain unchanged, butauth_option may be followed by specifications for additional authentication methods. This section describesauth_option. For details about the optional MFA-related following clauses, seeCREATE USER Multifactor Authentication Options.

Clauses for random password generation apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use a plugin that performs authentication against a credentials system that is external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, seeSection 8.2.15, “Password Management”.

• auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in theplugin column of themysql.user system table.

  Forauth_option syntax that does not specify an authentication plugin, the server assigns the default plugin, determined as described inThe Default Authentication Plugin. For descriptions of each plugin, seeSection 8.4.1, “Authentication Plugins”.

• Credentials that are stored internally are stored in themysql.user system table. An'auth_string' value orRANDOM PASSWORD specifies account credentials, either as a cleartext (unencrypted) string or hashed in the format expected by the authentication plugin associated with the account, respectively:

  • For syntax that usesBY 'auth_string', the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in themysql.user table. A plugin may use the value as specified, in which case no hashing occurs.

  • For syntax that usesBY RANDOM PASSWORD, MySQL generates a random password and as cleartext and passes it to the authentication plugin for possible hashing. The result returned by the plugin is stored in themysql.user table. A plugin may use the value as specified, in which case no hashing occurs.

    Randomly generated passwords have the characteristics described inRandom Password Generation.

  • For syntax that usesAS 'auth_string', the string is assumed to be already in the format the authentication plugin requires, and is stored as is in themysql.user table. If a plugin requires a hashed value, the value must be already hashed in a format appropriate for the plugin; otherwise, the value cannot be used by the plugin and correct authentication of client connections does not occur.

    A hashed string can be either a string literal or a hexadecimal value. The latter corresponds to the type of value displayed bySHOW CREATE USER for password hashes containing unprintable characters when theprint_identified_with_as_hex system variable is enabled.

    Important

    Although we show'auth_string' with quotation marks, a hexadecimal value used for this purpose mustnot be quoted.

  • If an authentication plugin performs no hashing of the authentication string, theBY 'auth_string' andAS 'auth_string' clauses have the same effect: The authentication string is stored as is in themysql.user system table.

• IDENTIFIED BY 'auth_string'

  Sets the account authentication plugin to the default plugin, passes the cleartext'auth_string' value to the plugin for possible hashing, and stores the result in the account row in themysql.user system table.

• IDENTIFIED BY RANDOM PASSWORD

  Sets the account authentication plugin to the default plugin, generates a random password, passes the cleartext password value to the plugin for possible hashing, and stores the result in the account row in themysql.user system table. The statement also returns the cleartext password in a result set to make it available to the user or application executing the statement. For details about the result set and characteristics of randomly generated passwords, seeRandom Password Generation.

• IDENTIFIED WITHauth_plugin

  Sets the account authentication plugin toauth_plugin, clears the credentials to the empty string, and stores the result in the account row in themysql.user system table.

• IDENTIFIED WITHauth_plugin BY 'auth_string'

  Sets the account authentication plugin toauth_plugin, passes the cleartext'auth_string' value to the plugin for possible hashing, and stores the result in the account row in themysql.user system table.

• IDENTIFIED WITHauth_plugin BY RANDOM PASSWORD

  Sets the account authentication plugin toauth_plugin, generates a random password, passes the cleartext password value to the plugin for possible hashing, and stores the result in the account row in themysql.user system table. The statement also returns the cleartext password in a result set to make it available to the user or application executing the statement. For details about the result set and characteristics of randomly generated passwords, seeRandom Password Generation.

• IDENTIFIED WITHauth_plugin AS 'auth_string'

  Sets the account authentication plugin toauth_plugin and stores the'auth_string' value as is in themysql.user account row. If the plugin requires a hashed string, the string is assumed to be already hashed in the format the plugin requires.

• 2fa_auth_option: Specifies a factor 2 authentication method. The following example definescaching_sha2_password as the factor 1 authentication method, andauthentication_ldap_sasl as the factor 2 authentication method.

  CREATE USER 'u1'@'localhost'  IDENTIFIED WITH caching_sha2_password    BY 'sha2_password'  AND IDENTIFIED WITH authentication_ldap_sasl    AS 'uid=u1_ldap,ou=People,dc=example,dc=com';

• 3fa_auth_option: Following2fa_auth_option, there may appear a3fa_auth_option clause to specify a factor 3 authentication method. The following example definescaching_sha2_password as the factor 1 authentication method,authentication_ldap_sasl as the factor 2 authentication method, andauthentication_webauthn as the factor 3 authentication method

  CREATE USER 'u1'@'localhost'  IDENTIFIED WITH caching_sha2_password    BY 'sha2_password'  AND IDENTIFIED WITH authentication_ldap_sasl    AS 'uid=u1_ldap,ou=People,dc=example,dc=com'  AND IDENTIFIED WITH authentication_webauthn;

• initial_auth_option: Specifies an initial authentication method for configuring FIDO/FIDO2 passwordless authentication. As shown in the following, temporary authentication using either a generated random password or a user-specifiedauth-string is required to enable WebAuthn passwordless authentication.

  CREATE USERuser  IDENTIFIED WITH authentication_webauthn  INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'};

  For information about configuring passwordless authentication using WebAuthn pluggable authentication, SeeWebAuthn Passwordless Authentication.

• NONE

  Indicates that all accounts named by the statement have no SSL or X.509 requirements. Unencrypted connections are permitted if the user name and password are valid. Encrypted connections can be used, at the client's option, if the client has the proper certificate and key files.

  CREATE USER 'jeffrey'@'localhost' REQUIRE NONE;

  Clients attempt to establish a secure connection by default. For clients that haveREQUIRE NONE, the connection attempt falls back to an unencrypted connection if a secure connection cannot be established. To require an encrypted connection, a client need specify only the--ssl-mode=REQUIRED option; the connection attempt fails if a secure connection cannot be established.

  NONE is the default if no SSL-relatedREQUIRE options are specified.

• SSL

  Tells the server to permit only encrypted connections for all accounts named by the statement.

  CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;

  Clients attempt to establish a secure connection by default. For accounts that haveREQUIRE SSL, the connection attempt fails if a secure connection cannot be established.

• X509

  For all accounts named by the statement, requires that clients present a valid certificate, but the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. Use of X.509 certificates always implies encryption, so theSSL option is unnecessary in this case.

  CREATE USER 'jeffrey'@'localhost' REQUIRE X509;

  For accounts withREQUIRE X509, clients must specify the--ssl-key and--ssl-cert options to connect. (It is recommended but not required that--ssl-ca also be specified so that the public certificate provided by the server can be verified.) This is true forISSUER andSUBJECT as well because thoseREQUIRE options imply the requirements ofX509.

• ISSUER 'issuer'

  For all accounts named by the statement, requires that clients present a valid X.509 certificate issued by CA'issuer'. If a client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X.509 certificates always implies encryption, so theSSL option is unnecessary in this case.

  CREATE USER 'jeffrey'@'localhost'  REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/    O=MySQL/CN=CA/emailAddress=ca@example.com';

  BecauseISSUER implies the requirements ofX509, clients must specify the--ssl-key and--ssl-cert options to connect. (It is recommended but not required that--ssl-ca also be specified so that the public certificate provided by the server can be verified.)

• SUBJECT 'subject'

  For all accounts named by the statement, requires that clients present a valid X.509 certificate containing the subjectsubject. If a client presents a certificate that is valid but has a different subject, the server rejects the connection. Use of X.509 certificates always implies encryption, so theSSL option is unnecessary in this case.

  CREATE USER 'jeffrey'@'localhost'  REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/    O=MySQL demo client certificate/    CN=client/emailAddress=client@example.com';

  MySQL does a simple string comparison of the'subject' value to the value in the certificate, so lettercase and component ordering must be given exactly as present in the certificate.

  BecauseSUBJECT implies the requirements ofX509, clients must specify the--ssl-key and--ssl-cert options to connect. (It is recommended but not required that--ssl-ca also be specified so that the public certificate provided by the server can be verified.)

• CIPHER 'cipher'

  For all accounts named by the statement, requires a specific cipher method for encrypting connections. This option is needed to ensure that ciphers and key lengths of sufficient strength are used. Encryption can be weak if old algorithms using short encryption keys are used.

  CREATE USER 'jeffrey'@'localhost'  REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

• MAX_QUERIES_PER_HOURcount,MAX_UPDATES_PER_HOURcount,MAX_CONNECTIONS_PER_HOURcount

  For all accounts named by the statement, these options restrict how many queries, updates, and connections to the server are permitted to each account during any given one-hour period. Ifcount is0 (the default), this means that there is no limitation for the account.

• MAX_USER_CONNECTIONScount

  For all accounts named by the statement, restricts the maximum number of simultaneous connections to the server by each account. A nonzerocount specifies the limit for the account explicitly. Ifcount is0 (the default), the server determines the number of simultaneous connections for the account from the global value of themax_user_connections system variable. Ifmax_user_connections is also zero, there is no limit for the account.

• Password expiration options: You can expire an account password manually and establish its password expiration policy. Policy options do not expire the password. Instead, they determine how the server applies automatic expiration to the account based on password age, which is assessed from the date and time of the most recent account password change.

• Password reuse options: You can restrict password reuse based on number of password changes, time elapsed, or both.

• Password verification-required options: You can indicate whether attempts to change an account password must specify the current password, as verification that the user attempting to make the change actually knows the current password.

• Incorrect-password failed-login tracking options: You can cause the server to track failed login attempts and temporarily lock accounts for which too many consecutive incorrect passwords are given. The required number of failures and the lock time are configurable.

Except for the options that pertain to failed-login tracking, password-management options apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use a plugin that performs authentication against a credentials system that is external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, seeSection 8.2.15, “Password Management”.

• PASSWORD EXPIRE

  Immediately marks the password expired for all accounts named by the statement.

  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

• PASSWORD EXPIRE DEFAULT

  Sets all accounts named by the statement so that the global expiration policy applies, as specified by thedefault_password_lifetime system variable.

  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

• PASSWORD EXPIRE NEVER

  This expiration option overrides the global policy for all accounts named by the statement. For each, it disables password expiration so that the password never expires.

  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

• PASSWORD EXPIRE INTERVALN DAY

  This expiration option overrides the global policy for all accounts named by the statement. For each, it sets the password lifetime toN days. The following statement requires the password to be changed every 180 days:

  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

• PASSWORD HISTORY DEFAULT

  Sets all accounts named by the statement so that the global policy about password history length applies, to prohibit reuse of passwords before the number of changes specified by thepassword_history system variable.

  CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY DEFAULT;

• PASSWORD HISTORYN

  This history-length option overrides the global policy for all accounts named by the statement. For each, it sets the password history length toN passwords, to prohibit reusing any of theN most recently chosen passwords. The following statement prohibits reuse of any of the previous 6 passwords:

  CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;

• PASSWORD REUSE INTERVAL DEFAULT

  Sets all statements named by the account so that the global policy about time elapsed applies, to prohibit reuse of passwords newer than the number of days specified by thepassword_reuse_interval system variable.

  CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL DEFAULT;

• PASSWORD REUSE INTERVALN DAY

  This time-elapsed option overrides the global policy for all accounts named by the statement. For each, it sets the password reuse interval toN days, to prohibit reuse of passwords newer than that many days. The following statement prohibits password reuse for 360 days:

  CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 360 DAY;

• PASSWORD REQUIRE CURRENT

  This verification option overrides the global policy for all accounts named by the statement. For each, it requires that password changes specify the current password.

  CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

• PASSWORD REQUIRE CURRENT OPTIONAL

  This verification option overrides the global policy for all accounts named by the statement. For each, it does not require that password changes specify the current password. (The current password may but need not be given.)

  CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

• PASSWORD REQUIRE CURRENT DEFAULT

  Sets all statements named by the account so that the global policy about password verification applies, as specified by thepassword_require_current system variable.

  CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

• FAILED_LOGIN_ATTEMPTSN

  Whether to track account login attempts that specify an incorrect password.N must be a number from 0 to 32767. A value of 0 disables failed-login tracking. Values greater than 0 indicate how many consecutive password failures cause temporary account locking (ifPASSWORD_LOCK_TIME is also nonzero).

• PASSWORD_LOCK_TIME {N | UNBOUNDED}

  How long to lock the account after too many consecutive login attempts provide an incorrect password.N must be a number from 0 to 32767, orUNBOUNDED. A value of 0 disables temporary account locking. Values greater than 0 indicate how long to lock the account in days. A value ofUNBOUNDED causes the account locking duration to be unbounded; once locked, the account remains in a locked state until unlocked. For information about the conditions under which unlocking occurs, seeFailed-Login Tracking and Temporary Account Locking.

• User comment

  To set a user comment, addCOMMENT 'user_comment' to theCREATE USER statement, whereuser_comment is the text of the user comment.

  Example (omitting any other options):

  CREATE USER 'jon'@'localhost' COMMENT 'Some information about Jon';

• User attribute

  A user attribute is a JSON object made up of one or more key-value pairs, and is set by includingATTRIBUTE 'json_object' as part ofCREATE USER.json_object must be a valid JSON object.

  Example (omitting any other options):

  CREATE USER 'jim'@'localhost'    ATTRIBUTE '{"fname": "James", "lname": "Scott", "phone": "123-456-7890"}';

• The plugin named in the original statement, if one was specified.

• Otherwise, the default authentication plugin. In particular, if a useru1 already exists and uses a nondefault authentication plugin, the statement written to the binary log forCREATE USER IF NOT EXISTS u1 names the default authentication plugin. (If the statement written to the binary log must specify a nondefault authentication plugin for a user, include it in the original statement.)

• CREATE USER ... IDENTIFIED WITH .. INITIAL AUTHENTICATION IDENTIFIED WITH ... statements are written to the binary log asCREATE USER .. IDENTIFIED WITH .. INITIAL AUTHENTICATION IDENTIFIED WITH .. AS 'password-hash', where thepassword-hash is the user-specifiedauth-string or the random password generated by server when theRANDOM PASSWORD clause is specified.