Przejdź do treści
Biblioteka skilli
Bezpieczeństwo · Przegląd i audyt

configuring-audit-logging

/configuring-audit-logging

Konfiguruje audyt SQL w klastrach CockroachDB, rejestrując uwierzytelnianie, zmiany uprawnień i dostęp do wrażliwych danych, wraz z politykami opartymi na rolach i weryfikacją ustawień.

Repozytorium
claude-plugin ↗
Licencja
Apache-2.0
Ostatnia zmiana repozytorium
09.10.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
2
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
4
Przestarzałość

Brak sygnałów. Repozytorium zmienione 09.10.2026, bez odwołań do wycofanych modeli i API.

Licencja

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

Co robi i kiedy

Konfiguruje audyt SQL w klastrach CockroachDB, rejestrując uwierzytelnianie, zmiany uprawnień i dostęp do wrażliwych danych, wraz z politykami opartymi na rolach i weryfikacją ustawień.

Oryginalny opis (EN)

Configures SQL audit logging on CockroachDB clusters to capture security-relevant events including authentication, privilege changes, and sensitive data access. Use when enabling audit logging for compliance, setting up role-based audit policies, or verifying audit configuration.

Jak działa

  1. When to Use This Skill
  2. Prerequisites
  3. Steps
  4. Safety Considerations
  5. Rollback
  6. References
Sekcje z nagłówków SKILL.md, w oryginale · 179 linii, 814 słów
Podgląd SKILL.md
---
name: configuring-audit-logging
description: Configures SQL audit logging on CockroachDB clusters to capture security-relevant events including authentication, privilege changes, and sensitive data access. Use when enabling audit logging for compliance, setting up role-based audit policies, or verifying audit configuration.
compatibility: Requires admin role for cluster setting changes. Role-based audit logging available on CockroachDB 22.2+.
metadata:
  author: cockroachdb
  version: "1.0"
---

# 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.

## 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

## 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();
```

## Steps

### 1. Check Current Audit Configuration

```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](references/sql-queries.md) for additional audit-related queries.

### 2. Enable Admin Audit Logging

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

### 3. Configure Role-Based Audit Logging

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';
```

### 4. Configure Slow Query Logging (Supplemental)

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';
```

### 5. Verify Audit Logging

```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
# On CockroachDB Cloud, check log export configuration
ccloud cluster info <cluster-name> -o json
# Look for log_export_config section
```

## Safety Considerations

**Performance impact:** Audit logging increases CPU and I/O overhead. The impact depends on the audit scope:

| Audit Scope | Performance Impact | Recommendation |
|-------------|-------------------|----------------|
| Admin audit only | Minimal | Safe for all environments |
| Role-based audit (targeted roles) | Low to moderate | Recommended for production |
| Cluster-wide all-statement logging | High | Use only during investigations |
| Slow query logging (threshold > 0) | Minimal | Safe for all environments |
| Slow query logging (threshold = 0) | Very high | Never 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

## 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;
```

## References

**Skill references:**
- [SQL queries for audit logging](references/sql-queries.md)

**Related skills:**
- [auditing-cloud-cluster-security](../auditing-cloud-cluster-security/SKILL.md) — Run a full security posture audit
- [hardening-user-privileges](../hardening-user-privileges/SKILL.md) — Create purpose-specific roles for targeted auditing

**Official CockroachDB Documentation:**
- [SQL Audit Logging](https://www.cockroachlabs.com/docs/stable/sql-audit-logging.html)
- [Role-Based Audit Logging](https://www.cockroachlabs.com/docs/stable/role-based-audit-logging.html)
- [Cluster Settings](https://www.cockroachlabs.com/docs/stable/cluster-settings.html)
- [Log Export (CockroachDB Cloud)](https://www.cockroachlabs.com/docs/cockroachcloud/export-logs.html)
Pliki w paczce (2)
  • SKILL.md 7 KB
  • references/sql-queries.md 4 KB

Wersje

W bazie nie ma innych skilli o nazwie configuring-audit-logging.

Migawka bazy z 13.11.2026 · wersja paczki c6ec1097cfbe (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 98db-audit-logging
    Konfiguruje audyt baz danych: pgaudit, wtyczki MySQL, SQL Server Audit i inne, rejestrując logowania, zmiany uprawnień i dostęp do wrażliwych tabel.
  • 98configuring-windows-event-logging-for-detection
    Konfiguruje rozbudowane zasady audytu zdarzeń w Windows, by dostarczać zdarzenia bezpieczeństwa do reguł wykrywania w SIEM.
  • 100enforcing-password-policies
    Konfiguruje i wymusza polityki haseł w klastrach CockroachDB: minimalną długość, wymogi złożoności i koszt hashowania, do wzmocnienia uwierzytelniania.
  • 90logging-monitoring
    Przegląda logowanie i monitoring pod kątem widoczności bezpieczeństwa, wycieków danych wrażliwych i pokrycia audytu.
  • 90audit-logging
    Zapisuje zdarzenia istotne dla bezpieczeństwa w odpornym na manipulacje dzienniku tylko do dopisywania, który mówi, kto co i kiedy zrobił.
  • 95auditing-cloud-cluster-security
    Audytuje bezpieczeństwo klastra CockroachDB w chmurze lub na własnym serwerze: sieć, uwierzytelnianie, uprawnienia, szyfrowanie, logi i kopie.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.