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:
CREATE ROLE ‘admin’;
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:
CREATE ROLE ‘dba’, ‘developer’, ‘readonly’;
mysql> CREATE ROLE ‘dba’, ‘developer’, ‘readonly’;
Query OK, 0 rows affected (0.01 sec)
If you try to run:
mysql> CREATE ROLE ‘dba’;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘dba’@’%’
It causes an error because the role already exists.
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';|
For each of the responses you will get something similar to this:
Query OK, 0 rows affected (0.00 sec)
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:
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';|
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';|
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|
This way, the default role for that user on that host will be the one used, as shown at the example below:
mysql> SHOW GRANTS FOR millhouse_houten@’localhost’;
2 rows in set (0.00 sec)
| Grants for millhouse_houten@localhost |
| GRANT USAGE ON *.* TO `millhouse_houten`@`localhost` |
| GRANT `developer`@`%` TO `millhouse_houten`@`localhost` |
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.
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
DELETE. In this case I manipulated the creation and grating using the specific commands for it.
Thank you, Paul DuBois, for the reminder.