According to documentation
(https://mariadb.com/kb/en/library/alter-user/) the syntax is "ALTER
USER ... IDENTIFIED BY 'password'" or "ALTER USER ... IDENTIFIED BY
PASSWORD 'password_hash'". So if you want to provide the encoded
password to ALTER USER, you need to add the keyword PASSWORD.

Pavel

On Sat, Aug 24, 2019 at 2:18 PM Jeff Dyke <jeff.dyke@gmail.com> wrote:
>
> So i've been on a bit of a quest to nail down the reasons i can't autodeploy (via saltstack) mariadb-grants and mariadb-users.  But this has nothing to do with saltstack, as i've boiled it down to direct commands executed on 10.3.17
>
> Using ALTER USER foo@'bar' seems to double encode the password:
>
> MariaDB [mysql]> select User, Host, Password from user where User = 'jeff';
> +------+----------+-------------------------------------------+
> | User | Host     | Password                                  |
> +------+----------+-------------------------------------------+
> | jeff | 172.16.% | *D02D4C65F4A93325D91DC9E07D45521A8CD2960B |
> +------+----------+-------------------------------------------+
> 1 row in set (0.000 sec)
>
> MariaDB [mysql]> alter user jeff@'172.16.%' identified by  '*D02D4C65F4A93325D91DC9E07D45521A8CD2960B';
> Query OK, 0 rows affected (0.000 sec)
>
> MariaDB [mysql]> select User, Host, Password from user where User = 'jeff';
> +------+----------+-------------------------------------------+
> | User | Host     | Password                                  |
> +------+----------+-------------------------------------------+
> | jeff | 172.16.% | *42EF65913FAC3841BEFC25DBCBBB800364B91853 |
> +------+----------+-------------------------------------------+
> 1 row in set (0.000 sec)
>
> MariaDB [mysql]> alter user jeff@'172.16.%' identified with mysql_native_password as '*D02D4C65F4A93325D91DC9E07D45521A8CD2960B';
> Query OK, 0 rows affected (0.000 sec)
>
> MariaDB [mysql]> select User, Host, Password from user where User = 'jeff';
> +------+----------+-------------------------------------------+
> | User | Host     | Password                                  |
> +------+----------+-------------------------------------------+
> | jeff | 172.16.% | *D02D4C65F4A93325D91DC9E07D45521A8CD2960B |
> +------+----------+-------------------------------------------+
>
>
> MariaDB [mysql]> select password('*D02D4C65F4A93325D91DC9E07D45521A8CD2960B');
> +-------------------------------------------------------+
> | password('*D02D4C65F4A93325D91DC9E07D45521A8CD2960B') |
> +-------------------------------------------------------+
> | *42EF65913FAC3841BEFC25DBCBBB800364B91853             |
> +-------------------------------------------------------+
>
> I can use SET PASSWORD FOR....
> MariaDB [mysql]> set password for jeff@'172.16.%' = '*D02D4C65F4A93325D91DC9E07D45521A8CD2960B';
> Query OK, 0 rows affected (0.000 sec)
>
> MariaDB [mysql]> select User, Host, Password from user where User = 'jeff';
> +------+----------+-------------------------------------------+
> | User | Host     | Password                                  |
> +------+----------+-------------------------------------------+
> | jeff | 172.16.% | *D02D4C65F4A93325D91DC9E07D45521A8CD2960B |
> +------+----------+-------------------------------------------+
>
> but as you can see above, i can not use alter user jeff@'172.16.%' identified by 'hash' without using mysql_native_password as 'hashed_password'
>
> If this does not have enough info i'm glad to give it.  if you can decrypt the hash, bully its not my real password :)
>
> Thanks and Best,
> Jeff
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp