See Password expiration date

[yannmiquel]

Hi,

Is there a way to read directly when a password will expire ? Table USERS only shows when a password is modified...

I'm wondering if deactivating a user extends password validity. For instance, if one deactivates DBADMIN, should we still change the password every 3 months.

Thanks

Yann

[Dan_vL]

The following SQL may be of help. It attempts to show the date when each user's password will expire.

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,
			LAST_PASSWORD_CHANGE_TIME,
			CASE
				WHEN LAST_PASSWORD_CHANGE_TIME IS NULL THEN 'DISABLED. No expiration'
				WHEN LAST_PASSWORD_CHANGE_TIME > '' THEN 'Password live time is Activated. - Starting date : [ ' || to_varchar( LAST_PASSWORD_CHANGE_TIME, 'YYYY-MM-DD' ) || ' ] - Valid for :' || LV_DAYS || ' days - Expiring date : [ ' || to_varchar( add_days( LAST_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 LAST_PASSWORD_CHANGE_TIME ASC;
END;

Regards,Dan van Leeuwen