MySQL 8.0 role management

1, MySQL 8.0 role management (role)

01 what is a role?

The first sentence of the official document directly tells us what the role is. A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.

It is not difficult to see that MySQL roles are "permission sets". We can reclaim or grant permissions to a role like an account.

We can assign a role to a user, and then the user has all the permissions under the role. This method allows us to define different roles to assign to different users. For example, we can define "developer role", "read-only role", "read-write role", etc. different roles correspond to different permissions. In this way, for developers Zhang San and Li Si, we only need to grant the "developer role" to their personal account. For some query users, we only need to grant the "read-only role". For business accounts, we need to grant the "read-write role" of the database

02 what operations are supported by roles?

The supported operations, that is, role management, are as follows:

1. CREATE ROLE and DROP ROLE create and delete roles.

2. GRANT and REVOKE assign or reclaim permissions for user accounts and roles.

3. SHOW GRANTS displays the privileges and role assignments for user accounts and roles.

4. SET DEFAULT ROLE specifies which account roles are active by default.

5. SET ROLE changes the active role in the current session.

6,CURRENT_ The role() function displays the active roles in the current session.

7. When the user logs in to the server, the system variable is essentially_roles and activate_all_roles_on_login allows you to define mandatory roles and automatically activate granted roles.

Just listing is not easy to understand. Let's take a few examples:

Create role and delete role:

mysql> create role "role_ro","role_rw","role_dev";
Query OK, 0 rows affected (0.07 sec)

mysql> create role "role_test"@"localhost";
Query OK, 0 rows affected (0.01 sec)

mysql> drop role "role_test"@"localhost";
Query OK, 0 rows affected (0.01 sec)

mysql> drop role "role_ro","role_rw","role_dev";
Query OK, 0 rows affected (0.00 sec)

Assign permissions to specific roles:

#Create two roles, role_ro and role_rw
mysql> create role "role_ro","role_rw";
Query OK, 0 rows affected (0.00 sec)

#Grant role_ro select permission, grant role_rw add, delete, modify and query permissions
mysql> grant select,show databases on yeyz.* to "role_ro";
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,insert,update,delete,show databases on yeyz.* to "role_rw";
Query OK, 0 rows affected (0.01 sec)

#Create two users yeyz_ro and yeyz_rw
mysql> create user yeyz_ro@'%' identified by 'yeyz'; 
Query OK, 0 rows affected (0.01 sec)

mysql> create user yeyz_rw@'%' identified by 'yeyz';
Query OK, 0 rows affected (0.01 sec)

#Transfer two roles to role_ro and role_rw, respectively mapped to user yeyz_ro and yeyz_rw up
mysql> grant 'role_ro' to yeyz_ro@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant 'role_rw' to yeyz_rw@'%';
Query OK, 0 rows affected (0.00 sec)

After classifying permissions, let's view them:

#View the account permissions and find that only roles and usage permissions can be viewed
mysql> show grants for yeyz_ro@'%';
+--------------------------------------+
| Grants for yeyz_ro@%                 |
+--------------------------------------+
| GRANT USAGE ON *.* TO `yeyz_ro`@`%`  |
| GRANT `role_ro`@`%` TO `yeyz_ro`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

