ALTER USER

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

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;

See also

• CREATE USER