Back to Details

implementing-database-audit-logging

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Audit Logger

数据库审计日志工具

This skill provides automated assistance for database audit logger tasks.
本技能可为数据库审计日志相关任务提供自动化协助。

Prerequisites

前置要求

Before using this skill, ensure:
  • Database credentials with CREATE TABLE and CREATE TRIGGER permissions
  • Understanding of compliance requirements (GDPR, HIPAA, SOX, PCI-DSS)
  • Sufficient storage for audit logs (estimate 10-30% of data size)
  • Decision on audit log retention period
  • Access to database documentation for table schemas
  • Monitoring tools configured for audit log analysis
使用本技能前，请确保满足以下条件：
  • 拥有具备CREATE TABLE和CREATE TRIGGER权限的数据库凭证
  • 了解合规要求（GDPR、HIPAA、SOX、PCI-DSS）
  • 有足够的存储空间存储审计日志（预估为数据体量的10%-30%）
  • 确定审计日志的留存周期
  • 可访问包含表结构的数据库文档
  • 已配置用于审计日志分析的监控工具

Instructions

使用说明

Step 1: Define Audit Requirements

步骤1：定义审计需求

  1. Identify tables requiring audit logging based on compliance needs
  2. Determine events to audit (INSERT, UPDATE, DELETE, SELECT for sensitive data)
  3. Define which columns contain sensitive data requiring audit
  4. Document retention requirements for audit logs
  5. Identify users/roles whose actions need auditing
  1. 根据合规需求确定需要开启审计日志的表
  2. 确定需要审计的事件（针对敏感数据的INSERT、UPDATE、DELETE、SELECT操作）
  3. 明确哪些列包含需要审计的敏感数据
  4. 记录审计日志的留存要求
  5. 识别需要审计其操作的用户/角色

Step 2: Choose Audit Strategy

步骤2：选择审计策略

  1. Trigger-Based Auditing: Best for comprehensive row-level tracking
    • Pros: Automatic, no application changes, captures all changes
    • Cons: Performance overhead, complex trigger maintenance
  2. Application-Level Auditing: Best for selective auditing
    • Pros: Flexible, lower database overhead, easier debugging
    • Cons: Requires application changes, can miss direct database changes
  3. Change Data Capture (CDC): Best for real-time streaming
    • Pros: Minimal performance impact, real-time analysis, external processing
    • Cons: Complex setup, requires CDC infrastructure
  4. Native Database Logs: Best for general monitoring
    • Pros: No setup, captures everything, built-in
    • Cons: High volume, limited retention, difficult to query
  1. 基于触发器的审计：最适合全面的行级追踪
    • 优点：自动执行，无需修改应用，可捕获所有变更
    • 缺点：有性能开销，触发器维护复杂
  2. 应用层审计：最适合选择性审计场景
    • 优点：灵活，数据库开销更低，更易调试
    • 缺点：需要修改应用代码，可能遗漏直接操作数据库的变更
  3. 变更数据捕获（CDC）：最适合实时流场景
    • 优点：性能影响极小，可实时分析，支持外部处理
    • 缺点：配置复杂，需要配套CDC基础设施
  4. 数据库原生日志：最适合通用监控场景
    • 优点：无需额外配置，可捕获所有操作，原生内置
    • 缺点：日志体积大，留存期有限，查询难度高

Step 3: Design Audit Table Schema

步骤3：设计审计表结构

  1. Create audit log table with these core columns:
    • audit_id (primary key), table_name, action (INSERT/UPDATE/DELETE)
    • record_id (reference to audited record), old_values (JSON), new_values (JSON)
    • changed_by (user), changed_at (timestamp), client_ip, application_context
  2. Add indexes on table_name, changed_at, changed_by for query performance
  3. Partition audit table by date for efficient archival
  4. Configure tablespace for audit logs separate from primary data
  1. 创建包含以下核心字段的审计日志表：
    • audit_id（主键）、table_name、action（INSERT/UPDATE/DELETE）
    • record_id（被审计记录的引用）、old_values（JSON）、new_values（JSON）
    • changed_by（操作用户）、changed_at（时间戳）、client_ip、application_context
  2. 为table_name、changed_at、changed_by字段添加索引以提升查询性能
  3. 按日期对审计表做分区以便高效归档
  4. 为审计日志配置与主数据分离的表空间

Step 4: Implement Audit Mechanism

