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.
                                  Exhibit: 7               
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;
     


This document is prepared based on version SAP HANA 1.0 SPS 05 revision 46.

 

In part 1 of HANA Password security, we seen how to view the different password policy parameters and how to change them. Also for the parameter 9 of the password policy (password_layout) we can enforce the user to include one upper-case letter, one lower-case letter, one numeric and optionally one special character in his/her password. If you have not read this document before, I suggest you to have a look, as the part 2 document is related to my previous one. Here is the link: http://scn.sap.com/docs/DOC-41573 

 

Even though the user includes all these rules in his/her password, we can add some additional rules to password.

 

Scenario: The organization came up with certain rules to implement in password policy as mentioned below:

 

Rule 1:

• A password should not contain a specific word which are easy to guess for other users, like sap or hana.
• Also the above words should not be part of the password, like sapWorld1 or Spshana5
• Also should not be case sensitive of that restricted word, like SapWorld1 or SpsHana5

 

Rule 2:

• The password should not contain user name.

If any of these points of rule 1 and 2 gets satisfied, then the system should not accept the user password.
In this document, we will be implementing the above scenario.

 

If you have read my previous document then you might got some idea whether it is possible to implement at-least one of the above rules? The answer is no. With the help of password policy parameters ( of total 11), it is not possible to implement the above rule(s).

 

In HANA studio, SAP has provided some standard schema's and one of them which is relevant to our scenario is _SYS_SECURITY schema which contains only 1 table "_SYS_PASSWORD_BLACKLIST" and has three columns:

1. BLACKLIST_TERM
2. CHECK_PARTIAL_PASSWORD
3. CHECK_CASE_SENSITIVE

and when you do the data preview for this table, it will be empty by default for new instance as shown below, provided if you have the SELECT privilege on this table.
             
Now with the help of this table, it is simple to implement our rule(s) of password policy by just inserting our policy terms (or simply record).

 

In order to Insert into or Delete from this table, the user should have INSERT and DELETE privileges on either the table _SYS_PASSWORD_BLACKLIST or the entire schema _SYS_SECURITY.

Column	Description
BLACKLIST_TERM	Include the word you want to restrict the user to use (sap or hana)
CHECK_PARTIAL_PASSWORD	Some part of the restricted word not allowed (sap, hana) and value can either be True or False. True means for partial words or terms. False means for whole words or terms.
CHECK_CASE_SENSITIVE	Value can be either True or False. The whole words or terms can be either case or non-case sensitive. Use False for non-case sensitive for restricted word.

 

Now we need two insert statements into this table. One to meet the requirement for the word sap and the other for the word hana.

 

1. INSERT INTO _SYS_SECURITY._SYS_PASSWORD_BLACKLIST VALUES ('sap', 'TRUE', 'FALSE');  
2. INSERT INTO _SYS_SECURITY._SYS_PASSWORD_BLACKLIST VALUES ('hana', 'TRUE', 'FALSE');  

INSERT INTO _SYS_SECURITY._SYS_PASSWORD_BLACKLIST VALUES ('sap', 'TRUE', 'FALSE');
INSERT INTO _SYS_SECURITY._SYS_PASSWORD_BLACKLIST VALUES ('hana', 'TRUE', 'FALSE');

 

In SQLEditor execute the above statements and do the Data Preview.
         
Now our new Rule 1 of password blacklist is in effective regardless of how the password layout and minimal password length are defined in corresponding parameters of password policy (part 1 document) . Lets test this rule by creating a new user, say USER1 and give password in such a way that it matches with our new policy password terms i.e. say enter password as sapTest1 (which contains the restricted word sap). When we try to save the new user, it should throw an error, something like this:
                   
So far Rule 1 implemented successfully
Now coming to Rule 2 which says password should not contain the user name.

 

Before creating the user first we need to insert a record with user name in BLACKLIST table as

 

1. insert into "_SYS_SECURITY"."_SYS_PASSWORD_BLACKLIST" values('user1','TRUE','FALSE');  

insert into "_SYS_SECURITY"."_SYS_PASSWORD_BLACKLIST" values('user1','TRUE','FALSE');

              
Now the Rule 2 is also in effective and lets test this by creating the user USER1 with password as User1234.
   
If you enter the password as User4321, it will accept as password does not contain the user name USER1.

 

Disadvantage: If you create another user, say USER2 then all the above rules we created will apply and USER2 will also not able to use password such as User1234, though the password User1234 does not contain the user User2

 

This can be made better if we can check the password blacklist against the particular user only, while creating the new user or modifying the password of existing user for Rule 2. If you have the solution for this then I would be glad to know.