Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the functions of MySQL roles?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Brief introduction to role

The role role feature is not special to Oracle databases and is often used in Oracle. The function of role is finally added to MySQL version 8.0, which provides a new idea for database user rights management.

Role can be thought of as a collection of permissions with a uniform name, role name. Multiple database users can be granted the same role permission, and the permission change can be realized directly by modifying the role. There is no need for each user to change one by one, which is convenient for operation and management. Role can create, delete, modify, and act on the users it manages.

Let's experience the role role feature in detail:

# create role mysql > create role' dev_role'; Query OK, 0 rows affected (0.15 sec) # grant role permissions mysql > grant select on db1.* to 'dev_role'@'%'; Query OK, 0 rows affected (0.12 sec) # View the permissions mysql > show grants for' dev_role'@'%' of role +-+ | Grants for dev_role@% | +-+ | GRANT USAGE ON *. * TO `dev_ role` @ `%` | | GRANT SELECT ON `db1`. * TO `dev_ role` @ `%` | +-+ # create a user and give the role permission 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 dev1 user ~] # 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 happened? It seems that unlike what we imagined, after the user was given permission for a role, the user did not get the corresponding permission.

The reason for this is that the role granted to the user is inactive in the user session. The user has permissions for this role only if the granted role is active in the session, and to determine which roles are active in the current session, use the CURRENT_ROLE () function.

# activate the role mysql > SET DEFAULT ROLE ALL TO dev1; Query OK using the set default role command, 0 rows affected (0.77 sec) # re-login found normal permissions 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 roles, you can also modify the system variable activate_all_roles_on_login, which determines whether to activate role automatically. The default is OFF, which is not automatically activated. It is recommended that you change this variable to ON, so that you do not need to activate manually after you assign roles to new users.

# check the activate_all_roles_on_login variable mysql > 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 dynamically and then add this parameter to the my.cnf configuration file mysql > set global activate_all_roles_on_login = on Query OK, after 0 rows affected (0.50 sec) #, the character automatically activates mysql > create user 'dev2'@'%' identified by' 123456 mysql; 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 the creation of roles and the granting of role permissions to users. There are many more operations related to role, let's take a look at it.

# change role permissions mysql > grant select on db2.* to 'dev_role'@'%'; Query OK, 0 rows affected (0.33 sec) # permissions of users with this role will also change after re-login root@localhost ~] # mysql-udev1-p123456 mysql > show databases +-+ | Database | +-+ | db1 | | db2 | | information_schema | +-+ 3 rows in set (2.01 sec) # Recycling role permissions mysql > revoke SELECT ON db2.* from 'dev_role'@'%' Query OK, 0 rows affected (0.31 sec) # revoke the 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 the role (the corresponding user will also lose the permissions of the role after deleting 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 a mandatory role, the server assigns that role by default to all users without the need to display the executive grant role. You can use a my.cnf file or use SET PERSIST for configuration, for example:

# my.cnf configuration [mysqld] mandatory_roles='dev_role' # set change variable SET PERSIST mandatory_roles='dev_role'

It is important to note that roles configured in mandatory_roles cannot revoke their permissions or delete them.

Summary:

The knowledge about role role can be summarized as follows:

Role is a collection of permissions that can be assigned different permissions.

Turn on the activate_all_roles_on_login variable to activate the role automatically.

A user can have multiple roles, and a role can be granted to multiple users.

The role permission change is applied to the corresponding user.

If you delete a role, the user with that role will also lose permissions for this role.

A mandatory role can be set so that all users have permissions for this role.

Role management is similar to user management, except that roles cannot be used to log in to the database.

This is the end of the content of "what are the MySQL role functions"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.