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:
- 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
- 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:
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.
|BLACKLIST_TERM||Include the word you want to restrict the user to use (sap or hana)|
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.
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
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.
That’s it we are done with implementing password policy for a given simple scenario.