Knowing if Oracle account has been locked?

Question

We have an Oracle that locks up randomly; we get error ORA-28000: the account is locked.

Is there some log that will tell me if an oracle account was locked throughout the day? Right now the Oracle account works fine.

I don't have admin rights in this database. We have Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

Thanks.

Answer 1

If you don't have admin rights you probably won't be able to troubleshoot the issue without your DBAs. Some hints for troubleshooting (again, with your DBAs)

• If the user is locked after some time (and not multiple times a day) the PASSWORD_LIFE_TIME in the (default) profile for your user might be the culprit.
• If the user is locked randomly and you don't have something fancy like auditing enabled, you can look at your listener.log and check if there are connections from hosts you aren't aware of. Often some web service or workstation with old credentials caused user locking at my place.

If you don't have any access to the server's log files and/or the admin views this issue is hard to resolve. Team up with your DBAs

Answer 2

There are really only two ways for an account to get a status of 'locked'. First is for someone with proper privileges - like a DBA - to issue ALTER USER ACCOUNT LOCK; The second is for someone - or more likely, some process - to repeatedly try to connect with the wrong password until the number of failed attempts exceeds the value of FAILED_LOGON_ATTEMPTS set in the profile assigned to the user. My money is on the second. You've got a job/process/application that has been given the wrong password (the password recently changed and the process wasn't updated) and just keeps hammering with the wrong pwd.

Answer 3

This is the script we use but you need admin rights.

Common return codes are:

1017 "invalid username/password; logon denied"

Invalid login attempt,locks the account by user's profile parameter FAILED_LOGIN_ATTEMPTS

28000 "the account is locked"

Account was already locked when the user tried. The script will show you the exact hour it was locked. Before the 28000 you should have at least 3 of the 1017 which was the user that locked it.

select
  USERNAME "ORA USER", OS_USERNAME "OS USER", userhost,terminal,to_char(timestamp, 'YYYY-MON-DD HH24') Hour,returncode,count(*) "HOW MANY"
from
  DBA_AUDIT_SESSION
where
  returncode != 0 and
  timestamp>sysdate-2
group by 
  username,OS_USERNAME, userhost,terminal, to_char(timestamp, 'YYYY-MON-DD HH24'), returncode
order by 
  1,5 desc;

Answer 4

Assuming it is locked by a user entering a bad password, you could create a trigger to log information about the connection attempts. Something like this:

CREATE TABLE logon_trigger
(
USERNAME VARCHAR2(30)
,USERHOST VARCHAR2(128)
,TIMESTAMP DATE
,CLIENT Varchar2(300)
,OSUSER Varchar2(30)
);

CREATE OR REPLACE TRIGGER logon_trigger AFTER SERVERERROR ON DATABASE
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   vServerError Binary_Integer;
BEGIN
   vServerError := dbms_standard.server_error(1);
   IF (vServerError = 1017) THEN
      INSERT INTO logon_trigger VALUES(sys_context('USERENV', 'AUTHENTICATED_IDENTITY')
        , sys_context('USERENV', 'HOST'), SYSDATE, sys_context('USER', 'CLIENT_IDENTIFIER')
        , sys_context('USER', 'OS_USER'));
      COMMIT;
   END IF;
END;
/
ALTER USER SYSTEM ACCOUNT UNLOCK;

As soon as the account is locked the table should have the pertinent info in it.

SELECT * FROM logon_trigger;
DROP TRIGGER logon_trigger;
DROP TABLE logon_trigger;