Robin Schumacher

<p>DataStax Enterprise 3.0 and above supports the ability to perform data auditing on a database cluster. Data auditing allows an administrator to understand “who looked at what/when” and “who changed what/when”. It basically enables the logging of some or all the user activity that occurs on a database.</p>

<p>Many businesses and organizations today have either external mandates or internal security policies that require the auditing of user actions on a database, so having a built-in way to accomplish this with DataStax Enterprise is helpful to administrators in such environments.</p>

<p>Let’s take a brief look at how to set up and use data auditing in DataStax Enterprise.</p>

<h2>Getting Started</h2>

<p>Auditing is implemented in DataStax Enterprise via the log4J mechanism that’s built into the product. This allows for the most efficient way of auditing large amounts of activity on a cluster. It also provides a good deal of flexibility to the administrator over what is audited, where the data is written, and how it is presented.</p>

<p>Data auditing is disabled by default in DataStax Enterprise, which means no auditing occurs. To enable auditing, an administrator edits the log4j-server.properties file that is found in the DataStax Enterprise install/resources/cassandra/conf/ directory.</p>

<p>In the log4j-server.properties file, there is a section that is commented out, which is designated for data auditing. The general parameters for log based auditing are the following:</p>

<pre>
# audit log</pre>

<pre>
#log4j.logger.DataAudit=INFO, A</pre>

<pre>
#log4j.additivity.DataAudit=false</pre>

<pre>
#log4j.appender.A=org.apache.log4j.RollingFileAppender</pre>

<pre>
#log4j.appender.A.File=/var/log/cassandra/audit.log</pre>

<pre>
#log4j.appender.A.bufferedIO=true</pre>

<pre>
#log4j.appender.A.maxFileSize=200MB</pre>

<pre>
#log4j.appender.A.maxBackupIndex=5</pre>

<pre>
#log4j.appender.A.layout=org.apache.log4j.PatternLayout</pre>

<pre>
#log4j.appender.A.layout.ConversionPattern=%m%n</pre>

<pre>
#log4j.appender.A.filter.1=com.datastax.bdp.cassandra.audit.AuditLogFilter</pre>

<pre>
#log4j.appender.A.filter.1.ActiveCategories=ALL</pre>

<pre>
#log4j.appender.A.filter.1.ExemptKeyspaces=do_not_log</pre>

<p>The first five log4j lines in the file control the enablement of auditing in the following fashion:</p>

