Register Login

SAP HANA Security Interview Question and Answer

Updated May 18, 2018

FAQ: SAP HANA Security

Listed below are the SAP HANA alerts that indicate problems in the security area:

Alert

Name

Description

57

Secure store file system (SSFS) consistency

Regarding the database if there is consistency with the secure storage file system (SSFS) is determined by this.

62

Expiration of database user passwords

The database users are identified whose password in line with the configured password policy is due to expire. The user will get locked if the password expires. The application availability can be impacted if the technical user is in question. In case of technical users it is recommended that the password lifetime check is disabled to prevent the password from getting expired (ALTER USER DISABLE PASSWORD LIFETIME).

63

Granting of SAP_INTERNAL_HANA_SUPPORT role

To any database users if the internal support role (SAP_INTERNAL_HANA_SUPPORT) is granted currently is determined.

64

Total memory usage of table-based audit log

The database table used for table-based audit logging consumes the percentage of the effective allocation limit is determined.

SQL: "HANA_Configuration_MiniChecks" returns a potentially critical issue (C = 'X') for one of the following individual checks:

Check ID

Details

1310

Secure store (SSFS) status

1330

Number of users with expiration date

1335

Number of SAP users with password expiration

1340

CATALOG READ privilege granted to current user

1360

Size of audit log table (GB)

SQL: "HANA_Security_MiniChecks" returns potentially critical issues (C = 'X').

To analyze security topics which tools are there?

In SAP 1969700 the following analysis commands are available:

SQL statement

Details

SQL: "HANA_Security_CopyPrivilegesAndRoles_CommandGenerator"

From one grantee to another to copy privileges and roles GRANT commands are generated

SQL: "HANA_Security_GrantedRolesAndPrivileges"

Roles and privileges granted to roles and users are displayed(via roles either directly or indirectly)

SQL: "HANA_Security_MiniChecks"

In the SAP HANA Security Check List a subset of provided checks and from the expectation the deviations are marked as potentially critical is executed by this command(C = 'X').

SQL: "HANA_Security_Roles"

Overview of defined SAP HANA roles

SQL: "HANA_Security_Users"

Overview of SAP HANA users and schemas

Information about security related topics is provided by the following monitoring views and dictionary tables:

  • EFFECTIVE_APPLICATION_PRIVILEGES
  • EFFECTIVE_PRIVILEGES
  • EFFECTIVE_PRIVILEGE_GRANTEES (SAP HANA >= SPS 12)
  • EFFECTIVE_ROLES
  • EFFECTIVE_STRUCTURED_PRIVILEGES
  • GRANTED_PRIVILEGES
  • GRANTED_ROLES
  • PRIVILEGES
  • ROLES
  • STRUCTURED_PRIVILEGES
  • USERS

Explain the CATALOG READ privilege effect

In SAP HANA dictionary tables (e.g. TABLE_COLUMNS or INDEXES) to what extent a user can access data is controlled by CATALOG READ. All information is visible if CATALOG READ is granted. The information for own objects only is shown if CATALOG READ is not granted. Due to the required security checks at the same time the performance can be worse for these dictionary queries.

A missing CATALOG READ right does not result in an error unlike on other databases; the result set of dictionary queries is just restricted.

Which SAP HANA security topics are addressed by SAP component?

HAN-DB-SEC is the central SAP HANA Security component. In case of security related issues you can check on this component for SAP Docs or open SAP incidents. For specific SAP HANA components security there can be security-relevant SAP Docs and one should remain aware for this also on other components SAP Docs can be created.

Where can a reference for SQL statements related to SAP HANA security be found?

In the SAP HANA SQL reference at "SQL statements" -> "Access control statements" Security related SQL statements can be found.

The SAP HANA database user of transaction DBACOCKPIT requires which configuration?

A role called DBA_COCKPIT is suggested to be defined among others for DBACOCKPIT operations with the appropriate privileges.

Authorization issues are indicated by which errors?

For authorization issues among others, the errors that are listed below are symptoms:

transaction rolled back by an internal error: insufficient privilege: Not authorized
search table error: [2950] user is not authorized
Error during Plan execution of model _SYS_BIC:onep.Queries.qnoverview/CV_QMT_OVERVIEW (-1), reason: user is not authorized
pop1 (rc 2950, user is not authorized)
insufficient privilege: search table error: [2950] user is not authorized
Could not execute 'SELECT * FROM"_SYS_BIC".""' SAP DBTech JDBC: [258]: insufficient privilege: Not authorized.SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

 

For enabling data volume encryption what is the performance impact?

During read when data is decrypted from disk and when writing to disk it is encrypted an overhead is incurred by the data volume encryption. There is no performance penalty associated with access to in-memory data as data in memory is always decrypted.

