MySQL 8 User creation and grant privileges

Environment

MySQL Version: 8.0.21
OS: CentOS 7 Minimal install 64bit

MySQL User

MySQL user can be created using by the following command in MySQL console

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1';

If the application connects to MySQL service from a specific IP address application server IP address can be specified in create user statement.

CREATE USER 'user1'@'192.168.1.1' IDENTIFIED BY 'password1';

If MySQL needs to connect from different sources using a single username and password. Wildcard(%) can be used instead of an IP address.

CREATE USER 'user1'@'%' IDENTIFIED BY 'password1';

% is a wildcard - It can be used as  '%.domain.com' or '%.123.123.123' 

To grant privileges to a database, the user must create a database. 

Following command granting privileges to a database with name 'school'

GRANT ALL ON school.* TO 'user1'@'192.168.1.1';

For localhost connections

GRANT ALL ON school.* TO 'user1'@'localhost';

Granting connections from any source

GRANT ALL ON school.* TO 'user1'@'%';

Note

MySQL 8 removed shorthand for creating user and granting permissions, ie user can use one single command which handles creating a new user and granting privileges.

Example

GRANT ALL PRIVILEGES ON mydb.* TO 'userOld'@'localhost' IDENTIFIED BY 'oldPassword';

On MySQL version 8 It will lead to error and execution will fail.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY oldPassword'' at line 1

To overcome this problem, create the user and assign privileges in different steps as explained in this article.