Setting Up Auditing

Objective

After completing this lesson, you will be able to configure and enable auditing

Auditing Overview

Auditing Overview

Auditing provides visibility on what actions were performed or attempted in the SAP HANA database. This allows you, for example, to log and monitor read access to sensitive data. It is therefore recommended to create a basic set of policies.

Auditing can be enabled and disabled per tenant database, you can configure auditing independently for each database using SAP HANA cockpit or SQL statements. Changes to the auditing configuration in one database have no effect on auditing in other databases in an SAP HANA system.

Audit logging is not enabled by default.

The auditing service of the SAP HANA database allows you to monitor and record selected actions that are performed in your database. To use this service, first activate it for the database. You can then create and activate the required audit policies.

Audit trails are configured only in the system database and cannot be changed later in a tenant database. Audit entries are created in one or more audit trails (database table, linux syslog, CSV file, or kernel trace) when an audit policy is triggered.

Audit Policies

An audit policy defines the actions to be audited. It also outlines the conditions under which the action must occur for it to be relevant for auditing. When an action occurs, the policy is triggered and an audit event is written to the audit trail. Audit policies are database-specific.

An audit policy can specify any number of actions to be audited, but not all actions can be combined together in the same policy. In addition to the actions to be audited, an audit policy specifies parameters that further narrow the number of events actually audited. You define the audited action status (successful or unsuccessful), determine the target objects like schemas or tables. Also, the audited users and the audit level (ALERT, WARNING, INFO, and so on) need to be set.

When the audit policy is triggered, an audit entry of the corresponding level is written to the audit trail. This allows tools that check audited actions to find the most important information.

Audit Actions

An action corresponds to the execution of an action in the database by SQL statement. For example, to track user provisioning in your system, create an audit policy that audits the execution of the SQL statements CREATE USER and DROP USER.

Although most actions correspond to the execution of a single SQL statement, some actions cover the execution of multiple SQL statements. For example, the action GRANT ANY audits the granting of multiple entities for the SQL statements GRANT PRIVILEGE, GRANT ROLE, GRANT STRUCTURED PRIVILEGE, and GRANT APPLICATION PRIVILEGE.

Other actions that are typically audited are, for example, creating objects, querying or manipulating data in the database, changes to user authorization, or access and changes to the system configuration or data, and so on.

Both successful and unsuccessful actions can be recorded.

Some sample auditable actions for changes in the SAP HANA database on users, roles, and authorization events are as follows:

  • Create or drop user, and create or drop role

  • Grant or revoke role

  • Grant or revoke SQL privilege, system privilege, and analytical privilege

  • Create or drop analytical privilege

  • Authentication of users like connection attempts to the database

Changes to system configuration, for example, .ini file, uploading license keys, set or unset system license for all, or changes to the data volume encryption are actions that can be logged. When you enable audit logging for configuration changes, the previous values of parameters are written to the audit trail.

Access to or changing of sensitive data in schemas, tables, views, and procedures can be audited. Both write and read access (select, insert, update, delete and execute statements) to data can be recorded.

Mandatory Audit Actions

If auditing is active, certain actions are always audited and are therefore not available for inclusion in user-defined audit policies. In the audit trail, these actions are labeled with the internal audit policy Mandatory Audit Policy.

Mandatory audit actions include the creation, modification, or deletion of audit policies, also the deletion of audit entries from the audit trail, if they are written to column store database tables. Changes to auditing configuration, like enabling or disabling auditing, changing the audit trail target, or changing the location of the audit trail target if it is a CSV text file will also be logged as an audit action.

Unauditable Events

Only actions that take place inside the database engine can be audited. If the database engine is not online when an action occurs, it cannot be detected, and therefore cannot be audited. These actions include, for example, an upgrade of an SAP HANA database instance, or direct changes to system configuration files using operating system commands.

An upgrade is triggered when the instance is offline. When it becomes available online again, you cannot determine which user triggered the upgrade and when.

Only changes that are made using SQL are visible to the database engine. You can change configuration files when the system is offline.

Audit Trails

When an audit policy is triggered, that is, when an action in the policy occurs under the conditions defined in the policy, an audit entry is created in one or more audit trails.

The following audit trail targets are supported for production systems:

Audit Trails

Audit Trail TargetDescription
Internal database tableMakes it possible to query and analyze auditing information quickly. It also provides a secure and tamper-proof storage location. Audit entries are only accessible through the public system views.
Linux syslogDefault log daemon in UNIX systems. Secure storage location for the audit trail because not even the database administrator can access or change it.
CSV text fileThis should only be used for test purposes in non-production systems.

