Roles on MySQL 8.0

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:

-- Grants access to all databases and tables to the role "dba"
GRANT ALL ON *.* TO 'dba';
-- Grants access to the database "app" to the role "readonly"
GRANT SELECT ON app.* TO 'readonly';
-- Grants access to developers
GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, CREATE VIEW,
SHOW VIEW, TRIGGER, CREATE TEMPORARY TABLES ON app.* TO 'developer';
view raw Roles.Example1.sql hosted with ❤ by GitHub

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:

CREATE USER 'lisa_simpson'@'%' IDENTIFIED BY 'lisa_pwd';
CREATE USER 'millhouse_houten'@'localhost' IDENTIFIED BY 'millhouse_pwd';
CREATE USER 'homer_simpson'@'localhost' IDENTIFIED BY 'homer_pwd';
CREATE USER 'bart_simpson'@'localhost' IDENTIFIED BY 'bart_pwd';
view raw Roles.Example2.sql hosted with ❤ by GitHub

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:

-- Attributes the users to existing roles
GRANT 'dba' TO 'lisa_simpson'@'%';
GRANT 'developer' TO 'millhouse_houten'@'localhost';
GRANT 'readonly' TO 'homer_simpson'@'localhost', 'bart_simpson'@'localhost';
view raw Roles.Example3.sql hosted with ❤ by GitHub

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:

SET DEFAULT ROLE ALL TO
'lisa_simpson'@'%',
'millhouse_houten'@'localhost',
'homer_simpson'@'localhost',
'millhouse_houten'@'localhost';
view raw Roles.Example4.sql hosted with ❤ by GitHub

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.

4 thoughts on “Roles on MySQL 8.0

Leave a Reply to lefred Cancel reply