<ul>
	<li>The first line (right after the #audit log line) defines the log4j logger and gives it a name</li>
	<li>The second ensures the root log4j appender is not used</li>
	<li>The third pushes the auditing data through a rolling file appender</li>
	<li>The fourth line sets the location of the audit file</li>
	<li>The fifth line enables the best throughput using buffered IO, but it means that not every audit event might be seen when opening the audit file. However, the plugin ensures that all buffered events are flushed when the server is shutdown</li>
</ul>

<p>These lines then need to be uncommented to set up the audit log file:</p>

<pre>
log4j.appender.A.layout=org.apache.log4j.PatternLayout</pre>

<pre>
log4j.appender.A.layout.ConversionPattern=%m%n</pre>

<pre>
log4j.appender.A.filter.1=com.datastax.bdp.cassandra.audit.AuditLogFilter</pre>

<p>Finally, the other parameter that mandates attention is the ActiveCategories setting.&nbsp; This controls the granularity of auditing that an administrator desires. Everything may be audited or only certain types of actions, with the following options being:</p>

<ul>
	<li>QUERY – all SELECT operations</li>
	<li>DML – all INSERT, UPDATE, and DELETE actions</li>
	<li>DDL – all object creations, modifications, and drops</li>
	<li>DCL – all user creations, drops, list users and security privilege grants/revokes</li>
	<li>AUTH – login events</li>
	<li>ADMIN – audits various admin commands such as describe schema versions, cluster name, version, ring, etc.</li>
	<li>ALL – audits DDL, DML, queries and errors</li>
</ul>

<p>The parameter’s default is ALL.</p>

<p>If an administrator wants to selectively exclude certain Cassandra keyspaces from being audited, they can list them in the ExemptKeyspaces parameter.</p>

<h2>Auditing User Activity</h2>

<p>Once the log4j audit section has been properly configured, the server needs to be restarted for the changes to take effect. After that, whatever activity occurs on that node in the cluster will be audited.</p>

<p>For example, with the ALL option set for auditing, the following activity:</p>

<pre>
$ ./cqlsh -3 localhost -u cassandra -p cassandra</pre>

<pre>
Connected to Test Cluster at localhost:9160.</pre>

<p>[cqlsh 2.2.0 | Cassandra 1.1.8.1-SNAPSHOT | CQL spec 3.0.0 | Thrift protocol 19.33.0]</p>

<pre>
Use HELP for help.</pre>

<p>cqlsh&gt; use dev;</p>

<pre>
cqlsh:dev&gt; select * from emp;</pre>

<pre>
empid | first_name | last_name&nbsp; | ssn</pre>

<pre>
------+------------+------------+-----------</pre>

<pre>
1     |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; robin | schumacher | 123456789</pre>

<pre>
2     |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; laura |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; jung | 213456789</pre>

<pre>
cqlsh:dev&gt; insert into emp (empid, first_name, last_name, ssn)</pre>

<pre>
... values (3, 'john','smith',213657898);</pre>

<pre>
cqlsh:dev&gt; delete from emp where empid=3;</pre>

<p>Produces this audit output:</p>

<pre>
$ more audit.log</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737912619|category:AUTH|type:LOGIN|operation:Successful login for user - cassandra</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737912623|category:ADMIN|type:DESC_VERSION</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737912642|category:DML|type:SET_KS|ks:system|operation:USE system;</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737912650|category:ADMIN|type:DESC_CLUSTER_NAME</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737912654|category:QUERY|type:CQL_SELECT|ks:system|cf:Versions|operation:select component, version from system."Versions"</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737928768|category:DML|type:SET_KS|ks:dev|operation:use dev;</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359737933896|category:QUERY|type:CQL_SELECT|ks:dev|cf:emp|operation:select * from emp;</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359738484084|category:DML|type:CQL_UPDATE|ks:dev|cf:emp|operation:insert into emp (empid, first_name, last_name, ssn)</pre>

<pre>
values (3, 'john','smith',213657898);</pre>

<pre>
host:robinsmac.local/192.168.24.2|source:/127.0.0.1|user:cassandra|timestamp:1359738490932|category:DML|type:CQL_DELETE|ks:dev|cf:emp|operation:delete from emp where empid=3;</pre>

<h2>Writing Audit Data to Tables / Column Families</h2>

<p>If an administrator chooses, they can write audit data directly to a table / column family, although the overhead is a little more than using only log files. The same config files is used to direct audit activity to a particular keyspace and table vs. a system file.</p>

<p>Note that if the audit data is deemed sensitive, DSE’s built-in encryption can be used to encrypt the table being used to hold the audit information.</p>

<h2>Auditing Hadoop and Solr Activities</h2>

<p>Auditing Hadoop and Solr user activities is possible in DataStax Enterprise. For Hadoop, all activities that occur against the Cassandra column families used to house Hadoop data can be audited.</p>

<p>For Solr, in addition to the audit setup described earlier, an admin will need to edit the installation directory /resources/solr/web/solr/WEB-INF/web.xml file and uncomment the filter-mapping section contained in the file:</p>

<pre>
&lt;!--&nbsp; To enable audit logging of Solr HTTP requests, enable this filter-mapping&nbsp;
      which includes the audit logging filter in the filter chain. The audit logging config in DSE's log4j configuration file must also be enabled.&nbsp;&nbsp;&nbsp; --&gt;</pre>

<pre>
&lt;!--</pre>

<pre>
&lt;filter-mapping&gt;</pre>

<pre>
&lt;filter-name&gt;DseAuditLoggingFilter&lt;/filter-name&gt;</pre>

<pre>
&lt;url-pattern&gt;/*&lt;/url-pattern&gt;</pre>

<pre>
&lt;/filter-mapping&gt;</pre>

<pre>
--&gt;</pre>

<h2>Other Considerations</h2>

<p>A few other things to keep in mind when using data auditing in DataStax Enterprise:</p>

<ul>
	<li>All nodes must have auditing turned on so that the total activity for a cluster may be collected.</li>
	<li>The overhead of auditing is dependent on a number of factors including the intensity of concurrent user activity, the level of auditing used, etc.</li>
</ul>

<h2>Next Steps</h2>

<p>To give data auditing and all the other features of DataStax Enterprise a try,&nbsp;download&nbsp;a copy today. DataStax Enterprise is completely free to use in development environments with no restrictions, however production deployments do require that a subscription be purchased.</p>

<p>For more information on all of 3.0's security features, please see our&nbsp;online documentation&nbsp;and our&nbsp;“What’s New in DataStax 3.0?” white paper.</p>


Build and scale cloud native apps

Discover the benefits of DBaaS and why your apps deserve an upgrade.

Learn about NoSQL Databases

Learn about NoSQL databases with Apache Cassandra and Astra DB.

2022 Recap: A Year for Customers, Community, and Real-Time Data

Highlights from 2022 and a glimpse into the year ahead.

ViewingTechnical How To’s

A Look at Data Auditing in DataStax Enterprise 3.0

Sign up for our Developer Newsletter