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:
|
<<: Pure JS method to export table to excel
>>: Summary of data interaction between Docker container and host
Recommend
5 ways to make your JavaScript codebase cleaner
Table of contents 1. Use default parameters inste...
Summary of MySQL InnoDB locks
Table of contents 1. Shared and Exclusive Locks 2...
Simple principles for web page layout design
This article summarizes some simple principles of...
When catalina.bat is set to UTF-8 in Tomcat, garbled characters appear on the console
1. The catalina.bat must be set to UTF-8. If I do...
Three uses and differences of MySQL not equal
Judgment symbols are often used in MySQL, and not...
HTML Frameset Example Code
This article introduces a framework made by Frame...
Detailed explanation of MySQL Group by optimization
Table of contents Standard execution process opti...
Detailed explanation of how to exit Docker container without closing it
After entering the Docker container, if you exit ...
HTML Editing Basics (A Must-Read for Newbies)
Open DREAMWEAVER and create a new HTML. . Propert...
A brief discussion on logic extraction and field display of Vue3 in projects
Table of contents Logical Layering Separate busin...
js uses Canvas to merge multiple pictures into one implementation code
Solution function mergeImgs(list) { const imgDom ...
The difference between HTML name id and class_PowerNode Java Academy
name Specify a name for the tag. Format <input...
How to obtain a permanent free SSL certificate from Let's Encrypt in Docker
1. Cause The official cerbot is too annoying. It ...
A detailed introduction to the CSS naming specification BEM from QQtabBar
BEM from QQtabBar First of all, what does BEM mea...
Linux command line operation Baidu cloud upload and download files
Table of contents 0. Background 1. Installation 2...