During customer visits and interesting talks at conferences I noticed how the topic of security is moving more and more into focus driven by regulations like GDPR, SoX, HIPPA, MaRSIK or KRITIS, just to mention some. Beside those, company-internal guidelines and official data protection guidelines also contribute to the increased demand in this area.
How does the Db2 audit facility come into play?
One of the tools that Db2 delivers is the
db2audit facility, which is slightly unknown and not widely used in the SAP customer base. Especially the performance impact and the scope of the audited data might cause confusion and uncertainty.
That’s why I took the chance to run some tests to assess the impact on the SAP database of
db2audit, and I would like to share the results in this blog.
Setting up db2audit
Before going into performance considerations, let’s first clarify the setup because the available documentation is somewhat obfuscating.
The confusion starts with how to set up db2audit. There are two different ways:
instance-level auditing and database-level auditing
Instance-level auditing was the first version of auditing that became available with Db2. While it still exists, database-level auditing, which was added later, is typically the better choice if you want to audit your SAP database. Database-level auditing is less intrusive and doesn't need to be started explicitly after every start and stop of the Db2 instance.
To use database-level auditing, you only use the
db2audit command to set up the targets for the active audit log and archive log files as follows:
db2audit configure datapath <AuditPath>
db2audit configure archivepath <ArchPath>
After that, do
not use the
db2audit command any further.
Instead, create a db2audit policy and assign it to the objects you want to monitor:
db2 "create audit policy testpolicy categories
checking status both error type audit"
db2 "audit database using policy testpolicy"
In this example, records are created during the authorization checking of attempts to access or manipulate database objects or functions. This means you can determine who tried to access any table in the database.
The audit policy is stored in the system catalog of the database. Therefore, auditing will always be active as soon as the database is activated and no additional start of the db2 audit facility is required.
In the context of securing and monitoring the access to the database, it’s always tempting to log whatever possible. If someone accessed a certain object, you might want to log also the SQL statement that was executed. This is exactly what the dbaudit categories “context” and “execute” do. The category “context” is inherited from the initial
db2audit setup. I suggest you use the category “execute” instead, like I did in my further tests.
What and how I tested
I started a sequence of tests with the workload generated by the SAP HA test tool. This tool was originally designed to simulate workload that can be used to verify a high availability setup like Db2 HADR or the use of cluster software. I found this tool to be a good alternative to synthetic benchmark drivers as it’s installed and set up in just a couple of hours. For more information, see the blog
Test Tool for High Availability Environments.
My database was not optimized or tuned for benchmarking. The Db2 version I used was Db2 11.5.0.0. in a 3-tier setup with both the application server and the database server running on Linux.
As a baseline, I ran the database workload and got a throughput of 1480 dialog steps per second. The test ran for about 5 minutes.
After this, I enabled
db2audit “all-you-can-get” with instance-level and database-level auditing as well as with the categories “context” and “execute” enabled:
db2audit configure scope context status both
db2 "create audit policy fullpolicy categories
audit status both, checking status both,
objmaint status both, secmaint status both,
sysadmin status both, validate status both,
execute status both,
context status both error type audit"
db2 "audit database using policy fullpolicy"
A 5-minute test is a good chance to get some coffee. I did so and got delayed on the way back. When I returned, I expected the next test to be completed. To my surprise, it was still running. I looked at the CPU data and saw idle CPU but heavy writes to some of my disks. At the end I got a throughput of about 70 dialog steps per second – so only 5% of the baseline.
Asynchronous write of the db2audit log
After some problem determination I finally found that the db2audit buffer was set to zero. Looking at
SAP Note 1474404, I figured that this was not a good idea and so I enabled the asynchronous writes for the Db2 audit facility and set the buffer to 10.000 4K pages using the following command:
db2 "update DBM CFG using AUDIT_BUF_SZ 10000"
After this change, the throughput went up to 1200 dialog steps per second. Not as good as the baseline without any auditing, but by far better than without buffer defined. But still 20% impact is significant.
The trade-off of using the audit buffer is that there may be lost audit records in case of a database crash. Therefore, it’s a good idea to keep the buffer as small as possible. The good news is, in my test, a buffer of only 100 pages also ended up with the same performance as a larger buffer of 1.000, 5.000 and 50.000 4K pages. This may vary in other environments with higher workload, but it gives you a good indication to better start small and only grow the buffer if needed.
Reducing the performance impact
So, after this detour to the audit buffer, let’s come back to the performance impact of the different categories. If you read through the sparse information about the different categories, you will find that the categories "execute" and "context" have the highest impact on performance. My next step was to create a policy without the categories “context” and “execute” and disable “context” on instance level:
db2audit configure scope context status none
db2 "create audit policy limitpolicy categories
audit status both, checking status both,
objmaint status both, secmaint status both,
sysadmin status both, validate status both
error type audit"
db2 "audit database using policy limitpolicy"
This setup ended with a throughput of 1.440 dialog steps per second, which is 97% of the throughput of the baseline test sequence without db2audit enabled.
I tried to further optimize the performance by disabling more categories, but the results were basically the same, within the limits of accuracy of the measurements. I also did various combinations of “execute” and “context” enabled and disabled, but this did not provide any further insights.
While analyzing the Db2 audit logs, I realized that I was missing some audit log files on instance level. This happened because instance-level auditing needs to be explicitly started. It remains active if you recycle the instance, but if you reboot the server, it may not be started.
Data volume generated by the db2audit facility
There is one more dimension to look at in the db2 audit facility: The amount of data produced, and the time required to extract the data for analysis.
The db2audit setup with “execute” produced 30GB of audit log data within 5 minutes, while, without “execute”, less than 5MB were written to disk.
The extraction of 30GB audit logs took 4 minutes, which is almost as long as the execution of the test sequence itself. Extracting the small audit log file without “execute” enabled finished in less than a second.
While the performance impact on the production database with “execute” enabled might be acceptable, the amount of data generated and the time needed to extract the data are somewhat prohibitive. This leads me to the recommendation to not use the “execute” or “context” categories for the entire database.
Summary of my findings
- Setting the db2 audit buffer to a value other than zero is mandatory, but you can keep the value relatively small.
- Auditing “execute” or “context” will have a larger impact on performance – in my case almost 20%.
- Disabling the db2audit categories “context” and “execute” has significantly less impact on performance – in my case 3%.
- Instance-level auditing is not advisable because it needs to be started or stopped explicitly. Database-level auditing using policies is enabled automatically and therefore better.
- The amount of data generated by the “execute” category is larger by factors and has a big impact on the required disk space, extraction processing, and IO performance.
Db2 Audit and other available tools
Db2 audit is not the only tool that lets you enable the traceability of certain events. The strength of Db2 audit is that it can audit all accesses to the database, regardless of whether they come from within the SAP system or from outside. But Db2 audit has only very little application context for accesses that come from the SAP system. Therefore, the level of detail that can be logged for accesses from the SAP system is limited.
In addition, there are many products, tools and best practices available that can help you fulfill your traceability and auditing requirements inside the SAP system. Auditing inside the SAP system can provide a much higher level of detail. The limitation is that auditing inside the SAP system cannot cover SQL statements that come from outside the SAP stack directly to the database.
I therefore believe that the use of Db2 audit alone probably is not the best approach. A combination of Db2 audit together with SAP-level auditing functions like the audit information system, the security audit log, read access logging, SAP security optimization services, and other tools and services are probably much better suited to help you fulfill your security requirements.
The data in the database should only be accessed by the SAP application itself. You can take several actions to ensure that external access is not possible. For example, disable interactive login for the SAP connect user, use a firewall, implement separation of duties for Db2, etc.
So, what would be a good recommendation for using db2audit for SAP applications?
Considering that the data should only be accessed by the SAP application and the SAP connect user, a good configuration would be to audit everything except the access coming from the SAP application itself. Unfortunately, this is not possible with Db2 as of today because Db2 does not offer a way to exclude certain connections from auditing.
So, the currently available approach would be to run auditing for the whole database with minimal impact and additionally set up Db2 audit for other users or roles that have database access (like the SAP system administrator and Db2 database administrator) with the “execute” category to audit the access from those technical users. Presumably, the number of SQL statements issued by these users is significantly less than those executed by the SAP application, and so the impact on performance and the amount of data generated are also significantly less.
With this example, all connection attempts, authorization checks, create, drop or alter statements and much more are logged. The regular DML SQL activities of the SAP connect user used by the SAP application is not logged, but logging is enabled for the users db2<dbsid> and <sapsid>adm. I admit this is not an ideal solution, but it can be valuable to log access to data from administrators or external tools.
You can enable such a setup using the following statements:
db2 "create audit policy sapdbpolicy categories
audit status both, checking status both,
objmaint status both, secmaint status both,
sysadmin status both, validate status both
error type audit"
db2 "create audit policy sapsqlpolicy categories
execute status both
error type audit"
db2 "audit database using policy sapdbpolicy"
db2 "audit user <USERNAME> using policy sapsqlpolicy
This example can be applied to other users by assigning the policy for statement auditing of roles, groups, or other users that have database access. If you assign the sample policy “sapsqlpolicy” to the db2 instance owner db2<sid>, db2audit will also log internal actions, e.g. tasks scheduled by the administrative task scheduler.
For more information about the users when running SAP applications with Db2, please see
User Management and Security in the
Database Administration Guide for SAP on IBM Db2 for Linux, UNIX, and Windows.
This example is one possible implementation of db2 auditing and can be used as a starting point. The actual implementation may vary, depending on your IT environment, your enterprise, and legal requirements. Feel free to use the information in this blog as a starting point and develop your own enterprise-specific db2audit setup.
Do you intend to run some tests with db2audit now? Or have you used it in the past?
Let me know your thoughts in the comments section, any feedback is welcome.
