Can not use `caching_sha2_password` login

[Icemap]

Bug Report

I used CREATE USER 'username' IDENTIFIED WITH caching_sha2_password statement to create a user. MySQL can use this user login, but TiDB would not.

1. Minimal reproduce step (Required)

1. Install TiDB
2. Install MySQL (and load caching_sha2_password AuthPlugin)
3. Clone this code
4. Point JDBC URL to TiDB host
5. Run mvn clean package && java -jar target/tidb-java-gitpod-0.0.1-jar-with-dependencies.jar
6. Point JDBC URL to MySQL host
7. Run mvn clean package && java -jar target/tidb-java-gitpod-0.0.1-jar-with-dependencies.jar
8. TiDB can not login by caching_sha2_password user

2. What did you expect to see? (Required)

Java code gets the connection and output result of SELECT CURRENT_USER() when linked MySQL:

1. create user with caching_sha2_password by no password
sha2user@%
2. create user with caching_sha2_password by password exists
sha2user@%

3. What did you see instead (Required)

Java code can not get the connection and print error stack when linked TiDB:

1. create user with caching_sha2_password by no password
java.sql.SQLException: Access denied for user 'sha2user'@'127.0.0.1' (using password: YES)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:190)
        at com.pingcap.App.testCachingSHA2Password(App.java:52)
        at com.pingcap.App.main(App.java:23)
2. create user with caching_sha2_password by password exists
java.sql.SQLException: Access denied for user 'sha2user'@'127.0.0.1' (using password: YES)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:190)
        at com.pingcap.App.testCachingSHA2Password(App.java:52)
        at com.pingcap.App.main(App.java:25)

4. What is your TiDB version? (Required)

+-----------------------------------------+
| version()                               |
+-----------------------------------------+
| 5.7.25-TiDB-v6.2.0-alpha-101-gb91bdd087 |
+-----------------------------------------+

[jebter]

@leobeijing2000 @lastincisor PTAL

[lastincisor]

mysql used "caching_sha2_password" create user is ok CREATE USER 'song1'@'%' IDENTIFIED WITH caching_sha2_password BY '123456'; use "mysql cli" login is ok。 mysql -usong1 -p

tidb used "caching_sha2_password" create user is ok CREATE USER 'song1'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';

use "mysql cli" login is error. mysql -usong1 -hxxx.xxx.xxx.xxx -Pxxxx -p error info ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

bug not used jdbc.

@Icemap

[bb7133]

error info ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure >connection.

@lastincisor @Icemap Did you have the 'SSL' enabled for TiDB? According to the manual, the connection should be encrypted for caching_sha2_password authentication, you could verify it by using:

select @@have_ssl;

[dveeden]

Note that MySQL does caching. So after the connection succeeds once over a secure connection subsequent connections can be established over insecure connections. The cache is only in memory so after a MySQL restart it will require a secure connection again. TiDB currently always requires a secure connection.

I think we could:

1. Close this as not-a-bug.
2. Change this to a feature request for the caching part of caching_sha2_password.

[dveeden]

Note that MySQL does caching. So after the connection succeeds once over a secure connection subsequent connections can be established over insecure connections. The cache is only in memory so after a MySQL restart it will require a secure connection again. TiDB currently always requires a secure connection.

I think we could:

1. Close this as not-a-bug.

2. Change this to a feature request for the caching part of `caching_sha2_password`.

With https://github.com/pingcap/tidb/issues/56747 I think we should go for option 1.

[Icemap]

Got it. Thanks for helping.