ALTER USER

Overview

Modifies user characteristics, including changing and setting account authentication, TLS restrictions, and password expiration.

USAGE

Common syntax:

ALTER USER <user_name> [ @ <host_name> ] [<authentication_option>]
   [SET [<alter_user_option>] [, <alter_user_option>] ...]

Authentication option:

IDENTIFIED BY '<cleartext_password>'
IDENTIFIED BY PASSWORD '<password_hash>'
IDENTIFIED WITH {mysql_native_password | sha256_password} [{BY | AS} '<cleartext_password>']

ALTER USER option:

[PRIORITY [=] <-64..63>] | [PROFILING [=] {TRUE|FALSE}]

DETAILS

MariaDB Xpand's ALTER USER statement has some differences from MariaDB Enterprise Server:

  • The user account can't be specified using CURRENT_USER, CURRENT_USER(), or SESSION_USER()

  • The REQUIRE clause is not supported

  • The following resource limit options are not supported and cause a syntax error to be raised:

    • MAX_QUERIES_PER_HOUR

    • MAX_UPDATES_PER_HOUR

    • MAX_CONNECTIONS_PER_HOUR

    • MAX_USER_CONNECTIONS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

Set a Password

Let's create new users:

CREATE USER usr1, usr2;
SHOW CREATE USER usr1\G
*************************** 1. row ***************************
CREATE USER 'usr1'@'%': CREATE USER 'usr1'@'%' IDENTIFIED WITH mysql_native_password AS ""
SHOW CREATE USER usr2\G
*************************** 1. row ***************************
CREATE USER 'usr2'@'%': CREATE USER 'usr2'@'%' IDENTIFIED WITH mysql_native_password AS ""

To set a password for the newly created users:

ALTER USER usr1 IDENTIFIED BY 'Password!123', usr2 IDENTIFIED BY 'Password!456';
SHOW CREATE USER usr1\G
*************************** 1. row ***************************
CREATE USER 'usr1'@'%': CREATE USER 'usr1'@'%' IDENTIFIED WITH mysql_native_password AS "*595EFEEBC127216D362E114A7284E8A66F802705"
SHOW CREATE USER usr2\G
*************************** 1. row ***************************
CREATE USER 'usr2'@'%': CREATE USER 'usr2'@'%' IDENTIFIED WITH mysql_native_password AS "*636D0C0FAB248D01E89007B572D790EBFE5A2743"

Set User Options

To turn off profiling for a user:

ALTER USER usr1 SET PROFILING = FALSE;

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES