概念描述

 本文通过实际工作中的一个客户需求来简单介绍一下MySQL数据库中的代理用户这个概念，所谓的代理用户，顾名思义就是作为真实用户的代理来登录数据库，对数据库进行访问，原则上讲，官方文档中描述的情况是建议不要用隐藏在代理用户后面的真实用户登录，代理就是为了不暴露真实用户而已。
 实际使用中，客户有这么一个需求：应用代码中连接数据库的用户信息（用户名/密码）写死在程序代码包中，部署了很多台机器应用服务器，发布后不想再进行对用户和密码的修改，而开发也想在上线前期使用该业务用户进行数据运维，后期上线后进行收回或者注销，这样同时使用代理用户和真实用户，就可以实现这个功能。

测试验证

 接下来我们通过实际例子来进一步了解：

-- 1. 创建真实用户real_user 并赋权
root@localhost:mysql 11:51:29 >create user real_user@'%' identified by 'real_user';
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql 11:51:56 >grant all on test.* to real_user@'%';
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql 11:52:50 >flush privileges;
Query OK, 0 rows affected (0.01 sec)

-- 2. 创建代理用户p_user 使用认证插件 mysql_native_password 
root@localhost:mysql 11:53:05 > create user p_user@'%' identified with mysql_native_password by 'p_user';
Query OK, 0 rows affected (0.00 sec)

-- 3. 给代理用户授予代理权限
root@localhost:mysql 11:57:00 >grant proxy on real_user to p_user;
Query OK, 0 rows affected (0.00 sec)

-- 5. 提示：mysql_native_password这个插件自带 proxy 用户功能，所以需要设置一下相关参数：
root@localhost:mysql 11:59:21 >show global variables like '%proxy%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| check_proxy_users                 | OFF   |
| mysql_native_password_proxy_users | OFF   |
| sha256_password_proxy_users       | OFF   |
+-----------------------------------+-------+
3 rows in set (0.01 sec)

root@localhost:mysql 12:02:20 >set global check_proxy_users=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql 12:02:31 >set global mysql_native_password_proxy_users=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql 12:02:40 >set global sha256_password_proxy_users=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql 12:03:20 >exit
Bye

-- 6. 使用代理用户p_user登录MySQL: 可以正常登录
[root@c1 ~]# /data/mysql/mysql5730/bin/mysql -up_user -pp_user -h192.168.139.128 -P5730
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 11
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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.

p_user@192.168.139.128:(none) 12:07:15 >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

-- 7. 确认下代理用户的信息
## 变量proxy_user的值为代理用户p_user
p_user@192.168.139.128:(none) 12:07:20 >select @@proxy_user;
+--------------+
| @@proxy_user |
+--------------+
| 'p_user'@'%' |
+--------------+
1 row in set (0.00 sec)

## 查看当前登录用户current_user()，用户实际上是real_user
p_user@192.168.139.128:(none) 12:08:15 >select user(),current_user();
+-----------+----------------+
| user()    | current_user() |
+-----------+----------------+
| p_user@c1 | real_user@%    |
+-----------+----------------+
1 row in set (0.00 sec)

## 代理用户p_user的权限与真实用户real_user的权限一致，其实就是真实用户的权限。
p_user@192.168.139.128:(none) 12:08:42 >show grants;
+-----------------------------------------------------+
| Grants for real_user@%                              |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'real_user'@'%'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'real_user'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

-- 8. 使用真实用户登录：
[root@c1 ~]# /data/mysql/mysql5730/bin/mysql -ureal_user -preal_user -h192.168.139.128 -P5730
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 12
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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.

real_user@192.168.139.128:(none) 12:19:53 >select user(),current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| real_user@c1 | real_user@%    |
+--------------+----------------+
1 row in set (0.00 sec)

real_user@192.168.139.128:(none) 12:20:06 >select @@proxy_user;
+--------------+
| @@proxy_user |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

real_user@192.168.139.128:(none) 12:20:18 >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

real_user@192.168.139.128:(none) 12:20:31 >
说明：真实用户同样可以登录，和代理用户具有相同的权限。

-- 9. 使用root用户查看在线会话
root@localhost:(none) 12:21:58 >show full processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+-----------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                  | Info                  |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+-----------------------+
|  1 | system user |           | NULL | Connect | 21591 | Connecting to master                                   | NULL                  |
|  2 | system user |           | NULL | Connect | 19983 | Slave has read all relay log; waiting for more updates | NULL                  |
|  7 | root        | localhost | NULL | Query   |     0 | starting                                               | show full processlist |
| 12 | real_user   | c1:53254  | NULL | Sleep   |   108 |                                                        | NULL                  |
| 13 | p_user      | c1:53256  | NULL | Sleep   |     3 |                                                        | NULL                  |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)
说明：在这里可以很明显识别出代理用户和真实用户，方便后台运维查找，符合客户使用场景，通过用户名来区分哪些是来自代码中的用户（real_user），哪些是来自业务人员的用户（p_user）。

知识总结

1. 真实用户不能是匿名用户，也不能给用户赋予一个匿名 PROXY 用户。
2. 多个用户可以共用一个代理用户，但是不推荐。
3. 代理相关参数需要注意开启：check_proxy_users，mysql_native_password_proxy_users，sha256_password_proxy_users
4. 通常代理后面的真实用户不登录，只使用代理用户登录（特殊场景，如本例中的场景除外）
5. MySQL发展到现在的版本（8.0），使用角色可以替代代理用户的功能。

参考文档

https://dev.mysql.com/doc/refman/5.7/en/proxy-users.html