April 4, 2012
A fair number of articles on this blog are Oracle performance related in one respect or another. I started thinking about how to measure how much more efficient something is when compared to something that is never performed. Consider a situation where you had a database user account that is a member of the DEFAULT Oracle Database profile that is configured as follows:
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION NULL;
With the above configuration, passwords do not expire after a specified number of days, there are no password complexity requirements, and after five failed logon attempts, the account is automatically locked for one day. The above configuration is not ideal from a security standpoint, but that is not the point of this blog article.
Assume that the one database user account is shared by multiple people (or multiple utility programs with an embedded username and password). Suddenly, you find that your efficient utility program becomes inefficient… to the point that the “utility” portion of the program never has an opportunity to execute. Well, that was unexpected, the database user account is locked out. How did that happen?
The following SQL statement is probably quite simplistic for many of the readers of this blog, and probably should have been included in my Neat Tricks article that I published a couple of years ago, but I thought that I would include the SQL statement here to save myself a couple of minutes of typing the next time I need to figure out who-done-it:
SELECT TO_CHAR(TIMESTAMP,'MM/DD HH24:MI') TIMESTAMP, SUBSTR(OS_USERNAME,1,20) OS_USERNAME, SUBSTR(USERNAME,1,20) USERNAME, SUBSTR(TERMINAL,1,20) TERMINAL, ACTION_NAME, RETURNCODE FROM SYS.DBA_AUDIT_SESSION WHERE USERNAME LIKE 'MYUSER%' AND TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE ORDER BY TIMESTAMP DESC; TIMESTAMP OS_USERNAME USERNAME TERMINAL ACTION_NAME RETURNCODE ----------- -------------------- -------- --------------- -------------------- ---------- 04/03 11:33 USER1 MYUSER CUSER1 LOGON 0 04/03 11:33 USER1 MYUSER CUSER1 LOGOFF 0 04/03 11:33 USER1 MYUSER CUSER1 LOGOFF 0 04/03 11:33 USER1 MYUSER CUSER1 LOGON 0 04/03 10:54 USER2 MYUSER CUSER2 LOGOFF 0 04/03 10:53 USER2 MYUSER CUSER2 LOGON 0 04/03 10:53 USER2 MYUSER CUSER2 LOGON 0 04/03 10:53 USER2 MYUSER CUSER2 LOGON 0 04/03 09:58 USER3 MYUSER CUSER3 LOGOFF 0 04/03 09:56 USER4 MYUSER CUSER4 LOGON 0 04/03 09:56 USER4 MYUSER CUSER4 LOGOFF 0 04/03 09:51 USER5 MYUSER CUSER5 LOGON 0 04/03 09:51 USER5 MYUSER CUSER5 LOGON 0 04/03 09:51 USER3 MYUSER CUSER3 LOGON 0 04/03 09:51 USER5 MYUSER CUSER5 LOGOFF 0 04/03 09:51 USER5 MYUSER CUSER5 LOGOFF 0 04/03 09:44 USER4 MYUSER CUSER4 LOGOFF 0 04/03 09:29 USER3 MYUSER CUSER3 LOGOFF 0 04/03 09:10 USER4 MYUSER CUSER4 LOGON 0 04/03 09:09 USER3 MYUSER CUSER3 LOGON 0 04/03 09:06 USER3 MYUSER CUSER3 LOGOFF 0 04/03 09:06 USER3 MYUSER CUSER3 LOGOFF 0 04/03 08:47 USER3 MYUSER CUSER3 LOGON 0 04/03 07:50 USER4 MYUSER CUSER4 LOGON 0 04/03 07:50 USER4 MYUSER CUSER4 LOGOFF 0 04/03 07:50 USER4 MYUSER CUSER4 LOGOFF BY CLEANUP 0 04/03 07:50 USER4 MYUSER CUSER4 LOGOFF BY CLEANUP 0 04/03 07:50 USER4 MYUSER CUSER4 LOGOFF BY CLEANUP 0 04/03 07:50 USER4 MYUSER CUSER4 LOGON 0 04/03 07:50 USER4 MYUSER CUSER4 LOGON 0 04/03 07:50 USER4 MYUSER CUSER4 LOGON 0 04/03 07:12 USER3 MYUSER CUSER3 LOGON 0 04/03 07:12 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/03 07:11 NETWORK SERVICE MYUSER SERVER LOGON 0 04/03 07:11 IUSR_WEBSERV MYUSER SERVER LOGON 0 04/03 06:35 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/03 06:35 IUSR_WEBSERV MYUSER SERVER LOGOFF 0 04/03 06:35 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/03 06:34 IUSR_WEBSERV MYUSER SERVER LOGON 0 04/03 06:34 NETWORK SERVICE MYUSER SERVER LOGON 0 04/03 06:34 NETWORK SERVICE MYUSER SERVER LOGON 0 04/03 06:29 USER3 MYUSER CUSER3 LOGOFF 0 04/03 06:29 USER3 MYUSER CUSER3 LOGON 0 04/03 06:28 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/03 06:28 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/03 06:27 IUSR_WEBSERV MYUSER SERVER LOGOFF 0 04/03 06:27 NETWORK SERVICE MYUSER SERVER LOGON 0 04/03 06:26 IUSR_WEBSERV MYUSER SERVER LOGON 0 04/03 06:26 USER3 MYUSER CUSER3 LOGOFF 0 04/03 06:26 USER3 MYUSER CUSER3 LOGON 0 04/03 06:26 NETWORK SERVICE MYUSER SERVER LOGON 0 04/03 06:23 USER3 MYUSER CUSER3 LOGON 28000 04/03 06:23 USER3 MYUSER CUSER3 LOGON 28000 04/03 06:22 NETWORK SERVICE MYUSER SERVER LOGON 28000 04/03 06:22 NETWORK SERVICE MYUSER SERVER LOGON 28000 04/03 02:30 USER5 MYUSER SERVER2 LOGON 28000 04/02 19:53 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 19:53 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 19:11 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 19:11 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 18:37 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 18:37 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 18:37 USER1 MYUSER CUSER1-LT LOGON 1017 04/02 18:37 USER1 MYUSER CUSER1-LT LOGON 28000 04/02 18:32 USER1 MYUSER CUSER1-LT LOGON 1017 04/02 18:32 USER1 MYUSER CUSER1-LT LOGON 1017 04/02 18:31 USER1 MYUSER CUSER1-LT LOGON 1017 04/02 18:31 USER1 MYUSER CUSER1-LT LOGON 1017 04/02 17:08 USER3 MYUSER CUSER3 LOGOFF 0 04/02 17:03 USER3 MYUSER CUSER3 LOGOFF 0 04/02 17:03 USER3 MYUSER CUSER3 LOGON 0 04/02 16:55 USER3 MYUSER CUSER3 LOGOFF 0 04/02 16:55 USER3 MYUSER CUSER3 LOGON 0 04/02 16:54 USER3 MYUSER CUSER3 LOGON 0 04/02 16:52 USER3 MYUSER CUSER3 LOGOFF 0 04/02 16:45 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/02 16:45 NETWORK SERVICE MYUSER SERVER LOGOFF 0 04/02 16:45 IUSR_WEBSERV MYUSER SERVER LOGOFF 0 04/02 16:44 NETWORK SERVICE MYUSER SERVER LOGON 0 04/02 16:44 NETWORK SERVICE MYUSER SERVER LOGON 0 04/02 16:44 IUSR_WEBSERV MYUSER SERVER LOGON 0 04/02 16:43 USER3 MYUSER CUSER3 LOGOFF 0 04/02 16:42 USER3 MYUSER CUSER3 LOGON 0 04/02 16:42 USER3 MYUSER CUSER3 LOGON 0 04/02 16:32 USER6 MYUSER CUSER6 LOGON 0 04/02 16:32 USER6 MYUSER CUSER6 LOGON 0 04/02 16:32 USER6 MYUSER CUSER6 LOGOFF 0 04/02 16:32 USER6 MYUSER CUSER6 LOGOFF 0 04/02 16:21 USER3 MYUSER CUSER3 LOGON 0 04/02 16:20 USER3 MYUSER CUSER3 LOGON 0 04/02 16:20 USER3 MYUSER CUSER3 LOGOFF 0 04/02 16:19 USER3 MYUSER CUSER3 LOGOFF 0 04/02 16:15 USER3 MYUSER CUSER3 LOGON 0 04/02 15:40 USER3 MYUSER CUSER3 LOGON 0 04/02 15:40 USER3 MYUSER CUSER3 LOGOFF 0 04/02 15:31 USER3 MYUSER CUSER3 LOGOFF 0 04/02 15:06 USER1 MYUSER USER1 LOGON 0 04/02 15:06 USER1 MYUSER USER1 LOGOFF 0 04/02 15:06 USER1 MYUSER USER1 LOGON 0
A couple of the rows in the above output are slightly out of sequence, but the order of the output is close enough for my needs. The RETURNCODE column is the number associated with the ORA- error code that was returned to the client computer:
- RETURNCODE=0 indicates success
- RETURNCODE=1017 indicates bad password
- RETURNCODE=28000 indicates account is locked out
Reviewing the above output, operating system user USER3 logged on successfully at 5:03 PM and logged off at 5:08 PM. Operating system user USER1 attempted to log on at 6:31 PM from a computer named CUSER1-LT, but failed due to an invalid password. By 6:37 PM, that same operating system user and computer combination had entered an incorrect password five times, which resulted in an ORA-28000 account lockout error being returned starting with the sixth logon attempt.
At 2:30 AM, USER5 on a computer named SERVER2 attempted to connect using the same database user account, but was also greeted with an ORA-28000. The same fate awaited operating system user NETWORK SERVICE (this was a web-based logon attempt) and USER3 at 6:22 AM and 6:23 AM, respectively. The problem was obviously corrected by 6:26 AM, quite likely through the application of the following SQL statement:
ALTER USER MYUSER ACCOUNT UNLOCK;
—
If you tried executing the above query, you might have noticed that the query execution required a bit of time. Enterprise Manager also, apparently, executes a somewhat similar SQL statement every 30 minutes. You can learn more about the potential performance impact of Enterprise Manager’s use of SYS.DBA_AUDIT_SESSION here.
I again started thinking about how to measure how much more efficient something is when compared to something that is never performed… I suppose that it is important to first determine whether or not the task at hand is important before deciding if not doing something is infinitely more efficient or infinitely less efficient than would be the case if the task at hand were completed.
Charles Hooper's Oracle Notes 
[…] Charles Hooper takes another view at the failed logon attempts. […]
From the date this seems to old post. No, No, No… This is very actual. Help me a lot… Thank you a lot…..