Hiding Passwords for Oracle

[oradba]

Does anybody knows how to hide passwords when executing SQLPLUS ?

[John Palmer]

Hi,

If you mean hiding them from appearing in a ps listing because they've been supplied as arguments to sqlplus then instead of running:

sqlplus /

In an interactive session, run:
sqlplus
/
...

In a script run:
sqlplus << EOD
/
...
EOD

Regards,
John

[Steven E. Protter]

Prior post shows you how to script sqlplus, that kind of code is available all over the net.

What you can do is create a deep, dark password vault for oracle.

A fs where only the oracle script user can access. Then you can store passwords there and read them into your script.

When the oracle password changes, you'll have to update the files.

You'll also want to encrypt the files, so that if someone gains root access they can't get those passwords, though if they get root access you're pretty much screwed anyway.

P

[CCIL]

HI1

yous should have the oracle client (8i) , the file tnsnames.ora file should have the entry of entry of Oracle databse listner . then you can issue sqlplus with connect strings i.e name of database & user name , then it will ask for password which can not be seen.

[CCIL]

the exact syntax is

sqlplus username@connect_string
it will ask for password which will not be displayed

[Yogeeraj_1]

hello,

Well, my favorite solution to this is to use an "identified externally" account.
For example, I've set:


NAME TYPE VALUE
------------------------------ ------- --------------------
os_authent_prefix string ops$

in my init.ora. I then:

create user ops$yd identified externally;

This lets me:

$ id
uid=12997(yd) gid=1(other)

$ sqlplus /
SQL*Plus: Release 8.1.5.0.0 - Production on Fri Mar 10 19:28:46 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

ops$yd@8i> show user
USER is "OPS$YD"
ops$yd@8i>


I do not need a username password anymore (i can still use them but I can always
use / to log in as my). This is perfect for cron jobs, at jobs and the like.
You have to be logged into unix to become that account.

Hope this helps!

Best Regards
Yogeeraj

[Yogeeraj_1]

hi again,

Another way, when on korn shell you can issue

print PASS | oracletool

where 'oracletool' is most of the common oracle ones - exp, imp, sqlplus etc



for example:

print my_pass | exp userid=system file=...

or

print my_pass | sqlplus system @my_script

Or for my lengthy scripts

print "
connect user/pass
select ...
exit" | sqlplus /nolog


Hope this helps too!

Cheers
Yogeeraj

[Ravi_8]

Hi,

oracle> sqlplus username@instance_name
password:

passwd wouldn't be echoed

[Chuck Higgins]

I like your answer Yogeeraj, the one about using ops$.

I have been puzzled by this same question before and have never had a solution that I felt offered good security. My need for this has always been to automate a process with a script, which means that somewhere you need to get a clear text password because there will be no user around to type in the password.

I have considered using a encrypted file, but that means that you need to identify the file name and the encryption key in clear text and it is just one more step to get the password.

I think it makes a lot of sense to get the permissions from the user that is running the script so no password is required. If I was the author of the question, I would give you some points!

Only one problem, which is that the answer is Oracle specific. Maybe there is a similar solution for other DBs such as Solid?

Cheers,

Chuck

[Philippe Cussigh]

Hello,

Just a small comment about the "externally" identified user.
It is a great solution I do appreciate.
The issue is that if you create the OPS$xyz user, any user can create a xyz user on his own system, connect as xyz, then access the database with the connect / sequence.

Oracle can limit the "admin" rigts you can get remotely but the security failure is quite important. Anyway if you have only a limited number of "Oracle client", for instance with application serevers, you can easily restrict in the protocol.ora file the list of TCP/IP addresses which can access the system.

Philippe

[Chuck Higgins]

Seems that there are always loopholes to security. It is good to know about the users coming in over the network, thanks for the advice. I was so far just considering the local machine, which is a bit near-sited.
It would be nice if you could lock that feature down to just the local server while still allowing full user/passwd logins from the network. Without that, for database servers that expect many network connections, it would probably be better to lock down the server and hide the clear text passwords in a file as suggested before. At least you know that somebody has to get a login to your server first.

Chuck

[Volker Borowski]

Chuck,

