A guideline to manage Sap Hana Users How to create, update and give privileges to users?
Sap Hana User Step by step
You access sap hana databases via a user connexion. Sap hana users are created and specific roles and privileges are assigned to them in relation to their work and to their need.
How to manage hana users ?
The Sap hana administrator has the responsibility to manage all hana users. Users can be created in various ways. A Hana user can be created via the adminsitrator himself or via existing Sap development management tools. Whatever way a user is created the hana administrator has to make sure all created users are respecting the company security requirements.
Whether You want to find out the last user connexion date, or whether a user is authorized to connect or not, the best and easiest way is to use SQL script. You can run SQL script either in Hana Studio SQL Console or using hdbsql .
Look for a user name
SET 'S_USERNAME' = 'SYSTEM'; SELECT * FROM "SYS"."USERS" WHERE USER_NAME = (SELECT SESSION_CONTEXT('S_USERNAME') FROM DUMMY); UNSET 'S_USERNAME'
Show User common status
SELECT USER_NAME, VALID_UNTIL, USER_DEACTIVATED, LAST_SUCCESSFUL_CONNECT from USERS where user_name not like '_SYS%';
Show de-activate Users
SELECT * FROM "SYS"."USERS" WHERE USER_DEACTIVATED='TRUE';
How to create hana users?
Creating a sap hana user is very easy and there are many ways to create a hana user. You can proceed using Sap hana Studio, Sap Hana Cockpit or SQL script.
If you want to control user name and privileges, SQL script is a better option that sap hana tool interface for serveral reasons. Many Users and Privileges can be managed easily and controled in an organized way. A new administrator user may not be familiar with hana studio or hana cockpit, where as he would be able to run an SQL script in no time. SQL script format would be standard in the company. Errors are unlikely to happen.
How to create hana users via hana studio?
Create sap hana user using hana studio
Hana studio offers a create user interface process. It is very easy to fill in details in order to create a user. It is as easy when it comes to assign privileges to the new user.
The tool is not to be used to create user on a standard basis unless you are dealing with bench or testing environment. Creating users in any other environment will leave a mess behind. In a long run, it will be difficult to identify which user is to clear or not.
How to create hana users via SQL Script?
Create sap hana user using sql script
Creating a user via SQL script is the most straight forward and safest process. Creating hana users via SQL script or creating hana users using procedures should be a standard within a company. User creation process will respect all required company standard by using predefined templates. User name format and privileges group will be assigned accordingly and there will be no messing about with names and privileges.
The administrator may have to de-activate temporary certain users. The SYSTEM user for instance should be de-activated and replace with an Administrator user.
De-activate/Activate standard user
CAUTION : Make sure there is a user with privilege "USER ADMIN" before de-activating user SYSTEM. ALTER USER < USERNAME > DEACTIVATE USER NOW; ALTER USER < USERNAME > ACTIVATE USER NOW;
De-activate user SYSTEM
NOTE : The user you are actually using, have to have the privilege "USER ADMIN" in order to re-activating the user SYSTEM. ALTER USER SYSTEM ACTIVATE USER NOW.
User with "USER ADMIN" privilege
SELECT grantee, grantee_type, object_type,privilege,is_valid from GRANTED_PRIVILEGES where PRIVILEGE = 'USER ADMIN'
Re-activate system User
Require condition: Log as a User with "USER ADMIN" privilege ALTER USER SYSTEM ACTIVATE USER NOW;
Various sap hana SQL queries
Sap hana SQL queries will certainly help with getting immediate result. Open the SQL console in Hana studio and paste any SQL Script. It is one way to do it to get what you are looking for.
If you are on the server itself using hdbsql command line, type or paste the SQL script you have chosen. using hdbsql command is another way to execute an SQL Command.
Show de-activated User
SELECT * from users where PASSWORD_CHANGE_TIME IS NULL;