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