MariaDB starting with 10.2.0
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] ...]
user_specification:
username [authentication_option]
authentication_option
IDENTIFIED BY 'authentication_string'
| IDENTIFIED BY PASSWORD 'hash_string'
| IDENTIFIED {VIA|WITH} authentication_plugin
| IDENTIFIED {VIA|WITH} authentication_plugin BY 'authentication_string'
| IDENTIFIED {VIA|WITH} authentication_plugin {USING|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
Contents
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 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.
CURRENT_USER - or CURRENT_USER() - can be used to identify the account to be modified.
The account can be given a password with the optional IDENTIFIED BY clause.
To specify the password in plain text, omit the PASSWORD keyword. To
specify the password as the hashed value as returned by the
PASSWORD function, include the PASSWORD keyword.
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.
If you specify a plugin using the VIA clause, the plugin name must be an
active authentication plugin as per show plugins. If it
doesn't show up you will need to install it with INSTALL
PLUGIN or INSTALL SONAME. Some plugins require a plugin_option (like
mysql_native_password).
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.
MAX_*_PER_HOUR
Limit to the number of queries, updates or connections the user can place or make per hour. The query count excludes results returned from the query cache. If set to zero, the default, no limit is imposed.
MAX_USER_CONNECTIONS
Limit to the number of simultaneous connections that the user can hold. If set to zero, the default, no limit is imposed.
tls_options
SSL
Specifies that the server will only permit encrypted connections for the user.
X509
Requires the client to have a valid certificate whose signature can be verified with one of the CA certificates, although the issuer, subject and exact certificate are not checked. Implies encryption, so there's no need for the SSL option.
ISSUER
Requires the client to have a valid certificate issued by the specified issuer. Implies encryption, so there's no need for the SSL option. The client must specify the --ssl-key and --ssl-cert options to connect, and it is also recommended that the --ssl-ca option be specified in order to verify the server's public certificate.
SUBJECT
Requires the client to have a valid certificate with the specified subject. Implies encryption, so there's no need for the SSL option. The client must specify the --ssl-key and --ssl-cert options to connect, and it is also recommended that the --ssl-ca option be specified in order to verify the server's public certificate.
CIPHER
Specifies that a specific cipher method is used for encrypting connections.
Examples
ALTER USER CURRENT_USER;
ALTER USER foo IDENTIFIED BY 'something' REQUIRE SSL;
ALTER USER foo WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 MAX_USER_CONNECTIONS 40;
