Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Support for caching_sha2_password #9411

Closed
ian-p-cooke opened this issue Feb 21, 2019 · 5 comments · Fixed by #24991
Closed

Support for caching_sha2_password #9411

ian-p-cooke opened this issue Feb 21, 2019 · 5 comments · Fixed by #24991
Assignees
@bb7133
Labels
component/mysql-protocol component/privilege feature/accepted This feature request is accepted by product managers priority/P1 The issue has P1 priority. type/feature-request Categorizes issue or PR as related to a new feature.
Milestone
Requirement pool

Comments

@ian-p-cooke
Copy link
Contributor

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    If possible, provide a recipe for reproducing the error.

Windows 10
libmysql 8.0.4-3 installed via vcpkg
Visual Studio 2017

// MysqlTest.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include <mysql/mysql.h>

int main()
{
	MYSQL mysql;

	mysql_init(&mysql);
	mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
	if (!mysql_real_connect(&mysql, "127.0.0.1", "root", "", "information_schema", 4000, NULL, 0))
	{
		fprintf(stderr, "Failed to connect to database: Error: %s\n",
			mysql_error(&mysql));
		return 1;
	}
	else
	{
		printf("success!\n");
		return 0;
	}
}

build/run an example C client that uses libmysql to connect to database. I actually started out trying to use Rust with Diesel but boiled the problem down to using the C library.

  1. What did you expect to see?

"success!"

especially since DataGrip (JDBC driver) and mysql cli can connect and work fine.

  1. What did you see instead?

"Failed to connect to database: Error: Unknown MySQL error"

TiDB had no errors it its log.

  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

deployed via docker-compose (https://github.com/pingcap/tidb-docker-compose)

Release Version: v3.0.0-beta-27-g6398788
Git Commit Hash: 6398788
Git Branch: master
UTC Build Time: 2019-01-31 08:42:28
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
@ian-p-cooke
Copy link
Contributor Author

I found a solution: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

So, after calling

	mysql_options(&mysql, MYSQL_DEFAULT_AUTH, "mysql_native_password");

I was able to connect.

Do you plan on supporting AUTH via caching_sha2_password?

@zimulala
Copy link
Contributor

@ian-p-cooke
Thanks for your report!
We don't intend to support this feature in the near future.

@ian-p-cooke
Copy link
Contributor Author

ok, I can use mysql_native_password so I'm ok with that. You can close this issue if you like.

@ian-p-cooke ian-p-cooke mentioned this issue Feb 22, 2019
Closed
@morgo morgo changed the title can not connect via libmysql on Windows 10 Support for caching_sha2_password Feb 23, 2019
@kennytm kennytm mentioned this issue Feb 26, 2020
Closed
@zz-jason zz-jason added component/privilege type/feature-request Categorizes issue or PR as related to a new feature. and removed type/new-feature labels Apr 3, 2020
This was referenced Jul 16, 2020
Closed
Closed
@zz-jason zz-jason added the feature/reviewing This feature request is reviewing by product managers label Aug 6, 2020
@scsldb scsldb added feature/discussing This feature request is discussing among product managers and removed feature/reviewing This feature request is reviewing by product managers labels Aug 18, 2020
@scsldb scsldb added priority/P1 The issue has P1 priority. and removed feature/discussing This feature request is discussing among product managers labels Aug 28, 2020
@zz-jason zz-jason added the feature/accepted This feature request is accepted by product managers label Aug 28, 2020
@scsldb scsldb added this to the Requirement pool milestone Aug 28, 2020
@ghost ghost mentioned this issue Aug 30, 2020
Closed
@ghost
Copy link

ghost commented Sep 12, 2020

Support for auth switch has been added to master in #19959 - so MySQL 8.0 clients will now work without changes.

I will leave this issue open for actually adding caching_sha2_password. But likely the priority is now lower.

@ghost ghost mentioned this issue Sep 22, 2020
Open
70 tasks
@dveeden dveeden mentioned this issue Jun 24, 2021
Open
dveeden added a commit to dveeden/tidb that referenced this issue Jun 30, 2021
@dveeden
Support for caching_sha2_password authentication
82f365c 
Issue link: pingcap#9411

What this does:
- Check the `plugin` column of the `mysql.user` table.
- Based on the plugin from the user record and the plugin the client
  sent we may need to switch the authentication plugin to match the
  one from the user record
- For accounts with `caching_sha2_password` send the "fast
  authentication failed" response to trigger full authentication.
- call `auth.CheckShaPassword` to validate the user.

Implemented functionality:
- Full authentication with `caching_sha2_password` over TLS
- The `default_authentication_plugin` variable
- `CREATE USER... IDENTIFIED WITH 'caching_sha2_password'...`
- `SET PASSWORD...`
- `ALTER USER ... IDENTIFIED BY...`

Missing functionality:
- Support for the RSA public key request packet & response
- Support for RSA key based secret exchange
- Fast authentication (validate against cached entry)

Related:
- Requires pingcap/parser#1232
- pingcap#24141 makes testing
  of this easier, but this is not required.
dveeden added a commit to dveeden/tidb that referenced this issue Aug 2, 2021
@dveeden
util,server: Automatically create TLS certificates
7763c4f 
If no `ssl-cert` or `ssl-key` are specified: Create a self signed
cert in the temp storage and use that.

This allows TLS to be used when no user created certificates are
available.

Especially for `tiup playground` and other simple cases this should be
sufficient.

Note that for `caching_sha2_password` support we will either need TLS
connections or RSA keypairs. This brings us a step closer in that
direction.

The created certificate are valid for 90 days and new certificates are
created every 30 days.

See also:
- "Automatic SSL and RSA File Generation" on https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files-using-mysql.html
- https://docs.pingcap.com/tidb/stable/enable-tls-between-clients-and-servers
- pingcap#9411
- pingcap#18084
@dveeden dveeden mentioned this issue Sep 2, 2021
Draft
xhebox pushed a commit to xhebox/tidb that referenced this issue Oct 8, 2021
@dveeden @xhebox
[parser] auth: add the caching sha2 algorithm for authentication (pin…
389b10d 
…gcap#1232)

This allows validating passwords against the `authentication_string`
data that MySQL stores for caching_sha2 passwords.

Related:
- pingcap#9411
ti-chi-bot pushed a commit that referenced this issue Oct 9, 2021
@dveeden @ti-chi-bot
[parser] auth: add the caching sha2 algorithm for authentication (#1232)
8d23ad2 
This allows validating passwords against the `authentication_string`
data that MySQL stores for caching_sha2 passwords.

Related:
- #9411
@dveeden dveeden mentioned this issue Oct 21, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@bb7133 bb7133

Labels
component/mysql-protocol component/privilege feature/accepted This feature request is accepted by product managers priority/P1 The issue has P1 priority. type/feature-request Categorizes issue or PR as related to a new feature.
Projects
None yet
Milestone
Requirement pool
Development

Successfully merging a pull request may close this issue.

*: Support for caching_sha2_password authentication dveeden/tidb
7 participants
@morgo @bb7133 @dveeden @ian-p-cooke @zimulala @zz-jason @scsldb