Scenarios that have a performance impact as they involve access to data volumes are:

Area

SAP Doc

Column loads

2127458

Savepoints and database snapshots

2100009

Data backups

1642148

Merges

2057046

Hybrid LOBs

1994962

By I/O these scenarios are dominated and the minor is the encryption related CPU overhead. Than a medium single-digit percentage the overall performance impact is not higher usually.

In what way the tracing for security topics like authorization, authentication and login can be activated?

With the following parameter in general an authorization trace can be activated on a temporary basis:

.ini ->  [trace] -> authorization = info

To the normal service trace files further authorization information will be written as a consequence. It may be sufficient to set the parameter temporarily in order to trace connection issues.

.ini -> [password policy] -> detailed_error_on_connect = true

Instead of enabling data volume encryption at a later time why is it recommended to enable it directly after installation?

In the persistence at a given time there are typically multiple copies of a single data page stored due to use of shadow paging in the data volume persistence of SAP HANA. An update of all pages will write encrypted versions of these pages that are in use are triggered if data volume encryption is enabled. Automatically pages that are not in use will not be encrypted but only over time when by an update with an encrypted page they get overwritten. A preference to overwrite plaintext pages are not tracked also all remaining plaintext pages have been overwritten at which time is not tracked. During the lifetime of the database it will never happen possibly. Recover into a fresh installation that has data volume encryption enabled is recommended for extra security than for existing systems to turn on data volume encryption simply.

If a system is reinstalled on file system / storage level there are still unencrypted pages remaining, one should be aware of this as even a fresh installation will not guarantee that.

For data volume encryption which crypto library is recommended?

SAP CommonCryptoLib (CCL) is the preferred option. Ensure to use CCL version 8.4.32 or higher for data volume encryption support, as for HANA data volume encryption performance optimizations are vital previous versions are missing.

When using data volume encryption is there anything special thing that one should be aware of?

At the file system level when cloning systems with more and more encryption functionality in SAP HANA it is vital to handle the Secure Store FS (SSFS) file properly. During regular database recovery the SSFS is properly updated, during a file system-based system copy it is the administrator's responsibility that along with data / log volumes the SSFS file is copied. To detect a mismatch between the SSFS file and data persistence tighter checks were introduced with SAP HANA SPS 09.

In what way the authentication types used by connections to SAP HANA can be deteremined?

Via column AUTHENTICATION_METHOD of monitoring view M_CONNECTIONS one can determine the authentication method used by a connection.
In what way including roles and privileges can a user be copied?

With the related catalog roles it is not possible to copy a user easily. In the section "Copy a User Based on SAP HANA Repository Roles" the procedure to copy a user including repository roles has been described.

In SAP HANA environments related to GRANT and REVOKE of privileges and roles is there something specific to consider?

To a user (grantee) by different users (grantors) in SAP HANA, privileges and roles can be granted. In the database catalog each grant is persisted, if successful, and by grantor, grantee, and the role or the privilege it is uniquely identified. During the revoke of the role or privilege this leads to following behavior:

  • This user can still have the same role or privilege when a role or privilege is revoked from a user and by other users if granted.
  • Even if the executing user (revoker) did not grant any role or privilege to the user a REVOKE statement from whom the statement tries to revoke the role or privilege, successfully executes. 

What purpose does the RESOURCE ADMIN privilege serve?

For creating a runtime dump or for administration tasks like resetting SAP HANA monitoring views (ALTER SYSTEM RESET MONITORING VIEW) RESOURCE ADMIN is required. As of 2016 it will be included although this privilege was not granted to the DBA_COCKPIT role originally. This privilege to the DBA_COCKPIT role can be granted manually if required:

GRANT RESOURCE ADMIN TO DBA_COCKPIT

In what way one can activate and deactivate SSL?

To activate and deactivate SSL the following SAP HANA parameters can be used:

Parameter

Default

SAP Doc

Details

global.ini -> [communication] -> enable_ssl

off

2256091

If set to 'on', the SAP HANA communication uses SSL.

global.ini -> [system_replication_communication] -> enable_ssl

off

2256091

If set to 'on', the SAP HANA system replication communication uses SSL.

Encryption can be deactivated on SAP ABAP side with the following parameter setting:

dbs/hdb/connect_property = ENCRYPT=FALSE 

In order to use SSL actually further actions are required (e.g. installation of crypto library and configuration of certificates) and one should remain aware regarding this.

Sometimes trouble can be caused by SSL, e.g.:

  • with SAP HANA Rev. 102.04 Indexserver crash
  • when a runtime dump is triggered due to network package loss inconsistent session occurs

The SSL should be disabled temporarily in these situations as it can be useful.


×