CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
DEFAULT ROLE role [, role ] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
| PASSWORD HISTORY DEFAULT
| PASSWORD HISTORY N
| PASSWORD REUSE INTERVAL DEFAULT
| PASSWORD REUSE INTERVAL N DAY
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
The CREATE USER
statement creates
new MySQL accounts. It enables authentication, SSL/TLS,
resource-limit, and password-management properties to be
established for new accounts, and controls whether accounts are
initially locked or unlocked.
To use CREATE USER
, you must have
the global CREATE USER
privilege,
or the INSERT
privilege for the
mysql
database. When the
read_only
system variable is
enabled, CREATE USER
additionally
requires the CONNECTION_ADMIN
or
SUPER
privilege.
CREATE USER
either succeeds for
all named users or rolls back and has no effect if any error
occurs. By default, an error occurs if you try to create a user
that already exists. If the IF NOT EXISTS
clause is given, the statement produces a warning for each named
user that already exists, rather than an error.
The statement is written to the binary log if it succeeds, but
not if it fails; in that case, rollback occurs and no changes
are made. A statement written to the binary log includes all
named users. If the IF NOT EXISTS
clause is
given, this includes even users that already exist and were not
created.
The statement written to the binary log specifies an authentication plugin for each user, determined as follows:
The plugin named in the original statement, if one was specified.
Otherwise, the default authentication plugin. In particular, if a user
u1
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.)
If the server adds the default authentication plugin for any nonexisting users in the statement written to the binary log, it writes a warning to the error log naming those users.
Under some circumstances, CREATE
USER
may be recorded in server logs or on the client
side in a history file such as
~/.mysql_history
, which means that
cleartext passwords may be read by anyone having read access
to that information. For information about the conditions
under which this occurs for the server logs and how to control
it, see Section 6.1.2.3, “Passwords and Logging”. For similar
information about client-side logging, see
Section 4.5.1.3, “mysql Logging”.
There are several aspects to the CREATE
USER
statement, described under the following topics:
For each account, CREATE USER
creates a new row in the mysql.user
system
table. The account row reflects the properties specified in
the statement. Unspecified properties are set to their default
values:
Authentication: The authentication plugin defined by the
default_authentication_plugin
system variable, and empty credentialsDefault role:
NONE
SSL/TLS:
NONE
Resource limits: Unlimited
Password management:
PASSWORD EXPIRE DEFAULT PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
Account locking:
ACCOUNT UNLOCK
An account when first created has no privileges and a default
role of NONE
. To assign privileges or
roles, use the GRANT
statement.
Example 1: Create an account that uses the default authentication plugin and the given password. Mark the password expired so that the user must choose a new one at the first connection to the server:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
Example 2: Create an account that uses the
sha256_password
authentication plugin and
the given password. Require that a new password be chosen
every 180 days:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH sha256_password BY 'new_password'
PASSWORD EXPIRE INTERVAL 180 DAY;
Each account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
The host name part of the account name, if omitted, defaults
to '%'
.
Each user
value naming an account
may be followed by an optional
auth_option
value that indicates
how the account authenticates. These values enable account
authentication plugins and credentials (for example, a
password) to be specified. Each
auth_option
value applies
only to the account named immediately
preceding it.
Following the user
specifications,
the statement may include options for SSL/TLS, resource-limit,
password-management, and locking properties. All such options
are global to the statement and apply to
all named accounts.
Example: This statement creates two accounts, specifying some per-account properties and some global properties:
CREATE USER
'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'new_password1',
'jeanne'@'localhost' IDENTIFIED WITH sha256_password
BY 'new_password2'
REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
ACCOUNT LOCK;
Each auth_option
value
(IDENTIFIED WITH ... BY
in this case)
applies only to its immediately preceding account, so each
account uses the named authentication plugin and password. The
remaining properties apply globally to the statement. For both
accounts, connections must be made using a valid X509
certificate and up to 60 queries per hour are permitted. Both
accounts are locked initially, so effectively they are
placeholders and cannot be used until an administrator unlocks
them.
An account name may be followed by an
auth_option
authentication option
that specifies the account authentication plugin, credentials,
or both:
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.For
auth_option
syntaxes that do not specify an authentication plugin, the default plugin is indicated by the value of thedefault_authentication_plugin
system variable. For descriptions of each plugin, see Section 6.5.1, “Authentication Plugins”.Credentials are stored in the
authentication_string
column of themysql.user
system table. An'
orauth_string
''
value specifies account credentials, either as a cleartext (unencrypted) string or hashed in the format expected by the authentication plugin associated with the account, respectively:hash_string
'For syntaxes that use
'
, the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in theauth_string
'authentication_string
column. A plugin may use the value as specified, in which case no hashing occurs.For syntaxes that use
'
, the string is assumed to be already hashed in the format required by the authentication plugin. If the hash format is inappropriate for the plugin, it will not be usable and correct authentication of client connections will not occur.hash_string
'
CREATE USER
permits these
auth_option
syntaxes:
IDENTIFIED BY '
auth_string
'Sets the account authentication plugin to the default plugin, passes the cleartext
'
value to the plugin for hashing, and stores the result in theauth_string
'mysql.user
account row.IDENTIFIED WITH
auth_plugin
Sets the account authentication plugin to
auth_plugin
, clears the credentials to the empty string, and stores the result in themysql.user
account row.IDENTIFIED WITH
auth_plugin
BY 'auth_string
'Sets the account authentication plugin to
auth_plugin
, passes the cleartext'
value to the plugin for hashing, and stores the result in theauth_string
'mysql.user
account row.IDENTIFIED WITH
auth_plugin
AS 'hash_string
'Sets the account authentication plugin to
auth_plugin
and stores the hashed'
value as is in thehash_string
'mysql.user
account row. The string is assumed to be already hashed in the format required by the plugin.
Example 1: Specify the password as cleartext; the default plugin is used:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY 'password';
Example 2: Specify the authentication plugin, along with a cleartext password value:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password';
In each case, the password value stored in the account row is
the cleartext value
'
after
it has been hashed by the authentication plugin associated
with the account.
password
'
For additional information about setting passwords and authentication plugins, see Section 6.3.7, “Assigning Account Passwords”, and Section 6.3.10, “Pluggable Authentication”.
The DEFAULT ROLE
clause defines which roles
become active when the user connects to the server and
authenticates, or when the user executes the
SET ROLE
DEFAULT
statement during a session.
Each role name uses the format described in Section 6.2.5, “Specifying Role Names”. For example:
CREATE USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;
The host name part of the role name, if omitted, defaults to
'%'
.
The DEFAULT ROLE
clause permits a list of
one or more comma-separated role names. These roles need not
exist at the time CREATE USER
is executed.
MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name and credentials. For background information on the use of SSL/TLS with MySQL, see Section 6.4, “Using Encrypted Connections”.
To specify SSL/TLS-related options for a MySQL account, use a
REQUIRE
clause that specifies one or more
tls_option
values.
Order of REQUIRE
options does not matter,
but no option can be specified twice. The
AND
keyword is optional between
REQUIRE
options.
CREATE USER
permits these
tls_option
values:
NONE
Indicates that the account has no SSL or X509 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 have
REQUIRE 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 the account.
CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
Clients attempt to establish a secure connection by default. For accounts that have
REQUIRE SSL
, the connection attempt fails if a secure connection cannot be established.X509
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 X509 certificates always implies encryption, so the
SSL
option is unnecessary in this case.CREATE USER 'jeffrey'@'localhost' REQUIRE X509;
For accounts with
REQUIRE 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
'Requires that clients present a valid X509 certificate issued by CA
'
. If a client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so theissuer
'SSL
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';
Because
ISSUER
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
'Requires that clients present a valid X509 certificate containing the subject
subject
. If a client presents a certificate that is valid but has a different subject, the server rejects the connection. Use of X509 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
'
value to the value in the certificate, so lettercase and component ordering must be given exactly as present in the certificate.subject
'Because
SUBJECT
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
'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';
The SUBJECT
, ISSUER
, and
CIPHER
options can be combined in the
REQUIRE
clause:
CREATE USER 'jeffrey'@'localhost'
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL demo client certificate/
CN=client/emailAddress=client@example.com'
AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL/CN=CA/emailAddress=ca@example.com'
AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
It is possible to place limits on use of server resources by
an account, as discussed in Section 6.3.6, “Setting Account Resource Limits”.
To do so, use a WITH
clause that specifies
one or more resource_option
values.
Order of WITH
options does not matter,
except that if a given resource limit is specified multiple
times, the last instance takes precedence.
CREATE USER
permits these
resource_option
values:
MAX_QUERIES_PER_HOUR
,count
MAX_UPDATES_PER_HOUR
,count
MAX_CONNECTIONS_PER_HOUR
count
These options restrict how many queries, updates, and connections to the server are permitted to this account during any given one-hour period. If
count
is0
(the default), this means that there is no limitation for the account.MAX_USER_CONNECTIONS
count
Restricts the maximum number of simultaneous connections to the server by the account. A nonzero
count
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.
Example:
CREATE USER 'jeffrey'@'localhost'
WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
CREATE USER
supports several
password_option
values for password
management:
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 expiration and reuse-restriction options apply to
accounts that use a MySQL built-in authentication plugin
(mysql_native_password
,
sha256_password
, or
caching_sha2_password
). For accounts that
use plugins that perform authentication against an external
credential system, password expiration and reuse restrictions
must be handled externally as well. For information about
password management policy, see
Section 6.3.8, “Password Management”.
A client has an expired password if the account password was expired manually or the password age is considered greater than its permitted lifetime per the automatic expiration policy. In this case, the server either disconnects the client or restricts the operations permitted to it (see Section 6.3.9, “Server Handling of Expired Passwords”). Operations performed by a restricted client result in an error until the user establishes a new account password.
CREATE USER
permits these
password_option
values for password
expiration:
PASSWORD EXPIRE
Causes the password for the new account to be marked expired.
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
PASSWORD EXPIRE DEFAULT
Sets the account so that the global expiration policy applies, as specified by the
default_password_lifetime
system variable.CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
PASSWORD EXPIRE NEVER
Disables password expiration for the account so that its password never expires.
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
PASSWORD EXPIRE INTERVAL
N
DAYSets the account password lifetime to
N
days. This statement requires the password to be changed every 180 days:CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
CREATE USER
permits these
password_option
values for
controlling reuse of previous passwords based on required
minimum number of password changes:
PASSWORD HISTORY DEFAULT
Sets the account so that the global policy about password history length applies, to prohibit reuse of passwords before the number of changes specified by the
password_history
system variable.CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY DEFAULT;
PASSWORD HISTORY
N
Sets the account password history length to
N
passwords, to prohibit reusing any of theN
most recently chosen passwords. This statement prohibits reuse of any of the previous 6 passwords:CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;
CREATE USER
permits these
password_option
values for
controlling reuse of previous passwords based on time elapsed:
PASSWORD REUSE INTERVAL DEFAULT
Sets the account so that the global policy about time elapsed applies, to prohibit reuse of passwords newer than the number of days specified by the
password_reuse_interval
system variable.CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL DEFAULT;
PASSWORD REUSE INTERVAL
N
DAYSets the account password reuse interval to
N
days, to prohibit reuse of passwords newer than that many days. This statement prohibits password reuse for 360 days:CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 360 DAY;
If multiple password-management options of a given type
(PASSWORD EXPIRE
, PASSWORD
HISTORY
, PASSWORD REUSE INTERVAL
)
are specified, the last one takes precedence.
MySQL supports account locking and unlocking using the
ACCOUNT LOCK
and ACCOUNT
UNLOCK
options, which specify the locking state for
an account. For additional discussion, see
Section 6.3.12, “User Account Locking”.
If multiple account-locking options are specified, the last one takes precedence.
GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'%' IDENTIFIED BY 'UnencriptedPa55w0RdHeRe' WITH GRANT OPTION;
FLUSH PRIVILEGES;