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密码错误外，还会遇到其他相关错误代码：

可以使用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. 安全建议与最佳实践

1. 定期审查审计记录：建议每天至少检查一次失败的登录尝试，特别是针对特权账户的尝试。

2. 设置账户锁定策略：通过profile设置合理的FAILED_LOGIN_ATTEMPTS和PASSWORD_LOCK_TIME参数：

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1/24; -- 锁定1小时

3. 限制敏感账户的登录来源：使用数据库触发器限制特定账户只能从特定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; /

4. 定期清理审计记录：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$表没有返回任何记录，可能的原因包括：

1. 审计功能未开启
2. 审计记录已被清理
3. 查询的时间范围设置不当
4. 没有足够的权限查询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$中，可能的原因是：

1. 审计策略没有覆盖这些事件
2. 审计表空间已满
3. 审计记录写入失败

解决方案：

-- 检查当前审计策略 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$表记录过多时，查询可能会变慢。可以考虑以下优化措施：

1. 创建适当的索引

CREATE INDEX idx_aud_returncode ON aud$(returncode) TABLESPACE users; CREATE INDEX idx_aud_timestamp ON aud$(ntimestamp#) TABLESPACE users;

2. 使用分区表（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; /

3. 定期归档和清理旧记录（如前文所述）

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)系统中：

1. 使用Oracle GoldenGate将aud$表变更实时同步到其他系统
2. 编写定期导出脚本，将审计记录发送到SIEM系统
3. 使用Oracle Audit Vault集中管理多数据库审计数据

9.3 自定义审计策略

除了默认的登录审计，还可以设置更精细的审计策略：

-- 审计特定用户的所有登录尝试 AUDIT SESSION BY jingyu; -- 审计所有失败的登录尝试 AUDIT SESSION WHENEVER NOT SUCCESSFUL; -- 审计特定权限的使用 AUDIT SELECT ANY TABLE, UPDATE ANY TABLE BY ACCESS;

10. 实际案例分析

假设我们遇到一个场景：应用服务器突然无法连接数据库，日志显示密码错误，但确认密码没有更改过。

排查步骤：

1. 首先查询最近的审计记录：

SELECT * FROM failed_logins_vw WHERE username = 'app_user' AND event_time > SYSDATE - 1/24 ORDER BY event_time DESC;

2. 发现记录显示来自应用服务器的IP确实有密码错误，但密码确认正确。

3. 检查可能的字符集问题：

-- 检查数据库字符集 SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET'; -- 检查客户端NLS_LANG设置 -- 在应用服务器上执行 echo $NLS_LANG

4. 发现应用服务器的NLS_LANG被修改，导致密码字符串处理方式变化。

5. 解决方案：

• 恢复原来的NLS_LANG设置
• 或者在数据库端创建密码时考虑字符集因素：

-- 使用明确的字符集转换 ALTER USER app_user IDENTIFIED BY "password" REPLACE "old_password" USING 'AL32UTF8';

这个案例展示了审计记录如何帮助诊断看似神秘的连接问题。