| How To Find the cause of user locked [message #690398] |
Wed, 20 August 2025 14:10  |
wtolentino
Messages: 427
Registered: March 2005
|
Senior Member |
|
|
We've been investigating why a user account keeps getting locked. Our analysis of the unified_audit_trail shows return codes 1017 and 28000. To further troubleshoot, we've also created a logon trigger to capture more info but it does not appear to be even close to the lock_date from the dba_users view.
So far, we've checked the dba_users view, which shows the lock_date as '06-Aug-2025 07:06:03 pm'. However, in the unified_audit_trail, the closest event_timestamp we can find is '06-AUG-25 09:11:43.937600000 AM' with an action_name of 'LOGON' and a return_code of '28000'. Notably, the dates and times don't exactly match, which we think that we missed something."
unified_audit_trail:
database logon trigger:
create or replace trigger logon_trigger
after servererror on database
begin
-- ORA-01017: invalid username/password; logon denied
if (is_servererror(1017)) then
insert into login_attempts
values (sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),
sys_context('USERENV', 'HOST'),
sysdate,
1017, 'ORA-01017: invalid username/password; logon denied',
substr(sys_context('USERENV','PROXY_USER'),1,15),
SYS_CONTEXT('USERENV','OS_USER'));
-- 'ORA-28000: the account is locked
elsif (is_servererror(28000)) then
insert into login_attempts
values (sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),
sys_context('USERENV', 'HOST'),
sysdate,
28000, 'ORA-28000: the account is locked',
substr(sys_context('USERENV','PROXY_USER'),1,15),
SYS_CONTEXT('USERENV','OS_USER'));
end if;
end;
result from the logon trigger "logon_trigger" on table LOGIN_ATTEMPTS:
SQL> select 'xxxxx' username, 'xxxxx' userhost, timestamp, returncode, comments, 'xxxxx' proxy, 'xxxxx' os_username
2 from LOGIN_ATTEMPTS order by timestamp desc;
USERNAME USERHOST TIMESTAMP RETURNCODE COMMENTS PROXY OS_USERNAME
---------- ---------- ------------------------- ---------- -------------------------------- ---------- ------------
xxxxx xxxxx 19-Aug-2025 02:03:49 pm 28000 ORA-28000: the account is locked xxxxx xxxxx
xxxxx xxxxx 19-Aug-2025 11:21:20 am 28000 ORA-28000: the account is locked xxxxx xxxxx
xxxxx xxxxx 06-Aug-2025 09:11:43 am 28000 xxxxx xxxxx
xxxxx xxxxx 30-Jul-2025 11:29:17 am 1017 xxxxx xxxxx
xxxxx xxxxx 29-Jul-2025 04:13:31 pm 28000 xxxxx xxxxx
dba_users:
SQL> select 'xxxxx' username, account_status, lock_date
2 from dba_users where username = 'xxxxx';
USERNAME ACCOUNT_STATUS LOCK_DATE
---------- -------------------------------- -----------------------
xxxxx LOCKED 06-Aug-2025 07:06:03 pm
profile:
SQL> select 'XXXX_PWD_CHECK' profile, resource_name, resource_type, limit
2 from dba_profiles where profile = 'XXXX_PWD_CHECK' and resource_type = 'PASSWORD';
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------- -------------------------------- -------- --------------------
XXXX_PWD_CHECK FAILED_LOGIN_ATTEMPTS PASSWORD 3
XXXX_PWD_CHECK PASSWORD_LIFE_TIME PASSWORD UNLIMITED
XXXX_PWD_CHECK PASSWORD_REUSE_TIME PASSWORD 365
XXXX_PWD_CHECK PASSWORD_REUSE_MAX PASSWORD 24
XXXX_PWD_CHECK PASSWORD_VERIFY_FUNCTION PASSWORD CSDM_VERIFY_PASSWORD
XXXX_PWD_CHECK PASSWORD_LOCK_TIME PASSWORD UNLIMITED
XXXX_PWD_CHECK PASSWORD_GRACE_TIME PASSWORD 0
XXXX_PWD_CHECK INACTIVE_ACCOUNT_TIME PASSWORD 120
XXXX_PWD_CHECK PASSWORD_ROLLOVER_TIME PASSWORD DEFAULT
9 rows selected.
SQL>
|
|
|
|
| Re: How To Find the cause of user locked [message #690401 is a reply to message #690398] |
Thu, 21 August 2025 06:54  |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You will also get an ora-1017 if the username is wrong, there is no reason to assume that it was caused by a bad password. So the 1017s in your audit trail might not be related to your problem.
Also, if the account were explicitly locked, your trigger would not catch that. In the audit trail it would show up as action ALTER USER with return code 0.
Can you check dba_users.account_status? Does it show LOCKED or LOCKED(TIMED) ?
|
|
|
|
|
|
| Re: How To Find the cause of user locked [message #690403 is a reply to message #690402] |
Thu, 21 August 2025 09:12 |
 |
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
As I said in your previous "How to find server process/scripts thats locking an account" topic:
Michel Cadot wrote on Sat, 09 November 2024 17:01
First it depends on which lock type it is.
See ACCOUNT_STATUS in DBA_USERS
Is it "LOCKED"? Then someone (maybe itself) locked the account (ALTER USER ACCOUNT LOCK).
Is it "LOCKED(TIMED)"? Then lock came from numerous wrong password at connection time.
Then you have DBA_USERS.LOCK_DATE which will give when lock happened and the audit trail will give you has attempted to connect at this time (OS_USERNAME, USERHOST, TERMINAL); COMMENT_TEXT may be of great help.
So if it is "LOCKED", you have to audit the ALTER USER statements.
[Updated on: Thu, 21 August 2025 09:14] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How To Find the cause of user locked [message #690406 is a reply to message #690405] |
Thu, 21 August 2025 14:49 |
wtolentino
Messages: 427
Registered: March 2005
|
Senior Member |
|
|
The user account is a common user and the audit policy is enabled on a pdb. I am connected to the pdb.
SQL> select * from audit_unified_enabled_policies where policy_name = 'ALTER_USER_POLICY';
POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI
------------------------- --------------- ------------------------- ------- --- ---
ALTER_USER_POLICY BY USER ALL USERS USER YES YES
I tried manualy to lock and unlock (using the alter user ...) I can see it is being captured on the unified_audit_trail.
ACTION_NAME SQL_TEXT EVENT_TIMESTAMP
-------------------- --------------------------------------------- -----------------------------------
ALTER USER alter user xxxxx account unlock 21-AUG-25 03.42.28.146846 PM
ALTER USER alter user xxxxx account lock 21-AUG-25 03.42.18.835455 PM
However, for some reasons when the account is getting locked no audits is being recorded on the unified_audit_trail. We are trying to find out what is causing the account to lock.
[Updated on: Thu, 21 August 2025 15:02] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
