Skip to Content

SAP HANA Audit Trail - Best Practice

Version 1.0     2013
Version 1.1     2014/02   Updates referring to SPS 7

CONTEXT

General Information regarding the SAP HANA Audit Trail can be found in chapter 12 Auditing Activity in SAP HANA Systems of the SAP_HANA_Administration_Guide and chapter 9 Auditing Activity in SAP HANA Systems SAP_HANA_Security_Guide.

This version of the document refers to SAP HANA SPS 6 (revision 60).
Updates delivered with SAP HANA SPS 7 (revision 70) have been implemented.
For details see the SAP HANA Database SPS 7 Release Notes at http://help.sap.com/hana/Whats_New_SAP_HANA_Platform_Release_Notes_en.pdf -> section “Auditing (New and Changes)”

Introduction

The main requirement for auditing of a system is traceability of actions performed in that system. The main question is: Who did (or tried to do) what and when?

In a database, typical actions that need to be audited are:

  • changes of a user's authorization
  • creation or deletion of database objects
  • authentication of users
  • changes of the system configuration
  • changes of the audit configuration
  • access or alter sensitive information

Auditing by itself does not directly increase the system's security; however wisely set up and administered, it can help:

  • uncover security holes in case too many privileges were granted to some user
  • show attempts to break security
  • protect the system owner against accusations of security violation and data misuse
  • the system owner meet their security standards

For auditing to be introduced to an instance, some global settings must be configured at the outset to then enable the auditing globally and to specify where the auditing results should be stored (for clarity, the stored result is termed “audit trail”).

Additionally, one or more so-called “audit policies” have to be defined and enabled to specify which actions by which users on which objects should be audited.

Up to SPS 6: OS SYSLOG is the only supported audit trail target for production environments. The OS SYSLOG provides a means to safely store the audit trail in a manner such that even the database administrator cannot access or change it. There are numerous storage possibilities for the OS SYSLOG, including storing it on other systems. Also, the OS SYSLOG is the default log daemon in Unix/Linux systems, so that many IT departments have an existing strategy in place to deal with OS SYSLOG entries. Overall, OS SYSLOG provides a high degree of flexibility and security, as well as integration into a larger established system landscape.

As of SPS 7 a new option for the audit trail target was introduced: the audit trail can be sent to a database table within SAP HANA.

Basic Configuration

For production systems activate the Audit Trail and in the default configuration to write to the OS SYSLOG (or use the DB table option as of SPS 7). This is also recommended for all other system types

The OS SYSLOG must be configured properly to process the messages from the SAP HANA audit process; use the OS SYSLOG manual for technical configuration details.

The OS SYSLOG can write its messages to several targets at a time. Targets to be considered are:

  • A central directory on an external Log Server, to prevent loss of data due to server issues or data manipulation. This also protects the data against access of users with extended local privileges (such as local DB and OS administrators).
  • A local directory on OS level to prevent loss of data due to network issues or log server downtime. Be sure to restrict the group of users with change privileges on these files to an absolute minimum.
  • The HANA trace file directory <HANA_ROOT>/<SID>/HDB<InstNr>/<servername>/trace because this file can easily be read by HANA administrators with the privilege CATALOG READ or the role MONITORING using the HANA Studio.

Configure the targets based on your compliance requirements and according to your analytic processes which consume the log.

Align you auditing policies and users having access to the logs with the relevant data protection rules.

Global Audit Configuration using SAP HANA Studio

In the SAP HANA Studio there is an interface to set the global audit configuration. A user needs the system privilege AUDIT ADMIN or INIFILE ADMIN to perform these tasks.

The auditing can be enabled or disabled globally.

The type of the audit trail should be set to 'SYSLOGPROTOCOL'.

The type of the audit trail can be set to 'CSVTEXTFILE', too, but THIS MUST NOT BE USED ON PRODUCTION SYSTEMS as it has severe restrictions. On non-productive systems it will help checking the audit results. For  a non-productive instance using 'CSVTEXTFILE', you can specify the path where the text file should be stored.

Global Audit Configuration using SQL statements

In the evenat that the SAP HANA Studio cannot be used, the user with the system privilege AUDIT ADMIN or INIFILE ADMIN can use the following SQL statements to alter the configuration of the auditing system.

Activate/deactivate global auditing:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') set ('auditing configuration','global_auditing_state' ) = 'true' with reconfigure;


ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') set ('auditing configuration','global_auditing_state' ) = 'false' with reconfigure;


Set audit trail type:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') set ('auditing configuration','default_audit_trail_type' ) = 'CSVTEXTFILE' with reconfigure;


ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') set ('auditing configuration','default_audit_trail_type' ) = 'SYSLOGPROTOCOL' with reconfigure;


Set audit target path:

