Encrypt DB password in Script

Shell Programming and Scripting
1

Hi,

I have a SQL which i want to run through a shell script.

query_result=`/home/oracle/product/11.2.0/bin/sqlplus -S uname/pwd@DBNAME <<!
set heading off feedback off trimspool on 
set pagesize 0
set linesize 9999
spool $PARAM_PATH/param_name.txt;
Select sysdate from dual;
spool off;
exit
!`
echo $query_result

I do not want to Password to be visible.
So i gathered some information on the way to do this.
The most common way was to have the sql in a file and run the.sql file in a script and have only required permissions on it. Like below

sqlplus -S system/manager @my_sql_script.sql

But even here system/manager is the uname and pwd.
So how to avoid the pwd from being visible.?

Thanks,
Chetan.C

2

HI , you may define the username and password in profile and make it restricted.
Then you may use the variables against the username/password.

But I am sure that others on this portal may come up with great idea's.
An intelligent guy at your site may try to echo the variable values.

1 Like
3

Obscuring it isn't much help. You have to be able to retrieve it, and anything you can retrieve, so can anyone else.

Putting it in variables won't help, it'll still be visible in ps.

1 Like
4

@chetan.c: When you say visible, do you mean in ps output or defined in the script?
You can invoke sqlplus without logging in but calling a .sql-commandfile and use the SQL*Plus connect command to log in to your database. This way username and password do not show up in ps and are not visible in your main script.
Calling script:

...
$ORACLE_HOME/bin/sqlplus -s /nolog @/path/to/your/commandfile.sql $PARAM_PATH
...

commandfile.sql:

connect username/password
set heading off feedback off trimspool on 
set pagesize 0
set linesize 9999
spool &1/param_name.txt;
Select sysdate from dual;
spool off;
exit

Another way to hide login-information from ps-output is to use coprocesses (ksh or bash >= 4.0) or pipes. Here the implementation is a bit more tricky and depends on the shell you use.

1 Like
5

Thanks Ashish/Corona688.

@cero:
Actually i want the best practice to secure the Password.
So i should be setting appropriate permissions on the command file right?

Thanks,
Chetan.C

6

The commandfile has to be readable, so appropriate permissions will not keep the passwords completely hidden. Actually I do not think there is a way to secure the password that anyone who is able to read and understand your script can not revert.
An alternative would be to use SSL authentification instead of a password.

7

"Best practices" for retrievably-stored passwords is to not do that, at all, ever :stuck_out_tongue: Most sane login systems refuse to use stored passwords, and some even provide safer alternative methods(like ssh keys).

If it's unavoidable, keep it in a file that's only readable by the application you need, then pass the password into the relevant application with pipes so they can't be snooped on ps.