Separate audit trail targets may be configured for the audit level, that is, the severity of the action being audited.

Audit entries from audit policies with the audit level EMERGENCY, CRITICAL, or ALERT are written to the audit trail targets specified for the audit level in question. If no audit trail target is configured for an audit level, entries are written to the audit trail target configured for the database.

Audit policy-specific targets are also possible in the system database. In this case, audit entries from a particular policy are written to the specified audit trail targets. If no audit trail target is configured for an audit policy, entries are written to the audit trail target for the audit level if configured, or the audit trail target configured for the database. Several audit trail targets can be configured for each individual policy.

Note

By default, tenant database administrators cannot configure audit trail targets independently for their database since the underlying system properties are in the default configuration change blocklist (multidb.ini). The default target for all audit trails in tenant databases is internal database table. Although not recommended, it is possible to change the audit trail target of a tenant database.

Caution

To ensure the privacy of tenant database audit trails, it is recommended that you do not change the default audit trail target (internal database table) of tenant databases.

Activation of Audit Policies

Auditing is implemented through the creation and activation of audit policies. An audit policy defines the actions to be audited, as well as the conditions under which the action must be performed to be relevant for auditing. For example, actions in a particular policy are audited only when they are performed by a particular user on a particular object. When an action occurs, the audit policy is triggered and an audit event is written to the audit trail.

Enable Auditing in SAP HANA Cockpit

The figure, Configuring Audit Logging, outlines how to configure and switch on audit logging for the system database using SAP HANA cockpit.

Note

Auditing can be activated or disabled per tenant in a multi-tenant SAP HANA database. In the system database, you can also configure the audit trail targets. In a tenant database, this cannot be changed.

Configure the Base Setup for Audit Policies

You can use a wizard to quickly enable auditing and to apply a basic configuration for audit policies.

Instead of manually configuring audit policies, you can use a wizard to apply SAP's recommended configuration settings. This allows you to quickly start working with audit policies.

Note

The base setup does not guarantee that the configuration is optimal for your particular system. To optimize the configuration, it may be advisable to manually configure specific settings.

Create Audit Policies

You can specify any number of actions to audit in an audit policy. Not all actions can be combined together in the same policy; therefore, compatible audit actions are grouped together. When you select an action, any actions that are incompatible with the selected action are unavailable for selection.

If you want to select two incompatible audit actions, you need to create two separate audit policies.

In addition to the actions to be audited, an audit policy also specifies additional parameters that further narrow the number of events actually audited, as follows:

Audited action status: On successful execution, on unsuccessful execution or both.

Target object or objects : Schema, Tables, Views, Procedures, and Sequences.

Audited user or users: All users, or individual users, can be included or excluded from an audit policy.

Audit level: EMERGENCY, ALERT, CRITICAL, WARNING, or INFO.

When an audit policy is triggered (that is, when an action in the policy occurs under the conditions defined in the policy) an audit entry is created in the audit trail.

Firefighter logging logs all actions performed by a specific user. This covers all actions that can be audited individually, as well as actions that cannot otherwise be audited. Such a policy is useful if you want to audit the actions of a particularly privileged user.

Note

Some actions cannot be audited using database auditing, even with a policy that includes all actions, in particular, system restart and system recovery.

Caution

Firefighter logging can generate many audit entries, so only enable it if necessary.

Create Audit Policies for Tenant Databases from the System Database

As of SAP HANA 2.0 SPS04 you can audit specific tenant database activities from the system database.

In general, audit policies in a tenant database are created from within the tenant database. In some scenarios, like hosting, you might want to centrally monitor activities in tenant databases.

Actions for specific areas like managing certificates, authentication or encryption beside session management, system configuration changes and installation or deletion for licenses can be audited directly from the system database.

Audit policies created in the system database for a tenant database are visible in the tenant database but cannot be changed from there.

Monitor the Size of the Audit Trail Table

To avoid the audit table growing indefinitely, you can delete old audit entries by truncating the table. The system monitors the size of the table in relation to the overall memory allocation limit of the system. It issues an alert when it reaches defined values, which are 5%, 7%, 9%, and 11% of the allocation limit, by default. Configure this behavior can be configured with check 64, Total memory usage of table-based audit log. Only users with the system privilege AUDIT OPERATOR can truncate the audit table.

Note

This alert only applies if you select a database table as an audit trail target (not for syslog).

You can use the SAP HANA cockpit to delete audit entries created up until a certain time from the audit table.

