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):
>> 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:
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 126.96.36.199.0 and also on Oracle Server Release 188.8.131.52.0.
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.
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