This is a blogpost about MySQL 8.0.0, the content here could be subject to changes in the future since this is not a stable release.

This is a feature that will make life the many DevOps and DBAs easier. Believe it or not, a lot of people control access to the database by sharing the same username and password. Which is completely insecure for a number of reasons:

• If someone is fired, they still have access
• If you get hacked for using the same password, well, I only can say: good luck

That also means: no more querying to Stack Overflow to get that giant GRANT statement for each user you need to create. (Thank you @mwop for reminding me of this).

Creating a Role

This is a group of privileges that will be assigned to users:

[code lang=”sql”]
CREATE ROLE ‘admin’;
[/code]

You can also create more than one role at once, however if one of them is already created, the whole statement fails and it won’t be created:

[code lang=”sql”]
CREATE ROLE ‘dba’, ‘developer’, ‘readonly’;
[/code]

Response:

[code lang=”text”]
mysql> CREATE ROLE ‘dba’, ‘developer’, ‘readonly’;
Query OK, 0 rows affected (0.01 sec)
[/code]

If you try to run:

[code lang=”text”]
mysql> CREATE ROLE ‘dba’;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘dba’@’%’
[/code]

It causes an error because the role already exists.

Granting privileges

Notice you didn’t state the privileges at this point, you need use GRANT to do so, and it works the same way as granting privileges to a user:

For each of the responses you will get something similar to this:

[code lang=”text”]
Query OK, 0 rows affected (0.00 sec)
[/code]

Creating Users

In my example I will add Lisa Simpson as ‘dba’, Millhouse V. Houten as ‘developer’ and Bart Simpson and Homer Simpson as ‘readonly’.

So this is the proposed idea:

USER	ROLE
lisa_simpson	dba
millhouse_houten	developer
homer_simpson	readonly
bart_simpson	readonly

The generated SQL will be:

After the user is created is time to tell which roles they are using, a role can have more than one user and a user can have more than one role. To attribute users to a role, do the following:

Granting the roles doesn’t mean they will start automatically using them. Remember, a user can have multiple roles, so you need to tell the server which role the user is using:

This way, the default role for that user on that host will be the one used, as shown at the example below:

[code lang=”text”]
mysql> SHOW GRANTS FOR millhouse_houten@’localhost’;
2 rows in set (0.00 sec)
[/code]

Result:

[code lang=”text”]
+———————————————————+
| Grants for millhouse_houten@localhost                   |
+———————————————————+
| GRANT USAGE ON *.* TO `millhouse_houten`@`localhost`    |
| GRANT `developer`@`%` TO `millhouse_houten`@`localhost` |
+———————————————————+
[/code]

Conclusion

This way is simpler to change user permissions in batch, not having to go user by user. If the user is using a different role for any reason, it must be informed before any SQL manipulation.

Source: MySQL 8.0.0 manual.

Update

Previously I had use FLUSH PRIVILEGES when granting permissions to users, this however is not needed because I didn’t manipulate the table users using INSERT, UPDATE, DELETE. In this case I manipulated the creation and grating using the specific commands for it.

Thank you, Paul DuBois, for the reminder.

• Article