Oracle数据库：创建DDL触发器跟踪账户锁定及失败登录的咨询

一、扩展事件（Extended Events）—— 优先推荐 #

这是性能开销最低的方案，能精准捕获失败登录尝试和账户锁定事件，对数据库运行影响极小。

• 核心追踪事件：
  • login_failed：捕获所有失败登录尝试（包括导致账户锁定的多次失败尝试）
  • locked_out：专门触发于账户被系统锁定的瞬间

创建追踪会话脚本 #

CREATE EVENT SESSION [TrackFailedLoginsAndLockouts] ON SERVER 
ADD EVENT sqlserver.login_failed(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.session_id, sqlserver.username)),
ADD EVENT sqlserver.locked_out(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.session_id, sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'TrackFailedLoginsAndLockouts.xel', max_file_size=(10), max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON);
GO

-- 启动会话
ALTER EVENT SESSION [TrackFailedLoginsAndLockouts] ON SERVER STATE = START;
GO

查询追踪数据 #

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
    event_data.value('(event/data[@name="username"]/value)[1]', 'varchar(100)') AS Username,
    event_data.value('(event/data[@name="error_number"]/value)[1]', 'int') AS ErrorNumber,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS ClientHostname,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(100)') AS ClientAppName
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('TrackFailedLoginsAndLockouts*.xel', NULL, NULL, NULL)
) AS x;

二、SQL Server Audit —— 合规审计场景适用 #

适合需要长期留存审计记录、满足合规要求的场景，审计数据持久化存储，支持集中管理。

配置步骤 #

1. 创建审计文件对象

CREATE SERVER AUDIT [FailedLoginAndLockoutAudit]
TO FILE (FILEPATH = N'C:\SQLAudits\', MAXSIZE = 10 MB, MAX_ROLLOVER_FILES = 5, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT [FailedLoginAndLockoutAudit] WITH (STATE = ON);
GO

2. 创建服务器审计规范，关联目标事件组

CREATE SERVER AUDIT SPECIFICATION [TrackFailedLoginsAndLockoutsSpec]
FOR SERVER AUDIT [FailedLoginAndLockoutAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (LOCKED_OUT_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION [TrackFailedLoginsAndLockoutsSpec] WITH (STATE = ON);
GO

3. 查询审计记录

SELECT 
    event_time,
    action_id,
    session_server_principal_name AS Username,
    client_ip,
    application_name,
    statement
FROM sys.fn_get_audit_file('C:\SQLAudits\FailedLoginAndLockoutAudit_*.sqlaudit', DEFAULT, DEFAULT);

三、Windows事件日志监控 #

SQL Server的失败登录和账户锁定事件会同步写入Windows安全日志，适合结合系统监控工具批量分析。

• 关键事件ID：
  • 18456：SQL Server失败登录尝试
  • 18461：SQL Server账户被锁定

查看与分析方式 #

1. 手动查看：打开事件查看器 → 导航至「Windows日志」→「安全」，筛选事件ID为18456和18461的记录
2. PowerShell批量导出：

Get-WinEvent -FilterHashtable @{LogName='Security';ID=18456,18461} | Select-Object TimeCreated, Id, Message

注意事项 #

• 扩展事件的性能开销远低于传统SQL Trace，优先用于生产环境
• SQL Server Audit需要ALTER ANY SERVER AUDIT等服务器级别权限
• Windows事件日志需要操作系统管理员权限，需配置合理的日志留存周期避免被覆盖
• 所有方案需确保存储路径有足够磁盘空间，防止日志溢出导致的问题

内容的提问来源于stack exchange，提问作者Sadhana