步骤4：实现审计机制

  1. For trigger-based: Create AFTER INSERT/UPDATE/DELETE triggers on each table
  2. Capture old and new row values as JSON in trigger body
  3. Record user context (CURRENT_USER, application user, IP address)
  4. Handle trigger failures gracefully (log but don't block operations)
  5. Test triggers with sample data modifications
  1. 若采用触发器方案：在每张表上创建AFTER INSERT/UPDATE/DELETE触发器
  2. 在触发器逻辑中将新旧行值以JSON格式捕获
  3. 记录用户上下文（CURRENT_USER、应用用户、IP地址）
  4. 优雅处理触发器故障（记录日志但不阻塞业务操作）
  5. 使用示例数据修改操作测试触发器

Step 5: Configure Audit Log Management

步骤5：配置审计日志管理

  1. Set up automated archival of old audit logs to cold storage
  2. Implement audit log analysis queries for common compliance reports
  3. Create alerts for suspicious activities (bulk deletes, off-hours changes)
  4. Document audit log query procedures for compliance auditors
  5. Schedule periodic audit log reviews with security team
  1. 设置旧审计日志自动归档到冷存储的规则
  2. 实现用于生成常见合规报告的审计日志查询语句
  3. 针对可疑活动（批量删除、非工作时间变更）创建告警
  4. 为合规审计人员记录审计日志查询流程
  5. 定期安排安全团队对审计日志进行复盘

Step 6: Validate Audit Implementation

步骤6：验证审计实现

  1. Perform test operations on audited tables
  2. Verify audit log entries are created with complete data
  3. Test audit log queries for performance
  4. Confirm audit logs cannot be modified by regular users
  5. Document audit implementation for compliance documentation
  1. 在被审计表上执行测试操作
  2. 验证审计日志条目已生成且数据完整
  3. 测试审计日志查询的性能
  4. 确认普通用户无法修改审计日志
  5. 记录审计实现细节用于合规文档

Output

输出产物

This skill produces:
Audit Table Schema: SQL DDL for audit log table with proper indexes and partitioning
Audit Triggers: Database triggers for automatic audit log population on data changes
Audit Log Queries: Pre-built SQL queries for compliance reports and change tracking
Implementation Documentation: Configuration details, trigger logic, and maintenance procedures
Compliance Report Templates: SQL queries for GDPR access logs, SOX change reports, etc.
本技能可生成以下内容：
审计表结构：包含合理索引和分区配置的审计日志表SQL DDL语句
审计触发器：可在数据变更时自动填充审计日志的数据库触发器
审计日志查询语句：用于合规报告和变更追踪的预置SQL查询
实现文档：配置细节、触发器逻辑和维护流程说明
合规报告模板：用于GDPR访问日志、SOX变更报告等场景的SQL查询

Error Handling

错误处理

Trigger Performance Issues:
  • Audit only critical tables, not all tables
  • Use asynchronous audit logging with queue systems
  • Batch audit log inserts instead of individual inserts
  • Monitor trigger execution time and optimize trigger logic
Audit Table Growth:
  • Implement automated archival of audit logs older than retention period
  • Partition audit table by month or quarter
  • Compress old audit log partitions
  • Move historical audit logs to cheaper storage tiers
Missing Audit Context:
  • Set application context in database session before operations
  • Use database session variables to pass user identity
  • Implement connection pooling with session initialization
  • Log application user separately from database user
Permission Issues:
  • Ensure audit log table is writable by trigger execution context
  • Grant INSERT on audit table to all database users
  • Protect audit table from modifications (no UPDATE/DELETE grants)
  • Use separate schema for audit tables with restricted access
触发器性能问题：
  • 仅审计核心表，而非所有表
  • 配合队列系统使用异步审计日志
  • 批量插入审计日志而非单条插入
  • 监控触发器执行时间并优化触发器逻辑
审计表体积过大：
  • 自动归档超过留存期的审计日志
  • 按月或按季度对审计表做分区
  • 对旧的审计日志分区做压缩
  • 将历史审计日志迁移到更廉价的存储层级
审计上下文缺失：
  • 操作前在数据库会话中设置应用上下文
  • 使用数据库会话变量传递用户身份信息
  • 实现带会话初始化的连接池
  • 将应用用户与数据库用户分开记录
权限问题：
  • 确保触发器执行上下文可写入审计日志表
  • 为所有数据库用户授予审计表的INSERT权限
  • 保护审计表不被篡改（不授予UPDATE/DELETE权限）
  • 为审计表使用单独的schema并限制访问权限

Resources

资源

Audit Table Templates:
  • PostgreSQL audit trigger: 
    {baseDir}/templates/postgresql-audit-trigger.sql
  • MySQL audit trigger: 
    {baseDir}/templates/mysql-audit-trigger.sql
  • Audit table schema: 
    {baseDir}/templates/audit-table-schema.sql
Compliance Report Queries: 
{baseDir}/queries/compliance-reports/
  • GDPR data access report
  • SOX change audit report
  • User activity summary
  • Suspicious activity detection
Audit Strategy Guide: 
{baseDir}/docs/audit-strategy-selection.md
Performance Tuning: 
{baseDir}/docs/audit-performance-optimization.md
Archival Procedures: 
{baseDir}/scripts/audit-archival.sh
审计表模板：
  • PostgreSQL审计触发器：
    {baseDir}/templates/postgresql-audit-trigger.sql
  • MySQL审计触发器：
    {baseDir}/templates/mysql-audit-trigger.sql
  • 审计表结构：
    {baseDir}/templates/audit-table-schema.sql
合规报告查询：
{baseDir}/queries/compliance-reports/
  • GDPR数据访问报告
  • SOX变更审计报告
  • 用户活动汇总
  • 可疑活动检测
审计策略指南：
{baseDir}/docs/audit-strategy-selection.md
性能调优：
{baseDir}/docs/audit-performance-optimization.md
归档流程：
{baseDir}/scripts/audit-archival.sh

Overview

概述

This skill provides automated assistance for the described functionality.
本技能可为上述功能提供自动化协助。

Examples

示例

Example usage patterns will be demonstrated in context.
上下文将演示使用示例。