在运维场景中，在定位到某个SQL引起系统故障之后，想知道是哪台机器发过来的，方便定位源头，该如何解决？
在 Oracle 数据库中记录登录用户的 IP 地址可以通过多种方法实现。以下是几种常见的方法，包括使用触发器、审计功能和自定义日志记录。
方法一：使用触发器记录登录用户的 IP 地址
创建一个日志表：
- 创建一个表来存储登录用户的 IP 地址和其他相关信息。
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE login_log ( log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, sid NUMBER, username VARCHAR2(30), program VARCHAR2(48), machine VARCHAR2(64), ip_address VARCHAR2(15), login_time TIMESTAMP ); |
创建一个触发器：
- 创建一个触发器，在用户登录时自动记录 IP 地址和其他信息。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN BEGIN INSERT INTO login_log (sid, username, program, machine, ip_address, login_time) SELECT s.sid, s.username, s.program, s.machine, SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYSTIMESTAMP FROM v$session s WHERE s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); EXCEPTION WHEN OTHERS THEN -- 记录错误信息 DBMS_OUTPUT.PUT_LINE('Error in logon_trigger: ' || SQLERRM); END; END; / |
方法二：使用审计功能记录登录用户的 IP 地址
启用审计功能：
- 启用 Oracle 的审计功能，记录用户的登录活动。
|
1 |
AUDIT SESSION; |
查询审计日志：
- 使用 DBA_AUDIT_TRAIL 视图查询审计日志，获取登录用户的 IP 地址
|
1 2 3 4 5 6 7 8 9 10 |
SELECT username, userhost, terminal, action_name, timestamp# FROM dba_audit_trail WHERE action_name = 'LOGON'; |
方法三：使用自定义日志记录
创建一个日志表：
- 创建一个表来存储登录用户的 IP 地址和其他相关信息。
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE login_log ( log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, sid NUMBER, username VARCHAR2(30), program VARCHAR2(48), machine VARCHAR2(64), ip_address VARCHAR2(15), login_time TIMESTAMP ); |
创建一个存储过程：
- 创建一个存储过程，用于记录登录用户的 IP 地址。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE OR REPLACE PROCEDURE log_login_info ( p_sid NUMBER, p_username VARCHAR2, p_program VARCHAR2, p_machine VARCHAR2, p_ip_address VARCHAR2 ) IS BEGIN INSERT INTO login_log (sid, username, program, machine, ip_address, login_time) VALUES (p_sid, p_username, p_program, p_machine, p_ip_address, SYSTIMESTAMP); END log_login_info; /
-- 如果想记录错误信息，参考如下： CREATE OR REPLACE PROCEDURE log_login_info ( p_sid NUMBER, p_username VARCHAR2, p_program VARCHAR2, p_machine VARCHAR2, p_ip_address VARCHAR2 ) IS BEGIN INSERT INTO login_log (sid, username, program, machine, ip_address, login_time) VALUES (p_sid, p_username, p_program, p_machine, p_ip_address, SYSTIMESTAMP); EXCEPTION WHEN OTHERS THEN -- 记录错误信息 DBMS_OUTPUT.PUT_LINE('Error in log_login_info: ' || SQLERRM); END log_login_info; / |
创建一个触发器：
- 创建一个触发器，在用户登录时调用存储过程记录 IP 地址。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN log_login_info( SYS_CONTEXT('USERENV', 'SID'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYS_CONTEXT('USERENV', 'MODULE'), SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'IP_ADDRESS') ); END; /
-- 如果想记录错误信息，参考如下： CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN BEGIN log_login_info( SYS_CONTEXT('USERENV', 'SID'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYS_CONTEXT('USERENV', 'MODULE'), SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'IP_ADDRESS') ); EXCEPTION WHEN OTHERS THEN -- 记录错误信息 DBMS_OUTPUT.PUT_LINE('Error in logon_trigger: ' || SQLERRM); END; END; / |
方法四：使用 DBMS_NETWORK_ACL_ADMIN 包
创建一个日志表：
- 创建一个表来存储登录用户的 IP 地址和其他相关信息。
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE login_log ( log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, sid NUMBER, username VARCHAR2(30), program VARCHAR2(48), machine VARCHAR2(64), ip_address VARCHAR2(15), login_time TIMESTAMP ); |
创建一个触发器：
- 创建一个触发器，在用户登录时记录 IP 地址。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGIN INSERT INTO login_log (sid, username, program, machine, ip_address, login_time) SELECT s.sid, s.username, s.program, s.machine, SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYSTIMESTAMP FROM v$session s WHERE s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); END; / |
使用 DBMS_NETWORK_ACL_ADMIN 包
虽然 DBMS_NETWORK_ACL_ADMIN 包主要用于管理网络访问控制列表（ACL），但它与记录登录用户的 IP 地址没有直接关系。如果你有其他特定的需求，比如限制某些 IP 地址的访问，可以使用 DBMS_NETWORK_ACL_ADMIN 包来实现。但在这个场景中，我们主要关注的是记录登录用户的 IP 地址，所以不需要使用 DBMS_NETWORK_ACL_ADMIN 包。
注意事项
- 权限：
- 确保你有足够的权限创建表、触发器和存储过程。通常需要 SYSDBA 或 DBA 角色。
- 性能：
- 记录登录信息可能会对性能产生一定影响，特别是在高并发环境下。可以根据实际情况调整记录频率或使用异步记录方法。
- 安全性：
- 确保日志表的安全性，防止未授权访问和篡改。
通过以上方法，你可以有效地记录 Oracle 数据库中登录用户的 IP 地址。希望这些方法对你有所帮助！
