Oracle数据库登录失败审计与安全分析
1. 问题背景与审计需求
在数据库运维工作中，我们经常会遇到用户密码错误导致登录失败的情况。特别是在企业环境中，可能有多个应用系统共享同一个数据库，当出现连接问题时，快速定位到具体的错误源头显得尤为重要。
Oracle数据库提供了强大的审计功能，通过aud$基表可以记录各种登录事件。其中，当用户使用错误的用户名或密码尝试登录时，数据库会返回ORA-01017错误，同时在aud$表中留下相应记录。这些记录包含了客户端IP、登录时间等重要信息，可以帮助DBA快速定位问题。
注意：aud$表是Oracle审计功能的核心表，默认情况下只有SYS用户有查询权限。如果需要让其他用户查询，需要显式授权。
2. 审计功能配置检查
在开始查询之前，我们需要确保数据库的审计功能已经正确配置。Oracle数据库的审计功能可以通过以下SQL检查：
-- 检查审计参数设置 SELECT name, value FROM v$parameter WHERE name LIKE 'audit%'; -- 检查审计表空间使用情况 SELECT tablespace_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name = 'SYSAUX';如果审计功能未开启，可以使用以下命令开启基本审计：
-- 开启数据库审计 ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE; -- 重启数据库使设置生效 SHUTDOWN IMMEDIATE; STARTUP;3. 查询密码错误登录记录
3.1 基础查询方法
最基本的查询方式是直接筛选returncode为1017的记录，这对应着ORA-01017错误：
SELECT sessionid, userid, userhost, comment$text, spare1, ntimestamp# FROM aud$ WHERE returncode = 1017 AND ntimestamp# > SYSDATE - 1 ORDER BY ntimestamp# DESC;这个查询会返回过去24小时内所有密码错误的登录尝试，包含以下关键信息：
- sessionid：会话ID
- userid：尝试登录的用户名
- userhost：客户端主机信息
- comment$text：认证方式和客户端地址
- ntimestamp#：事件发生的时间戳
3.2 增强版查询
为了获取更详细的信息，我们可以改进查询：
SELECT TO_CHAR(ntimestamp#, 'YYYY-MM-DD HH24:MI:SS') AS event_time, userid AS username, REGEXP_SUBSTR(userhost, '[^\\]+$') AS client_hostname, REGEXP_SUBSTR(comment$text, 'HOST=[^)]+') AS client_ip, returncode, CASE returncode WHEN 1017 THEN '无效的用户名/密码' WHEN 28000 THEN '账户被锁定' WHEN 28009 THEN 'SYS用户需要指定SYSDBA/SYSOPER' ELSE '其他错误' END AS error_message FROM aud$ WHERE returncode IN (1017, 28000, 28009) AND ntimestamp# > SYSDATE - 1/24 -- 最近1小时 ORDER BY ntimestamp# DESC;这个增强版查询提供了：
- 格式化的事件时间
- 清晰的错误消息描述
- 提取出的客户端IP地址
- 客户端主机名（去除了域名部分）
4. 常见错误代码解析
在审计记录中，除了1017密码错误外，还会遇到其他相关错误代码：
| 错误代码 | 含义 | 可能原因 |
|---|---|---|
| 1017 | 无效的用户名/密码 | 密码错误或用户名不存在 |
| 28000 | 账户被锁定 | 多次密码错误导致账户锁定 |
| 28009 | 需要指定SYSDBA/SYSOPER | 使用SYS用户登录时未指定权限 |
| 1005 | 空密码 | 尝试使用空密码登录 |
| 1920 | 用户名冲突 | 用户名与现有用户或角色冲突 |
可以使用Oracle提供的oerr工具查询错误代码的详细信息：
[oracle@db01 ~]$ oerr ora 1017 01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action:5. 高级分析与报表
5.1 按用户统计失败次数
SELECT userid, COUNT(*) AS failed_attempts, MIN(ntimestamp#) AS first_attempt, MAX(ntimestamp#) AS last_attempt FROM aud$ WHERE returncode = 1017 AND ntimestamp# > SYSDATE - 7 -- 最近7天 GROUP BY userid ORDER BY failed_attempts DESC;这个查询可以帮助识别哪些账户经常出现密码错误，可能是：
- 用户忘记了密码
- 应用程序配置了错误的密码
- 有人尝试暴力破解账户
5.2 按客户端IP统计
SELECT REGEXP_SUBSTR(comment$text, 'HOST=[^)]+') AS client_ip, COUNT(*) AS failed_attempts, LISTAGG(userid, ',') WITHIN GROUP (ORDER BY userid) AS attempted_users FROM aud$ WHERE returncode = 1017 AND ntimestamp# > SYSDATE - 1 GROUP BY REGEXP_SUBSTR(comment$text, 'HOST=[^)]+') ORDER BY failed_attempts DESC;这个查询可以识别：
- 哪些IP地址在尝试大量密码错误登录
- 这些IP在尝试哪些用户账户
- 可能的暴力破解攻击来源
6. 自动化监控方案
6.1 创建监控视图
为了方便日常监控，可以创建一个专门的视图：
CREATE OR REPLACE VIEW failed_logins_vw AS SELECT TO_CHAR(ntimestamp#, 'YYYY-MM-DD HH24:MI:SS') AS event_time, userid AS username, REGEXP_SUBSTR(userhost, '[^\\]+$') AS client_hostname, REGEXP_SUBSTR(comment$text, 'HOST=[^)]+') AS client_ip, returncode, CASE returncode WHEN 1017 THEN '无效的用户名/密码' WHEN 28000 THEN '账户被锁定' WHEN 28009 THEN 'SYS用户需要指定SYSDBA/SYSOPER' ELSE '其他错误' END AS error_message FROM aud$ WHERE returncode IN (1017, 28000, 28009) ORDER BY ntimestamp# DESC;6.2 设置定期监控任务
可以创建一个定期运行的脚本，将可疑的登录尝试发送给DBA：
BEGIN FOR rec IN ( SELECT * FROM failed_logins_vw WHERE event_time > SYSDATE - 1/24 -- 最近1小时 ORDER BY event_time DESC ) LOOP -- 这里可以替换为实际的告警逻辑 DBMS_OUTPUT.PUT_LINE('警报: ' || rec.username || '从' || rec.client_ip || '登录失败: ' || rec.error_message); END LOOP; END; /7. 安全建议与最佳实践
定期审查审计记录：建议每天至少检查一次失败的登录尝试，特别是针对特权账户的尝试。
设置账户锁定策略：通过profile设置合理的FAILED_LOGIN_ATTEMPTS和PASSWORD_LOCK_TIME参数：
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1/24; -- 锁定1小时- 限制敏感账户的登录来源：使用数据库触发器限制特定账户只能从特定IP登录：
CREATE OR REPLACE TRIGGER restrict_login AFTER SERVERERROR ON DATABASE DECLARE v_ip VARCHAR2(100); BEGIN IF (IS_SERVERERROR(1017)) THEN SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO v_ip FROM dual; -- 如果SYS账户从非管理IP尝试登录 IF (USER = 'SYS' AND v_ip NOT IN ('192.168.1.100', '192.168.1.101')) THEN -- 记录额外审计信息 DBMS_AUDIT_MGMT.CREATE_AUDIT_EVENT( 'SYS_LOGIN_ATTEMPT', 'SYS login attempt from untrusted IP: ' || v_ip, DBMS_AUDIT_MGMT.LEVEL_HIGH); -- 可选：立即锁定会话 -- EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION '''||SYS_CONTEXT('USERENV','SESSIONID')||''' IMMEDIATE'; END IF; END IF; END; /- 定期清理审计记录：aud$表会不断增长，需要定期清理：
-- 设置审计记录自动清理 BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, last_archive_time => SYSTIMESTAMP-30); END; / -- 初始化清理作业 BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 24); END; /8. 常见问题排查
8.1 查询不到审计记录
如果查询aud$表没有返回任何记录，可能的原因包括：
- 审计功能未开启
- 审计记录已被清理
- 查询的时间范围设置不当
- 没有足够的权限查询aud$表
解决方案：
-- 检查审计状态 SELECT name, value FROM v$parameter WHERE name = 'audit_trail'; -- 检查当前用户的权限 SELECT * FROM session_privs WHERE privilege LIKE '%AUDIT%'; -- 尝试扩大查询时间范围 SELECT COUNT(*) FROM aud$ WHERE ntimestamp# > SYSDATE - 30;8.2 审计记录不完整
有时会发现某些失败的登录尝试没有记录在aud$中，可能的原因是：
- 审计策略没有覆盖这些事件
- 审计表空间已满
- 审计记录写入失败
解决方案：
-- 检查当前审计策略 SELECT * FROM dba_stmt_audit_opts; SELECT * FROM dba_priv_audit_opts; -- 检查表空间使用情况 SELECT tablespace_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name = 'SYSAUX'; -- 检查审计写入错误 SELECT * FROM dba_audit_trail WHERE returncode = 2002;8.3 性能问题
当aud$表记录过多时，查询可能会变慢。可以考虑以下优化措施：
- 创建适当的索引
CREATE INDEX idx_aud_returncode ON aud$(returncode) TABLESPACE users; CREATE INDEX idx_aud_timestamp ON aud$(ntimestamp#) TABLESPACE users;- 使用分区表（Oracle 12c及以上版本）
-- 需要先迁移aud$到分区表 BEGIN DBMS_AUDIT_MGMT.AUDIT_TRAIL_MOVE_TABLE( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, table_name => 'AUD$', new_table_name => 'AUD_PART', tablespace_name => 'AUDIT_TS'); END; /- 定期归档和清理旧记录（如前文所述）
9. 扩展应用场景
9.1 结合操作系统审计
除了数据库层面的审计，还可以结合操作系统审计日志，获取更全面的安全信息：
# Linux系统查看认证日志 grep 'oracle' /var/log/secure # Windows系统查看安全日志 Get-EventLog -LogName Security -InstanceId 4625 -After (Get-Date).AddDays(-1)9.2 集成到SIEM系统
可以将数据库审计记录集成到企业安全信息与事件管理(SIEM)系统中：
- 使用Oracle GoldenGate将aud$表变更实时同步到其他系统
- 编写定期导出脚本，将审计记录发送到SIEM系统
- 使用Oracle Audit Vault集中管理多数据库审计数据
9.3 自定义审计策略
除了默认的登录审计，还可以设置更精细的审计策略：
-- 审计特定用户的所有登录尝试 AUDIT SESSION BY jingyu; -- 审计所有失败的登录尝试 AUDIT SESSION WHENEVER NOT SUCCESSFUL; -- 审计特定权限的使用 AUDIT SELECT ANY TABLE, UPDATE ANY TABLE BY ACCESS;10. 实际案例分析
假设我们遇到一个场景：应用服务器突然无法连接数据库，日志显示密码错误，但确认密码没有更改过。
排查步骤：
- 首先查询最近的审计记录：
SELECT * FROM failed_logins_vw WHERE username = 'app_user' AND event_time > SYSDATE - 1/24 ORDER BY event_time DESC;发现记录显示来自应用服务器的IP确实有密码错误，但密码确认正确。
检查可能的字符集问题：
-- 检查数据库字符集 SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET'; -- 检查客户端NLS_LANG设置 -- 在应用服务器上执行 echo $NLS_LANG发现应用服务器的NLS_LANG被修改，导致密码字符串处理方式变化。
解决方案：
- 恢复原来的NLS_LANG设置
- 或者在数据库端创建密码时考虑字符集因素：
-- 使用明确的字符集转换 ALTER USER app_user IDENTIFIED BY "password" REPLACE "old_password" USING 'AL32UTF8';这个案例展示了审计记录如何帮助诊断看似神秘的连接问题。