Well hello i just wanted to ask you a question. Before 1.5 years i installed 23ai version and i downloaded sql developer. Before 1 month i opened my sql developer and i saw that my hr user is locked so i had to connect to sys user and then i pressed the following code ->
ALTER SESSION SET CONTAINER = FREEPDB1;
ALTER USER hr ACCOUNT UNLOCK; -- or ALTER USER hr IDENTIFIED BY ** (password: hr) ACCOUNT UNLOCK;
My question is from the time that i unlocked the user hr so i can connect again when the account will lock again? is there any query so i can see the remaining time that when is finished it will lock again the hr user? thanks.
Once you've unlocked the user you'll be able to login as them again.
Whether they'll be locked again depends on their profile's password settings.
The things to look at are:
- FAILED_LOGIN_ATTEMPTS => how many consecutive times they can get the password incorrect before the account is locked
- INACTIVE_ACCOUNT_TIME => how many consecutive days since last login until the account is locked
- PASSWORD_LIFE_TIME => the number of days until the password expires (this puts the account in the expired rather than locked status)
- PASSWORD_GRACE_TIME => grace period in days after the password has expired where you can still use the old password, but will receive an error on login.
This query can help you identify the settings for the HR user in your database:
select expiry_date,
resource_name,
limit,
last_login + case
when resource_name = 'INACTIVE_ACCOUNT_TIME'
then limit
end inactive_lock
from dba_users u
join dba_profiles d
using ( profile )
where username = 'HR'
and resource_type like 'PASSWORD';