Why do I have to set MySQL user password with mysql_native_password to connect with Workbench? I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword'
If I use this below I can connect to it:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'myPassword';
I remember at MySQL 8 there was a default password policy change.
But still after years of that change, do the drivers (c/c++, node.js etc) not support the new password authentication?
Does MySQL's own Workbench don't support the new way of authentication?
Or am I missing something?
MySQL Workbench 8 accepts, if mysql server is set to legacy mode, also native_passwords.
But it  also accepts users that use cashing_sha2_password or sh2 passwords.
So you must check the users roles and for which. first try to use % in LImit to host matching and DBA as role, so that your user has all privileges to test.