Well-Controlled (SAP): What are the HANA Audit Policies you can configure to meet your regulators & business needs? (Design specification guide)

[mostafa_signifysolution]

Key Highlights - SAP HANA Audit Policies

• Audit logs in HANA enables management to monitor and record selected actions performed at the database level. Many important audit policies, including HANA user level activities, need to be manually turned on. Without HANA audit logging, actions compromising security or data integrity may go unnoticed.


• HANA audit policies should be configured with the following few guiding principles in mind: (1) should be only configured if it addresses a specific requirement, (2) should only be targeting relevant objects or users, (3) should not be duplicate and should be concise (e.g. not configuring an audit policy for already monitored activities).


• Minimum audit policies can be classified into 5 main areas: User & Role Management, Authorization Management, Data Manipulation, Object Maintenance and System Management. Each of these policies need to be manually configured in the HANA database.


• Bonus tables at the end of the article: An example of the design output is posted within this article; the tables at the end outline the audit policies and the technical configurations for each policy (i.e. what policies need to be configured). Please remember, it is important to go through your change management process & to test and tweak the design tables per your environment.

Why HANA audit policies are important to be designed and configured as part of the system design phase?

SAP S4 implementations are ramping up and IT departments (and their consultants) continue to be accountable for implementing and deploying HANA level security. HANA requires management to place additional IT controls to effectively mitigate the new risks introduced by S4 HANA technology.

Specifically, management needs to design controls to address the risks around: access management, change management, data backup & recovery and development activities (e.g. data driven applications developed on the HANA level).

Below in this post, we are going to discuss an example of the design specifications needed to record user level activities in the HANA database and here is why:

• Audit policies supports the monitoring & testing activities performed by security operators, internal auditors and external auditors. As such, audit policies should be configured to support each of the stakeholders requirements, while taking into consideration any regulation and system performance impact.


• Auditing the user level activities in HANA is not enabled by default. User-level auditing can be customized per management requirement and its maintenance is subject to each company policy.

What Audit Policies are enabled by default? (SAP-enabled audit policies)

As mentioned, user level auditing by default is not enabled. SAP left it to each company to configure whatever they deem appropriate. This is like the Security Audit Logs - SM20 reports on the SAP application layer.

However, to maintain the integrity of the audit policies, SAP configured HANA with specific actions that are monitored by default. These actions are always audited and recorded. It is important to understand that these audited actions are not comprehensive to the needs of the different stakeholders, thus, additional policies need to be manually designed and configured.

Here are the details of these actions enabled by default:

• Maintenance of auditing HANA configuration (Audit Activation Configuration): Changes to auditing configuration (i.e. enabling or disabling auditing), Changing the audit trail target, Changing the location of the audit trail target, Changing the maximum length of a statement that is audited completely, and Changing enabled authentication methods.


• Maintenance of User-defined audit policies (Audit Policies Configuration): Creation, modification, or deletion of audit policies that are configured by the database administrator. Audit policies are the “what” activities monitored in the HANA database.


• Deletion of the audit logs (Actual User Recorded Activities): Deletion of audit entries from the audit trail. This only applies to the audit trail written to an internal database table. It is not possible to delete audit entries from the syslog audit trail target.


• Password change of the SYSTEM account (SAP HANA SAP*-like account): Changing the password of the SYSTEM user of a tenant database from the system database. An audit entry is written to the audit trail of both the system database and the tenant database.

What this means to you: For these actions outlined, SAP recommends that management should not create audit policies for these actions because they are automatically audite). It is important to know these still to be able to articulate them to anyone who wants to understand what policies you have enabled on HANA.

What Audit Policies need to be configured in the HANA database? (Manually configured audit policies)

After going through the default policies, below are the policies needed to be configured at minimum for a typical environment.

However, before we go into details, let us level set some key terminologies:

• Audit Policy: This is a user-defined name in the HANA system. An audit policy is configured with the actions that will be relevant to it (e.g. User Management policy will record activities like creating a user). The Audit Policy is the “report” the company will generate out of the HANA database to view the actions audited.


• Audited Action/Event: Actions or events are the actual activity performed or statements executed by a user at the database level (e.g. Create or Drop statements). These are specific to HANA and would not be different from one HANA database to another, at least same HANA database versions.


• Audit Level: For each action configured in a policy, an audit level can be configured (e.g. Info, Warning, and Critical). These serve only informational purposes. It does not impact what SAP HANA records in the audit log. However, it may have a process impact on how you treat the audited action if they are manually/automatically monitored (i.e. a company would not treat Critical events in the same weight as Info events)


• Audit Status: This defines whether successful, unsuccessful or all executions of the specified audit actions are audited.


• Targeted/Excluded User: These are user-defined fields in an audit policy. Obviously, these are the specific users that HANA would monitor from a specific action.