This only works when the audit trail type is CSVTEXTFILE.

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') set ('auditing configuration','default_audit_trail_path' ) = '<path>' with reconfigure;


Audit Policies

Audit Policies define which events to audit.

Audit Policies once created are not automatically enabled, meaning they will not trigger any auditing. They have to be enabled explicitly. They can be disabled and enabled at any point in time afterwards by an administrator. The administrator needs the system privilege AUDIT ADMIN to enable or disable an audit policy.

The policy has several further attributes, which are used to narrow the number of events that are audited (see appendix).

Users with any of the system privileges AUDIT ADMIN, CATALOG READ or DATA ADMIN are able to check existing audit policies in the public view AUDIT_POLICIES.

Audit Classes

To have a more functional view on auditing we introduce the phrase “Audit Classes”. An Audit Class represents a functional aspect that should be tracked using the audit trail while the audit policies represent the technical implementation.

These Classes can be categorized into three main groups:

  • Auditing
    • Self-Auditing
    • User SYSTEM
    • Technical Users
    • Logon Monitoring
    • Exceptional Users
  • Change Documents of User and Authorization Management
  • Data Access and Procedure Execution

Background information for the audit classes is included directly below. Sample templates for defining audit policies in these categories are provided in table format beneath the audit class description.

Self-Auditing

As of SPS 7 a ‘Mandatory Audit Policy’ is always enabled when the audit trail is activated. This new feature fulfills the need of self-auditing. No further action is required.

Beneath SPS 7 self-auditing needs to be configured manually:

To ensure the reliability to the audit trail it is essential to track changes in the audit trail configuration itself. These changes can be of two types:

  1. Changes regarding the System Settings for Auditing
    Define an Audit Policy for the action ‘SYSTEM CONFIGURATION CHANGE’
    Monitor the audit trail for changes of the three parameters global.ini -> auditing configuration -> default_audit_trail_path, default_audit_trail_type and global_auditing_state
  2. Changes of the auditing policies
    Define an Audit Policy for the actions ‘ENABLE AUDIT POLICY’ and ‘DISABLE AUDIT POLICY’.

Limitations and Mitigations of Self-Auditing:

  1. LIMITATION: ‘SYSTEM CONFIGURATION CHANGE’ writes currently (SPS6) to the defined audit target only. When the target is changed from SYSLOG to CSV File this parameter change is written to the new target CSV file only and is not documented in the old target SYSLOG. You may only notice that no further entries are written to the SYSLOG.
    MITIGATION: The content of all ini-files are available in the Configuration Validation tool in the SAP Solution Manager and should be monitored.
  2. LIMITATION: The actions ENABLE/DISABLE AUDIT POLICY do not audit the deletion or change of an audit policy.
    MITIGATION: Periodically check the content of the audit policies. You may use the views public.AUDIT_POLICIES or sys.AUDIT_POLICIES.
  3. SAP considers delivering a mandatory audit policy that forces a self-auditing and solves these limitations.

Auditing the SYSTEM User

User SYSTEM should be deactivated in a productive environment and only used in exceptional situations. Every attempt to use user SYSTEM must be treated as critical. As soon as someone activates the user in an emergency situation you should monitor all activities.


Note: Currently (SPS7) the SYSTEM user must be active for patching the database.

Technical Users

Technical users have static or no passwords that are not changed but stored in external incoming connections or background jobs. Because of this it is very unusual that such users provide invalid credentials. You should pay attention, when it happens that the logon of a technical user fails because

  • a failed logon could indicate a misuse of the account.
  • a repeated failed logon leads to a locked technical account and cause downtime for the business process/interface that uses this account.

Logon Monitoring

Track unsuccessful connect attempts of all users to detect critical activities.
Track successful connects to 'invalidate' preceding unsuccessful attempts (for example: caused by mistyping a password).

Exceptional Users

Track all activities of exceptional users like SUPPORT, SAPOSS, EMERGENCY, <firefighter>. These users use to have enhanced authorizations and, are for temporary use only. They are often provided to external persons in exceptional situations.

Change Documents of User and Authorization Management

In an ABAP environment, so called ‘Change Documents’ allow tracking of activities in user and authorization management, such as creating and deleting users, altering roles, or granting privileges and roles. In SAP HANA this is currently not available as separate functionality however you can track these activities using the audit trail. It is not primarily used to detect suspicious issues but to provide a history of changes:
Common activities in User Management, Role and Privilege Management and System Configuration should be recorded for information purposes.

Data Access and Procedure Execution

Based on your business needs, it may be useful to monitor activities on dedicated data objects and procedures

SAMPLTE TEMPLATE FOR AUDIT POLICY DEFINITIONS for Technical Implementation

Find a proposal for the technical definition of Audit Policies on SAP HANA in the following tables using the audit class approach.

