ALTER USER
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
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(), orSESSION_USER()The
REQUIREclause is not supportedThe following resource limit options are not supported and cause a syntax error to be raised:
MAX_QUERIES_PER_HOURMAX_UPDATES_PER_HOURMAX_CONNECTIONS_PER_HOURMAX_USER_CONNECTIONS
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;