How to check for sap hana user password expiration date?
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 syntax executed in hana studio SQL console will list all users password expiration status :
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,
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"
where user_name not like '_SYS%' and
user_name not like 'XSS%'
order by PASSWORD_CHANGE_TIME asc;