Setting up Database Auditing

Capture DataStax Enterprise (DSE) database 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.

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 the type of installation:

Package installations: /etc/dse/cassandra/logback.xml
Tarball installations: <installation_location>/resources/cassandra/conf/logback.xml

Choosing a File versus Table

Audit logging options are configured on a per node basis and therefore can be different on each node. DataStax Enterprise (DSE) 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 dse_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 (SLF4J) 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.

DSE does not support data encryption for the audit.log file. Encryption is only available for the dse_audit.audit_log table.

`audit.log`

The location of the audit.log file is:

/var/log/cassandra/audit/audit.log

To capture events to the audit.log file:

Locate the dse.yaml configuration file. The location of this file depends on the type of installation:
- Package installations: /etc/dse/dse.yaml
- Tarball installations: <installation_location>/resources/dse/conf/dse.yaml
Set the audit_logging_options in the dse.yaml file:

audit_logging_options:
    enabled: true
    logger: SLF4JAuditWriter

enabled: true - Turns on logging after the next start up.
logger: SLF4JAuditWriter - Logger name.

In the Cassandra log directory, DataStax Enterprise creates audit/audit.log. After the log file reaches the configured size threshold, it rolls over, and the log file name is changed. The file names include a numerical suffix that is determined by the maxBackupIndex property.

Enabling Audit Logging to a Table

Use the CassandraAuditWriter logger to record all database activity that happens on the local node to the dse_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 Assigning permissions and Transparent Data Encryption (TDE), see Encrypting tables.

Procedure

To capture events to the dse_audit.audit_log table:

Locate the dse.yaml configuration file. The location of this file depends on the type of installation:
- Package installations: /etc/dse/dse.yaml
- Tarball installations: <installation_location>/resources/dse/conf/dse.yaml

Set the audit_logging_options in the dse.yaml file:

audit_logging_options:
    enabled: true
    logger: 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 dse_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 settings:

DESC TABLE dse_audit.audit_log

CREATE TABLE dse_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 nodesync = {'enabled': 'true'}
    AND speculative_retry = '99PERCENTILE';

Optional: By default, NodeSync is diabled for the audit_log table. To use NodeSync in order to maintain data consistency, enable it.
```
ALTER TABLE dse_audit.audit_log WITH
nodesync = { 'enabled' : true };
```

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

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

Force data replication to the newly added datacenters using one of the following methods:
- If NodeSync is enabled:
  nodesync -cu <user_name> -cp <password> \ validation submit dse_audit.audit_log
  where <user_name> is an internal login role name or LDAP/Kerberos with permission to access the CQL table.
- If NodeSync is disabled:
  nodetool repair dse_audit audit_log

Filtering Event Categories

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

The location of the dse.yaml configuration file depends on the type of installation:

Package installations: /etc/dse/dse.yaml
Tarball installations: <installation_location>/resources/dse/conf/dse.yaml

audit_logging_options:
    enabled: true
    logger: <logger_name>
#    included_categories:
#    excluded_categories:

where <logger_name> is SLF4JAuditWriter or CassandraAuditWriter.

By default, both category parameters are commented out and therefore all events are captured. Use only one of the following parameters to limit which events are captured:

included_categories - Includes only listed categories; excludes all others.
excluded_categories - Excludes listed categories; includes all others.

The category keywords are:

QUERY
DML
DDL
DCL
AUTH
ERROR
UNKNOWN

Category types

By default, DataStax Enterprise captures all event categories when auditing is enabled. Use either the included_categories or excluded_categories parameter limit which categories are captured.

All events have both a category and a type. A type usually maps directly to CQL command. The following list shows all the types in each category.

QUERY

Logs the following types of data retrieval events:

CQL_SELECT - SELECT
SOLR_QUERY - SELECT statement filtered by the Search index
GRAPH_TINKERPOP_TRAVERSAL - Calls to a table using the graph traversal instance (g). See Discovering properties about graphs and traversals
RPC_CALL_STATEMENT

DML

(data manipulation language) Logs the following types of data changes:

SET_KS - USE
INSERT - INSERT
BATCH - BATCH
TRUNCATE - TRUNCATE
CQL_UPDATE - UPDATE
CQL_DELETE - DELETE
CQL_PREPARE_STATEMENT - DataStax driver prepared statement, such as Java - Prepared Statement
SOLR_UPDATE
SOLR_COMMIT_SEARCH_INDEX_STATEMENT - COMMIT SEARCH INDEX

DDL

(data definition language) Logs the following types of database schema changes:

