MySQL 实战笔记 第01期：MySQL 角色管理

mysql> show variables like "%proxy%"; #查看当前proxy是否开启，OFF 表示没有开启
+-----------------------------------+-------+
| 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

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)

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)

mysql> grant select on *.* to role_dba;
Query OK, 0 rows affected (0.01 sec)

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)

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

$ 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)