• Targeted/Excluded Object: These are user-defined fields in an audit policy. Similar to user level, these are the specific objects (e.g. tables or schemas) that that HANA would monitor from a specific action.

Now, that we went through these quick terminologies, let's go into the details of each policy that usually are configured in a HANA environment. Generally, I like to classify audit policies into five (5) main areas:

(Area 1) User & Role Management

• These are the set of HANA audit policies covering granting and revoking of privileges to end users and roles as well as creating and deleting roles & users.


• Overall, these audit policies should be configured across all objects and all users. System performance generally are not impacted by these policies.

Audit Policy Name	Audit Action Name	SAP HANA Audit Action	Audit Status	Targeted Users
User and Role Management	ALTER ROLE	Altering roles	Successful	All Users
	ALTER USER	Altering users	Successful	All Users
	ALTER USER GROUP	Administering user groups	Successful	All Users
	CREATE ROLE	Creating roles	Successful	All Users
	CREATE USER	Creating users	Successful	All Users
	CREATE USER GROUP	Creating user groups	Successful	All Users
	DROP ROLE	Dropping roles	Successful	All Users
	DROP USER	Dropping users	Successful	All Users
	DROP USER GROUP	Dropping user groups	Successful	All Users
Granting and Revoking of Authorization	GRANT ANY	Granting of privileges, structured privileges or roles to users or roles	Successful	All Users
	REVOKE ANY	Revoking of privileges, structured privileges or roles from users or roles	Successful	All Users

(Area 2) Authorization Management

• These are the set of HANA audit policies that includes changes related to the maintenance of structured and repository privileges.


• Overall, these audit policies should be configured across all objects and all users. System performance generally are not impacted by these policies.

Audit Policy Name	Audit Action Name	SAP HANA Audit Action	Audit Status	Targeted Users
Structured Privilege Management	ALTER STRUCTURED PRIVILEGE	Altering structured/analytical privilege	Successful	All Users
	CREATE STRUCTURED PRIVILEGE	Creating structured/analytical privileges	Successful	All Users
	DROP STRUCTURED PRIVILEGE	Dropping structured/analytical privilege	Successful	All Users
Repository Privilege Management	ACTIVATE REPOSITORY CONTENT	Activating repository design time objects	Successful	All Users
	EXPORT REPOSITORY CONTENT	Exporting repository design time objects	Successful	All Users
	IMPORT REPOSITORY CONTENT	Importing repository design time objects	Successful	All Users

(Area 3) Data Manipulation

• These are the set of HANA audit policies that includes direct database changes updates. This policy is a key hot topic for many regulators and frameworks; specifically, I see that this a main area of concern for SOX compliance activities.


• This audit policy needs rigorous testing. These audit policies would generate huge amount of data if not configured appropriately. Some generic or technical IDs would generate tons of data within few hours (e.g. Schema IDs). When configuring this audit policy, the main focus should be the users who directly connect to the database and have the potential to update, insert, delete data in tables.

Audit Policy Name	Audit Action Name	SAP HANA Audit Action	Audit Status	Excluded Users
Data Query and Manipulation	DELETE	Deleting rows from tables/views and truncating tables. Allows specification of target objects.	Successful	Schema IDs and any technical generic accounts
	INSERT	Using insert/replace/upsert statements on tables and views. Allows specification of target objects.	Successful	Schema IDs and any technical generic accounts
	UPDATE	Using UPDATE/REPLACE/UPSERT statements on tables and views. Allows specification of target objects.	Successful	Schema IDs and any technical generic accounts

(Area 4) Object Maintenance

• These are the set of HANA audit policies that includes maintaining database objects including: functions, tables, views, etc.


• Overall, these audit policies should be configured across all objects and all users. System performance generally are not impacted by these policies. Again, the focus should be the end users who directly access the HANA database for development or support activities.

Audit Policy Name	Audit Action Name	SAP HANA Audit Action	Audit Status	Targeted Users
Data Definition	ALTER FUNCTION	Altering functions	Successful	All Users
	ALTER PROCEDURE	Altering procedures	Successful	All Users
	ALTER TABLE	Altering tables	Successful	All Users
	ALTER VIEW	Altering views	Successful	All Users
	CREATE FUNCTION	Creating functions	Successful	All Users
	CREATE PROCEDURE	Creating procedures	Successful	All Users
	CREATE SCHEMA	Creating schemas	Successful	All Users
	CREATE TABLE	Creating tables	Successful	All Users
	CREATE TRIGGER	Creating triggers	Successful	All Users
	CREATE VIEW	Creating views	Successful	All Users
	DROP FUNCTION	Dropping functions	Successful	All Users
	DROP PROCEDURE	Dropping procedures	Successful	All Users
	DROP SCHEMA	Dropping schemas	Successful	All Users
	DROP TABLE	Dropping of tables	Successful	All Users
	DROP TRIGGER	Dropping triggers	Successful	All Users
	DROP VIEW	Dropping views	Successful	All Users

