Back to Details

configuring-audit-logging

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Configuring Audit Logging

配置审计日志

Configures SQL audit logging on CockroachDB clusters to capture security-relevant events such as authentication attempts, privilege changes, DDL operations, and sensitive data access. Supports both cluster-wide audit settings and role-based audit policies for targeted logging.
在CockroachDB集群上配置SQL审计日志，以捕获与安全相关的事件，例如身份验证尝试、权限变更、DDL操作和敏感数据访问。支持集群级审计设置和基于角色的审计策略，实现针对性日志记录。

When to Use This Skill

何时使用此技能

  • Enabling audit logging to meet SOC 2, HIPAA, or PCI DSS compliance requirements
  • Setting up role-based audit policies for specific users or roles
  • Verifying that audit logging is properly configured and capturing events
  • Responding to a security audit finding about missing audit trails
  • Investigating security incidents by reviewing audit log configuration
  • 启用审计日志以满足SOC 2、HIPAA或PCI DSS合规要求
  • 为特定用户或角色设置基于角色的审计策略
  • 验证审计日志是否已正确配置并捕获事件
  • 响应有关缺失审计跟踪的安全审计发现
  • 通过查看审计日志配置调查安全事件

Prerequisites

前提条件

  • SQL access with admin role (required to modify cluster settings)
  • CockroachDB version: 22.2+ for role-based audit logging
  • Log export configured for persistent audit trail (CockroachDB Cloud exports logs to your cloud provider)
  • Storage planning: Audit logging increases log volume; plan for additional storage
Check your access:
sql
-- Verify admin role
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();

-- Check CockroachDB version
SELECT version();
  • SQL访问权限：拥有admin角色（修改集群设置必需）
  • CockroachDB版本：22.2+（支持基于角色的审计日志）
  • 日志导出：已配置持久化审计跟踪（CockroachDB Cloud会将日志导出至你的云服务商）
  • 存储规划：审计日志会增加日志量，需规划额外存储
检查你的访问权限：
sql
-- 验证admin角色
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();

-- 检查CockroachDB版本
SELECT version();

Steps

步骤

1. Check Current Audit Configuration

1. 检查当前审计配置

sql
-- User audit logging configuration
SHOW CLUSTER SETTING sql.log.user_audit;

-- Admin audit logging
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- All audit-related settings
SELECT variable, value
FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable LIKE '%audit%'
ORDER BY variable;
See SQL queries reference for additional audit-related queries.
sql
-- 用户审计日志配置
SHOW CLUSTER SETTING sql.log.user_audit;

-- 管理员审计日志
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- 所有审计相关设置
SELECT variable, value
FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable LIKE '%audit%'
ORDER BY variable;
查看SQL查询参考文档获取更多审计相关查询语句。

2. Enable Admin Audit Logging

2. 启用管理员审计日志

Admin audit logging captures all SQL statements executed by users with the admin role.
sql
-- Enable admin audit logging
SET CLUSTER SETTING sql.log.admin_audit.enabled = true;
What is captured:
  • All SQL statements executed by admin users
  • DDL operations (CREATE, ALTER, DROP)
  • Grant and revoke operations
  • Cluster setting changes
管理员审计日志会捕获所有拥有admin角色的用户执行的SQL语句。
sql
-- 启用管理员审计日志
SET CLUSTER SETTING sql.log.admin_audit.enabled = true;
捕获内容：
  • 管理员用户执行的所有SQL语句
  • DDL操作（CREATE、ALTER、DROP）
  • 授权和撤销操作
  • 集群设置变更

3. Configure Role-Based Audit Logging

3. 配置基于角色的审计日志

Role-based audit logging allows targeted logging for specific roles. This is more efficient than cluster-wide logging.
sql
-- Enable audit logging for a specific role
-- Format: <role_name> <audit_mode>
-- Audit modes: ALL (all statements), READ (reads only), WRITE (writes only), NONE (disable)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';
Multiple roles:
sql
-- Audit multiple roles (newline-separated)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL
security_admin ALL
app_service_account READ';
Create purpose-specific audit roles:
sql
-- Create a role for users accessing sensitive data
CREATE ROLE sensitive_data_reader;
GRANT SELECT ON TABLE customers, payments, pii_table TO sensitive_data_reader;

-- Assign users to the audited role
GRANT sensitive_data_reader TO app_user;

-- Enable audit logging for this role
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';
基于角色的审计日志允许针对特定角色进行针对性日志记录，比集群级日志记录更高效。
sql
-- 为特定角色启用审计日志
-- 格式：<角色名称> <审计模式>
-- 审计模式：ALL（所有语句）、READ（仅读操作）、WRITE（仅写操作）、NONE（禁用）
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';
多个角色配置：
sql
-- 审计多个角色（换行分隔）
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL
security_admin ALL
app_service_account READ';
创建特定用途的审计角色：
sql
-- 创建用于访问敏感数据的角色
CREATE ROLE sensitive_data_reader;
GRANT SELECT ON TABLE customers, payments, pii_table TO sensitive_data_reader;

