在运维场景中,在定位到某个SQL引起系统故障之后,想知道是哪台机器发过来的,方便定位源头,该如何解决?
在 Oracle 数据库中记录登录用户的 IP 地址可以通过多种方法实现。以下是几种常见的方法,包括使用触发器、审计功能和自定义日志记录。
方法一:使用触发器记录登录用户的 IP 地址
-
创建一个日志表:
- 创建一个表来存储登录用户的 IP 地址和其他相关信息。
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 地址和其他信息。
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGINBEGININSERT 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'),SYSTIMESTAMPFROM v$session sWHERE s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');EXCEPTIONWHEN OTHERS THEN-- 记录错误信息DBMS_OUTPUT.PUT_LINE('Error in logon_trigger: ' || SQLERRM);END; END; /
方法二:使用审计功能记录登录用户的 IP 地址
-
启用审计功能:
- 启用 Oracle 的审计功能,记录用户的登录活动。
AUDIT SESSION;
-
查询审计日志:
- 使用
DBA_AUDIT_TRAIL
视图查询审计日志,获取登录用户的 IP 地址。
SELECT username, userhost, terminal, action_name, timestamp# FROM dba_audit_trail WHERE action_name = 'LOGON';
- 使用
方法三:使用自定义日志记录
-
创建一个日志表:
- 创建一个表来存储登录用户的 IP 地址和其他相关信息。
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 地址。
CREATE OR REPLACE PROCEDURE log_login_info (p_sid NUMBER,p_username VARCHAR2,p_program VARCHAR2,p_machine VARCHAR2,p_ip_address VARCHAR2 ) IS BEGININSERT 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 BEGININSERT INTO login_log (sid, username, program, machine, ip_address, login_time)VALUES (p_sid, p_username, p_program, p_machine, p_ip_address, SYSTIMESTAMP); EXCEPTIONWHEN OTHERS THEN-- 记录错误信息DBMS_OUTPUT.PUT_LINE('Error in log_login_info: ' || SQLERRM); END log_login_info; /
-
创建一个触发器:
- 创建一个触发器,在用户登录时调用存储过程记录 IP 地址。
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGINlog_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 BEGINBEGINlog_login_info(SYS_CONTEXT('USERENV', 'SID'),SYS_CONTEXT('USERENV', 'SESSION_USER'),SYS_CONTEXT('USERENV', 'MODULE'),SYS_CONTEXT('USERENV', 'HOST'),SYS_CONTEXT('USERENV', 'IP_ADDRESS'));EXCEPTIONWHEN OTHERS THEN-- 记录错误信息DBMS_OUTPUT.PUT_LINE('Error in logon_trigger: ' || SQLERRM);END; END; /
方法四:使用 DBMS_NETWORK_ACL_ADMIN 包
-
创建一个日志表:
- 创建一个表来存储登录用户的 IP 地址和其他相关信息。
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 地址。
CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON DATABASE BEGININSERT 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'),SYSTIMESTAMPFROM v$session sWHERE 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 地址。希望这些方法对你有所帮助!