(Area 5) System Management

• These are the set of HANA audit policies that includes maintaining and supporting different functionalities within the HANA database


• Overall, these audit policies should be configured across all objects and all users. System performance generally are not impacted by these policies. Again, the focus should be the end users who directly access the HANA database for development or support activities.


• For policy #9 All Actions (Firefighter), this policy should only be configured to an emergency account used by an end user in critical situations. Also, sometimes this audit policy can be enabled for 3rd party vendor performing ad-hoc maintenance activities, where it is not expected they would create a lot of audit logs data. This policy #9 needs to be configured correctly to limit the data it would generate.

Audit Policy Name	Audit Action Name	SAP HANA Audit Action	Audit Status	Targeted Users
User Connection	CONNECT	Creation of a user connection to the database	Unsuccessful	All Users
System Configuration Management	STOP SERVICE	Audits stop of services	Successful	All Users
	SYSTEM CONFIGURATION CHANGE	Changes to the system configuration (for example, INIFILE)	Successful	All Users
All Actions (Firefighter)	ACTIONS	All user-triggered database actions. Used for specific users	Successful	N/A - Should only be enabled for a Firefighter account
Backup Deletion	BACKUP CATALOG DELETE	Deleting entries in the backup catalog	Successful	All Users
Volume Encryption	ALTER APPLICATION ENCRYPTION	Altering application encryption keys	Successful	All Users
	ALTER APPLICATION ENCRYPTION ROOT KEY	Altering application encryption root keys	Successful	All Users
	ALTER BACKUP ENCRYPTION	Altering backup encryption status	Successful	All Users
	ALTER BACKUP ENCRYPTION ROOT KEY	Altering backup encryption root keys	Successful	All Users
	ALTER LOG ENCRYPTION	Altering log encryption status	Successful	All Users
	ALTER LOG ENCRYPTION ROOT KEY	Altering log encryption root keys	Successful	All Users
	ALTER PERSISTENCE ENCRYPTION	Altering database persistence encryption status and page encryption keys	Successful	All Users
	ALTER PERSISTENCE ENCRYPTION ROOT KEY	Altering database persistence encryption root keys	Successful	All Users
	ALTER ROOT KEYS BACKUP PASSWORD	Altering backup password used to protect backup root keys	Successful	All Users
Client-side Encryption	ALTER CLIENTSIDE ENCRYPTION COLUMN KEY	Altering a column encryption key (CEK)	Successful	All Users
	CREATE CLIENTSIDE ENCRYPTION COLUMN KEY	Creating a CEK	Successful	All Users
	CREATE CLIENTSIDE ENCRYPTION KEYPAIR	Creating a client-side encryption key pair	Successful	All Users
	DROP CLIENTSIDE ENCRYPTION COLUMN KEY	Dropping a CEK	Successful	All Users
	DROP CLIENTSIDE ENCRYPTION KEYPAIR	Dropping a client-side encryption key pair	Successful	All Users
Application Auditing	PERSONAL DATA ACCESS	Audit log access to personal data	Successful	All Users
	PERSONAL DATA MODIFICATION	Audit log modification of personal data	Successful	All Users
	CONFIGURATION CHANGE	Audit log configuration change events	Successful	All Users
	SECURITY EVENT	Audit log security events	Successful	All Users

 

Bringing it all together

Audit policies, including HANA user level activities, are critical to compliance and security frameworks. Audit policies in HANA need to be manually turned on. HANA audit policies should be only configured if it addresses a specific requirement, should be targeting relevant objects or users, and should not be duplicate and should be concise.

In future posts, we can explore how to export and review the audited actions. Please feel free to add/change any of your thoughts via direct messaging me or in the comments below.

Follow me via Twitter and LinkedIn

---

The views, information or opinions expressed in this short article are views of my own.  All information in this article is provided “as is”, with no guarantee of completeness, accuracy, timeliness or of the results obtained from the use of this information.

[former_member607927]

Thank you for this good blog entry!

[mostafa_signifysolution]

Thank you Rainer! Feel free to reach out if you have any questions!

[sheffy]

Hey, Great Blog.

Can you help  how to take export and import of Audit Logs in Sap Hana?

[mostafa_signifysolution]

Absolutely – it should be a simple database table/view export. There are some prerequisites though:

1. Audit logs should be enabled to be able to view the entries.
2. You should have either AUDIT ADMIN and AUDIT OPERATOR to view the audit_log view entries
3. From your HANA Studio console, your SQL query should be simple one: SELECT * FROM AUDIT_LOG OR SELECT * FROM “PUBLIC”.”AUDIT_LOG”

Please note that I consider AUDIT ADMIN and AUDIT OPERATOR as elevated privileges and should only be granted to monitored accounts (e.g. FireFighter-like accounts).

