Creating users

So far, you have used only the root user to connect to MySQL and execute statements. The root user should never be used while accessing MySQL, except for administrative tasks from localhost. You should create users, restrict the access, restrict the resource usage, and so on. For creating new users, you should have the CREATE USER privilege. During the initial set up, you can use the root user to create other users.

Connect to mysql using the root user and execute CREATE USER command to create new users.

CREATE USER IF NOT EXISTS 'company_read_only'@'localhost'
IDENTIFIED WITH mysql_native_password
BY 'company_pass'
WITH MAX_QUERIES_PER_HOUR 500
MAX_UPDATES_PER_HOUR 100;

Query OK, 0 rows affected (0.06 sec)

You might get the following error if the password is not strong. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

The preceding statement will create users with:

• * Username: company_read_only.
• * access only from: localhost.
• You can restrict the access to the IP range. For example: 10.148.%.%. By giving %, the user can access from any host.
• * password: company_pass.
• * using mysql_native_password (default) authentication.
• You can also specify any pluggable authentication, such as sha256_password, LDAP, or Kerberos.
• The * maximum number of queries the user can execute in an hour is 500.
• The * maximum number of updates the user can execute in an hour is 100.

When a client connects to the MySQL server, it undergoes two stages:

1. Access control — connection verification
2. Access control — request verification

During the connection verification, the server identifies the connection by the username and the hostname from which it is connected. The server invokes the authentication plugin for the user and verifies the password. It also checks whether the user is locked or not.

During the request verification stage, the server checks whether the user has sufficient privileges for each operation.

You can directly create users by granting privileges. However, MySQL will deprecate this feature in the next release.

Refer to https://dev.mysql.com/doc/refman/8.0/en/create-user.html for more options on creating users.