Configure Audit Logging in YCQL

• YSQL
• 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 theycql_enable_audit_logtserver flag to true. By Default, each TServer will record all login events and YCQL commands issued to the server.

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

YCQL Audit logging can be further customized by additional TServer flags described below.

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 auditing filters described by the flags above. i.e. only the configured categories in the configured keyspaces by the configured users will be 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 will be logged except events in system keyspaces.
• If both the inclusion and exclusion flags are set for the same dimension (e.g. users) then statements or events will be recorded only if both match: 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 will be written (i.e. yb-tserver.INFO, yb-tserver.WARNING, or yb-tserver.ERROR).
  Note that only ERROR-level logs are immediately flushed to disk, lower levels might be buffered.

Audit Filters

Objects being audited

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 of them 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 table below:

Flag	Valid Values	Description	Default Value
ycql_enable_audit_log	true/false	Whether to enable YCQL audit	false
ycql_audit_included_categories	comma-separated list of statement categories.	categories to be audited.	empty
ycql_audit_excluded_categories		categories to be excluded from auditing.	empty
ycql_audit_included_users	comma-separated list of users.	users to be audited.	empty
ycql_audit_excluded_users		users to be excluded from auditing.	empty
ycql_audit_included_keyspaces	comma-separated list of keyspaces.	keyspaces to be audited.	empty
ycql_audit_excluded_keyspaces		keyspaces to be excluded from auditing.	system,system_schema,system_virtual_schema,system_auth
ycql_audit_log_level	INFO, WARNING, or ERROR.	Severity level at which an audit will be logged.	ERROR

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

Statements being audited

The valid statement categories are described in the table below.

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 will have 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