#Use the using syntax to view the permissions of the roles owned by the account
mysql> show grants for yeyz_ro@'%' using 'role_ro';
+----------------------------------------------+
| Grants for yeyz_ro@%                         |
+----------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_ro`@`%` |
| GRANT SELECT ON `yeyz`.* TO `yeyz_ro`@`%`    |
| GRANT `role_ro`@`%` TO `yeyz_ro`@`%`         |
+----------------------------------------------+
3 rows in set (0.00 sec)

Note that using the general show grants method can only view the roles of the account. You can use the using syntax to query the specific permissions of the roles.

2, Role management

Let's take a look at the key contents of role management.

01 create role

The newly created role is temporarily locked without a password. The properties of this role can be modified by users with create user permission. The locked account cannot be used to verify the server, that is, it cannot log in to the server directly. After unlocking the role, you can log in to the server.

In yesterday's article, we created two roles and assigned them to two accounts, as follows:

Role 1: role_ro

Permissions: select

Account No.: yeyz_ro

Role 2: role_rw

Permissions: select, update, insert, delete

Account No.: yeyz_rw

When we use yeyz_ When you log in to the database with RO's account, you can find:

1. Account can log in

2. Can't perform any query operations, even our database yeyz can't be seen.

As follows:

192:~ root# /usr/local/mysql_8.0/bin/mysql -uyeyz_ro -pyeyz -h127.0.0.1 --socket=/data/mysql_5306/tmp/mysql.sock --port=5306
........
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> use yeyz;
ERROR 1044 (42000): Access denied for user 'yeyz_ro'@'%' to database 'yeyz'

The reason is that no role is currently "activated", as follows:

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

So how to "activate" the role?

02 how to activate a role?

Use the set default role syntax to activate the roles required for user authentication. The specific methods are as follows:

mysql> set default role 'role_ro' to yeyz_ro@'%';
Query OK, 0 rows affected (0.00 sec)

Add role_ro this role is set to yeyz_ The default activation role of the RO account, so you can use yeyz_ro user to access the corresponding database, and the user will have a role_ro permissions for this role. As follows, use yeyz again_ Ro log in to MySQL service to view the current role:

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `role_ro`@`%`  |
+----------------+
1 row in set (0.00 sec)

Carry out relevant operations,

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yeyz               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yeyz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test_tbl0      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test_tbl0;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

If we delete, an error will be reported because of the role_ro has only select permission.

mysql> delete from yeyz.test_tbl0;
ERROR 1142 (42000): DELETE command denied to user 'yeyz_ro'@'127.0.0.1' for table 'test_tbl0'
mysql> 

This method of "activating" the role allows the user to have the permissions of the role, but it is not difficult to see that each time a role is bound to a new user, the user must be activated before the new user logs in. From the perspective of operation, it is not particularly convenient. How to make all the specified roles take effect immediately?

MySQL provides a system parameter to solve this problem. The parameter is:

mysql> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login         | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

This parameter is closed by default and can be opened directly.

03

How to switch between multiple roles?

We know that when we create a user, we can bind multiple roles to it, so how to switch between multiple roles is up to us.

First, create an account yeyz_ro_and_rw, and add the above role_ro and role_rw bind the two roles to the user and specify the role_ro is the default role:

mysql> create user yeyz_ro_and_rw@'%' identified by 'yeyz';
Query OK, 0 rows affected (0.02 sec)

mysql> grant 'role_ro' to yeyz_ro_and_rw@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant 'role_rw' to yeyz_ro_and_rw@'%';
Query OK, 0 rows affected (0.00 sec)


mysql> set default role 'role_ro' to yeyz_ro_and_rw@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
192:~ root# /usr/local/mysql_8.0/bin/mysql -uyeyz_ro_and_rw -pyeyz -h127.0.0.1 --socket=/data/mysql_5306/tmp/mysql.sock --port=5306
...
mysql> use yeyz;
Database changed

mysql> select * from test_tbl0;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

mysql> insert into test_tbl0 values (4,'zhaoliu');
ERROR 1142 (42000): INSERT command denied to user 'yeyz_ro_and_rw'@'127.0.0.1' for table 'test_tbl0'
mysql> 

As you can see, role_ro has the right to select, so the account yeyz_ro_and_rw can test the following tables in the yeyz database_ Tbl0 performs the select operation, but an error is reported when executing the insert operation. It's also easy to understand because the role_ro does not have insert permission.

Switch the user's role to role_rw, execute again:

mysql> insert into test_tbl0 values (4,'zhaoliu');
ERROR 1142 (42000): INSERT command denied to user 'yeyz_ro_and_rw'@'127.0.0.1' for table 'test_tbl0'
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `role_ro`@`%`  |
+----------------+
1 row in set (0.00 sec)

mysql> set role 'role_rw';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_tbl0 values (4,'zhaoliu');
Query OK, 1 row affected (0.01 sec)

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `role_rw`@`%`  |
+----------------+
1 row in set (0.00 sec)

You can see that when you execute the insert operation again, the execution is successful because the role is switched to role_rw, while role_rw has insert permission.

04 mandatory role definition

Mandatory role, as its name implies, is a role that the user account is forcibly bound to. If we want to give users certain permissions when creating users, we can set a mandatory role to give permissions to all newly generated users. Generally, mandatory roles need to be defined in my.cnf file. Suppose we want to define a mandatory role with select permission on yeyz library. There are two methods:

The first is to write in the configuration file:

[mysqld]
mandatory_roles='role_ro'

In this way, all newly created accounts have the permissions of this role.

The second is to enter the following commands during operation:

set persist mandatory_roles = 'role_ro';

In this way, the mandatory role takes effect permanently.

There are the following points to note

1. The permanent effect here means that the configuration will take effect even if the MySQL server is restarted. It is equivalent to changing the my.cnf configuration file, which requires set global syntax different from MySQL 5.7.

2. Mandatory roles also need to be activated by using the set default role method, or by modifying the parameter activate_ all_ roles_ on_ The login method can take effect only after it is "activated".

3. Forced roles cannot be recycled or deleted through the revoke method or drop syntax

4. Cannot include system_ The role of user permission is listed in the mandatory role list.

5. If only the role is specified as a mandatory role in the configuration file, but the role does not actually exist in the mysql.user table, the subsequently created account will not inherit the permissions of the role. If the mandatory roles are supplemented manually in the MySQL instance, flush privileges is required to ensure that the settings take effect.

05 revoke role and reclaim role permission

Similar to granting a role to an account, we can revoke the role from the account using the revoke method. Next, we demonstrate how to revoke the role from the account yeyz_ Undo role in RO_ Ro role:

mysql> show grants for yeyz_ro@'%' using 'role_ro';
+----------------------------------------------+
| Grants for yeyz_ro@%                         |
+----------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_ro`@`%` |
| GRANT SELECT ON `yeyz`.* TO `yeyz_ro`@`%`    |
| GRANT `role_ro`@`%` TO `yeyz_ro`@`%`         |
+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke 'role_ro' from yeyz_ro@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for yeyz_ro@'%' using 'role_ro';
ERROR 3530 (HY000): `role_ro`@`%` is not granted to `yeyz_ro`@`%`
mysql> show grants for yeyz_ro@'%';
+-------------------------------------+
| Grants for yeyz_ro@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `yeyz_ro`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

