Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Managing Users

Objective

After completing this lesson, you will be able to describe user management and administration

User Lifecycle Management

A security concept is required in SAP HANA for the following reasons:

To restrict database administration to skilled (and empowered) people
To restrict access to SAP ERP tables
To restrict editing of SAP HANA data models to owners of the model

Security is important in SAP HANA because user administration plays a significant role, as follows:

Several front end tools offer direct access into SAP HANA.
Access to objects and to data model content must be controlled within SAP HANA.
Information consumers need named users in SAP HANA.

An exception to the security concept is when information consumers do not require user management. This occurs in the following situations:

Access to data does not need to be controlled.
All data access occurs through the SAP Business Intelligence (BI) semantic layer, and security is implemented in SAP BusinessObjects Enterprise.

User Types

User administrators create and configure users in SAP HANA. User administrators have the system privilege USER ADMIN, which authorizes them to manage all users.

In SAP HANA exist two different types of users when creating database accounts:

Database Users that Correspond to Real People
Technical Database Users

Database users are created either as standard users or as restricted users.

The database administrator creates a database user for every person who works in the SAP HANA database. Database users that correspond to real people are dropped when the person leaves the organization. This means that the database objects they own are also automatically dropped, and the privileges they granted are automatically revoked.

Compared to standard database users, restricted users are initially limited in the following ways:

They cannot create objects in the database because they are not authorized to create objects in their own database schema.
They cannot view any data in the database because they are not granted (and cannot be granted) the standard PUBLIC role.
They are only able to connect to the database using HTTP/HTTPS.

To enable a restricted user to use an application, grant the required application-specific roles. Initially, restricted users can only connect to the database using HTTP or HTTPS.

Note

To allow restricted users to connect through ODBC or JDBC, enable ODBC or JDBC access explicitly.

Technical Database Users

Technical database users do not correspond to real people and are not dropped if a person leaves the organization. Typically this type of user is used for administrative tasks, which are done regularly, for example, creating objects and granting privileges for a particular application. Some technical users are available as standard, for example, users SYS and _SYS_REPO. Further use cases can be given when application-specific users are needed for specific scenarios.

Note

Further information for user types can be retrieved from SAP HANA User Management in the SAP HANA Security Guide for SAP HANA Platform.

Technically, these user types are the same. The only difference between them is conceptual.

