Detailed explanation of MySQL 8's new feature ROLE

What problems does MySQL ROLE solve?

If you are a DBA with good professional qualities and pay more attention to permission management, you may have encountered such a problem: there are multiple developer accounts in the database; one day you need to build

If you want all previous accounts to be able to operate tables under a new schema, you need to grant permissions to the first account separately before MySQL-8.0.

mysql-8.0.x abstracts permissions and uses ROLE to represent them. When you add new permissions to a ROLE, the permissions of all users associated with this ROLE also change.

The above scenario can be solved with just one SQL statement in MySQL 8.0.x.

【Smart MySQL Development】

MySQL introduced ROLE in a very clever way. Since ROLE is a symbol of a bunch of permissions, this thing already exists in MySQL! It is USER.

1): Create a character

create role devgroup;

Looking at the mysql.user table, I was really surprised by the wit of MySQL.

select user,host from mysql.user;                              
+------------------+-----------+
| user | host |
+------------------+-----------+
| devgroup | % |
| backup | 127.0.0.1 |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+

The role is actually just a user!

2): Empower roles

grant all on tempdb.* to devgroup;                             
Query OK, 0 rows affected (0.07 sec)

It is exactly the same as the operating user!

3): Create a user and assign the role permissions to it

create user tom@'127.0.0.1' identified by '123456';                     
Query OK, 0 rows affected (0.09 sec)

grant devgroup to tom@'127.0.0.1';                             
Query OK, 0 rows affected (0.09 sec)

4): Test whether the newly created user can log in

mysql -h127.0.0.1 -P3306 -utom -p123456                     
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;                                        
+-------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `tom`@`127.0.0.1` |
| GRANT `devgroup`@`%` TO `tom`@`127.0.0.1` |
+-------------------------------------------+
2 rows in set (0.00 sec)

[Role and user are just two sides of the same coin]

If you still think that "role" and "user" are two different things, then I can only use the ultimate move

1): Assign [email protected] user as a role to the tom user just now

grant root@'127.0.0.1' to tom@'127.0.0.1';                         
Query OK, 0 rows affected (0.04 sec)

2): User tom checks his own permissions

show grants;
+--------------------------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tom`@`127.0.0.1` |
| GRANT `devgroup`@`%`,`root`@`127.0.0.1` TO `tom`@`127.0.0.1` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

You can see that the permissions of [email protected] have been set. Since they are all root user permissions, let's delete a tempdb library and take a look!

3): Delete the library

drop database tempdb;                                    
ERROR 1044 (42000): Access denied for user 'tom'@'127.0.0.1' to database 'tempdb'

It seems that you don't have permission to delete this library! In fact, MySQL-8 does not activate roles by default. Whether to activate roles is controlled by the parameter activate_all_roles_on_login.

4): Enable activate_all_roles_on_login

set @@global.activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)

5): Log in tom again and try to delete the tempdb database

mysql -h127.0.0.1 -P3306 -utom -p123456                     
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tempdb;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| tempdb |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database tempdb;                                    
Query OK, 0 rows affected (0.09 sec)

The above is a detailed explanation of the new feature ROLE of MySQL 8. For more information about the new feature ROLE of MySQL 8, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: