Mtls Authentication in Mysql

MySQL & MariaDB
1

Hi team,
We have service account which we want to use with certs i.e as mtls user and instead of password we want to connect DB with that user using certificates. We have password plugin enabled on system due to which not able to create user as password less.

Can you help me if there is way in mysql we can create user without passowrd to use certs along with password plugin enabled i.e dosen’t hamper other password based user activity.

Version is Mysql 5.7.36

Regards,
Shruti

2

In MySQL 5.7, the default authentication plugin is ‘mysql_native_password’ which will accept an empty password. You must first UNINSTALL the password validation plugin in order to set no password. The following is MySQL 8.0 which is the current GA (Note: In 5.7 the validation is a plugin)

mysql> UNINSTALL COMPONENT 'file://component_validate_password';

mysql> CREATE USER 'bob'@'127.0.0.1' REQUIRE SSL;   <-- no password, SSL required

$ mysql -h 127.0.0.1 -ubob -p -e '\s' | grep "SSL"
Enter password: (empty)
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384

Simply re-install the validation plugin after you create this user. Re-installing the plugin will NOT enforce passwords on this account.

1 Like
3

We have this running in Prod and its not clean solution to uninstall the plugin as it would impact the running Application team with issue to fetch passowrd. Can we anyother resolution to it ?

Regards,
shruti diixd

1 Like
4

That is not correct. Uninstalling the verification password does not prevent any accounts from being maintained, updated, nor does it prevent any accounts from logging in. The verification plugin ONLY checks that passwords meet certain criteria when they are CREATED. The plugin has NOTHING to do with ongoing activities.

1 Like
5

@matthewb Is there a way to avoid password prompt when using MTLS ?

1 Like
6

Yes, simply don’t pass the -p flag and mysql won’t prompt you to enter a password.

1 Like
7

In my case, MySQL is forcing to use password even when the user is created without password. Does it have to do anything with the authentication_plugin… (MySQL_native_password) ?

1 Like
8

MySQL never forces you to use/provide a password. You have a misconfiguration in your app/connector if this is the case. As I showed above, if you don’t tell MySQL to request a password, then MySQL won’t ask you for one.

1 Like