how to make shell scripts that calls sqlplus secure ?

[Henrique Silva_3]

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

[Bill Hassell]

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.

[Brian Crabtree]

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]

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

[Edgar Avila]

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'.

[Volker Borowski]

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]

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

[Massimo Bianchi]

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]

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