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