The first table column ‘Action’ names the Audited Action, while the other column headers name the audit class. In the cells you find information about Audited Action Status (SUCCESSFUL, UNSUCCESSFUL, ALL), the Audit Level (EMERGENCY, CRITICAL, ALERT, WARNING, INFO) and if applicable the restriction to users.

AUDIT CLASS: AUDITING

Action

Self-Auditing
(obsolete with SPS 7)

User SYSTEM

Technical users

Logon Monitoring

Exceptional Users

ALTER USER

ALL - CRITICAL - User SYSTEM only

CONNECT

UNSUCCESSFUL - CRITICAL - User SYSTEM only

UNSUCCESSFUL - CRITICAL - User SAP<SID>


UNSUCCESSFUL - ALERT - <all technical users>

UNSUCCESSFUL – WARNING



SUCCESSFUL - INFO

SYSTEM CONFIGURATION CHANGE

SUCCESSFUL - CRITICAL for parameters global.ini -> auditing configuration -> default_audit_trail_path, default_audit_trail_type and global_auditing_state

ENABLE AUDIT POLICY

SUCCESSFUL - CRITICAL

DISABLE AUDIT POLICY

ALL ACTIONS

SUCCESFULL - INFO - User SYSTEM only

ALL - INFO - <list of users>

AUDIT CLASSES: CHANGE DOCUMENTS

DATA ACCESS and PROCEDURE EXECUTION

Action

Change Documents
of User and Authorization Management

Data Access and Procedure Execution

GRANT PRIVILEGE

 

REVOKE PRIVILEGE

 

GRANT STRUCTURED PRIVILEGE

 

REVOKE STRUCTURED PRIVILEGE

 

GRANT APPLICATION PRIVILEGE

 

REVOKE APPLICATION PRIVILEGE

 

GRANT ROLE

 

REVOKE ROLE

 

GRANT ANY

SUCCESSFUL – INFO

REVOKE ANY

CREATE USER

SUCCESSFUL – INFO

DROP USER

ALTER USER

CREATE ROLE

DROP ROLE

SYSTEM CONFIGURATION CHANGE

SUCCESSFUL - INFO

CREATE STRUCTURED PRIVILEGE

SUCCESSFUL - INFO

DROP STRUCTURED PRIVILEGE

ALTER STRUCTURED PRIVILEGE

ACTIVATE REPOSITORY CONTENT

SUCCESSFUL - INFO

IMPORT REPOSITORY CONTENT

EXPORT REPOSITORY CONTENT

SET SYSTEM LICENSE

SUCCESSFUL - INFO

UNSET SYSTEM LICENSE

DROP TABLE

SUCCESSFUL - INFO

INSERT

on demand for dedicated data objects (for revisions < 68 see comment**)

UPDATE

DELETE

SELECT

EXECUTE

Procedures activating, granting, revoking roles and privileges

and procedures

** Beneath revision 68 auditing INSERT can cause general SAP HANA system issues under special circumstances. Therefor INSERT should not be audited in lover revisions.
The INSERT statement is also included in ALL ACTIONS. Because of that ALL ACTIONS should be logged very rarely only for dedicated users.

General rules for audit Policy design

  • Create as few audit policies as possible
    • It is usually better to have one complex audit policy than several simple ones
    • Note: some audit actions cannot be combined in the same policy (audit action groups)
  • Use audit actions that combine other actions where appropriate
    • E.g. GRANT ANY instead of GRANT PRIVILEGE + GRANT STRUCTURED PRIVILEGE + ...
  • Only create audit policies for DML actions where required
    • Audit logging of DML actions usually has more performance impact than audit logging of DDL actions
  • Don’t create audit policies for actions that are automatically audited
    • “Mandatory audit actions” were introduced with SAP HANA SPS 7, they are always active
    • E.g. contain CREATE/ALTER/DROP AUDIT POLICY
  • Don’t create audit policies for database-internal tables that are affected by administrative actions but create audit policies for the administrative actions themselves
    • E.g. tables CACHE_P_USER_PASSWORD, P_USER_PASSWORD are database-internal and cannot be accessed by any user (not even SYSTEM), changes in these tables are carried out via internal mechanisms (not via DML). Don’t include these tables in an audit policy but create an audit policy for user changes instead.

Appendix

Syntax of SQL Statements to create an Audit Policy

The syntax looks as follows in SPS6. It is subject for further improvements; therefore you should check the latest SAP HANA SQL documentation which matches to the revision of your installation.

  
The name of the audit policy has to be unique to other audit policies.

The audit action specified behind the keyword AUDITING can be set to only successful, only unsuccessful or all statements.

Unsuccessful in this case means that the user was not authorized to execute the action. If another error occurs (e.g. misspellings in user or object names and syntax errors) the statement is not audited.

Only tables, views, and procedures can be specified as object. They should be named inclusive the schema, they belong to. Currently, sequences and synonyms cannot be chosen as objects to be audited.

