×

Sap Support

- Sap Support - Dump files for support

Log & Trace file

- Log and trace directories - Investigate backup log content
<

Tools : Hana Studio

- Installation - Using hana studio - Using Studio SQL Console

Tools : hdbrsutil

- hdbrsutil tips & tricks

Tools : Hana Cockpit

- Install hana cockpit 2 complete example - Un-Install hana cockpit 2
<

Disk

- Disk space used by log segments - Reclaim space from log segments - Retrieve table index space used on disk

Specific Operations

- Adding physical memory

Sap Hana User Privileges

- Role and privileges - Grant read access to a schema

Stored procedures

- Creating a procedure - Dropping a procedure - Compiling a procedure - Executing a procedure - Working with procedures - Using anonymous block

Sap Hana functions

- DateTime : ADD_YEARS - DateTime : EXTRACT

Parameters

- Configuring sap hana - Kill long queries automatically

SQL

- Sql guide line - How to use SQL -Sql references - Sql tips & tricks for tables

SQL Query

- SQL & Tables - SQL & syntaxes - How to practice SQL
<

Sap Hana Tables

- Managing tables - Various ways to create Tables - Sap Hana Tables - Sap Hana Temporary Tables

Table description & DDL

- Retrieve table description - Table description using table-columns-view - Table ddl with hana studio - Table ddl using 'get_object_definition' procedure - Table ddl using hdbsql

SELECT data

- Various ways to select data

INSERT data

- Various ways to insert data

Sap Hana Users

- Managing Users - Create user with hana studio - Create user with sql Script - Create multiple users in one go - Copy a user - Create User with SQL script - Create User via Hana Studio - Update User - Drop User

Create Standard User

- Create standard & personal User

Create Technical User

- Create a technical User - Create backup users - Create administrator users - Create Modeler User - Create Data provisioning User - Create Cockpit User - Create a user to unlock any users
<

Sap hana password policy configuration options

Sap Hana password policy can be managed via configuration. A Password policy is installed and configured by default when a new sap hana database is created. The default configuration is already good enough to guarantee a satisfactory password protection. Sap Hana password policy parameters can be modified to different level of protection. However for certain users other alternative protection will be required. Technical user passwords are the most vulnerable as certain restrictions are disabled in order to keep business running.

Sap Hana Default password policy options

PROPERTY VALUE ------------------------------------------- ------ force_first_password_change true last_used_passwords 5 minimum_password_lifetime 1 maximum_password_lifetime 182 maximum_unused_initial_password_lifetime 7 maximum_unused_inital_password_lifetime 7 maximum_unused_productive_password_lifetime 365 password_expire_warning_time 14 minimal_password_length 8 password_layout A1a detailed_error_on_connect false maximum_invalid_connect_attempts 6 password_lock_time 1440 password_lock_for_system_user true


What is the sap hana password length?

The sap hana default and minimum password length is 8 characters. It is defined as the password policy parameter "maximum_password_length". To enforce longer password you can increase the minimum value to a greater value.

To update Sap Hana password length policy

-- Operation executed on a tenant database -- Setting minimal password length to 12 characters -- ........................................ ALTER SYSTEM ALTER CONFIGURATION('indexserver.ini', 'SYSTEM') SET ('password policy','minimal_password_length') = '12' WITH RECONFIGURE;

How to force a sap hana user to change password?

By default, a new user will be required to change password the first time he logs in. If it is not the case, the administrator can update the user connection option so that he has to change his password on next login. This password update operation is only available once, at the user level. See also: Let user change password
The password policy is set to: "change password on first database connection". This default can be modified to "False". In that case, by default nobody will be asked to change their password. This is not advisable to deactivate the default value. This would mean someone will have to manage every single existing password. This means hassle with forgotten password for all personal users. A person is most likely to remember a own personal password. Why trying to make it worse by giving each user a weird and different password to remember. As far as Technical user is concerned, it is a different ball game. Password should stay as it has been entered in the first place and should only be updated when decided.

Forcing user to change passord on first login

-- SQL Command : To force a user to change password on first login ( As long as the default parameter "force_first_password_change" is true ) ------------------------------------------------------------------- SQL> CREATE USER USER_SMITH_A PASSWORD ToBeChange01; -- SQL Command : To force a user to change password on next login ------------------------------------------------------------------- SQL> ALTER USER USER_SMITH_B FORCE PASSWORD ToBeChange01; -- To deactivate the default change password policy on tenant database ( NOT ADVISABLE ) ---------------------------------------------------------------------- SQL> ALTER SYSTEM ALTER CONFIGURATION('indexserver.ini', 'SYSTEM') SET ('password policy','force_first_password_change') = 'false' WITH RECONFIGURE;

How to update sap hana password expiration date?