Feel free to reach out to me directly if you are still facing some issue – Keep me posted!

PS - Update: In the HANA 2.0 SP4, you can have a read-only access to audit trail. The new system privilege AUDIT READ allows read-only access to the database table audit trail.

[sheffy]

Thanks!

It helped me and my issue is resolved.

[BhavanaR]

Every Well Written. Thank you for such a detailed explanation of HANA Audit. and also thank you for helping us with HANA Audit Policy.  Really Appreciate.

 

[mostafa_signifysolution]

Thank you Bhavava!

[former_member690972]

Thanks mostafa_signifysolution for the post.

Just wanted to find out if there is a way to view policy log using SAP Hana cockpit?

[mostafa_signifysolution]

You need the SAP HANA Studio to view/export the audit entries. You can however configure you HANA database audit policies through HANA Cockpit. Also, with the latest release of HANA Cockpit that just came out with HANA 2.0 SPS05, you can view/configure pre-configured SAP HANA database audit policies. If I came across any new updated information, I will make sure to update you as well.

[former_member325147]

Hi

Is there any parameter or policy to check for audit setting changes, if some one would have done changes to existing settings(audit status - enabled or disabled)

[mostafa_signifysolution]

Good question - so yes there are 4 types of audit actions that are audited by default. These actions are audited by the internal audit policy MandatoryAuditPolicy. I talk about these in this post (right in the middle of the post). Also, here is the SAP Help link for details: https://help.sap.com/viewer/b3ee5778bc2e4a089d3299b82ec762a7/2.0.02/en-US/4f7cde1125084ea3b820603853...

[former_member700451]

Great post mostafa_signifysolution!
Are there any pre-configured audit policies available for SAP HANA 2.0 SPS04? or is it available for SPS05 only?

[mostafa_signifysolution]

Thank you! and yes so with the new HANA Update SPS05 (not earlier versions), there is an easy to use configuration wizard coming with the new SAP HANA Cockpit.

With the latest HANA Cockpit in SPS05, you can now use the wizard to apply basic HANA audit policies configuration with the recommended HANA Audit policies pre-configured.

I talk about this here. That said, the above policies should give you a similar jumpstart to design the audit policies that meet your needs.

 

[former_member618547]

Very nice post, thank you. Is there a way to take a backup of audit logs automatically on a regular basis?

 

[Toni1]

That depends on the used audit trail, Natarajan - refer to SAP HANA Security Guide for SAP HANA Platform > Audit Trails.

https://help.sap.com/viewer/b3ee5778bc2e4a089d3299b82ec762a7/2.0.05/en-US/db560e7bbb57101490d4a13644...

 

 

[vanvleetp2]

What is your recommendation for where the Audit Trail Target should be - the filesystem or the database?  I'm assuming yours is set for the database since your earlier response recommended pulling back entries from the ACTIVITY_LOG view w/SQL, but if the filesystem (ie. syslog) is being used, then SQL and HANA Permissions don't even apply and you would need a different way of viewing the syslog entries, correct?

[JoeGoerlich]

Hello vanvleetp2,

each audit trail comes with specific limitations:

For the Database Table these are

• Storing audit logs in a table of an in-memory database is rather expensive compared to the other methods while providing the highest level of confidentiality (db separation), integrity as well as availability.

The audit log data suffers from SAP HANAs' compression capabilities. Furthermore, for audit logs stored in the database a retention period can be defined per audit log policy. If this is utilized there is no need to take care about that by other means.

For the Syslog

• If the syslog daemon cannot write the audit trail to its destination, no log will be written and you will not be informed if there are no additional measures to monitor the syslog.

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 monitored whether it is accessible and has sufficient space available.

• At operating system level, all users with access to the syslog can access these audit logs. This may benefit from High Isolation mode.

For the CSV Text File:

• It is not sufficiently secure, since by default the file is written to the same directory as trace files. Thus, database users with the relevant system privileges can access it. The CSV file is listed on the Diagnosis Files tab in the Administration Editor of the SAP HANA Studio.
  At operating system level, any user in the SAPSYS group can access it. This may benefit from High Isolation mode.

The audit table can be exported as CSV, which may be a workaround for some use-cases.

• Audit trails are created per server. A separate CSV text file is created for every service that executes SQL. In a distributed database system it becomes more difficult to trace audit events which were executed across multiple servers (distributed execution).

Br,

Joe

[former_member842842]

Thank you Mostafa a great deal, for this blog!

Are there TCodes to determine that these 5 main area minimum audit policies are actually configured by the SAP HANA Admin? How does one verify this, and are they exportable to excel for audit review?

[Toni1]

With appropriate rights for the SQL editor (ST04, DBACOCKPIT) as well as the corresponding system views this can be realized - refer to AUDIT_POLICIES.