All sap hana users are created by default with a password expiration date. It is set at 182 days from the time the user has been created or from the time the password has been modified.
Certain users such as technical users will need to have their password expiration dates disabled in order to allow batch and procedures to work at all time. However standard user name created for dedicated people will require more security and password expiration will be enforced. The following SQL script executed in hana studio SQL console will list user's password expiration dates:
do begin
declare LV_DAYS integer := 0;
declare LV_START_DATE date;
declare LV_END_DATE date;
declare p_end_date Date;
declare v_end_date_plus date;
SELECT value into LV_DAYS FROM M_PASSWORD_POLICY where property = 'maximum_password_lifetime';
Select user_name, PASSWORD_CHANGE_TIME,
case
when PASSWORD_CHANGE_TIME is null then 'DISABLED. No expiration'
when PASSWORD_CHANGE_TIME > '' then 'Password live time is Activated. - Starting date : [ '||to_varchar(PASSWORD_CHANGE_TIME, 'YYYY-MM-DD' )||' ] - Valid for :'||LV_DAYS||' days - Expiring date : [ '||to_varchar(add_days(PASSWORD_CHANGE_TIME, LV_DAYS), 'YYYY-MM-DD')|| ' ]'
else 'Unknow status '
end as "Expiration Date"
from users
where user_name not like '_SYS%' and
user_name not like 'XSS%'
order by PASSWORD_CHANGE_TIME asc;
end;
USER_NAME PASSWORD_CHANGE_TIME Expiration Date
---------------- --------------------- ---------------
SYS ? DISABLED. No expiration
SYSTEM ? DISABLED. No expiration
DUMMY 13 feb. 2019 15:10:43 Password live time is Activated. - Starting date : [ 2019-02-13 ] - Valid for :100 days - Expiring date : [ 2019-05-24 ]
MYUSER 14 feb. 2019 09:21:07 Password live time is Activated. - Starting date : [ 2019-02-14 ] - Valid for :100 days - Expiring date : [ 2019-05-25 ]
USER_SMITH_PETER 19 jun. 2019 14:57:14 Password live time is Activated. - Starting date : [ 2019-06-19 ] - Valid for :100 days - Expiring date : [ 2019-09-27 ]
Using Sap Hana Studio SQL Console
