The ALTER USER statement was introduced in MariaDB 10.2.0.
 
Syntax
------ 
ALTER USER [IF EXISTS] 
 user_specification [,user_specification] ...
 [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
 [WITH resource_option [resource_option] ...]
 [password_option | lock_option] 
 
user_specification:
 username [authentication_option]
 
authentication_option:
 IDENTIFIED BY 'password' 
 | IDENTIFIED BY PASSWORD 'password_hash'
 | IDENTIFIED {VIA|WITH} authentication_plugin
 | IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS}
'authentication_string'
 | IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS}
PASSWORD('password')
 
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
 | MAX_STATEMENT_TIME time
 
password_option:
 PASSWORD EXPIRE
 | PASSWORD EXPIRE DEFAULT
 | PASSWORD EXPIRE NEVER
 | PASSWORD EXPIRE INTERVAL N DAY
 
lock_option:
 ACCOUNT LOCK
 | ACCOUNT UNLOCK
}
 
Description
----------- 
The ALTER USER statement modifies existing MariaDB accounts.
To use it, you must have the global CREATE USER privilege or
the UPDATE privilege for the mysql database. The global
SUPER privilege is also required if the read_only system
variable is enabled.
 
If any of the specified user accounts do not yet exist, an
error results. If an error occurs, ALTER USER will still
modify the accounts that do not result in an error. Only one
error is produced for all users which have not been
modified.
 
IF EXISTS
 
When the IF EXISTS clause is used, MariaDB will return a
warning instead of an error for each specified user that
does not exist.
 
Account Names
 
For ALTER USER statements, account names are specified as
the username argument in the same way as they are for CREATE
USER statements. See account names from the CREATE USER page
for details on how account names are specified.
 
CURRENT_USER or CURRENT_USER() can also be used to alter the
account logged into the current session. For example, to
change the current user's password to mariadb:
 
ALTER USER CURRENT_USER() IDENTIFIED BY 'mariadb';
 
Authentication Options
 
IDENTIFIED BY 'password'
 
The optional IDENTIFIED BY clause can be used to provide an
account with a password. The password should be specified in
plain text. It will be hashed by the PASSWORD function prior
to being stored to the mysql.user table.
 
For example, if our password is mariadb, then we can set the
account's password with:
 
ALTER USER foo2@test IDENTIFIED BY 'mariadb';
 
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password
is not a wildcard
to match any password. The user must connect without
providing a password if no
password is set.
 
The only authentication plugins that this clause supports
are mysql_native_password and mysql_old_password.
 
IDENTIFIED BY PASSWORD 'password_hash'
 
The optional IDENTIFIED BY PASSWORD clause can be used to
provide an account with a password that has already been
hashed. The password should be specified as a hash that was
provided by the PASSWORD function. It will be stored to the
mysql.user table as-is.
 
For example, if our password is mariadb, then we can find
the hash with:
 
SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)
 
And then we can set an account's password with the hash:
 
ALTER USER foo2@test IDENTIFIED BY PASSWORD
'*54958E764CE10E50764C2EECBB71D01F08549980';
 
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password
is not a wildcard
to match any password. The user must connect without
providing a password if no
password is set.
 
The only authentication plugins that this clause supports
are mysql_native_password and mysql_old_password.
 
IDENTIFIED {VIA|WITH} authentication_plugin
 
The optional IDENTIFIED VIA authentication_plugin allows you
to specify that the account should be authenticated by a
specific authentication plugin. The plugin name must be an
active authentication plugin as per SHOW PLUGINS. If it
doesn't show up in that output, then you will need to
install it with INSTALL PLUGIN or INSTALL SONAME.
 
For example, this could be used with the PAM authentication
plugin:
 
ALTER USER foo2@test IDENTIFIED VIA pam;
 
Some authentication plugins allow additional arguments to be
specified after a USING or AS keyword. For example, the PAM
authentication plugin accepts a service name:
 
ALTER USER foo2@test IDENTIFIED VIA pam USING 'mariadb';
 
The exact meaning of the additional argument would depend on
the specific authentication plugin.
 
In MariaDB 10.4 and later, the USING or AS keyword can also
be used to provide a plain-text password to a plugin if
it's provided as an argument to the PASSWORD() function.
This is only valid for authentication plugins that have
implemented a hook for the PASSWORD() function. For example,
the ed25519 authentication plugin supports this:
 
ALTER USER safe@'%' IDENTIFIED VIA ed25519 USING
PASSWORD('secret');
 
TLS Options
 
By default, MariaDB transmits data between the server and
clients without encrypting it. This is generally acceptable
when the server and client run on the same host or in
networks where security is guaranteed through other means.
However, in cases where the server and client exist on
separate networks or they are in a high-risk network, the
lack of encryption does introduce security concerns as a
malicious actor could potentially eavesdrop on the traffic
as it is sent over the network between them.
 
