Advanced MySQL 8

Creating roles and users in MySQL 8.0

Now, imagine that we need one user account with the developer role, one user account with read-only access, and two user accounts that can have read/write access.

To do this, first, we need to learn about creating roles. To create a role, use the following command:

CREATE ROLE privilege;

Let's create three roles for developer, read-only, and read-write rights:

mysql> CREATE ROLE user_dev, user_read, user_write;

Here, the role name is similar to the user account and it has both the user and host parts. Let's say role_name@host_name.

You can omit the host part to make it default to %, which means any hosts.

Now, we need to grant privileges to the roles we created, as follows:

mysql> GRANT ALL ON school.* ...