通过 SQL AUDIT 无法查询客户端 IP 地址

本文介绍当 v$sql_audit 不能显示正确的客户端 IP 地址时，应该如何处理。

使用版本

OceanBase 数据库 V2.2.30 版本

问题现象

v$sql_audit 不能显示正确的客户端 IP 地址，USER_CLIENT_IP 字段为 0.0.0.0，CLIENT_IP 字段为 OBProxy 的 IP 地址。

obclient> SELECT * FROM fruit;
obclient> SELECT SID,usec_to_time(request_time),USER_CLIENT_IP,CLIENT_IP,CLIENT_PORT,QUERY_SQL FROM v$sql_audit WHERE QUERY_SQL LIKE '%SEELCT * FROM fruit%';
+------------+----------------------------+----------------+----------------+-------------+---------------------+
| SID        | uesc_to_time(request_time) | USER_CLIENT_IP | CLIENT_IP      | CLIENT_PORT | QUERY_SQL           |
+------------+----------------------------+----------------+----------------+-------------+---------------------+
| xxxxxxxxxx | 2020-12-23 16:11:12.405005 | 0.0.0.0        | xxx.xxx.xx.xxx | xxxxx       | SELECT * FROM fruit |
+------------+----------------------------+----------------+----------------+-------------+---------------------+
1 row in set (0.77 sec)

规避方式

可以通过以下两种方法之一解决该问题。

方法一：通过连接 obproxy 节点日志查询

1. 登录到 CLIENT_IP 字段记录的 OBProxy 的 IP 地址，进入 OBProxy 日志目录。

   [root@hostname /]# cd /home/admin/logs/obproxy/log
   

2. 通过以下命令查看 OBProxy 日志。

   其中，client_ip={xxx.xxx.xx.xxx:xxxxx}, server_ip={xxx.xxx.xx.xxx:2881} 记录了客户端的 IP 地址。

   cat obproxy.*|grep "3221504202"|grep "update t1 set u_num"|grep "client_ip"
   [2020-12-12 19:34:51.406608] INFO  [PROXY.SM] ob_mysql_sm.cpp:5999 [117269][Y0-7F2290973B20] [lt=45] [dc=0] Slow Query: ((client_ip={xxx.xxx.xx.xxx:28725}, server_ip={x.x.x.112:2881}, server_trace_id=YB420BA64E70-0005B3BE503107D8, route_type=ROUTE_TYPE_NONPARTITION_UNMERGE_LOCAL, user_name=root, tenant_name=ob_test0_37, cluster_name=obv22_stable, ob_proxy_protocol=0, cs_id=59272, proxy_sessid=1225, ss_id=2035, server_sessid=3221504202, sm_id=1304462, cmd_size_stats={client_request_bytes:47, server_request_bytes:47, server_response_bytes:33, client_response_bytes:33}, cmd_time_stats={client_transaction_idle_time_us=0, client_request_read_time_us=10, client_request_analyze_time_us=9, cluster_resource_create_time_us=0, pl_lookup_time_us=3348, pl_process_time_us=16, congestion_control_time_us=1, congestion_process_time_us=0, do_observer_open_time_us=1, server_connect_time_us=0, server_sync_session_variable_time_us=0, server_send_saved_login_time_us=0, server_send_use_database_time_us=0, server_send_session_variable_time_us=0, server_send_all_session_variable_time_us=0, server_send_last_insert_id_time_us=0, server_send_start_trans_time_us=0, build_server_request_time_us=0, plugin_compress_request_time_us=0, prepare_send_request_to_server_time_us=3388, server_request_write_time_us=30, server_process_request_time_us=1460, server_response_read_time_us=1, plugin_decompress_response_time_us=0, server_response_analyze_time_us=4, ok_packet_trim_time_us=9, client_response_write_time_us=103, request_total_time_us=4993}, sql=update t1 set u_num=33 where name ='gavin')
   

方法二： 通过会话连接查询

1. 查询 v$sql_audit 对应记录的会话 ID。

   obclient> SELECT SID, QUERY_SQL FROM v$sql_audit WHERE QUERY_SQL LIKE '%SEELCT * FROM fruit%';
   +------------+---------------------+
   | SID        | QUERY_SQL           |
   +------------+---------------------+
   | xxxxxxxxxx | SELECT * FROM fruit |
   +------------+---------------------+
   1 row in set (0.77 sec)
   

2. 登录 sys 租户，根据会话 ID 查询会话记录表。其中 user_client_ip 即为实际客户端的 IP 地址。

   obclient> SELECT id,host,user_client_ip,state FROM __all_virtual_processlist WHERE id='sid';
   +------------+----------------------+----------------+--------+
   | id         | host                 | user_client_ip | state  |
   +------------+----------------------+----------------+--------+
   | xxxxxxxxxx | xxx.xxx.xx.xxx:xxxxx | xxx.xxx.xx.xxx | ACTIVE |
   +------------+----------------------+----------------+--------+
   1 row in set (0.01 sec)