New-logon option in SQLPLUS

New -LOGON option to SQL*Plus

by Jeff Hunter, Sr. Database Administrator

---

The -LOGON option was added to SQL*Plus in Oracle9i to help alleviate attacks and break-ins by hackers. By default, when a users attempts to login to the database using SQL*Plus with an invalid username/password combination, the database displays the error message:

C:\> sqlplus scott/badpassword

SQL*Plus: Release 9.2.0.3.0 - Production on Fri Jul 18 17:11:08 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

The attempt to login to the database errors out, but the username prompt remains. After three attempts, the return goes back to the operating system prompt. However, until then, the username prompt remains. Most security conscious organizations consider this bad policy, especially when running batch programs. In cases like this, it is a strict requirement that the application return to an OS prompt as soon as the first error is reported.

This is where the new LOGON option introduced in Oracle9i comes into play. The LOGON option is used to stop attempting connecting to the database after the first failure. Using the above example, we could login to SQL*Plus using the following syntax:

C:\> sqlplus -s -LOGON scott/badpassword
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

C:\>

In the above example, you can see that when the connection fails, regardless of the reason, the control returns to the operating system immediately after reporting the error - the username prompt is not displayed.

The LOGON option can also provide usefulness in writing a script to automate the checking of a database's availability. A common approach used by DBAs is to have a shell script with something similar to the following:

sqlplus -s scott/tiger@oradb1.mycompany.com 

If the connection does not succeed for some reason, because either the listener is down or the database is down, or because the userid and password combination is wrong, the script does not return to the operating system prompt. In an automated script, it will hang, making the script useless. Using the -LOGON option here can make the script useful in these automated situations.