Solved

Secure ftp

Posted on 2016-10-26
7
33 Views
Last Modified: 2016-11-13
I have a followup question to this Getting network error using utl tcp package.


I was able to implement file transferring to FTP site using Oracle. But in our production system ftp is not allowed. It has to be via sftp. Is there a way to sftp using oracle using the same program?

Thanks
0
Comment
Question by:gs79
  • 3
  • 3
7 Comments
 
LVL 23

Expert Comment

by:David
ID: 41861218
Have you tried it, following the similar setup for ACL rights, etc? I'd suggest you first ensure that an O/S user can sftp to the remote host, then perhaps as the Oracle account that's running in the application. Leave off the actual application at first, to keep this simple. It's an interesting and unique approach, I'll be curious to learn if this works for you.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41861233
the ftp package used in the previous question won't support sftp and it would be a significant rewrite to implement.

an easier solution would be to use a java stored procedure with a pl/sql front end.
0
 

Author Comment

by:gs79
ID: 41861342
Is there a documentation to use java stored procedure

I tried using external c program to execute shell commands and I get the following error

ORA-28576: lost RPC connection to external procedure agent

Not sure if there is anything wrong in the setup but it looks ok.

Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:gs79
ID: 41862897
I compiled c program into a shared object which can execute shell commands. I have wrapper plsql function which executes the library.

Now I can execute the following unix command with out any issues

begin
 retval := msi_extprocsh('/bin/ls /home/oracle/ > /home/oracle/output.txt');
end;
/

but when i try to execute following it fails though the string executes fine in the terminal:

retval := msi_extprocsh('/bin/osftp ftp.ftpsite.com ftpuser ftppass test /home/oracle/myftp xyz.csv');

/bin/osftp  is a soft link to a wrapper shell script secureftp.sh which accepts following six parameters

1. host
2. user
3. password
4. Change directory to in the target
5. LCD
6. file name

I get the following error from plsql:

ORA-28576: lost RPC connection to external procedure agent

Not sure why it should not execute /bin/osftp command when it can execute /bin/ls command.

Please let me know
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41862913
diagnosing that would be a completely new question pursuing the c, the listener, extproc setup etc.

if you don't mind using os commands, then try using dbms_scheduler to call ftp or your osftp script with external programs
0
 

Author Comment

by:gs79
ID: 41862951
Could it be C program or the setup? Because the program executes just fine for the command copying the contents of the file to a directory.

We use a 3 party scheduler  which can execute shell script. But its nice to generate the file and immediately ftp it with in plsql program
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41863364
>> Could it be C program or the setup?

sure

>>  ftp it with in plsql program

using dbms_scheduler you don't have to actually "schedule" the process, simply use it to run the external sftp command.  Doing that you can invoke it from pl/sql
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now