Introduction to MySQL role functions

Introduction to MySQL role functions

Preface:

In the previous article, we introduced the knowledge related to MySQL permission management. When there are a large number of libraries or users in a database instance, permission management becomes increasingly cumbersome, and permissions may need to be changed frequently. MySQL 8.0 adds the role function, which makes permission management more convenient. In this article, we will look at the role function under 8.0.

1. Introduction to role

The role function is not special to the Oracle database and is often used in Oracle. MySQL version 8.0 finally added the role function, providing a new idea for database user rights management.

A role can be seen as a collection of permissions, and this collection has a unified name: role. Multiple database users can be granted permissions of the same role. Permission changes can be achieved directly by modifying the role, without the need to change each user one by one, which facilitates operation and management. A role can be created, deleted, modified, and has its effects applied to the users it manages.

Let's take a closer look at the role functions:

# Create a role
mysql> create role 'dev_role';
Query OK, 0 rows affected (0.15 sec)

# Grant permissions to role mysql> grant select on db1.* to 'dev_role'@'%';
Query OK, 0 rows affected (0.12 sec)

# View the permissions of the role mysql> show grants for 'dev_role'@'%';
+-------------------------------------------+
| Grants for dev_role@% |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `dev_role`@`%` |
| GRANT SELECT ON `db1`.* TO `dev_role`@`%` |
+-------------------------------------------+

# Create a user and grant role permissions mysql> create user 'dev1'@'%' identified by '123456';
Query OK, 0 rows affected (0.68 sec)

mysql> grant 'dev_role' to 'dev1'@'%';
Query OK, 0 rows affected (0.38 sec)

# View user permissions mysql> show grants for 'dev1'@'%';
+------------------------------------+
| Grants for dev1@% |
+------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`%` |
| GRANT `dev_role`@`%` TO `dev1`@`%` |
+------------------------------------+
2 rows in set (0.63 sec)

# Log in to root@localhost using user dev1 ~]# mysql -udev1 -p123456

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

mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.59 sec)

What's going on? It seems that different from what we imagined, after granting a user a certain role permission, the user does not obtain the corresponding permission.

This occurs because the roles granted to the user are inactive in the user's session. The user has the privileges of this role only when the granted role is active in the session. To determine which roles are active in the current session, use the CURRENT_ROLE() function.

# Use the set default role command to activate the rolemysql> SET DEFAULT ROLE ALL TO dev1;
Query OK, 0 rows affected (0.77 sec)

# Re-login and find that the permissions are normal root@localhost ~]# mysql -udev1 -p123456

mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `dev_role`@`%` |
+----------------+
1 row in set (0.57 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
|db1|
| information_schema |
+--------------------+
2 rows in set (1.05 sec)

In addition to using the set default role command to activate the role, you can also modify the system variable activate_all_roles_on_login. This variable determines whether the role is automatically activated. The default is OFF, which means it is not automatically activated. It is recommended to change this variable to ON so that you do not need to manually activate the role after assigning it to a new user.

# View the activate_all_roles_on_login variablemysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (1.53 sec)

# Enable this variable. Enable it dynamically first. Then add this parameter to the my.cnf configuration file. mysql> set global activate_all_roles_on_login = on;
Query OK, 0 rows affected (0.50 sec)

# The role will then be automatically activatedmysql> create user 'dev2'@'%' identified by '123456';
Query OK, 0 rows affected (0.68 sec)

mysql> grant 'dev_role' to 'dev2'@'%';
Query OK, 0 rows affected (0.38 sec)

root@localhost ~]# mysql -udev2 -p123456

mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `dev_role`@`%` |
+----------------+
1 row in set (0.57 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
|db1|
| information_schema |
+--------------------+
2 rows in set (1.05 sec)

2. Role-related operations

Above we introduced how to create roles and grant role permissions to users. There are many more operations related to roles. Let's take a look at them.

# Change role permissions mysql> grant select on db2.* to 'dev_role'@'%';
Query OK, 0 rows affected (0.33 sec)

# The permissions of the user with this role will change accordingly after logging in again root@localhost ~]# mysql -udev1 -p123456

mysql> show databases;
+--------------------+
| Database |
+--------------------+
|db1|
| db2 |
| information_schema |
+--------------------+
3 rows in set (2.01 sec)

# Revoke role permissions mysql> revoke SELECT ON db2.* from 'dev_role'@'%';
Query OK, 0 rows affected (0.31 sec)

# Revoke a user's role mysql> revoke 'dev_role'@'%' from 'dev1'@'%';
Query OK, 0 rows affected (0.72 sec)

mysql> show grants for 'dev1'@'%';
+----------------------------------+
| Grants for dev1@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`%` |
+----------------------------------+
1 row in set (1.06 sec)

# Delete a role (after deleting a role, the corresponding user will also lose the permissions of the role)
mysql> drop role dev_role;
Query OK, 0 rows affected (0.89 sec)

We can also configure mandatory roles through the mandatory_roles variable. With mandatory roles, the server will grant the role to all users by default without the need to explicitly grant the role. You can use the my.cnf file or use SET PERSIST to configure, for example:

# my.cnf configuration [mysqld]
mandatory_roles = 'dev_role'

# set change variable SET PERSIST mandatory_roles = 'dev_role';


It should be noted that the permissions of the roles configured in mandatory_roles cannot be revoked or deleted.

Summarize:

Regarding role-related knowledge, a few points are briefly summarized as follows:

  • A role is a collection of permissions that can be assigned different permissions.
  • Enable the activate_all_roles_on_login variable to automatically activate roles.
  • A user can have multiple roles, and a role can be granted to multiple users.
  • Changes to role permissions will be applied to the corresponding users.
  • If you delete a role, users who have this role will also lose the permissions of this role.
  • You can set a mandatory role so that all users have the permissions of this role.
  • Role management is similar to user management, except that roles cannot be used to log in to the database.

The above is the detailed introduction of MySQL role function. For more information about MySQL role function, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL 8.0 user and role management principles and usage details
  • Detailed explanation of MySQL 8's new feature ROLE

<<:  Newbies quickly learn the steps to create website icons

>>:  About vue component switching, dynamic components, component caching

Recommend

Example code of vue + element ui to realize player function

The display without the effect picture is just em...

MySQL 5.7.13 installation and configuration method graphic tutorial (win10 64 bit)

This article shares with you the graphic tutorial...

How to detect Ubuntu version using command line

Method 1: Use the lsb_release utility The lsb_rel...

mysql trigger creation and usage examples

Table of contents What is a trigger Create a trig...

How to add shortcut commands in Xshell

As a useful terminal emulator, Xshell is often us...

MySql batch insert optimization Sql execution efficiency example detailed explanation

MySql batch insert optimization Sql execution eff...

mysql workbench installation and configuration tutorial under centOS

This article shares the MySQL Workbench installat...

MySQL 8.0.16 installation and configuration graphic tutorial under macOS

This article shares the installation and configur...

How to call the interrupted system in Linux

Preface Slow system calls refer to system calls t...

Detailed explanation of Linux netfilter/iptables knowledge points

Netfilter Netfilter is a packet processing module...

js canvas realizes circular water animation

This article example shares the specific code of ...

Example code for implementing equal width layout in multiple ways using CSS

The equal-width layout described in this article ...

Example of using the href attribute and onclick event of a tag

The a tag is mainly used to implement page jump, ...

In-depth analysis of Flex layout in CSS3

The Flexbox layout module aims to provide a more ...

Docker installs and runs the rabbitmq example code

Pull the image: [mall@VM_0_7_centos ~]$ sudo dock...