Setting up database auditing

Capture DataStax Enterprise database activity to a log file or table.

logback.xml

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
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.
Tip: 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.

Choosing a File vs 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

Configure the audit logger that captures database activity in a system log 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.

Note: 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

dse.yaml

The location of the dse.yaml file depends on the type of installation:
Package installations /etc/dse/dse.yaml
Tarball installations installation_location/resources/dse/conf/dse.yaml

To capture events to the audit.log file:

Set the audit_logging_options in the dse.yaml file:
audit_logging_options:
    enabled: true
    logger: SLF4JAuditWriter
  • : true - Turns on logging after the next start up.
  • : 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

Configure the audit logger that captures database activity in a database 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 capture 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 Transparnet Data Encryption (TDE), see Encrypting tables.

dse.yaml

The location of the dse.yaml file depends on the type of installation:
Package installations /etc/dse/dse.yaml
Tarball installations installation_location/resources/dse/conf/dse.yaml

Procedure

To capture events to the dse_audit.audit_log table:
  1. 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).
    Note: 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';
  2. (Optional) By default, NodeSync is diabled for the audit_log table. To use NodeSync to maintain data consistency, enable it. 
    ALTER TABLE dse_audit.audit_log WITH 
nodesync = { 'enabled' : true };
  3. 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   };
  4. 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

Set the event categories to log.

dse.yaml

The location of the dse.yaml file depends on the type of installation:
Package installations /etc/dse/dse.yaml
Tarball installations installation_location/resources/dse/conf/dse.yaml
Configure which categories to capture in the audit_logging_options section of the 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:

Category types

By default, DataStax Enterprise captures all event categories when auditing is enabled. Use either the or 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 -
  • SOLR_QUERY - SELECT statement filtered by the Search index
  • GRAPH_TINKERPOP_TRAVERSAL - Calls to a table using the graph traversal instance (g). See
  • RPC_CALL_STATEMENT
DML
(data manipulation language) Logs the following types of data changes:
  • SET_KS -
  • INSERT -
  • BATCH -
  • TRUNCATE -
  • CQL_UPDATE -
  • CQL_DELETE -
  • CQL_PREPARE_STATEMENT - DataStax driver prepared statement, such as Java - Prepared Statement
  • SOLR_UPDATE
  • SOLR_COMMIT_SEARCH_INDEX_STATEMENT -
DDL
(data definition language) Logs the following types of database schema changes:
  • ADD_CF -
  • DROP_CF -
  • UPDATE_CF -
  • ADD_KS -
  • DROP_KS -
  • UPDATE_KS -
  • CREATE_INDEX -
  • DROP_INDEX -
  • CREATE_VIEW -
  • DROP_VIEW -
  • UPDATE_VIEW -
  • CREATE_TYPE -
  • DROP_TYPE -
  • UPDATE_TYPE -
  • CREATE_FUNCTION -
  • DROP_FUNCTION -
  • CREATE_AGGREGATE -
  • DROP_AGGREGATE -
  • SOLR_RELOAD_SEARCH_INDEX -
  • SOLR_REBUILD_SEARCH_INDEX -
  • SOLR_GET_RESOURCE -
  • SOLR_UPDATE_RESOURCE -
  • SOLR_ALTER_SEARCH_INDEX_STATEMENT - or
  • SOLR_CREATE_SEARCH_INDEX_STATEMENT -
DCL
(data control syntax) - Logs the following types of role and permission management events:
  • CREATE_ROLE -
  • ALTER_ROLE -
  • DROP_ROLE -
  • LIST_ROLES -
  • GRANT -
  • REVOKE -
  • RESTRICT -
  • UNRESTRICT -
  • RESTRICT ROWS -
  • UNRESTRICT ROWS -
  • LIST_PERMISSIONS -
AUTH
(Authentication) Logs the following types of authentication related events:
  • LOGIN - Successful logins. and login requests sent from DataStax drivers.
  • LOGIN_ERROR - Failed logins.
  • UNAUTHORIZED_ATTEMPT.
Note: 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

Set the keyspaces where events are captured.

dse.yaml

The location of the dse.yaml 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.
    Note: When whitelisting keyspaces, 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

Track activity only for specific roles.

dse.yaml

The location of the dse.yaml 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

Configure the DSE audit writer to mask sensitive data before it is written to the 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.
Tip: Before sharing log files, redact sensitive data.

logback.xml

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

Procedure

  1. Open the logback.xml file in a text editor.
  2. 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> <!-- 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>
  3. Using the replace function on the message text to replace the first term with the second term. For example to find password set to any characters and replace the password characters with xxxxx: 
    %replace(%msg){"password\s*=\s*'.*'", "password='xxxxx'"}
    Note: DataStax automatically masks the password specified in the command.
  4. Restart the node or wait for the configuration to automatically reload.

Managing the audit logger settings

Change the SLF4JAuditWriter log file location, name, roll frequency and naming.

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>
Warning: Most database activity level is INFO.

logback.xml

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

Procedure

  1. Open the logback.xml file in a text editor.
  2. 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).
  3. Restart the node or wait for the configuration to automatically reload.

Enable authentication debugging

Capture authentication debugging messages in the debug.log.

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

logback.xml

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

Procedure

  1. Locate the logback.xml file and 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.
  2. Restart the node or wait for the configuration to automatically reload.
  3. Tail the debug.log: 
    tail -f /etc/dse/cassandra/debug.log
  4. 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
...