this is an use case of how to tackle the problem of suddenly having database accounts being autolocked by oracle. account autolocking in oracle may have a couple of root causes. at a first spot and for whatever reason you think *you* got hit by this, somebody may try to break your installation by some sort of brute force connect attempts. at a second one, and this is the most common case for intranet installations as to my experience, somebody/some application may use obsolete or wrong login credentials.
however, at this friday night where you jump around manually unlocking accounts over and over, the following might be your save of the day. please note that my first understanding of the topic was, with appreciation, advocated by this post “How to Audit Potential Attempts to Break a Username/Password” from Ayyu. below is an extended version, if you like.
the track down
after being stuffed with calls that third party applications can no longer login to the database with ORA-28000: the account is locked, a query of dba_users may reveal that one or more accounts have an account_status of 'LOCKED(TIMED)':
SELECT * FROM dba_users where account_status = 'LOCKED(TIMED)';
this status implies that a maximum number of login attempts have been failed continuously. the maximum number of login attempts, in turn, is determined in the accounts profile like this:
SELECT * FROM dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS';
with a default value of 10, obviously there were 10 failed attempts, but which application is doing so, most interestingly, where do this attempts come from? the question can be answered by oracle database auditing. to be specific, the following statements enable auditing (within the database, not to the filesystem, by the way) and dedicatedly log unsuccessful logins. please note, that the database needs a bounce to have auditing taking effect:
alter system set audit_trail = 'DB' scope = spfile; audit all by access whenever not successful;
the next step is to query aud$ for rows having column values of 1017 for returncode, where this is actually the log entry for the ORA-01017: invalid username/password; logon denied error, a failed login attempt receives on the client side (beware that anyone will again receive ORA-28000: the account is locked, once the account is autolocked again):
select * from ( select ntimestamp#, returncode, action#, userid, userhost, terminal, comment$text from aud$ order by ntimestamp# desc ) where rownum < 10;
columns terminal, by client host dns name, and comment$text, by authentication method and connect type, respectively, now contain the required information (userhost may also be valuable if you use a remote database and os authentication on the client).
after solving the problem, database auditing my be reset to do nothing by:
alter system set audit_trail = 'NONE' scope = spfile;
so, for me on a 10g, a strayed database link of a remote database was the actual root cause of the failed logins. for 11g installations, however, another new initialization parameters, like sec_max_failed_login_attempts and sec_case_sensitive_logon as well as new account profile defaults for PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME may need regard when tackling connect time issues.
sec_max_failed_login_attempts, although seeming very much the same, differs in phenomenon and effect from the scenario above because instead of account autolocking, a violation of the sec_max_failed_login_attempts limit will hardly drop connections for non-existing accounts. that is, a potential intrusion approach on a connection with different combinations of username/password, along a brute force attack for example, cannot be tracked for non-existing accounts and needs to be handled differently.
sec_case_sensitive_logon, on the other hand, may lead to a scenario that may be comparable to the one described above. this is especially true, if password versions, on a migration path from oracle 10g to 11g, come into play. to have it in order: oracle changed the password algorithm with 11g to allow for longer and more secure sha-1 hash values (160 bit). to support migration paths from 10g the newer password will be stored in another column of the user$ table. that way, with 11g, you will actually have two places for password storage, user$.password and user$.spare4. iff now a 10g password has never been touched on 11g, user$.spare4 will still be empty and sec_case_sensitive_logon has no effect at all. iff, however, the 10g password will be touched, user$.spare4 gets populated and sec_case_sensitive_logon will be regarded. see this article for more information How Oracle Stores Passwords from Sean.
select u.name, u.password, u.spare4, decode(length(u.password), 16, '10G ', NULL) || NVL2(u.spare4, '11G ', NULL) from USER$ u where u.type# = 1;
finally, iff an account points to a profile that uses a default PASSWORD_LIFE_TIME of 180 days as well as a default PASSWORD_GRACE_TIME of 7 days, the account_status may eventually turn into a value of 'EXPIRED(GRACE)'. this will happen, given the default numbers, on day dba_users.created + 180 where dba_users.expiry_date will show dba_users.created + 180 + 7. not that the status change will only happen iff a login has been performed after dba_users.created + 180 where PASSWORD_GRACE_TIME will always count down completely. being in status 'EXPIRED(GRACE)' requires the account owner to hand in a new password (old values are accepted).
select A.username, A.created, A.account_status, A.lock_date, A.expiry_date, A.password_versions, A.profile, (select limit from dba_profiles where profile = A.profile and resource_name = 'PASSWORD_LIFE_TIME') as pwd_life_time, (select limit from dba_profiles where profile = A.profile and resource_name = 'PASSWORD_GRACE_TIME') as pwd_grace_time from dba_users A;
failing login attempts for existing accounts, see sec_max_failed_login_attempts above, may sooner or later lead to account autolocking, the number of possible attempts as determined by the database account profiles in dba_profiles. database auditing very much helps in tracing failed logins by dedicated information about the client host identification and authentication method as well as type.
i’m having two questions now:
- why do i need to bounce the instance to review failed login information on the spot?
- do i have no way in pre 11g installations to prevent intruders from sitting on a connection and endlessly testing combinations of username/password?