Set up database auditing

Capture Hyper-Converged Database (HCD) activity to a log file or table. Each node only records the events that happen locally. Use the configuration to refine the type of events captured. DSE provides the following customizations:

Keyspace filtering: Capture activity in every keyspace or only targeted keyspaces. Filter keyspace names using regex.
Category filtering: Identify event categories to limit the number of events captured.
Role filtering: Track the activity of particular users or groups by their login role name.
Node specific: Enable auditing on one or more nodes. Allows auditing of only specific nodes, an entire datacenter, or the whole cluster.

You can configure logging levels, mask sensitive data, and for the log file set the file name, location, size threshold, and max log files in the logback.xml file.

The location of the logback.xml file depends on your installation type:

Package installations: /etc/hcd/cassandra/logback.xml
Tarball installations: INSTALL_DIRECTORY/resources/cassandra/conf/logback.xml

Write audit log events to a file or table

Audit logging options are configured on a per node basis and therefore can be different on each node. HCD supports the following methods to record database activity:

Log file (per node): The SLF4JAuditWriter [Simple Logging Facade for Java (SLF4J) Audit Writer] logger records all database activity that occurs on the local node to the audit.log file. When tracing a multi-node request, collect and parse log files from all the nodes that participated.
Unified table: The CassandraAuditWriter logger records all database activity that happens on the local node in the hcd_audit.audit_log table. Events from all nodes with the logger enabled are captured in the same table. This allows you to easily create reports that include multiple nodes.

Enable audit logging to a file

Use Simple Logging Facade for Java audit writer (SLF4JAuditWriter) logger to record all database activity that occurs on the local node to the audit.log file. Secure the log file by controlling access using standard Linux file system permissions.

HCD doesn’t support data encryption for the audit.log file. Encryption is only available for the hcd_audit.audit_log table.

The location of the audit.log file is /var/log/cassandra/audit/audit.log.

To capture events to the audit.log file, set the audit_logging_options in the cassandra.yaml file:

audit_logging_options:
    enabled: true
    logger:
      - class_name: SLF4JAuditWriter

enabled: true: Enables logging after the next start up.
logger: SLF4JAuditWriter Logger name.

In the Cassandra log directory, HCD creates audit/audit.log.

After the log file reaches the configured size threshold, it rolls over with a new log file name. The file names include a numerical suffix that is determined by the maxBackupIndex property.

Enable audit logging to a table

Use the CassandraAuditWriter logger to record all database activity that happens on the local node to the hcd_audit.audit_log table. Events from all nodes are captured in the same table, allowing you to easily create reports that include multiple nodes.

Using the table option provides a centralized location for all events across the cluster. Secure the table with DataStax role-based access control (RBAC), see Role-based access control and Transparent Data Encryption (TDE), see Encrypting tables.

To capture events to the hcd_audit.audit_log table:

Locate the cassandra.yaml configuration file.

The location of the cassandra.yaml file depends on your installation type:
- Package installations: /etc/hcd/cassandra/cassandra.yaml
- Tarball installations: INSTALL_DIRECTORY/resources/cassandra/conf/cassandra.yaml

Set the audit_logging_options in the cassandra.yaml file:

audit_logging_options:
    enabled: true
    logger:
      - class_name: CassandraAuditWriter
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:
#    included_roles:
#    excluded_roles:
    retention_time: 12

    cassandra_audit_writer_options:
        mode: sync
        batch_size: 50
        flush_time: 250
        queue_size: 30000
        write_consistency: QUORUM
#         dropped_event_log: /var/log/cassandra/dropped_audit_events.log
#         day_partition_millis: 3600000

enabled: true: Turns on logging after the next start up.
logger: CassandraAuditWriter: Logger name.
retention_time: 12: Number of hours to set the TTL (time-to-live) on the hcd_audit.audit_log table. Use this setting to automatically expire data. The default is 0 (disabled).

Customize the cassandra_audit_writer_options parameters as required (the default are shown above).

The audit_log table has the following schema:

DESC TABLE hcd_audit.audit_log

