On Aug 10, 2018, at 2:56 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Felipe!
On Aug 10, Felipe Gasper wrote:
Hi all,
What is the best way to fetch a user’s password hash? I’d ideally like something that works in both MySQL and MariaDB, as many versions as possible.
Thank you!
You can select from mysql.user. You can use IF() function to create a select that returns a password no matter what column it's in.
It'd be the most universal way, I think.
Yeah the problem there is that not all DB versions have “authentication_string”, so we’d be doing a query to determine if the column is there in the first place. Ideally I’d like to stay away from mysql.user since the handling of that table is inconsistent from version to version.
There're also SHOW GRANTS and SHOW CREATE USER, but they output is version dependent and might have weird privilege requirements to see the password.
SHOW GRANTS, as of MySQL 5.7, doesn’t return the password hash at all, so SHOW CREATE USER would be it. And that command’s output is radically different between MySQL 5.7 and MariaDB 10.2 … and 10.2’s output doesn’t appear to match the documentation. So I’m kind of leery about that command … though all things being equal I’d prefer it to hitting mysql.user. -FG