HANA password security
This document is prepared based on version HANA 1.0 SPS 05 revision 46.
While creating the new user in HANA studio, we have three types of Authentication.
- Password
- Kerberos (Third-party authentication provider)
- SAML (Security Assertion Markup Language)
Exhibit: 1
Every database user is identified with in the database by Authentication based on username and password.
In this document we will be concentrating on password and its policy parameters.
Passwords are subjected to security rules and are configured using the parameters in system properties files indexserver.ini. To have a look, let us open the “Administration Console” perspective -> Configuration tab -> Expand Indexserver.ini -> Expand password policy and you find 11 parameters in it.
Exhibit: 2
We will focus on all the 11 parameters and referring to this Exhibit 2 many times in this document.
First let us create a new user in HANA studio (Security -> Users -> right click -> New user) and click on as shown below:
Exhibit: 3
The information it is showing as “Password must have at least eight characters” in the first line, refers to the default value (8) in the minimal_password_length of parameter 7 in Exhibit 2. “meet the rule: ‘”upper-case”, “lower-case”, “digits”‘ in the second line, refers to the default value ‘A1a’ in the password_layout of parameter 10 in Exhibit 2.
One interesting point to know is the default value ‘A1a’ derives A(upper-case), 1(digit), a(lower-case) which means the password must contain at-least 1 upper-case, at-least 1 digit and at-least 1 lower-case.
This default value is equivalent to ‘B2b’ or ‘C3c’ or ‘Z1c’ etc. as all of these contains upper-case, digit, lower-case. In all of these, special characters (_ , -, etc.) is optional.
In order to include special character as must in password, just include any special character in the default value, example E8s*
Now let us change these two parameters: length as 6 and also include one special character as must in password.
Exhibit: 4
Now let us go back to user creation and click on and we observe that these changes are reflected.
Exhibit: 5
Now let us create the user, say USER1 and when this user log on for the first time, it will ask you to change the password which refers to default value ‘true’ in force_first_parameter_change of parameter 1 in Exhibit 2.
Exhibit: 6
If this parameter changed to false then when the user log on for the first time, it will not prompt to change password.
Note: Administrators can force a user to change password at any time with SQL command ALTER USER user_name FORCE PASSWORD CHANGE;
What happens when the user forgot his/her password and how many attempts can one make. It depends on the default value (6) in the maximum_invalid_connect_attempts of parameter 3 in Exhibit 2. Let us change this value to a small number, say 3 and give all the invalid passwords for the user USER1.
Now the user is locked until some time and this refers to the default value (1440) in the password_lock_time of parameter 11 in Exhibit 2. The value 1440 is in minutes which is equal to (24 * 60 hrs = 1 day). This value can be changed, say 2 hrs (120 min).
Exhibit: 8
The locked passwords can be reset by administrators with SQL command and/or can assign new password.
ALTER USER user_name RESET CONNECT ATTEMPTS;
Now the user can have the new password but cannot use the previous passwords and this refers to the default value (5) in the last_used_password of parameter 2 in Exhibit 2.
The new password can be subjected to rule of using for minimum and maximum number of days and this refers to the default value (1) in the minimum_password-lifetime of parameter 8, default value (182) in the maximum_password-lifetime of parameter 4 in Exhibit 2 respectively.
These values can be changed, say 3 for last_used_password, 2 for minimum_password-lifetime, and 30 for maximum_password-lifetime respectively.
Exhibit: 9
It is possible to remind the user before the password expiration, and can be done through password_expire_warning_time of parameter 9 in Exhibit 2. The default value is 14 days and can be changed.
When the user has assigned password for the first time, he/she should log in with in a specific number of days and this refers to the default value 28 of maximum_unused_initial_password_lifetime of parameter 5 in Exhibit 2.
If the user was logged in within the maximum_unused_initial_password_lifetime and not used for a while then there is a restriction that one has to re-log-in and this refers to default value of 365 days of parameter maximum_unused_productive_password_lifetime in Exhibit 2.
Now let us change these values:
password_expire_warning_time to 7 days
maximum_unused_initial_password_lifetime to 15 days
maximum_unused_productive_password_lifetime to 30 days respectively.
Exhibit: 10
However password lifetime check can be disabled by using SQL
ALTER USER user_name DISABLE PASSWORD LIFETIME;
Conclusion: The default values provided by SAP are the best and this document only shows how to change these values.
To reset all the change parameter values, just double click on the parameter and click “Restore Default” and save it.
Exhibit: 11
We can also query the parameter values of password policy using below SQL’s and produce the same result:
SELECT KEY, VALUE FROM M_INIFILE_CONTENTS
WHERE FILE_NAME = ‘indexserver.ini’
AND SECTION = ‘password policy’ ;
OR
SELECT * FROM M_PASSWORD_POLICY;
That’s it, we are now aware of different password policy parameters, to view current values, and to change, if necessary
Thank You.
Intersting !!!....pretty good article.....thnx Raj
Good article on security policies.
pretty clear, thanks for the information.
Very good information. Thanks for sharing Raj.
Raj,
Very informative - thanks!
Thank You Jagan, Azeem, Azmath, Ramesh, Rama.
Good one security settings
Thanks Shiva
Good Work!!
GooD Work
Nice post Raj.. Was looking for some specifics on HANA security and this post helped me with what I was looking for... Thanks for Sharing..
Durga
Thanks Durga
Thanks for it Raj Kumar... 🙂
Hi Raj,
Thanks for sharing...it's really helpful.
Was searching for some documentation on following types of authentication:
If you can get some documentation, plz share it with me 🙂
Regards,
Antony Jerald.
Nice post Mr Raj...
Hi Raj
Thanks for technical stuff on your blog, could you let me know is there anyway I can exclude a particular user from all these password policies, as I need to reset the password for the schema <SAPSID> it is not allowing me to use the previous passwords...
Thanks
Kishore Ch
Hi All,
One quick system, if i recall correctly, "SYSTEM" user password will not get expired even maximum_password_lifetime is set, am i right?
Because i never see any warning message or issue with SYSTEM user password expired.
Thanks,
Nicholas Chang
You can disable the password lifetime for SYSTEM. To protect the user from being blocked. Or you can set the validity to forever.
cheers Otto
very useful..
Is there any way by which we can force at least 2 digits in the password ?