Mtls for user authentication in Mysql

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi team,

We need to make one of User account to use mtls in mysql we have created user as below string in mysql

CREATE USER ‘xyz.abc.com’@‘100.85.34.245’ REQUIRE SUBJECT ‘/CN=xyz.abc.com/OU=management:idms.group.1103300/O=abc Inc./ST=California/C=US’;

and pass ssl cert and other paramter in cnf file but while making connection i am getting error
pymysql.err.OperationalError: (1045, “Access denied for user ‘xyz.abc.com’@‘ma-isdbsect-ldb03.corp.abc.com’ (using password: NO)”)

Can someone help here.

1 Like
3

this user is certifiacte based password as i created it with require subject so passowrd is required to pass here.

1 Like
4

Have a look at this answer and see if that helps. It would be helpful to see your code.

1 Like
5

There is difference though the one you shared has error with password:yes which yes requires passowrd for my error : its password no also when i created user without REQUIRE SUBJECT and replace it with REQUIRE SSL its lets the connection but with REQUIRE SUBJECT and REQUIRE X509 its giving error

1 Like
6

Please try passing a blank/empty password. I supplied a blank password in the example below and it worked correctly.

db1-T1 mysql> CREATE USER 'bob'@'%' REQUIRE SUBJECT "/C=US/ST=Anywhere/L=MyCity/O=Percona/OU=TrainingDept/CN=MyCoolClient";
Query OK, 0 rows affected (0.00 sec)

[rocky@db1-T1 ~]$ mysql -h 127.0.0.1 -ubob --ssl-cert=/etc/ssl/mysql/client-cert.pem -p
Enter password:   <just pressed return for empty string>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16701
Server version: 8.0.28-20 Percona Server (GPL), Release 20, Revision fd4b5a776a6
1 Like
7

password plugin is off and i am usin pymysql lib conn=pymysql.connect(user=“xyz.abc.com”,host=“rn2-mysqld-lmsdb34.rno.apple.com”,password=“”,port=3306,ssl={‘ssl’: {‘ca’:“/home/oracle/tls/ca.crt”,‘key’:“/home/oracle/tls/nonprod.key”,‘cert’:“/home/oracle/tls/cert.pem”,‘ssl-mode’:“verify_identity”}})

Traceback (most recent call last):
File “”, line 1, in
File “/home/oracle/lib64/python3.6/site-packages/pymysql/connections.py”, line 353, in init
self.connect()
File “/home/oracle/lib64/python3.6/site-packages/pymysql/connections.py”, line 633, in connect
self._request_authentication()
File “/home/oracle/lib64/python3.6/site-packages/pymysql/connections.py”, line 907, in _request_authentication
auth_packet = self._read_packet()
File “/home/oracle/lib64/python3.6/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “/home/oracle/lib64/python3.6/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “/home/oracle/lib64/python3.6/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, “Access denied for user ‘xyz.abc.com’@‘100.85.34.245’ (using password: NO)”)

1 Like
8

Look in the MySQL error log. I noticed during my testing there were extra messages about SSL auth that helped out in debugging.

1 Like