# EXPERIMENTAL_AUDIT

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

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

Warning:

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

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

As of version 2.0, 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]                 [5a]                     [5b]       [5c]  [6]  [7a] [7b]        [7c]            [7d]                         [7e]  [7f]  [7g] [7h]
    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

1. Date
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
   - a. Client address
   - b. Username
   - c. 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

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

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