there is hope ! (but not much :-)

init.ora parameter "remote_os_authent" (or likewise)
and the mentioned protocol.ora containing something like tcp.invited_nodes (or likewise).

I'd recommend to check the online docu CD on this, as I am not sure about the correct syntax, and I have no access to the docs now.

Keep in mind, that a PC as an "invited_node" may be down through certain hours, giving anyone the possibility to fake it's IP and become an "invited_node".
But this is a good option to ensure that only an application server can connect to the database and no other client.

The security implications with OPS$ are pretty good documented anywhere on the net. You may make it more difficult, but not impossible, beside you do not need network access.
On a local_only database, you might disable tcp-connect on the listener and switch to IPC or BEQ.

Hope this helps
Volker

PS: NEVER grant "dba" to an OPS$ - User !
I have seen people in SAP environment doing this just to make DB13 work !!! NEVER !!!

[Yogeeraj_1]

hi all,

A few clarifications!

OS Authentication lets me log in like this:

$ sqlplus /

SQL*Plus: Release 8.1.6.0.0 - Production on Sun Apr 1 17:34:47 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.2.0 - Production
With the Partitioning option
JServer Release 8.1.6.2.0 - Production

ops$yd@ORA8I.WORLD>

The database pickes up my identity from the OS. It works well on a single machine but not over a network. Over a network it can be very insecure as the machines just "trust" eachother. All I would need to do is put a machine in your network, create a user account in the OS named after some database account and I would be in. Never set remote_os_authent=true for this reason.

On NT it is more secure then on Unix with domains and such.

I use the OS identified accounts for all of my cron jobs -- that way I do not have a username/password stored in a script anywhere. Thats the strength of it.


I use ops$ accounts on all of my database machines for all admin accounts -- to avoid having passwords scattered about in a million scripts.

It is true if you use OS authenticated users over a network in a Unix environment (forcing you to set "remote_os_authent=true") that is a security hole.

The highest level of security you can have is using OS authenticated accounts on a host based system (no network).


It is ONLY true that there are problems if you set the init.ora parameter remote_os_authent=true -- which is something you should do only in an environment where you control ALL of the machines on the entire network.

Using OS authenticated accounts is safe, much much safer then what they propose.


As an example, just have to write their own script to do a "ps >> mylog.txt" using cron or just write a script:

while(1)
ps >> mylog.txt
sleep 20
end

leave that running and go home. Then use grep later to mine the data.

Conclusion:
============
USE OS AUTHENTICATED ACCOUNTS. They are 100% secure (more secure then even identifiedy by accounts. Do NOT use them over the network in Unix as they are based on "trust" (just like the r* commands rsh, rexec)

Hope this helps!

Best Regards
Yogeeraj

[Jean-Luc Oudart]

Seen on metalink a program named "hide" developed for NCR SVR4 (and not supported by Oracle).
doc id 1009091.6
"How do you hide username/password in ps?"

I did not try it myself.
The doc is back from 1997.

You would have to check it !

Rgds,
Jean-Luc

[Dax Desai]

.hide is a tool to mask the account/password info from being seen in the process list from the ps command in Unix. It is not supported by Oracle, but we have been using it to mask the password for several years from 7.3 to 8i with no problems to date on HPUX 10.2-11.0

1. The following executables need to be linked to hide file (I would recommend):
- sqlplus, imp, exp, sqlldr, sqlload

2. Copy the executable to executable.hide.
cp sqlplus sqlplus.hide
3. Remove the executable.
rm -f sqlplus
4. Link executable to hide.
ln -s hide sqlplus

Linking executable to hide files enables unix not to display the
user id password give in the command line when viewing the process
status via ps.

If anyone wants I can send the hide file.

[Dax Desai]

One more note to my previouse post:

If you link your executable for sqlplus to .hide the ps command will result in something like below:

ps -ef|grep sqlplus
dw8436 27450 29892 0 13:58:25 ? 0:00 sqlplus


in liue of the norm where you see the entire connect string such as sqlplus username/password@mydb

[Dax Desai]

Attached hide.c and instruction.

This works for other applications besides oracle as well to hide information from "ps" commands. Also have included a hide install file to simplify.