CREATE TABLE hcd_audit.audit_log (
    date timestamp,
    node inet,
    day_partition int,
    event_time timeuuid,
    authenticated text,
    batch_id uuid,
    category text,
    consistency text,
    keyspace_name text,
    operation text,
    source text,
    table_name text,
    type text,
    username text,
    PRIMARY KEY ((date, node, day_partition), event_time)
) WITH CLUSTERING ORDER BY (event_time ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.TimeWindowCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 7776000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 3600000
    AND min_index_interval = 128
    AND speculative_retry = '99PERCENTILE';

Set the keyspace replication strategy to NetworkTopologyStrategy and set a replication factor for each datacenter in the cluster where auditing is enabled:

ALTER KEYSPACE hcd_audit WITH
replication = {
               'class': 'NetworkTopologyStrategy',
               'datacenter1' : 2,
               'datacenter2' : 1   };

Force data replication to the newly added datacenters:
```
nodetool repair hcd_audit audit_log
```

Filter event categories

Configure which categories to capture in the audit_logging_options section of the cassandra.yaml file.

By default, HCD captures all event categories when audit_logging_options.enabled : true and the filters (included_categories and excluded_categories) are commented out:

audit_logging_options:
    enabled: true
    logger:
      - class_name: LOGGER_NAME
#    included_categories:
#    excluded_categories:

LOGGER_NAME must be either SLF4JAuditWriter or CassandraAuditWriter.

To set filters, uncomment one of the following parameters, and then set the value to the relevant event categories:

included_categories: Includes only listed categories, and excludes all others.
excluded_categories: Excludes listed categories, and includes all others.

For example, to include only data retrieval and manipulation events:

audit_logging_options:
    enabled: true
    logger:
      - class_name: SLF4JAuditWriter
    included_categories: QUERY, DDL, AUTH
#    excluded_categories:

Audit logging event categories and types

All events have both a category and a type. A type usually maps directly to a CQL command.

The following tables list all types in each category.

`DDL` category

The DDL category includes data definition language type events that modify the database schema:

Event type CQL command

Event type	CQL command
`ADD_KS`	CREATE KEYSPACE
`DROP_KS`	DROP KEYSPACE
`UPDATE_KS`	ALTER KEYSPACE
`ADD_CF`	CREATE TABLE
`DROP_CF`	DROP TABLE
`UPDATE_CF`	ALTER TABLE
`CREATE_INDEX`	CREATE INDEX
`DROP_INDEX`	DROP INDEX
`CREATE_TYPE`	CREATE TYPE
`DROP_TYPE`	DROP TYPE
`UPDATE_TYPE`	ALTER TYPE
`CREATE_FUNCTION`	CREATE FUNCTION
`DROP_FUNCTION`	DROP FUNCTION
`CREATE_AGGREGATE`	CREATE AGGREGATE
`DROP_AGGREGATE`	DROP AGGREGATE
`CREATE_VIEW`	CREATE MATERIALIZED VIEW
`DROP_VIEW`	DROP MATERIALIZED VIEW
`ALTER_VIEW`	ALTER MATERIALIZED VIEW

ADD_KS

DROP_KS

UPDATE_KS

ADD_CF

DROP_CF

UPDATE_CF

CREATE_INDEX

DROP_INDEX

CREATE_TYPE

DROP_TYPE

UPDATE_TYPE

CREATE_FUNCTION

DROP_FUNCTION

CREATE_AGGREGATE

DROP_AGGREGATE

CREATE_VIEW

CREATE MATERIALIZED VIEW

DROP_VIEW

DROP MATERIALIZED VIEW

ALTER_VIEW

ALTER MATERIALIZED VIEW

`DML` category

The DML category captures events related to data manipulation language operations in the database:

Event type CQL command

Event type	CQL command
`SET_KS`	USE
`INSERT`	INSERT
`BATCH`	BATCH
`TRUNCATE`	TRUNCATE
`CQL_UPDATE`	UPDATE
`CQL_DELETE`	DELETE
`CQL_PREPARE_STATEMENT`	Cassandra driver prepared statement, such as a Java driver prepared statement
`MANAGEMENT_API_OP`

SET_KS

INSERT

BATCH

TRUNCATE

CQL_UPDATE

CQL_DELETE

CQL_PREPARE_STATEMENT

Cassandra driver prepared statement, such as a Java driver prepared statement

MANAGEMENT_API_OP

`DCL` category

The DCL category captures events related to database control, role, and permission changes:

Event type CQL command

Event type	CQL command
`CREATE_ROLE`	CREATE ROLE
`ALTER_ROLE`	ALTER ROLE
`DROP_ROLE`	DROP ROLE
`LIST_ROLES`	LIST ROLES
`LIST_USERS`	LIST USERS
`LIST_PERMISSIONS`	LIST PERMISSIONS
`GRANT`	GRANT
`REVOKE`	REVOKE
`RESTRICT`	RESTRICT
`UNRESTRICT`	UNRESTRICT
`RESTRICT_ROWS_STATEMENT`	RESTRICT ROWS
`UNRESTRICT_ROWS_STATEMENT`	UNRESTRICT ROWS

CREATE_ROLE

ALTER_ROLE

DROP_ROLE

LIST_ROLES

LIST_USERS

LIST_PERMISSIONS

GRANT

REVOKE

RESTRICT

UNRESTRICT

RESTRICT_ROWS_STATEMENT

RESTRICT ROWS

UNRESTRICT_ROWS_STATEMENT

UNRESTRICT ROWS

`QUERY` category

The QUERY category captures events related to data retrieval operations:

Event type CQL command

Event type	CQL command
`CQL_SELECT`	SELECT
`RPC_CALL_STATEMENT`	Remote Procedure Call (RPC) statement.

CQL_SELECT

SELECT

RPC_CALL_STATEMENT

Remote Procedure Call (RPC) statement.

`AUTH` category

The AUTH category captures events related to authentication and authorization operations:

Event type CQL shell (cqlsh) command

Event type	CQL shell (`cqlsh`) command
`LOGIN_SUCCESS`	Successful login attempt from `LOGIN` or a login request sent from a Cassandra driver.
`LOGIN_ERROR`	Failed login attempt from `LOGIN` or a login request sent from a Cassandra driver.
`UNAUTHORIZED_ATTEMPT`	Unauthorized access attempt from `LOGIN` or a login request sent from a Cassandra driver.

LOGIN_SUCCESS

Successful login attempt from LOGIN or a login request sent from a Cassandra driver.

LOGIN_ERROR

Failed login attempt from LOGIN or a login request sent from a Cassandra driver.

UNAUTHORIZED_ATTEMPT

Unauthorized access attempt from LOGIN or a login request sent from a Cassandra driver.

`ERROR` category

The ERROR category captures events related to error occurrences:

Event type Information

Event type	Information
`ERROR`	CQL statement failures.
`REQUEST_FAILURE`	Failed requests.

ERROR

CQL statement failures.

REQUEST_FAILURE

Failed requests.

`UNKNOWN` category

The UNKNOWN category captures events related to unknown occurrences:

Event type Information

Event type	Information
`UNKNOWN`	Unknown events.

UNKNOWN

Unknown events.

Filter keyspaces

Configure which keyspaces to capture in audit logs in the audit_logging_options section of the cassandra.yaml file:

audit_logging_options:
    enabled: true
    logger:
      - class_name: LOGGER_NAME
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:

The LOGGER_NAME must be SLF4JAuditWriter or CassandraAuditWriter.

By default, both keyspace parameters are commented out, and events are captured for all keyspaces.

To filter keyspaces in audit log events, uncomment and set only one of the following parameters:

included_keyspaces: Include only matching keyspaces, and exclude all others.

When using included_keyspaces, AUTH messages are not captured.
excluded_keyspaces: Excludes matching keyspaces, and includes all others.

The value must be either a regular expression (regex) or a comma-separated list of keyspace names as exact matches.

For example, the system_local keyspace is queried on every log in. The following exclusion shows login events without showing additional queries to the system_local keyspace:

audit_logging_options:
    enabled: true
    logger:
      - class_name: SLF4JAuditWriter
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
    excluded_keyspaces: system_local

Filter roles

Track specific roles in audit logs in the audit_logging_options section of the cassandra.yaml:

audit_logging_options:
    enabled: true
    logger:
      - class_name: LOGGER_NAME
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:
#    included_roles:
#    excluded_roles:

LOGGER_NAME must be SLF4JAuditWriter or CassandraAuditWriter.

By default, both *_roles parameters are commented out, and audit log events are captured for all roles.

To track specific roles only, uncomment and set one of the following parameters:

included_roles: Includes only matching roles, and excludes all others.
excluded_roles: Excludes matching roles, and includes all others.

The value of these parameters must be a comma-separated list of role names as exact matches.

The following example records audit log events for all roles except hcd_admin and jim:

audit_logging_options:
    enabled: true
    logger:
      - class_name: CassandraAuditWriter
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:
#    included_roles:
    excluded_roles: hcd_admin, jim

Set up database auditing

Write audit log events to a file or table

Enable audit logging to a file

Enable audit logging to a table

Filter event categories

Audit logging event categories and types

`DDL` category

`DML` category

`DCL` category

`QUERY` category

`AUTH` category

`ERROR` category

`UNKNOWN` category

Filter keyspaces

Filter roles

See also

Was this helpful?

Give Feedback