Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

how to make shell scripts that calls sqlplus secure ?

Henrique Silva_3
Regular Advisor

how to make shell scripts that calls sqlplus secure ?


Hi, I was just wondering out there. I have written tons and tons of database scripts for sybase and oracle, but since these scripts were only for our internal group running on our internal boxes, security was never a big concern, so we simply hard-coded username and passwords for the database user.

Now, I need to write a script on a customer site, and they are concerned about hard coding the username and password on the script itself.

HOW do you reference username and especially passwords securely, on a shell, perl script in an unix environment ?

Just want to know what the best practices out there is !!!

Thanks,

Henrique
"to be or not to be, what was the question ???? "
8 REPLIES
Bill Hassell
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

There are two approaches:

1. Simply replace the hardcoded user/password text with a prompt to the user to supply the required text. This of course means the script is now interactive only.

2. Write a program (not a script) that validates the user is authorized to run the current script. Then the program returns back the user ID and password to be used inside the script.

The #2 solution isn't perfect but scripts can NEVER be secured since they must be readable as well as executable. Programs can be execute-only but scripts must be read by the shell in order to run.


Bill Hassell, sysadmin
Brian Crabtree
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

This isn't secure from the script standpoint, but the following should work:

echo password | sqlplus username

This should keep the password from being visible to the "ps" command which is what I assume you are worried about.

The best way is to use an externally identified user, which can only be accessed from the user on the local system (normally).

Thanks,

Brian
Yogeeraj_1
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

hi,

This is called OS authentication.

For a unix environment, just

create user USERNAME identified externally;

then you can "sqlplus /"

It is what I use. I set my:

os_authent_prefix string ops$

to ops$ actually, so that when I

create user ops$yogeeraj identified by password;

I have the OPTION of either

a) using "sqlplus /"
b) "sqlplus ops$yogeeraj/password"

as I choose (eg: when logged in locally, I use /, coming in over the network -- I use the password)

$ sqlplus scott/tiger

well, it is trivial for someone to ps -aef | grep sqlplus and see my credentials. sqlplus / -- they get nothing

In my scripts, run in the background (backups and what not), we use sqlplus /, we don't HAVE any passwords encoded in there.

CAUTION: setting remote_os_authent to true will be serious security loop hole.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Edgar Avila
Frequent Advisor

Re: how to make shell scripts that calls sqlplus secure ?

There are several tools around to translate your shell code and perl code to C.
Then you can compile those new programs and see how they work.
Production use it's not encouraged. But it can save you a lot of programming time.

-------------
If it's a SQL script for a customer why don't you try to write it in C calling the appropiate libraries? I know it's kind of hard but it's the only way 'trully secure'.
Knowledge is power... so... give me the power!!
Volker Borowski
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

Hmm,
several Options:
OPS$ - Connect already explained above.
If OS-DBA User can be used,
"connect .... as sysdba" may be a way out.

The best thing would be to write a stored procedure instead of a script and let oracle execute it as a job. No password, no prompt, and all access stuff defined inside the database.
Limitation: A stored procedure is not able to start a database.

Hope this helps
Volker
Yogeeraj_1
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

hi,

apart from using os authentication, you can also use stored procedures/Packages -- coupled with DBMS_JOB to schedule the execution.

With this approach, if the server (database) is not up -- the jobs will be run when the server comes up -- it is all in the database..

e.g. DBMS_JOB
create or replace procedure analyze_my_tables
as
begin
for x in ( select table_name from user_tables )
loop
execute immediate
'analyze table '||x.table_name||' compute statistics';
end loop;
end;
/

Now to schedule this to run tonight at 3 a.m. (tomorrow morning in fact), and everyday thereafter at 3am, use the following:

declare
l_job umber;
begin
dbms_job.submit( job => l_job,
what => 'analyze_my_tables;',
next_date => trunc(sysdate)+1+3/24,
interval => 'trunc(sysdate)+1+3/24');
end;
/


hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Massimo Bianchi
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

Hi,
mixing up previous answer, and adding some fantasy, i suggest you the following:

- implement ssh, this way you will have secure OS account

- then implement OPS$ mechanism, to access the database, so connection over network will be cripted and password sniffing through ps won't be feasible

- if you need extra access to password, you can set up a ops$ user that has only one view, that contains users and password, this last is weak, because you will have to have password in plain text in a table, althought in oracle, but this could help you in little changes of your script/procedure, providing a wrapper program that extracts the user/password.

Massimo

Jean-Luc Oudart
Honored Contributor

Re: how to make shell scripts that calls sqlplus secure ?

Hi Henrique

There was a previous thread on same type of issue. I don't know if answers were satisfactory as no point was assigned ...

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xb90f31ec5e34d711abdc0090277a778c,00.html

Rgds,
Jean-Luc
fiat lux