-- 将用户分配到该审计角色
GRANT sensitive_data_reader TO app_user;

-- 为该角色启用审计日志
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';

4. Configure Slow Query Logging (Supplemental)

4. 配置慢查询日志（补充）

Slow query logging captures queries exceeding a latency threshold, which can indicate unauthorized scans or data exfiltration attempts.
sql
-- Log queries taking longer than 1 second
SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '1s';

-- Log all queries (high overhead — use only for investigation)
-- SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '0';
慢查询日志会捕获超过延迟阈值的查询，这可以指示未授权扫描或数据泄露尝试。
sql
-- 记录执行时间超过1秒的查询
SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '1s';

-- 记录所有查询（开销高 — 仅用于调查场景）
-- SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '0';

5. Verify Audit Logging

5. 验证审计日志

sql
-- Confirm settings are active
SHOW CLUSTER SETTING sql.log.user_audit;
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- Execute a test statement to generate an audit event
SELECT 1;
Verify log delivery: On CockroachDB Cloud, audit logs are exported to your configured log sink (cloud provider logging service). Check your log export destination to verify events are being captured.
bash
undefined
sql
-- 确认设置已生效
SHOW CLUSTER SETTING sql.log.user_audit;
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- 执行测试语句生成审计事件
SELECT 1;
验证日志交付： 在CockroachDB Cloud中，审计日志会导出至你配置的日志接收器（云服务商日志服务）。检查你的日志导出目标以确认事件已被捕获。
bash
undefined

On CockroachDB Cloud, check log export configuration

在CockroachDB Cloud中，检查日志导出配置

ccloud cluster info <cluster-name> -o json
ccloud cluster info <cluster-name> -o json

Look for log_export_config section

查找log_export_config部分

undefined
undefined

Safety Considerations

安全注意事项

Performance impact: Audit logging increases CPU and I/O overhead. The impact depends on the audit scope:
Audit ScopePerformance ImpactRecommendation
Admin audit onlyMinimalSafe for all environments
Role-based audit (targeted roles)Low to moderateRecommended for production
Cluster-wide all-statement loggingHighUse only during investigations
Slow query logging (threshold > 0)MinimalSafe for all environments
Slow query logging (threshold = 0)Very highNever use in production
Storage impact: Audit logs increase log volume. Plan for:
  • Admin audit: ~1-5% increase in log volume
  • Role-based audit: Proportional to query volume of audited roles
  • All-statement logging: 10x+ increase in log volume
Recommendations:
  • Start with admin audit logging (minimal overhead, high value)
  • Add role-based auditing for sensitive data access roles
  • Avoid cluster-wide all-statement logging in production
  • Configure log rotation and retention policies
性能影响： 审计日志会增加CPU和I/O开销，影响程度取决于审计范围：
审计范围性能影响建议
仅管理员审计极小适用于所有环境
基于角色的审计（目标角色）低至中等推荐用于生产环境
集群级全语句日志记录高仅在调查期间使用
慢查询日志（阈值>0）极小适用于所有环境
慢查询日志（阈值=0）极高切勿在生产环境中使用
存储影响： 审计日志会增加日志量，需规划：
  • 管理员审计：日志量增加约1-5%
  • 基于角色的审计：与被审计角色的查询量成正比
  • 全语句日志记录：日志量增加10倍以上
建议：
  • 从管理员审计日志开始（开销极小，价值高）
  • 为敏感数据访问角色添加基于角色的审计
  • 避免在生产环境中使用集群级全语句日志记录
  • 配置日志轮转和保留策略

Rollback

回滚操作

sql
-- Disable user audit logging
SET CLUSTER SETTING sql.log.user_audit = '';

-- Disable admin audit logging
SET CLUSTER SETTING sql.log.admin_audit.enabled = false;

-- Reset slow query threshold to default
RESET CLUSTER SETTING sql.log.slow_query.latency_threshold;
sql
-- 禁用用户审计日志
SET CLUSTER SETTING sql.log.user_audit = '';

-- 禁用管理员审计日志
SET CLUSTER SETTING sql.log.admin_audit.enabled = false;

-- 将慢查询阈值重置为默认值
RESET CLUSTER SETTING sql.log.slow_query.latency_threshold;

References

参考资料

Skill references:
  • SQL queries for audit logging
Related skills:
  • auditing-cloud-cluster-security — Run a full security posture audit
  • hardening-user-privileges — Create purpose-specific roles for targeted auditing
Official CockroachDB Documentation:
技能参考：
  • 审计日志SQL查询
相关技能：
  • auditing-cloud-cluster-security — 执行完整的安全态势审计
  • hardening-user-privileges — 创建特定用途的角色以实现针对性审计
官方CockroachDB文档：