OraFAQ Forum: Windows » "Copy a file to DB server from a remote machine using plsql+java"

Home » Infrastructure » Windows » "Copy a file to DB server from a remote machine using plsql+java" (110201 win64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

"Copy a file to DB server from a remote machine using plsql+java" [message #597273]

Wed, 02 October 2013 08:42

Amine
Messages: 371
Registered: March 2010

Senior Member

Hi all,
We want to copy a file from a remote machine to the DB server.

copy \\<IP_ADRESS>\SHARED_FOLDER\some_file.sql d:\copied_file.sql

I've used Java and a wrapped it into a PL/SQL procedure so I can do :

exec run_os_command(p_command => 'copy \\<IP_ADRESS>\SHARED_FOLDER\some_file.sql d:\copied_file.sql');

The file was not copied.

I have mapped \\<IP_ADRESS>\SHARED_FOLDER\ to x:\ for example and issued :

exec run_os_command(p_command => 'copy x:\some_file.sql d:\copied_file.sql');

The file was not copied.

But when I issued from the OS prompt

copy x:\some_file.sql d:\copied_file.sql

then the file is copied.

What can I add to the PL/SQL procedure so it wil work ?

Thanks in advance,

Amine

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597274 is a reply to message #597273]

Wed, 02 October 2013 09:08

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

When you run it with PL/SQL you run it with the account that started the Oracle database service, most likely SYSTEM, which has NO rights on the network and so can't access your remote folder.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597293 is a reply to message #597274]

Wed, 02 October 2013 14:18

Amine
Messages: 371
Registered: March 2010

Senior Member

So what can we do :
- Share the network folder to SYSTEM and map a network drive on it ?
- Or use a PL/SQL package in 11g that does that kind of things (use shared resources over the network)
?

Advise something Michel,
Thanks in advance,
Amine

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597297 is a reply to message #597293]

Wed, 02 October 2013 14:38

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ No, never, this is a security hole. Do you want anyone that is able to write/call your procedure to be able to copy the files on your server on their own workstation? Or be able to do other things like that across the nerwork?
2/ The problem is not an Oracle one, it is Windows security; so no Oracle version will change that.

So the solution is external to Oracle.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597461 is a reply to message #597297]

Fri, 04 October 2013 14:01

Amine
Messages: 371
Registered: March 2010

Senior Member

I am going to explain the whole problem, may be someone will provide a better solution :

Say for example, a user has to validate 1000 requests and then generate 1000 requests document and saves them into the DB (to each request we will add another information, a BLOB column that contains a PDF file : the request document).

To be the more flexible we can, we want a PL/SQL procedure in the DB server to create PDF files. These PDF files are generated from a REP file (Oracle Reports 11g File), contained in the application server, that is passed to a rwrun command (rwrun comes with Oracle Reports 11g).

So we've installed Oracle Reports 11g in the DB server. We now want to copy the REP file from the application server to the DB server to run rwrun on it.
And this is why we want to copy a file from a remote server (the app server) to the db server.
So here are the steps :

1- copy REP from the app server
2- run rwrun on that REP file with appropriate parameters (the was passed to th PL/SQL procedure) : a PDF file is generated
3- add the PDF file to the requests table (into the BLOB column)

Any idea, solution, suggestion : will take all of them Smile

Amine

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597462 is a reply to message #597461]

Fri, 04 October 2013 14:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why don't run you rwrun in the application server then insert into the BLOB at this same application server?
Why the file has to be transfer in the db server?

[Updated on: Fri, 04 October 2013 14:27]

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597463 is a reply to message #597462]

Fri, 04 October 2013 14:38

Amine
Messages: 371
Registered: March 2010

Senior Member

This is why we want it to be felxible : do it into the DB server. Sometimes we want to update some old entries directly from SQL/PLUS. But in general, we want things to be done on the DB server

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597468 is a reply to message #597463]

Fri, 04 October 2013 15:23

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't understand, you said the file is in the application server and want to put it in a BLOB.
OK but then why do you transfer it to the DB server? You can insert it in a BLOB without putting the file in the DB server, depending on your programming language your have anyway some procedures to insert a BLOB.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597469 is a reply to message #597468]

Fri, 04 October 2013 15:36

Amine
Messages: 371
Registered: March 2010

Senior Member

Actually, we have 6i client server apps and 11g forms services through an application server. So the REP are both in :
1- the application server (11g)
2- another server that hosts 6i REP

Forms and Reports 6i doesn't create Arabic PDF files correctly (only printed correctly through Reports Runtime). It's not supported in 6i.
Forms and Reports 11g does (in the application server).

To be totally independent from 6i and 11g, we've installed Oracle Forms and Reports 11g in the DB server (just to get rwrun). So from the DB server, we can generate correct Arabic PDF files.
Now, why transfer from the app server to the DB server : because the DB server doesn't host the REP files (the application in general), they have to be in the DB server.
Now, why not inserting into BLOB from app server : because we have to types of clients (6i and 11g), we want to maintain a single procedure that deliver the service of inserting BLOB, and we decided to create a DB procedure that does that.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597488 is a reply to message #597469]

Sat, 05 October 2013 01:05

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Anyway, forget transferring the file with PL/SQL.
You can then do it as you want otherwise.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597504 is a reply to message #597488]

Sat, 05 October 2013 04:22

Amine
Messages: 371
Registered: March 2010

Senior Member

Any solution about how to do it otherwise ?

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597514 is a reply to message #597504]

Sat, 05 October 2013 05:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Windows shell script, see your first post.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597696 is a reply to message #597514]

Mon, 07 October 2013 12:42

Amine
Messages: 371
Registered: March 2010

Senior Member

I finally found a note thanks to Tom Kyte :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:241814624807

I have 2 questions regarding this note:
1- in point "Accessing Network Directories" : does the change from default login (SYSTEM) to administrator users have an impact on the good running of the database ?

2- if we add the user SYSTEM to access the shared resource, could the UTL_FILE methods "see" it ?

Thanks in advance,

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597697 is a reply to message #597696]

Mon, 07 October 2013 12:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

does the change from default login (SYSTEM) to administrator users have an impact on the good running of the database ?

What did I say?

Quote:

1/ No, never, this is a security hole. Do you want anyone that is able to write/call your procedure to be able to copy the files on your server on their own workstation? Or be able to do other things like that across the nerwork?

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597699 is a reply to message #597697]

Mon, 07 October 2013 13:21

Amine
Messages: 371
Registered: March 2010

Senior Member

Your quote answers my second question.
The first is about running OracleServiceXXXX (where XXXX is the instance name) under a user different from SYSTEM (but enough sufficient to run the service and the listener) ?

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597704 is a reply to message #597699]

Mon, 07 October 2013 13:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is the same question.
If you run the service with another account than SYSTEM you open security hole. The service and then all users executing PL/SQL have the privileges of the account that started the service.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597707 is a reply to message #597704]

Mon, 07 October 2013 13:53

Amine
Messages: 371
Registered: March 2010

Senior Member

Thanks for the explanation. Suppose we are aware about the security problem the 2 solutions will produce : what is the "less worse" : start services with other users than SYSTEM or share folder to SYSTEM ?

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597708 is a reply to message #597707]

Mon, 07 October 2013 13:55

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know, I am not an expert in Windows security.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597709 is a reply to message #597704]

Mon, 07 October 2013 13:57

Amine
Messages: 371
Registered: March 2010

Senior Member

Michel Cadot wrote on Mon, 07 October 2013 19:42

The service and then all users executing PL/SQL have the privileges of the account that started the service.

I am talking about Oracle security (as you said) here not about Windows one.

Report message to a moderator

Re: "Copy a file to DB server from a remote machine using plsql+java" [message #597713 is a reply to message #597709]

Mon, 07 October 2013 14:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oracle does not care about who started the service as long as it works.
I was talking about Windows security.
Or if you prefer, Windows security through PL/SQL (which includes embedded Java).

[Updated on: Mon, 07 October 2013 14:47]

Report message to a moderator

Previous Topic:	Am unable to start DBCONSOLE after initial installation of Oracle 11g R2
Next Topic:	Setup ODTwithODAC121010 on Windows server 2012 Standard Evaluation 64-bit

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 17:15:28 CDT 2024