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:

mysql> SELECT USER(),CURRENT_USER(),@@PROXY_USER;
+----------------+----------------+--------------+
| USER() | CURRENT_USER() | @@PROXY_USER |
+----------------+----------------+--------------+
| kate@localhost | kate@localhost | NULL |
+----------------+----------------+--------------+
1 row in set (0.00 sec)

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:

mysql> SELECT CURRENT_USER() AS User, IF(plugin='','Native MySQL',plugin) AS Auth_Method
-> FROM mysql.user WHERE CURRENT_USER()=CONCAT(user,'@',host);
+----------------+--------------------+
| User | Auth_Method |
+----------------+--------------------+
| kate@localhost | authentication_pam |
+----------------+--------------------+
1 row in set (0.05 sec)

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:

mysql> SELECT CURRENT_USER() AS User, IF(plugin='','Native MySQL',plugin) AS Auth_Method
-> FROM mysql.user WHERE CURRENT_USER()=CONCAT(user,'@',host);
+----------------+--------------------+
| User | Auth_Method |
+----------------+--------------------+
| kate@localhost | Native MySQL |
+----------------+--------------------+
1 row in set (0.05 sec)

Please note that the authentication methods presented may vary between different platforms.

Proxied User

As of MySQL 5.5 there exists the capability to represent one user as another, or proxy user. This can be seen in the active connection using the @@proxy_user variable as seen below:

mysql> SELECT USER(),CURRENT_USER(),@@PROXY_USER;
+----------------+---------------------+--------------+
| USER() | CURRENT_USER() | @@PROXY_USER |
+----------------+---------------------+--------------+
| kate@localhost | developer@localhost | ''@'' |
+----------------+---------------------+--------------+
1 row in set (0.00 sec)

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 developer@localhost, 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:

mysql> SELECT USER() AS User,CURRENT_USER() AS Auth_User, IF(plugin='','Native MySQL',plugin) AS Auth_Method

-> FROM mysql.user WHERE @@PROXY_USER=CONCAT(user,'@',host);
+----------------+---------------------+--------------------+
| User | Auth_User | Auth_Method |
+----------------+---------------------+--------------------+
| kate@localhost | developer@localhost | authentication_pam |
+----------------+---------------------+--------------------+
1 row in set (0.05 sec)

 

So now it is seen that although the specified user was kate@localhost, the proxied user is developer@localhost which is being authenticated using the PAM plugin via the ”@” user.

Looking at combining the two methods into one simpler query gives the following:

mysql> SELECT USER() AS User,CURRENT_USER() AS Auth_User, IF(plugin='','Native MySQL',plugin) AS Auth_Method
-> FROM mysql.user WHERE IFNULL(@@PROXY_USER,CURRENT_USER())=CONCAT(user,'@',host);
+----------------+---------------------+--------------------+
| User | Auth_User | Auth_Method |
+----------------+---------------------+--------------------+
| kate@localhost | developer@localhost | authentication_pam |
+----------------+---------------------+--------------------+
1 row in set (0.05 sec)

In MySQL, the user is defined as both the username and the hostname in the form user@host 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:

mysql> SELECT CONCAT(mu.user,'@',mu.host) AS User, IF(plugin='','Native MySQL',plugin) AS Auth_Method

-> FROM INFORMATION_SCHEMA.PROCESSLIST AS isp INNER JOIN mysql.user AS mu
-> ON (substring_index(isp.host,':',1)=mu.host and isp.user=mu.user);
+----------------+---------------------+--------------------+
| User | Auth_User | Auth_Method |
+----------------+---------------------+--------------------+
| root@localhost | root@localhost | Native MySQL |
| kate@localhost | developer@localhost | authentication_pam |
+----------------+---------------------+--------------------+
2 rows in set (0.05 sec)

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.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.