Only if all audit actions specified can be combined with all objects specified, the creation of the audit policy will be possible.

Objects have to exist, before they can be named in an audit policy.

If an object was named in an audit policy and will be deleted, the audit policy will remain at its current state.

If the object will be re-created, the audit policy will work for this object again.

If ACTIONS FOR is used, then a user_name has to be specified, for which the audit policy should trigger the auditing. For all other actions the specification of a user is optional and will restrict the auditing to those users.

Users have to exist before they can be named in an audit policy.

Each policy is assigned a level. Possible levels, in decreasing order of importance, are: EMERGENCY, ALERT, CRITICAL, WARNING, INFO.

Tools checking the audited actions can then find the most important information and separate from that just having WARNING LEVEL, for example.

References:

SAP HANA SQL Reference
http://help.sap.com/hana/SAP_HANA_SQL_and_System_Views_Reference_en.pdf

or

SAP HANA SQL Reference
http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/ff532c751910148657c32fe3431a9f/content.htm
-> CREATE AUDIT POLICY
http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d3d56075191014af43d6487fcaa603/content.htm

Audit Result / Audit Entry

The audited actions are written in so-called audit entries. Each action may result in one or more audit entries. If an audited action was done implicitly by a procedure, the call to this procedure will be audited together with the audited action.


Audit entries written to an audit trail have the following fields with the following meaning:


Field

Meaning

Example Value

Event Timestamp

When did the event occur? (in system local time)

  1. 08.09.2012 05:34

Service Name

Which service did the event occur in?

Indexserver

Hostname

myhanablade23.customer.corp

SID

HAN

Instance Number

23

Port Number

32303

Client IP Address

ip address of the client application

  1. 127.0.0.2

Client Name

name of the client machine

lu241511

Client Process ID

pid of the client process

19504

Client Port Number

port of the client process

47273

Policy Name

AUDIT_GRANT

Audit Level

CRITICAL

Audit Action

GRANT PRIVILEGE

Active User

the user that executed the statement

MYADMIN

Target Schema

On which schema was the privilege granted or in which schema is the target object?

PRIVATE

Target Object

On which object was the privilege granted?

Privilege Name

Which privilege was granted/revoked?

SELECT

Grantable

Was the privilege or role granted with/without GRANT/ADMIN OPTION

NON GRANTABLE

Role Name

Which role was granted/revoked?

Target Principal

Who was the target of the action. Useful for grant/revoke statements.

HAXXOR

Action Status

Was the statement successful or unsuccessful

SUCCESSFUL

Component

future use for configuration changes

Section

future use for configuration changes

Parameter

future use for configuration changes

Old Value

future use for configuration changes

New Value

future use for configuration changes

Comment

Executed Statement

GRANT SELECT ON SCHEMA PRIVATE TO HAXXOR

Session Id

id of the session the statement was executed in

400006

In the OS SYSLOG the fields are separated by ';', meaning that an entry looks like this:

<Event Timestamp>;<Service Name>;<Hostname>;<SID>;<Instance Number>;<Port Number>;<Client IP Address>;<Client Name>;<Client Process ID>;<Client Port Number>;<Audit Level>;<Audit Action>;<Active 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>;

Known limitations

Since the audit manager is part of the database engine, it can only audit events that happen inside of the database engine. If the database engine is not online while the event occurs, there is simply no way the event can be detected and processed by the audit manager.

There are currently two such events which are noteworthy:

  • The upgrade of a DB instance. The upgrade is triggered while the instance is offline and when it comes online again it’s not possible to reconstruct which user triggered the upgrade at what time.
  • Changes to the INI files containing the system configuration. Unless the change is done via the SQL interface, the database engine does not see it. It may also have been done when the system was offline.


Note: The audit manager is part of every server that is part of the DB. Thus, also the name server, statistics server or script server can write an audit trail. However, their ability to write audit trails is limited by the fact that they can't access the catalog tables that contain information about audit policies. Thus, they can only write non-policy-based events, e.g. startup or shutdown.

Performance Considerations

Putting a low performance impact on the running system is probably the most prominent quality of the auditing infrastructure. If auditing is enabled for a certain system, a lookup for qualifying audit policies has to be done for every incoming query. This lookup might be quite complex since it has to determine all underlying object accessed by a certain composite object (e.g. a view or a procedure). However, that call is in most cases a non-blocking call, because its outcome does not influence the query execution. On the other hand the audit entry is written whether or not the statement finishes successfully.

The second part possibly becoming a bottleneck is the log writing into OS-file audit trails. Those file container should have log write buffers that collect write requests to reduce disk IO. However, the audit administrator should be aware of the fact that using write buffers in a database crash scenario could potentially result in the loss of audit trail entries.

Tags: