# EXPERIMENTAL_AUDIT

`EXPERIMENTAL_AUDIT` is a subcommand of `ALTER TABLE` that is used to turn SQL audit logging on or off for a table.

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 a detailed description of exactly what is logged, see the Audit Log File Format section below.

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.

Warning:

This is an experimental feature. The interface and output are subject to change.

Tip:

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.

Note:

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

The audit log file format is as shown below. The numbers above each column are not part of the format; they correspond to the descriptions that follow.

    [1]     [2]             [3] [4]                 [5]                                       [6]  [7a] [7b]        [7c]            [7d]                         [7e]  [7f]  [7g] [7h]  [7i]
    I180211 07:30:48.832004 317 sql/exec_log.go:90  [client=127.0.0.1:62503, user=root, n1]   13   exec "cockroach" {"ab"[53]:READ} "SELECT nonexistent FROM ab" {}    0.123 12   ERROR 0

1. Log level (`INFO`, `WARN`, `ERROR`, or `FATAL`) and date (in YYMMDD format)
2. Time (in UTC)
3. Goroutine ID. This column is used for troubleshooting CockroachDB and may change its meaning at any time.
4. Where the log line was generated
5. Logging tags (the contents and order may vary)
   - Client address
   - Username
   - Node ID
6. Log entry counter
7. Log message:
   - a. Label indicating where the data was generated (useful for troubleshooting)
   - b. Current value of the `application_name` session setting
   - c. Logging trigger:
     - The list of triggering tables and access modes for audit logs, since only certain (read/write) activities are added to the audit log
   - d. Full text of the query (Note: May contain PII)
   - e. Placeholder values, if any
   - f. Query execution time (in milliseconds)
   - g. Number of rows produced (e.g., for `SELECT`) or processed (e.g., for `INSERT` or `UPDATE`).
   - h. Status of the query
     - `OK` for success
     - `ERROR` otherwise
   - i. Number of times the statement was retried automatically by the server so far.

## Audit log file storage location

By default, audit logs are stored in the same directory as the other logs generated by CockroachDB.

To store the audit log files in a specific directory, pass the `--sql-audit-dir` flag to `cockroach start`.

Tip:

If your deployment requires particular lifecycle and access policies for audit log files, point `--sql-audit-dir` at 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, CockroachDB synchronously logs all of the activity of every user on a cluster in a way that is durable to system failures. Every query that causes a logging event must access the disk of the node on which audit logging is enabled. As a result, enabling SQL audit logs negatively impacts performance, and we recommend using SQL audit logs for security purposes only.

For debugging and troubleshooting on production clusters, the most performant way to log all queries is to turn on the cluster-wide setting `sql.trace.log_statement_execute`. For details, see Troubleshoot Query Behavior.

## 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 added to the audit log with a line that looks like the following:

    I180211 07:30:48.832004 317 sql/exec_log.go:90  [client=127.0.0.1:62503,user=root,n1] 13 exec "cockroach" {"customers"[53]:READ} "SELECT * FROM customers" {} 123.45 12 OK
    I180211 07:30:48.832004 317 sql/exec_log.go:90  [client=127.0.0.1:62503,user=root,n1] 13 exec "cockroach" {"customers"[53]:READ} "SELECT nonexistent FROM customers" {} 0.123 12 ERROR

To turn on auditing for more than one table, issue a separate `ALTER` statement for each table.

For a description of the log file format, see the Audit Log File Format section.

Tip:

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

- SQL Audit Logging
- `ALTER TABLE`
- `cockroach start` logging flags
- `SHOW JOBS`