Managing password expiration in EPAS

This document explains the process to verify password expiration date or number of days left of a role in EPAS and its limitations.

Create a test profile and assign it to a role

edb=# create profile password_test limit PASSWORD_LIFE_TIME 90;
CREATE PROFILE 


edb=# select * from dba_profiles where profile='PASSWORD_TEST';
profile | resource_name | resource_type | limit | common 
PASSWORD_TEST | FAILED_LOGIN_ATTEMPTS | PASSWORD | DEFAULT | YES
PASSWORD_TEST | PASSWORD_ALLOW_HASHED | PASSWORD | DEFAULT | YES
PASSWORD_TEST | PASSWORD_GRACE_TIME | PASSWORD | DEFAULT | YES
PASSWORD_TEST | PASSWORD_LIFE_TIME | PASSWORD | 90 | YES
PASSWORD_TEST | PASSWORD_LOCK_TIME | PASSWORD | DEFAULT | YES
PASSWORD_TEST | PASSWORD_REUSE_MAX | PASSWORD | DEFAULT | YES
PASSWORD_TEST | PASSWORD_REUSE_TIME | PASSWORD | DEFAULT | YES
PASSWORD_TEST | PASSWORD_VERIFY_FUNCTION | PASSWORD | DEFAULT | YES
(8 rows)


edb=# create role testuser with password 'testpassword';
CREATE ROLE


edb=# alter role testuser profile password_test;
ALTER ROLE

To verify expiration date

edb=# select username, account_status, expiry_date, profile from dba_users where username='TESTUSER';
username | account_status | expiry_date | profile 
TESTUSER | OPEN | 05-DEC-23 16:09:44.221466 | PASSWORD_TEST
(1 row)

To verify number of days left

edb=# SELECT date_trunc('day', cast((SELECT dbau.expiry_date - current_timestamp
FROM dba_users dbau
WHERE username = upper('testuser')) AS INTERVAL)) AS time_left;
time_left 
89 days
(1 row)

Or below query (note that we changed to minutes here to demonstrate how to display the different possible precisions of the date-trun() function:

edb=# SELECT date_trunc('second',
cast((SELECT (SELECT pai.rolpasswordsetat
FROM pg_authid pai
WHERE pai.rolname = 'testuser')
+
(SELECT make_interval(secs => epr.prfpasswordlifetime)
FROM edb_profile epr
WHERE epr.prfname = 'password_test') 
-
current_timestamp) AS INTERVAL)
) AS time_left;
time_left 
89 days 22:33:58
(1 row)

The query shows how long until or past (negative) return value the password can expire, please note the password isn't actually marked as expired until the user log in after the expiration point and then the grace period starts, if set, before blocking the user from doing anything until they change their password.

Search

Create a test profile and assign it to a role

To verify expiration date

To verify number of days left

Was this article helpful?