Password expiration date is set to 182 days by default on SYSTEMDB and tenant databases. The parameter value is a number of days. In a normal context there should be no personal standard user on SYSTEMDB. Personal technical user with Admin or Backup profile may be created in SYSTEMDB. Those users should however not be restricted due to their high profile unless they have been created for temporary access reasons. The restriction will be done at the connection expiration date level, not at the password level.

A sap hana user password expiration date is automatically reset to 182 days whenever a new password update has been done for a user, unless the expiration date has been deactivated for that user. [ See : How to disable user password lifetime ]

The default sap hana user password expiration date can be modified to more or less time. This is a global change for all users which will be concerned with that expiration option. To control a valid connection period of an individual user or of a group of users refer to the connection validity period. [ See : How to manage user connection date and time frame ] Password expiration is not applicable to cover predefined project laps time.

To update Sap Hana password expiration date.

-- To update "maximum_password_lifetime" policy parameter to 30 days ( Ex: valid password for 30 days ) -- The same script will garantee password policy integrity for all databases -- At the sap hana SYSTEMDB level ( nameserver.ini ) ---------------------------------------- -- Operation done when logged to the SYSTEMDB -- Alter SYSTEM ALTER CONFIGURATION ('nameserver.ini','SYSTEM') set ('password policy','maximum_password_lifetime')='30' with reconfigure; -- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done when logged to the tenant database -- Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','maximum_password_lifetime')='30' with reconfigure;

How to keep sap hana password secured?

A certain number of rules have to be followed in order to keep a sap hana password secured. Those rules will make it hard to get hold of a valid password. What are the possible sap hana password threats?

Written Password threat: Many employees keep passwords written somewhere. It is not good but it is a normal fact when there are too many user connections to remember and no automatic authentication. So, in that case, updating sap hana password often would be a good idea. Now, It has to be a balance between 2 restrictions: How many previous password that cannot be reused and how often a password is renewed.
Solution: Sap hana Passwords have to be modified regularly and preferably with very different characters.
Parameters to consider: "last_used_passwords" and "maximum_password_lifetime".

Personal user password threat: Personal sap hana passwords are easier to crack than randomized password. Someone password is likely to content a meaningful name with birthdate which is easier to remember. A Cracking program will find out the password very quickly.
Solution: Force special characters, lowercase, uppercase and numeric value in the password content but also increase the minimum password length.
Parameters to consider: "password_layout" and "minimal_password_length".

Technical user threat: Those users are under great threat as some password restrictions are deactivated. Give passwords to admin users only. Make sure userstorekey are in place.
Solution: A strong password layout, and a password update schedule with procedure to cover all possible scripts.
Parameters to consider: "password_layout".

How to stop too many sap hana connection attempts?

Trying too many times to connect will end up locking the user password by default. This can be a pain in certain cases. Technical users may also suffer the consequenses. Any job will fail due to locked technical user. You have to contact your database administrator in order to have your account unlocked. This is an ideal feature against unauthorized access. Default parameter value is 6.
Important Note: UPDATING "maximum_invalid_connect_attempts" parameter value will NOT unlock a user.
See: Unlock any users

-- To update "maximum_invalid_connect_attempts" policy parameter to 3 attempts ( Ex: lock user password after 3 wrong successive attempts ) -- At the sap hana SYSTEMDB level ( nameserver.ini ) ---------------------------------------- -- Operation done when logged to the SYSTEMDB -- Alter SYSTEM ALTER CONFIGURATION ('nameserver.ini','SYSTEM') set ('password policy','maximum_invalid_connect_attempts')='3' with reconfigure; -- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done when logged to the tenant database -- Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','maximum_invalid_connect_attempts')='3' with reconfigure;

Sap hana password policy settings with M_PASSWORD_POLICY

Although sap hana tool are very convenient, sql queries have some advantages when it comes to retrieve sap hana password policy values. Apart from the fact of getting a result without the danger of altering values by mistake, SQL query result values can also be used when part of a script or a program.

select * from M_PASSWORD_POLICY; PROPERTY : VALUE --------------------------------------------:------- force_first_password_change : true last_used_passwords : 5 minimum_password_lifetime : 1 maximum_password_lifetime : 182 maximum_unused_inital_password_lifetime : 7 maximum_unused_productive_password_lifetime : 365 password_expire_warning_time : 14 minimal_password_length : 8 password_layout : A1a detailed_error_on_connect : false maximum_invalid_connect_attempts : 6 password_lock_time : 1440 password_lock_for_system_user : true #-- In a shell script variable #----------------------------- PWP_LUP_SETTING=`hdbsql -i 01 -U BHPBACKUP01K -m -A -F' ' << EOF | grep "VALUE:" | awk -F: '{print $2}' select 'VALUE:'||VALUE as COL1 from M_PASSWORD_POLICY where PROPERTY='last_used_passwords'; EOF` echo $PWP_LUP_SETTING 5 . . . . . .