ADD_CF - CREATE TABLE
DROP_CF - DROP TABLE
UPDATE_CF - ALTER TABLE
ADD_KS - CREATE KEYSPACE
DROP_KS - DROP KEYSPACE
UPDATE_KS - ALTER KEYSPACE
CREATE_INDEX - CREATE INDEX
DROP_INDEX - DROP INDEX
CREATE_VIEW - DROP INDEX
DROP_VIEW - DROP INDEX
UPDATE_VIEW - DROP INDEX
CREATE_TYPE - DROP INDEX
DROP_TYPE - DROP INDEX
UPDATE_TYPE - DROP INDEX
CREATE_FUNCTION - DROP INDEX
DROP_FUNCTION - DROP INDEX
CREATE_AGGREGATE - DROP INDEX
DROP_AGGREGATE - DROP INDEX
SOLR_RELOAD_SEARCH_INDEX - RELOAD SEARCH INDEX
SOLR_REBUILD_SEARCH_INDEX - REBUILD SEARCH INDEX
SOLR_GET_RESOURCE - dsetool create_core
SOLR_UPDATE_RESOURCE - dsetool reload_core
SOLR_ALTER_SEARCH_INDEX_STATEMENT - ALTER SEARCH INDEX CONFIG or ALTER SEARCH INDEX SCHEMA
SOLR_CREATE_SEARCH_INDEX_STATEMENT - CREATE SEARCH INDEX

DCL

(data control syntax) - Logs the following types of role and permission management events:

CREATE_ROLE - CREATE ROLE
ALTER_ROLE - ALTER ROLE
DROP_ROLE - DROP ROLE
LIST_ROLES - LIST ROLES
GRANT - GRANT
REVOKE - REVOKE
RESTRICT - RESTRICT
UNRESTRICT - UNRESTRICT
RESTRICT ROWS - RESTRICT ROWS
UNRESTRICT ROWS - UNRESTRICT ROWS
LIST_PERMISSIONS - LIST PERMISSIONS

AUTH

(Authentication) Logs the following types of authentication related events:

LOGIN - Successful logins. LOGIN and login requests sent from DataStax drivers.
LOGIN_ERROR - Failed logins.

UNAUTHORIZED_ATTEMPT.

Messages not captured included: Successful Kerberos authentication events are not logged by DSE. Audit Kerberos authentication from the KDS (Kerberos Distribution Server). DataStax Enterprise logs failed Kerberos authentication request as type LOGIN_ERROR. When whitelisting keyspaces, included_keyspaces, AUTH messages are not captured.

ERROR: Logs CQL statement failures. The only type is REQUEST_FAILURE.

UNKNOWN: Logs events where the category and type are both UNKNOWN.

Example

Include only data retrieval and manipulation events:

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

Filtering keyspaces

Locate the dse.yaml configuration file. The location of this file depends on the type of installation:

Package installations: /etc/dse/dse.yaml
Tarball installations: <installation_location>/resources/dse/conf/dse.yaml

Configure which keyspaces to capture in the audit_logging_options section of the dse.yaml.

audit_logging_options:
    enabled: true
    logger: <logger_name>
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:

where <logger_name> is SLF4JAuditWriter or CassandraAuditWriter.

By default, both keyspace parameters are commented out and therefore all events are captured. Use only one of the following parameters to limit which events are captured:

included_keyspaces - Includes only matching keyspaces; excludes all others.

When specifying that keyspaces are part of an access list by enabling the parameter included_keyspaces, AUTH messages are not captured.
excluded_keyspaces - Excludes matching keyspaces; includes all others.

Match keypsaces using a comma separated list of names or a single regular expression.

Example

The system local keyspace is queried on every log in, the following exclusion will show login events without showing additional queries to the system_local keyspace.

audit_logging_options:
    enabled: true
    logger: <logger_name>
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
    excluded_keyspaces: system_local

Filtering roles

Locate the dse.yaml configuration file. The location of this file depends on the type of installation:

Package installations: /etc/dse/dse.yaml
Tarball installations: <installation_location>/resources/dse/conf/dse.yaml

Set up activity tracking of specific login roles in the audit_logging_options section of the dse.yaml.

audit_logging_options:
    enabled: true
    logger: <logger_name>
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:
#    included_roles:
#    excluded_roles:

where <logger_name> is SLF4JAuditWriter or CassandraAuditWriter.

By default, both roles parameters are commented out and therefore events are captured for all roles. Use only one of the following parameters to limit the roles whose activity is tracked:

