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

This article mainly explains "how to use MySQL role management". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how to use MySQL role management".

1 configure proxy

Mysql > show variables like "proxy%" # check whether the current proxy is enabled OFF means +-- +-- + | Variable_name | Value | +-- +-+ | check_proxy_users | | OFF | | mysql_native_password_proxy_users | OFF | | proxy_user | sha256_password_proxy_users | OFF | +-- +-+ 4 rows in set (0.02 sec) mysql > set global check_proxy_users = on | Query OK, 0 rows affected (0.00 sec) mysql > set global mysql_native_password_proxy_users = on; Query OK, 0 rows affected (0.01 sec) mysql > exit

The above parameters are not valid for the current session. You need to log in again after exiting, or set them directly to my.cnf.

2 create roles and users

Mysql > create user role_dba; Query OK, 0 rows affected (1.03 sec) mysql > create user 'jack'; Query OK, 0 rows affected (0.01 sec) mysql > create user' mary'; Query OK, 0 rows affected (0.01 sec)

To set a password, you can use identified by'#'if you need a password.

3 permission mapping

Map the permissions of role_dba (map) to jack and mary

Mysql > grant proxy on role_dba to jack; Query OK, 0 rows affected (0.02 sec) mysql > grant proxy on role_dba to mary; Query OK, 0 rows affected (0.01 sec)

4 empower users

Weight role_dba (emulate role weight)

Mysql > grant select on *. * to role_dba; Query OK, 0 rows affected (0.01sec) mysql > show grants for role_dba +-- + | Grants for role_dba@% | +-+ | GRANT SELECT ON *. * TO 'role_dba'@'%' | +-- -- + 1 row in set (0.00 sec) mysql > show grants for jack +-+ | Grants for jack@% | +-+ | GRANT USAGE ON *. * TO 'jack'@'%' | | GRANT PROXY ON' role_dba'@'%' TO 'jack'@'%' | +-+ 2 rows in set (0.00 sec) mysql > show grants for mary +-+ | Grants for mary@% | +-+ | GRANT USAGE ON *. * TO 'mary'@'%' | | GRANT PROXY ON' role_dba'@'%' TO 'mary'@'%' | +-+ 2 rows in set (0.00 sec)

5 View mysql.proxies_priv

Mysql > select * from mysql.proxies_priv +-+ | Host | User | Proxied_host | Proxied _ user | With_grant | Grantor | Timestamp | +-+-- -+ | localhost | root | 1 | boot@connecting host | 0000-00-0000: 00:00 | |% | will |% | will_dba | 0 | root@localhost | 0000-00-0000: 00:00 | |% | tom |% | will_dba | | 0 | root@localhost | 0000-00-0000: 00:00 | |% | jack |% | role_dba | 0 | root@localhost | 0000-00-0000: 00:00 |% | mary |% | role_dba | 0 | root@localhost | 0000-00-0000: 00:00 | + | -+ 5 rows in set (0.01sec)

6 Verification

$mysql-h 127.0.0.1-u jack Welcome to the MySQL monitor. Commands end with; or\ g. Your MySQL connection id is 249 Server version: 5.7.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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 > select * from test.ssd limit 1 +-+ | a | b | c | +-- + | 1 | NULL | NULL | +-- + 1 row in set (0.01 sec) Thank you for your reading. This is the content of "how to use MySQL role Management". After studying this article I believe you have a deeper understanding of how to use MySQL role management, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.