EXPERIMENTAL_AUDIT is a subcommand of ALTER TABLE. When applied to a table, it enables or disables the recording of SQL audit events to the SENSITIVE_ACCESS logging channel for that table.
The SENSITIVE_ACCESS log output is also called the SQL audit log. See SQL Audit Logging for a detailed example.
SQL audit logs contain detailed information about queries being executed against your system, including:
- Full text of the query (which may include personally identifiable information (PII))
- Date/Time
- Client address
- Application name
For descriptions of all SQL audit event types and their fields, see Notable Event Types.
CockroachDB stores audit log information in a way that ensures durability, but negatively impacts performance. As a result, we recommend using SQL audit logs for security purposes only. For more information, see Performance considerations.
This feature is in preview. This feature is subject to change. To share feedback and/or issues, contact Support.
This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.
Synopsis
Required privileges
Only members of the admin role can enable audit logs on a table. By default, the root user belongs to the admin role.
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the table you want to create audit logs for. |
READ |
Log all table reads to the audit log file. |
WRITE |
Log all table writes to the audit log file. |
OFF |
Turn off audit logging. |
This command logs all reads and writes, and both the READ and WRITE parameters are required (as shown in the examples below). In a future release, this should change to allow logging only reads, only writes, or both.
Audit log file format
Audit log messages, like all log messages, consist of two sections:
- A payload that contains notable events structured in JSON. These can include information such as the application name, full text of the query (which may contain PII), user account that triggered the event, number of rows produced (e.g., for
SELECT) or processed (e.g., forINSERTorUPDATE), status of the query, and more. For more information on the possible event types logged to theSENSITIVE_ACCESSchannel, see Notable Event Types. - An envelope that contains event metadata (e.g., severity, date, timestamp, channel). Depending on the log format you specify when configuring logs, the envelope can be formatted either as JSON or as a flat prefix to the message.
Audit log file storage location
By default, audit logs are prefixed cockroach-sql-audit and are stored in the same directory as the other logs generated by CockroachDB.
To store the audit log files in a specific directory, configure the SENSITIVE_ACCESS channel with a custom dir path.
If your deployment requires particular lifecycle and access policies for audit log files, point SENSITIVE_ACCESS to a directory that has permissions set so that only CockroachDB can create/delete files.
Viewing schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.
Performance considerations
To ensure non-repudiation in audit logs, we recommend enabling auditable for the SENSITIVE_ACCESS channel. CockroachDB will then synchronously log all of the activity of every user on a cluster in a way that is durable to system failures. Note that every query that causes a logging event must access the disk of the node on which audit logging is enabled. As a result, enabling auditable on a logging channel negatively impacts performance, and we recommend using this setting for security purposes only.
For debugging and troubleshooting on production clusters, the most performant way to log all queries is to enable the SQL_EXEC logging channel. For details, see Logging Use Cases.
Examples
Turn on audit logging
Let's say you have a customers table that contains personally identifiable information (PII). To turn on audit logs for that table, run the following command:
ALTER TABLE customers EXPERIMENTAL_AUDIT SET READ WRITE;
Now, every access of customer data is logged to the SENSITIVE_ACCESS channel in a sensitive_table_access event that looks like the following:
I210323 18:50:10.951550 1182 8@util/log/event_log.go:32 ⋮ [n1,client=‹[::1]:49851›,hostnossl,user=root] 4 ={"Timestamp":1616525410949087000,"EventType":"sensitive_table_access","Statement":"‹SELECT * FROM \"\".\"\".customers›","User":"‹root›","DescriptorID":52,"ApplicationName":"‹$ cockroach sql›","ExecMode":"exec","NumRows":2,"Age":2.514,"FullTableScan":true,"TxnCounter":38,"TableName":"‹defaultdb.public.customers›","AccessMode":"r"}
The above example shows the default crdb-v2 log format. This can be changed to a different format (e.g., JSON). For details, see Configure Logs.
For descriptions of all SQL audit event types and their fields, see Notable Event Types.
To turn on auditing for more than one table, issue a separate ALTER statement for each table.
For a more detailed example, see SQL Audit Logging.
Turn off audit logging
To turn off logging, issue the following command:
ALTER TABLE customers EXPERIMENTAL_AUDIT SET OFF;
See also
Was this helpful?