included_roles - Includes only matching roles; excludes all others.
excluded_keyspaces - Excludes matching roles; includes all others.

Match roles using a comma-separated list of names.

Example

Events of all roles except for dse_admin and jim are captured.

audit_logging_options:
    enabled: true
    logger: CassandraAuditWriter
#    included_categories:
#    excluded_categories:
#    included_keyspaces:
#    excluded_keyspaces:
#    included_roles:
    excluded_roles: dse_admin, jim

Replacing Sensitive Data in the Audit Log

When audit logging is enabled and includes DML category events, sensitive data, such as password columns, are exposed in the message portion of the audit log. You can configure the audit log appender (SLF4JAuditWriterAppender) to replace strings in log message to mask the sensitive data. The replace function uses a regular expressions to modify the data. For more information on using the replace filter, see the logback documentation.

Additionally, because the auditing is configured through a text file in the file system, the file is vulnerable to OS-level security breaches. You can secure the file by setting the permissions to 0600 on the audit files. Be aware that if other tools look at the data, changing this setting can cause read problems. Alternately, you can store the audit file on an OS-level encrypted file system such as Vormetric.

Before sharing log files, redact sensitive data.

Procedure

Locate the logback.xml file. The location of the logback.xml file depends on the type of installation:
- Package installations: /etc/dse/cassandra/logback.xml
- Tarball installations: <installation_location>/resources/cassandra/conf/logback.xml
Open the logback.xml file in a text editor.

Locate the pattern element.

<!--audit log-->
<appender name="SLF4JAuditWriterAppender" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>${cassandra.logdir}/audit/audit.log</file> <!-- logfile location -->
    <encoder>
      <pattern>%-5level [%thread] %date{ISO8601} %F:%L - %msg%n</pattern> (1)
      <!-- the layout pattern used to format log entries -->
      <immediateFlush>true</immediateFlush>
    </encoder>
    <rollingPolicy class="ch.qos.logback.core.rolling.FixedWindowRollingPolicy">
      <fileNamePattern>${cassandra.logdir}/audit/audit.log.%i.zip</fileNamePattern>
      <minIndex>1</minIndex>
      <maxIndex>20</maxIndex> <!-- max number of archived logs that are kept -->
    </rollingPolicy>
    <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
      <maxFileSize>200MB</maxFileSize> <!-- The size of the logfile that triggers a switch to a new logfile, and the current one archived -->
    </triggeringPolicy>
  </appender>
<logger name="SLF4JAuditWriter" level="INFO" additivity="false">
    <appender-ref ref="SLF4JAuditWriterAppender"/>
  </logger>

1	Pattern element

Using the replace function on the message text to replace the first term with the second term. Here is an example to find password set to any characters and replace the password characters with xxxxx:
```
%replace(%msg){"password\s*=\s*'.*'", "password='xxxxx'"}
```
DataStax automatically masks the password specified in the CREATE ROLE command.
Restart the node or wait for the configuration to automatically reload.

Managing the Audit Logger Settings

The logback.xml file sets the SLF4JAuditWriter log file configuration including location, name, roll frequency and retention, and level.

<!--audit log-->
<appender name="SLF4JAuditWriterAppender" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <file>${cassandra.logdir}/audit/audit.log</file> <!-- logfile location -->
    <encoder>
      <pattern>%-5level [%thread] %date{ISO8601} %F:%L - %msg%n</pattern> <!-- the layout pattern used to format log entries -->
      <immediateFlush>true</immediateFlush>
    </encoder>
    <rollingPolicy class="ch.qos.logback.core.rolling.FixedWindowRollingPolicy">
      <fileNamePattern>${cassandra.logdir}/audit/audit.log.%i.zip</fileNamePattern>
      <minIndex>1</minIndex>
      <maxIndex>20</maxIndex> <!-- max number of archived logs that are kept -->
    </rollingPolicy>
    <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
      <maxFileSize>200MB</maxFileSize> <!-- The size of the logfile that triggers a switch to a new logfile, and the current one archived -->
    </triggeringPolicy>
  </appender>
<logger name="SLF4JAuditWriter" level="INFO" additivity="false">
    <appender-ref ref="SLF4JAuditWriterAppender"/>
  </logger>

Most database activity level is INFO.

Procedure