Database users that correspond to real people can be grouped according to different tasks (see lesson User Groups in this unit.

User Administration Tools

User management is configured using the SAP HANA cockpit.

There is no replication of existing authorizations from the source system.

All the user management functions can also be executed from the command line using SQL requests. This is useful when using scripts for automated processing.

SAP Identity Management provides additional support for user provisioning in the SAP HANA database.

SAP Identity Management 7.2 SP 3 contains a connector to the SAP HANA database (IDM connector). With SAP Identity Management, you can perform the following actions in the SAP HANA database:

Create and delete user accounts
Assign roles
Set passwords for users

For more information about the SAP Identity Management and the IDM connector, see SAP Community at https://www.sap.com/community/topic/identity-management.html.

Create User

You can create a standard database user for every person who works directly with the SAP HANA database. When you create a user, you also configure how the user is authenticated. You can do this on the User page of the SAP HANA cockpit.

When you create a user, specify the following properties:

User Name
Enter a unique user name.
Optional: User Group
Enter the user group assigned to the user.
Optional: E-mail address
Enter the user's e-mail address.
Optional: Validity Period, including the appropriate time zone
Enter the validity period of the user. If the user account is not currently within its validity period, the user is inactive and cannot log on. If no validity period is configured, the user is indefinitely valid.
Optional: Creation of Objects in Own Schema
Prevent the user from being able to create objects in his own database schema.
Optional: PUBLIC Role.
Prevent the user from being granted the standard PUBLIC role.
The PUBLIC role contains the privileges for filtered read-only access to the system views. To see data in a particular view, the user also needs the SELECT privilege on the view.
Optional: Disable ODBC or JDBC access.
This indicates whether or not the user can connect to the database via ODBC or JDBC. By default, ODBC and JDBC access is enabled for standard users and is disabled for restricted users. This means that restricted users can only connect via HTTP or HTTPS.
Optional: Comment
Enter a comment.
Optional: Set the authorization mode to LDAP if the user's authorization is based on LDAP group membership
Specify how the user can be authenticated

Additional User Parameters

You can configure additional user properties for client applications. The following custom user properties are available by default:

LOCAL
This is the user's locale.
PRIORITY
This is the priority with which the thread scheduler handles statements executed by the user. The priority can be in the range 0-9, with 9 representing the highest priority. 5 is the default priority.
STATEMENT MEMORY LIMIT
This is the maximum memory (in GB) that can be used by a statement executed by the user.
STATEMENT THREAD LIMIT
This is the maximum number of threads that can be used by a statement executed by the user.
TIME ZONE
This is the user's time zone. The standard database formats for locale and time zone are supported.

Manage Users

You can manage the maximum number of failed logon attempts that are possible. The user is locked as soon as this number is reached.

The parameter is maximum_invalid_connect_attempts.

The maximum number of invalid logon attempts is set to 6 by default. You must enter a value of at least 1.

The administrators can reset the number of invalid logons using the command ALTER USER <user_name> RESET CONNECT ATTEMPTS.

With the first successful logon after an invalid logon attempt, an entry is made into the INVALID_CONNECT_ATTEMPTS view showing:

The number of invalid logon attempts since the last successful logon
The time of the last successful logon

Administrators can delete the invalid logon attempt information using the command :

Code Snippet

1
ALTER USER <user_name> DROP CONNECT ATTEMPTS

Managing User Passwords

The force_first_password_change parameter defines whether users have to change their initial passwords immediately the first time they log on.

If this parameter is set to true, users can still log on with the initial password. But every action they try to perform returns an error message that states that they must change their password. Then only the following command can be executed:

Code Snippet

1
ALTER USER <current_user> PASSWORD <password>

If this parameter is set to false, users are not forced to change their initial password immediately when they first log on. However, if a user does not change the password before the number of days specified in the parameter maximum_unused_initial_password_lifetime, then the password still expires and must be reset by a user administrator.

A user administrator (that is, a user with the system privilege USER ADMIN) can force a user to change their password at any time with the following command:

Code Snippet

1
ALTER USER <user_name> FORCE PASSWORD CHANGE

SQL Statement for Password Policy

A user administrator can override this password policy setting for individual users in the Manage users app of SAP HANA cockpit or with the following SQL commands:

Code Snippet

1
CREATE USER <user_name> PASSWORD <password> [NO FORCE_FIRST_PASSWORD_CHANGE]

Code Snippet

1
Alter USER <user_name> PASSWORD <password> [NO FORCE_FIRST_PASSWORD_CHANGE]

This option is useful in the following situations:

New technical user account with a generated password, which is not meant to be entered interactively
New users generated and managed by an identity management system

Exclude Specific Users from the Mandatory Periodic Password Change

For connectivity purposes, you might create a technical user. This technical user should never change the password. You can re-enable the mandatory periodic password change with the following SQL commands:

Code Snippet

1
ALTER USER <user_name> DISABLE PASSWORD LIFETIME

Code Snippet

1
ALTER USER <user_name> ENABLE PASSWORD LIFETIME

Deactivate or Reactivate Users

Users can be automatically deactivated for security reasons, for example, if they violate password policy rules by providing multiple times invalid logon credentials, or when the password has expired. However, as a user administrator, you might need to deactivate a user, for example, if an employee temporarily leaves the company or if a security violation is detected.

After you deactivate the user account, the user cannot log on to the SAP HANA database until the administrator resets the user’s password or activates the user again.

As a user administrator, you might need to reactivate a user, for example, after you explicitly deactivated the user or when the user makes too many invalid logon attempts.

You can deactivate or activate a user as follows:

On the SAP HANA cockpit User page
With an SQL command

Manage User SYSTEM

The SYSTEM database user is the initial user that is created during the creation of the SAP HANA database.

SYSTEM is the database superuser. It has irrevocable system privileges, such as the ability to create other database users, access system tables, and so on. In addition, to ensure that the administration tool SAP HANA cockpit can be used immediately after database creation, SYSTEM is automatically granted several roles the first time the cockpit is opened with this user. Note, however, that user SYSTEM does not automatically have access to objects created in the SAP HANA repository.

Create Own Administrators and Deactivate User SYSTEM

In a system with multi-tenant database containers, the SYSTEM user of the system database has additional privileges for managing tenant databases, for example, creating and dropping databases, changing configuration (*.ini) files of databases, and performing database-specific data backups.

Caution

Do not use SYSTEM for day-to-day activities in production systems. Instead, use it to create database users with the minimum privilege required for their duties set (for example, user administration, system administration). Then, deactivate SYSTEM.

If the SYSTEM user's password is lost, you can reset it using the operating system user (<sid>adm user).

Managing the SYSTEM User

You can manage the SYSTEM user as follows:

Deactivate the SYSTEM user
ALTER USER SYSTEM DEACTIVATE USER NOW
Exempt SYSTEM user from locking
Parameter: password_lock_for_system_user
Reset the SYSTEM user's password
- Reset the SYSTEM user password of the system database
  Reset it using the <sid>adm operating system user
- Reset the SYSTEM user password of a tenant database
  The system administrator can reset it from the system database. Stop the tenant database and execute the following command:ALTER DATABASE <database_name> SYSTEM USER PASSWORD <new_password>

Note

Read the following documentation for the above described scenarios:

The parameter password_lock_for_system_user indicates whether or not the user SYSTEM is locked for the specified lock time (password_lock_time) after the maximum number of failed logon attempts (maximum_invalid_connect_attempts).

Deactivation of the SYSTEM User

SYSTEM is the database superuser. It has irrevocable system privileges, such as the ability to create other database users, access system tables, and so on. Do not use SYSTEM for day-to-day activities in production systems. Instead, use it to create database users with the minimum privilege required for their duties set (for example, user administration, system administration). Then, deactivate SYSTEM.

Execute the following statement, for example, in the SQL console of the SAP HANA cockpit:

ALTER USER SYSTEM DEACTIVATE USER NOW

The SYSTEM user is deactivated and can no longer connect to the SAP HANA database.

You can verify that this is the case in the user’s system view. For the SYSTEM user, check the values in the columns USER_DEACTIVATED, DEACTIVATION_TIME, and LAST_SUCCESSFUL_CONNECT.

Note

You can still use the SYSTEM user as an emergency user, even if it has been deactivated. Any user with the system privilege USER ADMIN can reactivate SYSTEM by using the statement ALTER USER SYSTEM ACTIVATE USER NOW. To ensure that an administrator does not do this secretly, create an audit policy to monitor ALTER USER statements.

Reset of the SYSTEM User's Password

The system database and all tenant databases each have their own SYSTEM user. The system administrator can reset the password of any SYSTEM user if it has been irretrievably lost.

Reset the SYSTEM User Password of a Tenant Database

Tenant: System User Password Reset

If the password of the SYSTEM user in a tenant database is unknown, you can reset it from the system database. A database administrator has two of options to reset the password for the SYSTEM user in a tenant database:

SAP HANA Cockpit

Navigate to the Database Management application, and stop the tenant database. From the Tenant Actions menu, choose the option Reset SYSTEM Password.

SQL

Connect to the SYSTEMDB using a Database Explorer - SQL Console, and stop the tenant database using the following SQL command:

Code Snippet

1
ALTER SYSTEM STOP DATABASE <database_name>

After the database is stopped, reset the SYSTEM password using the following SQL command:

Code Snippet

1
ALTER DATABASE <database_name> SYSTEM USER PASSWORD <new_password>

The password for the SYSTEM user is reset and the tenant database is started.

The next time you log on with the SYSTEM user, you will be prompted to change the password in line with the password policy of the tenant database

If the SYSTEM user was previously deactivated, locked, or expired, it is now activated again. We recommend that you deactivate it.

If auditing is enabled, the password change is automatically logged in both the system and tenant database audit trails.

Note

If you can log on as SYSTEM or another user with the system privilege USER ADMIN, do not use the procedure described here to change the password of the SYSTEM user. Instead, log on to the tenant database directly and either execute the command ALTER USER SYSTEM PASSWORD <new_password> or change the password using SAP HANA cockpit.

Reset the SYSTEM User Password of the System Database

If the SYSTEM user password in the SYSTEMDB is lost, you can reset it as the operating system administrator by starting the name server in emergency mode.

To reset the SYSTEM user password the credentials of the operating system user <sid>adm are needed. Log on to the server on which the name server of the system database is running and execute the commands described in the SAP HANA Administration Guide - Reset the SYSTEM User Password in the System Database.

After performing this procedure, the password for the SYSTEM user is reset. The next time you log on with this user, you have to change the password in accordance with the password policy of the system database.

If you previously deactivated the SYSTEM user, it is now also reactivated. This means you must deactivate it again.

Log in to track your progress & complete quizzes