To mitigate this concern, MariaDB allows you to encrypt data
in transit between the server and clients using the
Transport Layer Security (TLS) protocol. TLS was formerly
known as Secure Socket Layer (SSL), but strictly speaking
the SSL protocol is a predecessor to TLS and, that version
of the protocol is now considered insecure. The
documentation still uses the term SSL often and for
compatibility reasons TLS-related server system and status
variables still use the prefix ssl_, but internally, MariaDB
only supports its secure successors.
 
See Secure Connections Overview for more information about
how to determine whether your MariaDB server has TLS
support.
 
You can set certain TLS-related restrictions for specific
user accounts. For instance, you might use this with user
accounts that require access to sensitive data while sending
it across networks that you do not control. These
restrictions can be enabled for a user account with the
CREATE USER, ALTER USER, or GRANT statements. The following
options are available:
 
Option | Description | 
 
REQUIRE NONE | TLS is not required for this account, but can
still be used. | 
 
REQUIRE SSL | The account must use TLS, but no valid X509
certificate is required. This option cannot be combined with
other TLS options. | 
 
REQUIRE X509 | The account must use TLS and must have a
valid X509 certificate. This option implies REQUIRE SSL.
This option cannot be combined with other TLS options. | 
 
REQUIRE ISSUER 'issuer' | The account must use TLS and
must have a valid X509 certificate. Also, the Certificate
Authority must be the one specified via the string issuer.
This option implies REQUIRE X509. This option can be
combined with the SUBJECT, and CIPHER options in any order.
| 
 
REQUIRE SUBJECT 'subject' | The account must use TLS and
must have a valid X509 certificate. Also, the certificate's
Subject must be the one specified via the string subject.
This option implies REQUIRE X509. This option can be
combined with the ISSUER, and CIPHER options in any order. |

 
REQUIRE CIPHER 'cipher' | The account must use TLS, but no
valid X509 certificate is required. Also, the encryption
used for the connection must use one of the methods
specified in the string cipher. This option implies REQUIRE
SSL. This option can be combined with the ISSUER, and
SUBJECT options in any order. | 
 
The REQUIRE keyword must be used only once for all specified
options, and the AND keyword can be used to separate
individual options, but it is not required.
 
For example, you can alter a user account to require these
TLS options with the following:
 
ALTER USER 'alice'@'%'
 REQUIRE SUBJECT '/CN=alice/O=My Dom,
Inc./C=US/ST=Oregon/L=Portland'
 AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some
Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'
 AND CIPHER 'TLSv1.2';
 
If any of these options are set for a specific user account,
then any client who tries to connect with that user account
will have to be configured to connect with TLS.
 
See Securing Connections for Client and Server for
information on how to enable TLS on the client and server.
 
Resource Limit Options
 
MariaDB 10.2.0 introduced a number of resource limit
options.
 
It is possible to set per-account limits for certain server
resources. The following table shows the values that can be
set per account:
 
Limit Type | Decription | 
 
MAX_QUERIES_PER_HOUR | Number of statements that the account
can issue per hour (including updates) | 
 
MAX_UPDATES_PER_HOUR | Number of updates (not queries) that
the account can issue per hour | 
 
MAX_CONNECTIONS_PER_HOUR | Number of connections that the
account can start per hour | 
 
MAX_USER_CONNECTIONS | Number of simultaneous connections
that can be accepted from the same account; if it is 0,
max_connections will be used instead; if max_connections is
0, there is no limit for this account's simultaneous
connections. | 
 
MAX_STATEMENT_TIME | Timeout, in seconds, for statements
executed by the user. See also Aborting Statements that
Exceed a Certain Time to Execute. | 
 
If any of these limits are set to 0, then there is no limit
for that resource for that user.
 
Here is an example showing how to set an account's resource
limits:
 
ALTER USER 'someone'@'localhost' WITH
 MAX_USER_CONNECTIONS 10
 MAX_QUERIES_PER_HOUR 200;
 
The resources are tracked per account, which means
'user'@'server'; not per user name or per connection.
 
The count can be reset for all users using FLUSH
USER_RESOURCES, FLUSH PRIVILEGES or mysqladmin reload.
 
Per account resource limits are stored in the user table, in
the mysql database. Columns used for resources limits are
named max_questions, max_updates, max_connections (for
MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for
MAX_USER_CONNECTIONS).
 
Password Expiry
 
Besides automatic password expiry, as determined by
default_password_lifetime, password expiry times can be set
on an individual user basis, overriding the global setting,
for example:
 
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL
120 DAY;
 
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;
 
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;
 
See User Password Expiry for more details.
 
Account Locking
 
Account locking permits privileged administrators to
lock/unlock user accounts. No new client connections will be
permitted if an account is locked (existing connections are
not affected). For example:
 
ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;
 
See Account Locking for more details.
 


URL: https://mariadb.com/kb/en/alter-user/