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
Nodejs implements intranet penetration service
Table of contents 1. Proxy in LAN 2. Intranet pen...
The basic principles and detailed usage of viewport
1. Overview of viewport Mobile browsers usually r...
Detailed explanation of views in MySQL
view: Views in MySQL have many similarities with ...
MySQL error: Deadlock found when trying to get lock; try restarting transaction solution
Find the problem Recently, when I was filling in ...
Native js realizes the drag and drop of the nine-square grid
Use native JS to write a nine-square grid to achi...
Explain the deployment and configuration of Clickhouse Docker cluster with examples
Table of contents Written in front Environment de...
7 major elements of web page redesign Share the 7 major elements of web page redesign
Shopify Plus is the enterprise version of the e-c...
Several ways to center a box in Web development
1. Record several methods of centering the box: 1...
Instructions for using the --rm option of docker run
When the Docker container exits, the file system ...
DELL R730 server configuration RAID and installation server system and domain control detailed graphic tutorial
Recently, the company purchased a DELL R730 serve...
Detailed explanation of the simple use of MySQL query cache
Table of contents 1. Implementation process of qu...
HTML implementation of a simple calculator with detailed ideas
Copy code The code is as follows: <!DOCTYPE ht...
User-centered design
I've been asked a lot lately about an apparen...
Example of using CSS3 to achieve shiny font effect when unlocking an Apple phone
0. Introduction August 18, 2016 Today, I noticed ...
HTML table markup tutorial (30): cell dark border color attribute BORDERCOLORDARK
In cells, dark border colors can be defined indiv...