Determine mysql authentication methods

There are a number of different situations where a user or dba may require to identify how a user is connecting to the server and what authentication method was used. Depending whether the user has connected directly with an account that is specified in the mysql.user table or whether the user is via proxy user will vary how to determine these details.

Direct User Authentication

To identify the authentication method for their existing connection the following query can be used:

The results show how they are identified on the MySQL server. For a directly authentication account, whether that is using native authentication or pluggable authentication, the CURRENT_USER() field will be the identifying account to use. Then a query on the mysql.user table to find out if there is any authentication plugin associated with that user account:

In the above situation we can see that the PAM authentication plugin is being used. If the user was not using an authentication plugin, the field would by default be empty, so instead a substitute value is entered for better explanation. An account that uses the original authentication methods from the mysql.user table will show as:

Here we can see the current user is shown as developer, but the proxy user is no longer NULL. This means that the current user is being proxied as another user, in this case it is the ”@” user. This special user is the “catch all” user to help authenticate any account that may not be specified directly in the mysql.user table.

Because the user has not logged into the MySQL service as [email protected], the authentication method must be determined from the proxy user which in this case is the ”@” user. So the query will be very similar to before:

In MySQL, the user is defined as both the username and the hostname in the form [email protected] hence why the CONCAT() function is required on the mysql.user table fields. For an administrator/DBA type person who wants to try and validate all the active connections the following query may provide a useful method:

The CURRENT_USER() and @@proxy_user values are of no effect when trying to query a connection that is not your own. Instead the details from the processlist are used so this may not be effective for proxied users.