Which privilege is able to unlock or change a sap hana user password?
Dealing with common user locking issues can be done by someone with the USER AMIN system privilege. As a matter of fact it is better to build an administrator user profile up front first so that it can be assigned as easily to a person or to group.
You could also extend other perimeter of actions to the profile. For instance, CATALOG READ system privilege will allow inquiring the system views whenever there are some questions regarding a user being locked.
How to use SQL script to create a sap hana administrator user?
Using hana studio to create a very specific sap hana user is not advisable. It is easy to do but not easy to maintain in a production environment.
It is good practice to work with SQL Script. It will give you more relevant details than hana studio interface.
The following details can be added to a script as a comment: Why a user has been created, Why a user is part of a specific profile, What is the user responsibilities, Which project is the user involved in.
The following is a simple SQL script to create a user and a profile to handle user password issues in sap hana :
-- To create a permanent administrator user
CREATE USER U_ADMIN_USER PASSWORD ToBeKeptSecret01 NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER U_ADMIN_USER DISABLE PASSWORD LIFETIME;
-- To create a temporary administrator user limited in date and time. To face unpredicted request and to keep the main administrator user password safe.
CREATE USER U_ADMIN_USER_TMP PASSWORD AdminUser01 NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL '2019-11-10';
CREATE ROLE GRP_ADMIN_USER;
GRANT USER ADMIN TO GRP_ADMIN_USER; -- to deal with locking, password issues
GRANT INFILE TO GRP_ADMIN_USER; -- to reset password with the old password.
GRANT CATALOG READ TO GRP_ADMIN_USER; -- to inquire system views
GRANT GRP_ADMIN_USER TO U_ADMIN_USER;
GRANT GRP_ADMIN_USER TO U_ADMIN_USER_TMP;
Why getting a temporary sap hana administrator user?
A temporary administrator user should be created for the following cases: The main sap hana administrator user is off for few days and you need to make sure somebody else can take over while he is away.
A development project team leader would like to have a dedicated user to be able to manage developers while the project goes on. One urgency comes up to sort out a user issue and you have no time to deal with it.
Now, there are different ways to look at why providing a short term user management solution. On one hand, a temporary administrator user is an easy way to deal with events such as crises and projects. There is no hassle in the procedure and the security is kept. It is just a matter of activating a user profile for a predefined time. On the other hand you can give the user administration profile to any user, but you have to follow up and not to forget to remove that privilege when it is not necessary anymore.
A list of different sap hana technical users
How to create sap hana MODELER users?
Technical user which will deal with data architecture.
How to create sap hana ABAP DEVELOPER users?
Technical user which will perform abap development on the sap hana system databases.
How to create sap hana ADMINISTRATOR users?
Technical user to replace SYSTEM which will perform all current sap hana administration tasks.
How to create sap hana COCKPIT users?
Technical user which will be able to connect to the database system via the hana cockpit web interface.