Tip

Hiding the Oracle password

When you have more than one Oracle database, you may be required to supply the name of which database you want to log on to. If you are using SQL*PLus, for instance, you will need to provide three login parameters (username, password, and db_name) on the same line as follows (db1 is the database name):

    Requires Free Membership to View

 >> sqlplus scott/tiger@db1

This is a major security risk. To hide the password (i.e., only asterisks are displayed), you can type the following instead:

 >>sqlplus scott@db1

When you press the ENTER key after typing the above, you will then be prompted for the password, and when you do type in the password, characters you type will be replaced by asterisks (*), the normal way that Oracle deals with passwords. In affect, you are hiding the password from prying eyes...

I've tested this tip on Oracle8 Enterprise Edition Release 8.0.5.0.0 and also on Oracle Server Release 7.3.4.0.0.

 

Reader Feedback

Michael P. writes: This works well on versions greater than 8.0.5. But what about SQL*PLUS calls in some scripts? In this case, how can I hide the password?

Hing M. writes: To hide the password when calling SQL*Plus from OS scripts, the only way I have found so far is not to use a password at all: connect internal. Obviously this has limitations in that you must connect from the local server. A problem with this is that export/import doesn't accept userid=internal. So does anyone have a trick for hiding the password in this case?

Phillip D. writes: I've used the hideargs overlay for years to hide command line passwords from being visible in the process listings.

See: http://www.usg.edu/OIIT/support/oracle/General/Unix/HideArgs.html

Rather than leave passwords in scripts though, I normally use configure OS authentication for recurring background jobs as run from cron or at.

The author writes: Yes, SQL*Plus calls from within scripts will be an issue with the password. I do not know of any way of hiding the password in scripts. However, a workaround (like we do here in a Unix environment) is to set permissions on the scripts, giving access only to relevant users.

Denis D. writes: Assuming Unix, for SQL*Plus there's another way:

 sqlplus /nolog <<EOF connect usr/pass@somewhere rem put any commands inline: select sysdate from dual; @my_batch_program quit EOF

The above requires an Oracle 8 client. For older versions, the following should work:

 sqlplus <<EOF usr/pass@somewhere rem put any commands inline: select sysdate from dual; @my_batch_program quit EOF

The above should work in any Unix shell.

Geoff H. writes: I do like Philip D's comment -- I did not know about that hidearg overlay. The way in which you hide a password can be approached in two further ways. Firstly, doing a 'ps -ef ' to view the processes suggests that your user is logging on to the Unix server; therefore, why not simply make that user identified externally in their Oracle account? If that is not good enough because they wish to access remotely as well, then use some Batch user account on the Unix box instead. If you wish to restrict the user's access to scripts and hide the password, why not allow the user to SUDO to a superuser who may execute the script on thier behalf. These are all workable possibilities, and wholly dependant on how you manage your installation. Hope this comment is helpful.

Witold I. writes: I sometimes use another trick to hide the password -- use the environmental variable that I set just for my session. The environment variable can be set from a script available only to selected users. Something like:

 set the_password=mypass sqlplus user1/%the_password%@mydb

In fact, I use it most often in scripts calling TKPROF but have just tried it with sqlplus and it works as well.

 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.


This was first published in July 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.