Creating a new user in MySQL v8

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. This change affects both the server and the libmysqlclient client library.

So as of MySQL 8.x, the default user authentication method changes from mysql_native_password to caching_sha2_password. Many programs that try to authenticate to MySQL may fail unless they support the new authentication plugin (AP).

Creating and altering MySQL user accounts

• Create a new user using the default AP

  CREATE USER 'fred'@'localhost' IDENTIFIED BY 'password';
  

• Create a new user using the older AP (which may be more compatible with your application)

  CREATE USER 'fred'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
  

• Create a user account for local and remote access:

  CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
  CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
  CREATE USER 'myuser'@'192.168.1.50' IDENTIFIED BY 'mypass';
  

  The first is for access from the MySQL server itself. The second is so this user account can be used remotely from anywhere. The third is so this user account can be used remotely from a specific host.

• Change the AP for an existing user:

  ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password';
  

• Show all users, the host and AP:

  SELECT User,Host,plugin FROM mysql.user;
  

• Deleting a user:

  DROP USER 'username'@'host';