Hiding plain text password with sqlplus command line

Question

I wish to use a sqlplus command with password hidden from view such that it doesn't show up on ps -ef command.

I know there are a lot of solutions provided all over internet blogs but most of them seem to require admin privileges and I have restricted access on this server. And rest of them just don't seem to work me.

The command that I am currently using is as below:

sqlplus -s SOME_USERNAME/[email protected]:1500/SOMESID @some.sql

Legend:

SOME_USERNAME: schema/user

SOME_PASSWORD: password

SOMESID: SID for this DB.

@some.sql: An sql file containing insert statements.

Any pointers are much appreciated.

Update: Forgot to mention that this sqlplus command will be used inside a shell script.

Alex Poole · Accepted Answer · 2019-02-20 08:31:34Z

8

How do I input the password from within a shell script in this case?

You can use a heredoc:

sqlplus -s /nolog <<!EOF
connect SOME_USERNAME/[email protected]:1500/SOMESID
@some.sql
!EOF

The connect and @some.sql are treated as an input stream to SQL*Plus, as if you'd typed them in an interactive session, and are not part of the initial call to the executable - so the connection details don't appear in ps output.

You can also use variables if you want to, incidentally, as the variable expansion happens in the shell before it passes the stream to the executable - so even though SQL*Plus wouldn't understand say $PASSWD, referring to that in the heredoc works and the actual variable value is passed.

edited Feb 20, 2019 at 8:31

answered Feb 19, 2019 at 23:53

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vick_4444 Over a year ago

Thank you for the response. Only first solution worked for me. (the one with connect) The second one failed as below: {{ Enter password: ERROR: ORA-01005: null password given; logon denied SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identifier>] SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus }} But thank you very much for the first solution. I am going to use that. Much appreciated.

Alex Poole Over a year ago

Hmm, I'm sure there is a way to do that - though using echo for stdin doesn't work either - but it escapes me at the moment. As the first one works for you, I won't worry about it too much...

Christopher Jones · Accepted Answer · 2019-02-19 22:51:09Z

4

Use sqlplus -s SOME_USERNAME@\"somedns.intra.com:1500/SOMESID\" @some.sql and enter your password on the command line.
Or use external authentication and don't use a password at all
Finally, SOMESID is not a SID, it's a Service Name. The Easy Connect syntax you use only works with service names. SIDs are very very old-school.

answered Feb 19, 2019 at 22:51

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

2 Comments

vick_4444 Over a year ago

I forgot to mention that this sqlplus command will be executed within a shell script. Let me update the main question.

vick_4444 Over a year ago

How do I input the password from within a shell script in this case?

Collectives™ on Stack Overflow

Hiding plain text password with sqlplus command line

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related