> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Role-based SQL Audit Logging

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

export const version = "stable";

Role-based SQL audit logging gives you detailed information about queries being executed against your system by specific users or roles. An event of type <InternalLink path="eventlog#role_based_audit_event">`role_based_audit_event`</InternalLink> is recorded when an executed query belongs to a user whose role membership corresponds to a role that is enabled to emit an audit log via the <InternalLink path="cluster-settings">`sql.log.user_audit` cluster setting</InternalLink>. The event is logged in the <InternalLink path="logging#sensitive_access">`SENSITIVE_ACCESS`</InternalLink> logging channel.

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

This page shows how to enable role-based SQL audit logging in CockroachDB, including:

* [How to configure audit logging.](#configure-logging)
* [Where the audit log files are stored.](#file-storage-location)
* [What the audit log files look like.](#file-format)

<Note>
  Logging, in general, can negatively impact performance. Log only what you require to limit impact to your workload.
</Note>

<Tip>
  For the best visibility into security-related events on your cluster, we recommend configuring `SENSITIVE_ACCESS` together with the `USER_ADMIN`, `PRIVILEGES`, and `SESSIONS` logging channels. To learn more, refer to <InternalLink path="logging-use-cases#security-and-audit-monitoring">Logging Use Cases</InternalLink>.
</Tip>

## Configure Logging

### Prerequisites

You must have:

* <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink> or `MODIFYCLUSTERSETTING` <InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink> to configure the cluster setting,
* an <InternalLink path="licensing-faqs#set-a-license">Enterprise license configured</InternalLink>, and
* your <InternalLink path="configure-logs">logging infrastructure configured</InternalLink>, particularly the [location of audit logs](#file-storage-location).

### Syntax of audit settings

`sql.log.user_audit`

With the <InternalLink path="cluster-settings">`sql.log.user_audit` cluster setting</InternalLink>, you can set an audit logging configuration using a table-like syntax, as shown in the [Examples](#examples). Each row in the configuration represents an audit setting and must be separated by a new line. An audit setting is comprised of two columns separated by a space:

* `USER/ROLE`: the name of the user or role you want to audit.
  * The *special* `USER/ROLE` value `ALL` applies the audit setting to *all* users.
* `STATEMENT_FILTER`: the statement filter for the role. The following keywords are valid:
  * `ALL` will enable audit logging for all SQL statements for the given audit setting.
  * `NONE` will exclude audit logging for all SQL statements for the given audit setting.

Once `sql.log.user_audit` is set, the default behavior is for role-based SQL audit logging to take effect immediately within user sessions.

`sql.log.user_audit.reduced_config.enabled`

When enabled, the <InternalLink path="cluster-settings">`sql.log.user_audit.reduced_config.enabled` cluster setting</InternalLink> computes a "reduced" audit configuration based on the user's current <InternalLink path="security-reference/authorization#roles">role memberships</InternalLink> and the current value for the `sql.log.user_audit` cluster setting. The "reduced" audit configuration is computed at the **first SQL event emitted by the user, after the setting is enabled**. When the cluster setting is enabled, CockroachDB computes the audit configuration once at <InternalLink path="show-sessions">session</InternalLink> start, instead of at each SQL event. However with the setting enabled, changes to the audit configuration (user role memberships or cluster setting configuration) are not reflected within a user session. To reflect the configuration changes in auditing behavior, users need to start a new session.

### Matching order

<Note>
  The order in which the audit settings in the configuration are specified is important, as shown in the [example of a user with multiple roles](#user-with-multiple-roles). When determining whether to emit a log, the first audit setting that matches a user is the audit setting that gets applied.
</Note>

For each statement executed, CockroachDB gets the user's role memberships and iterates through the audit settings, looking for a matching audit setting by role or username. At the first audit setting match, CockroachDB stops iteration. If auditing is enabled (the `STATEMENT_FILTER` is set to `ALL`) for the matching audit setting, the statement is logged as an audit event. If the `STATEMENT_FILTER` is set to `NONE`, the statement is not logged.

### Validate setting

You can check the value of the `sql.log.user_audit` setting by running the <InternalLink path="show-cluster-setting">`SHOW CLUSTER SETTING`</InternalLink> command:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CLUSTER SETTING sql.log.user_audit;
```

## File format

Audit log messages, like all <InternalLink path="logging-overview">log messages</InternalLink>, 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., for `INSERT` or `UPDATE` ), status of the query, and more. For more details on the information logged, refer to common fields of <InternalLink path="eventlog#role_based_audit_event">`role_based_audit_event`</InternalLink>.
* An envelope that contains event metadata (e.g., severity, date, timestamp, channel). Depending on the log format you specify when <InternalLink path="configure-logs">configuring logs</InternalLink>, the envelope can be formatted either as JSON or as a flat prefix to the message.

## File storage location

By <InternalLink path="configure-logs#default-logging-configuration">default</InternalLink>, audit logs are prefixed `cockroach-sql-audit` and are stored in the <InternalLink path="configure-logs#logging-directory">same directory</InternalLink> as the other logs generated by CockroachDB.

To store the audit log files in a specific directory, <InternalLink path="configure-logs#output-to-files">configure the `SENSITIVE_ACCESS` channel</InternalLink> with a custom `dir` path.

<Tip>
  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.
</Tip>

## Examples

### Exclude one role from logging

With the audit settings in this example,

* Users with the username or role `service_account_role` will not emit audit logs for any statements they issue.
* All remaining users will emit audit logs for any statements they issue. `ALL` is used twice. The first `ALL` refers to all `USER/ROLE` s. The second `ALL` refers to logging all actions/statements.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.log.user_audit = '
    service_account_role NONE
    ALL ALL
';
```

### Include only one role for logging

With the audit settings in this example,

* Users with the username or role `test_role` will emit audit logs for all statements they issue.
* Users with the username or role `another_role` will not emit audit logs for any statements they issue. In principle, you can achieve this by simply omitting `another_role` from the configuration entirely. It is included here as a basis of comparison to the next example - [User with multiple roles](#user-with-multiple-roles).
* All remaining users will not emit audit logs for any statements they issue.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.log.user_audit = '
    test_role       ALL
    another_role    NONE
    ALL             NONE
';
```

### User with multiple roles

With the audit settings in this example,

* Users with the username or role `test_role` will not emit audit logs for any statements they issue.
* Users with the username or role `another_role` will emit audit logs for all statements they issue.
* All remaining users will not emit audit logs for any statements they issue.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.log.user_audit = '
    test_role       NONE
    another_role    ALL
    ALL             NONE
';
```

Grant an existing user, `test_user`, both roles `test_role` and `another_role`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT test_role to test_user;
GRANT another_role to test_user;
```

If `test_user` executes a statement, an audit log will not be emitted. In this case, CockroachDB would match `test_user` to role `test_role`, which is listed first and has the `STATEMENT_FILTER` set to `NONE`. Even though `test_user` is a member of role `another_role` which has `STATEMENT_FILTER` set to `ALL`, CockroachDB does not match `test_user` to that role because it is listed second.

## See also

* <InternalLink path="sql-audit-logging">Table-based SQL Audit Logging</InternalLink>
* <InternalLink path="grant">`GRANT`</InternalLink>
* <InternalLink path="logging-use-cases">Logging Use Cases</InternalLink>