How to view which mysql authentication plugin a user's password is using?

Question

Suppose I created two users, both of which use 2 different authentication plugins:

CREATE USER IF NOT EXISTS 'rachel' IDENTIFIED WITH mysql_native_password BY 'super_secure_password';

CREATE USER IF NOT EXISTS 'bob' IDENTIFIED WITH caching_sha2_password BY 'amazing_password';

Note how the users are using 2 different authentication plugins:

• User rachel is using mysql_native_password
• User bob is using caching_sha2_password

How can I query mysql to retrieve this information about my users?

Answer 1

select user, plugin from mysql.user;