How to make strong password and protection policy?

To make strong sap hana password policy means spending time to make your own password security options. Also you have to see further away as far as protection is concerned. Default sap hana password policy is already a good starting point which you can set security options to your requirements from. Security issues are likely to be different depending on whether it is a development database environment or a production system.

Basically, you have to think a bit further than what is the sap hana standard password policy is offering. Technical users for instance, require a steady password. You do not want password expiration on any valuable technical users. That will mean trouble, at one stage, for batch and backup. It is possible to stop password expiration. That is great, yes but security is not complete on that basis that someone, sometime, will find out about the password. So a technical user password has to be updated still, to keep high security level. This is not a simple update operation. It has to be planned in order to cover scripts and programs that use technical users. Password should not be hard written in any script but user connection key will. Therefore all relevant keys will have to be regenerated with the new password and connection has to be tested.

Here are policies you might want to look at according to your own company requirements:

1/ First of all, for any new and old personal users, you want them to personalize their passwords. Force users to change password on next connection. It is easier that way than to give each person a different password and to manage all of them.

-- Force user to charge password on first connection: ( Default password policy setting. "force_first_password_change:true" ) SQL> CREATE USER USER_DANIEL_PHIL PASSWORD ToBeChanged2021; -- Force user to charge password on the next connection: ( Enable et reset password lifetime setting : Default 182 days ) SQL> ALTER USER USER_SMITH_PETER PASSWORD ToBeChanged2021 FORCE PASSWORD CHANGE;


2/ Make sure old password cannot be reused for any user whenever a new password is entered. By default it is the last 5 passwords. You can change the restriction value according to your security requirements.

-- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done while connected to the tenant database -- Last 10 password cannot be reused. SQL> Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','last_used_passwords')='10' with reconfigure;

See also: Manage password history


3/ Set password validity up a specific number of days. It is set by default to 182 days for all users, but nothing stops the administrator to set the parameter to a different date. There are 2 different users to look at. This constraint will have to be deactivated for technical users. For those users, password update will be scheduled and planned according to business requirements but should not be deactivated by default after a certain date.

-- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done while connected to the tenant database -- default password to be valid for 90 days for every one. SQL> Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','maximum_password_lifetime')='90' with reconfigure;


See also: Update password lifetime period to another date
See also: Disable password lifetime period


4/ Set minimum password length. The default value is 8 characters. A long password will take longer to crack than a short one. So increase the minimum number of characters if password security is an really an issue. Be aware though, common users may find annoying entering long password each time they have to login.

-- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done while connected to the tenant database -- Minimum password length from 15 characters SQL> Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','minimal_password_length')='15' with reconfigure;


5/ Set Password complexity. A complex password structure is also harder to crack that a standard common word or name. Using standard is good but changing default could makes passwords harder to guess and to any sneaky program to find the value. So if you want to increase password security here is a guide line.
- Increase password minimum length to 10 characters.
- Add Special characters such as underscore to be part of password values.
- Lock personal user accounts after 5 successive logging attempts.
- Have a technical user management procedure with the following criteria: Change technical user password regularly with a user store key update at the same time. Check all connections are valid for batch and backups.

-- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done while connected to the tenant database -- password structure : a=lower case, A: UPPER CASE, 1:numbers, ?:Special Characters SQL> Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','password_layout')='aA1?' with reconfigure; -- for more detail ------------------- -- See also: password_layout

How to update sap hana password policy parameters?

Updating sap hana password policy can be done in various ways. Using sap tool such as hana studio or sap hana cockpit would be the easiest way. It is safe and typing error would be detected. On the other hand it is easy to make mistake and policy becomes different from one sap hana database to another. it is also possible to use SQL command. Using SQL script will guarantee the strict respect of the same password policy for every sap hana tenant database.

. Password policy parameter update from sap hana studio .

. Password policy parameter update with SQL command .

-- In order to update "last_used_passwords" policy ( Ex: the last 10 password which cannot be reused ) -- The same script will garantee password policy integrity for all databases -- At the sap hana SYSTEMDB level ( nameserver.ini ) ---------------------------------------- -- Operation done when logged to the SYSTEMDB -- Alter SYSTEM ALTER CONFIGURATION ('nameserver.ini','SYSTEM') set ('password policy','last_used_passwords')='10' with reconfigure; -- At the sap hana tenant database level ( indexserver.ini ) ---------------------------------------- -- Operation done when logged to the tenant database -- Alter SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') set ('password policy','last_used_passwords')='10' with reconfigure;

Sap hana SQL

Sap hana tools

- - -