DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC



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 log in 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 - 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 log in 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.

Using the LOGON Option

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 log in 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.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 04-Sep-2012 00:25:48 EDT
Page Count: 15142