For audit policies that are explicitly configured to write audit entries to an internal database table, you can specify an audit retention period in days. Once the retention period has elapsed for an audit entry, the audit entry is deleted. If you add a retention period to an existing audit policy, all existing audit entries that exceed the retention period are deleted.

The minimum default retention period is seven days.

Reasons why you need to delete audit log entries might be that they are no longer needed, that is, to free up database storage or to meet your company's compliance requirements.

To define retention periods, create a new or edit an existing audit policy using SAP HANA cockpit and ensure you have configured database table as the audit trail target.

If the table has grown so large that there is not enough memory to delete old entries, use the following SQL command to empty the table completely: ALTER SYSTEM CLEAR AUDIT LOG ALL

Note

A retention period can only be specified for audit policies that explicitly use the database table audit trail target. You cannot specify a retention period for audit policies that use the default audit trail targets, even if the default audit trail target is set to database table. If you add a retention period to an existing audit policy, all existing audit log entries that exceed the retention period will be deleted.

Monitor Audit Logging Status and Check Policies

The Auditing tile in SAP HANA cockpit allows you to view the audit logging status, and check which audit policies are active.

Note

Using SAP HANA cockpit, the SQL statement that corresponds to an existing audit policy can be shown in the audit policy details.

Viewing the Audit Trail

For each occurrence of an audited action, one or more audit entries are created and written to the audit trail. If the audit trail target is a database table, you can view the audit trail in the Auditing app of the SAP HANA cockpit. You can display, filter, and sort the audit trail in SAP HANA cockpit. Several options are available for configuring the layout.

Alternatively, you can query and analyze auditing information quickly using an SAP HANA database table as audit trail target. It provides a secure and tamper-proof storage location. You can only access audit entries through the public system view AUDIT_LOG. This view is read-only; only a user with system privilege AUDIT OPERATOR can delete old entries from the underlying internal table.

In the SQL console of SAP HANA cockpit, open the PUBLIC.AUDIT_LOG view, or display the system view using the SQL command: SELECT * FROM "PUBLIC"."AUDIT_LOG"

Example for Setting Up an Audit Policy with SQL

To set up and activate an audit policy, you can use the following SQL statements as an example:

Code Snippet
123
CREATE AUDIT POLICY policy_UserRoleManagement AUDITING ALL CREATE USER, DROP USER, CREATE ROLE, DROP ROLE LEVEL Critical; ALTER AUDIT POLICY policy_UserRoleManagement ENABLE;

When the audit trail is written to the Linux syslog or a CSV file, the audit logging output looks like:

Code Snippet
12
Syslog output (csv-compatible format ): /var/log/messages August 13 13:29:55 WDFLBMT7346 HDB[5212]: 2023-08-14 13:29:55;indexserver;wdflbmt7346.wdf.sap.corp;H46;00;30003;H46;127.0.0.2;lu306309;6776;58060;policy_UserRoleManagement;CRITICAL;CREATE USER;SYSTEM;;;;;;JohnDoe;SUCCESSFUL;;;;;;create user JohnDoe identified by XXXXXXXXXXXXX;434597;

Column header names are not written to the audit trail, so you need to add them manually.

Where an audit entry appears as follows:

Code Snippet
1
<Event Timestamp>;<Service Name>;<Hostname>;<SID>;<Instance Number>;<Port Number>;<Database Name>;<Client IP Address>;<Client Name>;<Client Process ID>;<Client Port Number>;<Policy Name>;<Audit Level>;<Audit Action>;<Session User>;<Target Schema>;<Target Object>;<Privilege Name>;<Grantable>;<Role Name>;<Target Principal>;<Action Status>;<Component>;<Section>;<Parameter>;<Old Value>;<New Value>;<Comment>;<Executed Statement>;<Session Id>; ...

Note

To create and activate the audit policy, you need root-authorization. The syslog is a secure storage location for the audit trail because not even the database administrator can access or change it. There are also numerous storage possibilities for the syslog, including storing it on other systems. In addition, the syslog is the default log daemon in UNIX systems. The syslog therefore provides a high degree of flexibility and security, as well as integration into a larger system landscape. For data protection and privacy, the SAP HANA audit log uses the authpriv facility with the prefix HDB. Check your operating system configuration files for the location of the syslog files. For more information about how to configure syslog, refer to the documentation of your operating system.

Caution

If the syslog daemon cannot write the audit trail to its destination, you will not be notified. To avoid a situation in which audited actions are occurring, but audit entries are not being written to the audit trail, ensure that the syslog is properly configured and that the audit trail target is accessible and has sufficient space available.

For more information, see the SAP HANA Security Guide.

Log in to track your progress & complete quizzes