How to alter the user password

Help Security
1

I have created a user as below.

CREATE USER ‘admin1’@’%’ IDENTIFIED BY ‘raojsv!123’;

When I tried to change the password using below command,I am facing the issue. Please guide me how to proceed.

ALTER USER admin1 IDENTIFIED BY ‘Ecmbu!23root’;

Error details:-

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 2

2

Looks like “!23root” is getting interpreted by the Linux command shell.

Use a password without an exclamation point.

3

Actually it will work if you use SET PASSWORD. ALTER USER is the wrong command to use to change a password. I have opened a task to clarify this in the docs, but here is the doc you need:
https://docs.memsql.com/v7.0/reference/sql-reference/security-management-commands/set-password/

SET PASSWORD FOR ‘admin1’ = PASSWORD(‘Ecmbu!23root’)

Should work for you

4

Hi all.

While attempting to change the password of root using ALTER USER, I found the following result:

--  An error occurred.
singlestore> ALTER USER root IDENTIFIED BY 'abcd';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

-- The query ran successfully, but the password was not modified.
singlestore> ALTER USER root IDENTIFIED BY 'abcd' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

-- The query ran successfully, and the password was modified. 
singlestore> ALTER USER root IDENTIFIED BY 'abcd' SET DEFAULT RESOURCE POOL = test_pool;
Query OK, 0 rows affected (0.00 sec)
singlestore> ALTER USER root IDENTIFIED BY 'efgh' REQUIRE NONE;
Query OK, 0 rows affected (0.00 sec)

It appears that changing the password works when using certain options.
Is it unexpected result, or is it normal?

If I can’t change the password with the ALTER USER command,
when should I use the IDENTIFIED BY option?