The database password expiration date has been reached, but the account status is open
I searched the 11gR2 oracle database to check for users expiring up to 7 days and I noticed that few of the expiration dates of users are today, but their account status is open, not Expired (Grace) and some users. past the expiration date but still open.
USERNAME ACCOUNT_STATUS EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- ------------------------------
SYSTEM OPEN 27-JAN-16 DEFAULT
SYS OPEN 28-DEC-14 DEFAULT
ERERD OPEN 18-JAN-16 DEFAULT
ERFWSE OPEN 04-DEC-14 DEFAULT
SERFW OPEN 03-AUG-15 DEFAULT
DERSZFERSS OPEN 04-DEC-14 DEFAULT
My query does not help in this case to find users whose account will be expired. Can anyone help me with modifying the query so that it will only list users and their accounts do expire by the number of days?
select username, account_status, trunc(expiry_date-sysdate) days_to_expire
from dba_users
where expiry_date is not null and trunc(expiry_date-sysdate) >= 0;
Update
SQL> select * from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_LIFE_TIME';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
source to share
Your request is fine.
Password expiration date and account status are two different things.
You may have a user with an expired password that will remain open while you try to connect to it.
If you have permission to change the password, you can set a new password, otherwise the user will be locked out.
This means that if you connect to an application and the application does not check the password expiration date, the user will be blocked and if there is no connection, the user will remain open.
source to share