Configure audit logging in YCQL

Audit logging can be used to record information about YCQL statements or events (such as login events) and log the records on a per-node basis into the YB-Tserver logs. Audit logging can be enabled on YugabyteDB cluster by setting the ycql_enable_audit_log TServer flag to true. By default, each TServer records all login events and YCQL commands issued to the server.

Audit record is logged before an operation attempts to be executed, and failures are audited as well. If an operation fails to execute, both operation execution and failure are logged. However, an error that happens during parsing or analysis of YCQL statement results only in an error audit record to be logged.

YCQL audit logging can be further customized using additional YB-TServer flags.

Enable audit logging

Audit logging for YCQL can be enabled by passing the --ycql_enable_audit_log flag to yb-tserver. The command to start the yb-tserver would look as follows:

$ yb-tserver <options> --ycql_enable_audit_log=true

Configure audit logging

Statements or events are recorded if they match all audit filters. That is, only the configured categories in the configured keyspaces by the configured users are recorded.

For the included flags, the default value (empty) means everything is included, while for the excluded flags the default value (empty) means nothing is excluded. By default everything is logged except events in system keyspaces.

If both the inclusion and exclusion flags are set for the same dimension (for example, users) then statements or events are recorded only if both match; that is, if they are in the set-difference between included entries and excluded entries. So that is allowed although it is redundant: the same semantics can be achieved by setting only the inclusion flag to the resulting set-difference.

The ycql_audit_log_level determines the log file where the audit records are written (that is, yb-tserver.INFO, yb-tserver.WARNING, or yb-tserver.ERROR).

Only ERROR-level logs are immediately flushed to disk, lower levels might be buffered.

Audit filters

Objects being audited

YB-TServer flags can be configured to determine which statements and events should be logged, audit logging can be configured along three different dimensions: categories (statement or event_)_ , users, and keyspaces.

Each can be configured either by inclusion (listing all statement categories, users, or keyspaces to be audited) or by exclusion of CQL commands (listing all statement categories, user, or keyspaces to be excluded from auditing).

The available flags are described in the following table:

Flag Valid Values Description Default Value
ycql_enable_audit_log true/false Enable YCQL audit false
ycql_audit_included_categories Comma-separated list of statement categories. Categories to audit empty
ycql_audit_excluded_categories Categories to exclude empty
ycql_audit_included_users Comma-separated list of users. Users to audit empty
ycql_audit_excluded_users Users to exclude empty
ycql_audit_included_keyspaces Comma-separated list of keyspaces. keyspaces to audit empty
ycql_audit_excluded_keyspaces keyspaces to exclude system,system_schema,system_virtual_schema,system_auth
ycql_audit_log_level INFO, WARNING, or ERROR. Severity level at which an audit is logged. ERROR

All the preceding flags are runtime flags, so they can be set without requiring yb-tserver restart.

Statements being audited

The valid statement categories are described in the following table.

Audit Category Covered YCQL statements or wire-protocol events
QUERY SELECT
DML INSERT, UPDATE, DELETE, BEGIN TRANSACTION, and batch statements.
DDL TRUNCATE, CREATE/ALTER/DROP KEYSPACE/TABLE/INDEX/TYPE
DCL LIST USERS/ROLES/PERMISSIONS, GRANT, REVOKE, CREATE/ALTER/DROP ROLE
AUTH Login error, login attempt, login success
PREPARE Prepared statement
ERROR Request failure
OTHER USE <keyspace>, EXPLAIN

Output format

Log record for a CREATE TABLE statement executed by user john, on keyspace prod:

E0920 09:07:30.679694 10725 audit_logger.cc:552] AUDIT: user:john|
host:172.151.36.146:9042|source:10.9.80.22|port:56480|timestamp:1600592850679|
type:CREATE_TABLE|category:DDL|ks:prod|scope:test_table|operation:create table
test_table(k int primary key, v int);

Each audit log record has the following components:

Field Notes
user User name (if available)
host IP of the node where the command is being executed
source IP address from where the request initiated
port Port number from where the request initiated
timestamp Unix timestamp (in milliseconds)
type Type of the request (`SELECT`, `INSERT`, etc.,)
category Category of the request (`DDL`, `DML`, etc.,)
ks Keyspace on which request is targeted to be executed (if applicable)
scope Target of the current operation, such as the table, user, type, or keyspace name for corresponding `CREATE`, `ALTER`, or `DROP` commands.
operation The YCQL command being executed.

Configuration examples

This section shows some examples of how to configure audit logging.

Log auth events only
ycql_enable_audit_log=true
ycql_audit_included_categories=AUTH
Log everything except SELECTs and DMLs
ycql_enable_audit_log=true
ycql_audit_excluded_categories=QUERY,DML
Log just DDLs on keyspaces ks1 by user1
ycql_enable_audit_log=true
ycql_audit_included_categories=DDL
ycql_audit_included_keyspace=ks1
ycql_audit_included_users=user1
Log DCLs by everyone except user dbadmin
ycql_enable_audit_log=true
ycql_audit_included_categories=DCL
ycql_audit_excluded_users=dbadmin