Locate the logback.xml file. The location of this file depends on the type of installation:
- Package installations: /etc/dse/cassandra/logback.xml
- Tarball installations: <installation_location>/resources/cassandra/conf/logback.xml
Open the logback.xml file in a text editor.
To configure data auditing, change the properties.
- file - Path to log file on the location system.
- pattern - Variables to write the log message. To hide sensitive data, see Replacing sensitive data in the audit log.
- immediateFlush - Whether to write event message to log immediately or not.
- fileNamePattern - Name convention for the log file when they roll.
- maxFileSize - Threshold to roll log (maximum log size).
Restart the node or wait for the configuration to automatically reload.

Enable Authentication Debugging

When initially setting up external authentication such as Kerberos or LDAP, use server and authentication loggers with TRACE level to capture internal, LDAP, and Kerberos debugging messages in the debug.log.

Procedure

Locate the logback.xml file. The location of this file depends on the type of installation:
- Package installations: /etc/dse/cassandra/logback.xml
- Tarball installations: <installation_location>/resources/cassandra/conf/logback.xml
Add the following lines before the end of the file:
```
   <logger name="com.datastax.bdp.transport.server" level="TRACE"/>
   <logger name="com.datastax.bdp.cassandra.auth" level="TRACE"/>
```
Only activity from the local node is recorded. Enable authentication tracing on the node where you want to troubleshoot authentication.
Restart the node or wait for the configuration to automatically reload.
Tail the debug.log:
```
tail -f /etc/dse/cassandra/debug.log
```
Log in using an account from the external server.

Example

Output from a successful LDAP login:

TRACE [IOThread-0] 2018-04-24 21:07:26,565  LdapUtils.java:484 - [ldap-fetch-user] username: dantest1 connection: org.apache.directory.ldap.client.api.LdapNetworkConnection@430680df
TRACE [IOThread-0] 2018-04-24 21:07:26,566  LdapUtils.java:499 - [ldap-fetch-user] bind to connection
TRACE [IOThread-0] 2018-04-24 21:07:26,568  LdapUtils.java:508 - [ldap-fetch-user] user_search_base: ou=users,dc=qaldap,dc=datastax,dc=lan, user_search_filter: (uid=dantest1)
TRACE [IOThread-0] 2018-04-24 21:07:26,608  LdapUtils.java:517 - [ldap-fetch-user] found entry for username: dantest1
TRACE [IOThread-0] 2018-04-24 21:07:26,610  LdapUtils.java:433 - [ldap-bind] userDN: cn=dantest1,ou=users,dc=qaldap,dc=datastax,dc=lan connection: org.apache.directory.ldap.client.api.LdapNetworkConnection@430680df
TRACE [IOThread-0] 2018-04-24 21:07:26,611  LdapUtils.java:438 - [ldap-bind] SUCCESS - bind succcessful for userDN: cn=dantest1,ou=users,dc=qaldap,dc=datastax,dc=lan
TRACE [IOThread-0] 2018-04-24 21:07:26,613  LdapUtils.java:325 - [ldap-authenticate] SUCCESS - username: dantest1, userDN: cn=dantest1,ou=users,dc=qaldap,dc=datastax,dc=lan

Example

Output from a failed LDAP login:

TRACE [IOThread-0] 2018-04-24 21:18:23,434  LdapUtils.java:303 - [ldap-authenticate] username: dantest1 not found in cache
TRACE [IOThread-0] 2018-04-24 21:18:23,434  LdapUtils.java:484 - [ldap-fetch-user] username: dantest1 connection: org.apache.directory.ldap.client.api.LdapNetworkConnection@430680df
TRACE [IOThread-0] 2018-04-24 21:18:23,434  LdapUtils.java:499 - [ldap-fetch-user] bind to connection
TRACE [IOThread-0] 2018-04-24 21:18:23,437  LdapUtils.java:508 - [ldap-fetch-user] user_search_base: ou=users,dc=qaldap,dc=datastax,dc=lan, user_search_filter: (uid=dantest1)
TRACE [IOThread-0] 2018-04-24 21:18:23,440  LdapUtils.java:517 - [ldap-fetch-user] found entry for username: dantest1
TRACE [IOThread-0] 2018-04-24 21:18:23,441  LdapUtils.java:433 - [ldap-bind] userDN: cn=dantest1,ou=users,dc=qaldap,dc=datastax,dc=lan connection: org.apache.directory.ldap.client.api.LdapNetworkConnection@430680df
TRACE [IOThread-0] 2018-04-24 21:18:23,451  LdapUtils.java:447 - [ldap-bind] ERROR - bind failed for userDN: cn=dantest1,ou=users,dc=qaldap,dc=datastax,dc=lan
...