We see that yeyz is recycled using the revoke syntax_ Role of RO_ After RO, the corresponding yeyz_ro account permissions are also reduced.

Recycle role permissions:

We can reclaim the permissions of a role through the revoke method, such as role_rw is the role's permission to add, delete, modify and query. We can reclaim its permission to delete, as follows:

mysql> show grants for yeyz_rw@'%' using 'role_rw';
+-------------------------------------------------------------------+
| Grants for yeyz_rw@%                                              |
+-------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_rw`@`%`                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `yeyz`.* TO `yeyz_rw`@`%` |
| GRANT `role_rw`@`%` TO `yeyz_rw`@`%`                              |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke delete on yeyz.* from  role_rw;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for yeyz_rw@'%' using 'role_rw';
+-----------------------------------------------------------+
| Grants for yeyz_rw@%                                      |
+-----------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO `yeyz_rw`@`%`              |
| GRANT SELECT, INSERT, UPDATE ON `yeyz`.* TO `yeyz_rw`@`%` |
| GRANT `role_rw`@`%` TO `yeyz_rw`@`%`                      |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

It can be seen that after we reclaim the permissions of the role, the permissions of the account bound to the role will be reduced accordingly, so this operation is linked.

3, Differences between roles and accounts

01 difference between roles and accounts

We know that when we create a role, we can give the role certain permissions. We can also create an account and give the account certain permissions. Both are used to access the database, so what are the differences between them?

Difference 1: CREATE ROLE creates a role, which is a locked permission identifier by default, while CREATE USER creates an account, which is an unlocked permission identifier by default. In other words, the role created by CREATE ROLE is a pre operation. Once we bind it to the account, the subsequent activated role can use the established account.

Difference 2: differences in permissions. If we have CREATE ROLE and DROP ROLE permissions, we are allowed to use CREATE ROLE and DROP ROLE statements; If we have CREATE USER permission, we are allowed to use statements such as ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES.

Difference 3: roles can be bound to accounts, which means that we can use a global role to batch manage all accounts.

02 account and role interchangeability

Roles can be bound to users. Of course, MySQL 8.0 also supports binding users to roles. To a certain extent, accounts and roles are interchangeable, as follows:

#Create a role r1 and an account u1
mysql> create role 'r1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)
#Respectively grant select permission, r1 has yeyz database permission, and u1 has yeyz2 database permission
mysql> grant select on yeyz.* to 'r1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on yeyz2.* to 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)

#--------------Bind u1 and r1 to each other-----------
mysql> grant 'u1' to 'r1';
Query OK, 0 rows affected (0.01 sec)

mysql> grant 'r1' to 'u1';
Query OK, 0 rows affected (0.00 sec)


#Check their permissions and find that u1 and r1 have each other's permissions.
mysql> show grants for 'u1' using 'r1';
+---------------------------------------+
| Grants for u1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`        |
| GRANT SELECT ON `yeyz`.* TO `u1`@`%`  |
| GRANT SELECT ON `yeyz2`.* TO `u1`@`%` |
| GRANT `r1`@`%` TO `u1`@`%`            |
+---------------------------------------+
4 rows in set (0.00 sec)

mysql> show grants for 'r1' using 'u1';
+---------------------------------------+
| Grants for r1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `r1`@`%`        |
| GRANT SELECT ON `yeyz`.* TO `r1`@`%`  |
| GRANT SELECT ON `yeyz2`.* TO `r1`@`%` |
| GRANT `u1`@`%` TO `r1`@`%`            |
+---------------------------------------+
4 rows in set (0.00 sec)

To a certain extent, an activated role and an account can replace each other. As long as the permissions are the same, we can think that using a role to access the database is the same as using an account to access the database. Because the essence of a role is a collection of permissions.

The interchangeability between roles and accounts provides us with an idea. If a database is in the development stage, there are multiple accounts on it, and the account permissions of a developer can bind a template development account with the developer account. In this way, if you want to manage the accounts of all developers at the same time, you only need to modify the template development account, It brings convenience to quickly manage permissions.

Posted on Fri, 26 Nov 2021 12:41:58 -0500 by Illusion