Re: External tables. Security concerns.

DA Morgan wrote:
> artmt_at_hotmail.com wrote:
> > DA Morgan wrote:
> >> artmt_at_hotmail.com wrote:
> >>>> Yes and no. they are correct to not allow uncontrolled use of UTL_FILE
> >>>> but directory objects are harmless and the only real risk with an
> >>>> External Table would be you dumping GB of into a file and using too
> >>>> much disk.
> >>>>
> >>>> Most likely they don't understand the technology because if they did
> >>>> they would know that DIRECTORY OBJECTS are wholly internal to the
> >>>> database.
> >>>> --
> >>>> Daniel A. Morgan
> >>>> University of Washington
> >>>> damorgan_at_x.washington.edu
> >>>> (replace x with u to respond)
> >>>> Puget Sound Oracle Users Group
> >>>> www.psoug.org
> >>> That answers my question about external tables.
> >>> Thanks.
> >>> How about the other components required to implement ETL via external
> >>> tables?
> >>> Would allowing FTP access or NFS mount necessarily jeopardize secutity
> >>> of the Oracle server?
> >> Not necessarily but my instinct would be to say H... no to FTP and I
> >> would need to know a lot more about what NFS mounting has to do with
> >> anything as it can only be done by root and you sure don't belong there
> >> from inside the database.
> >>
> >> If in 10g look at using DBMS_FILE_TRANSFER to get and put files.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan_at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > OK. I have Oracle database on one machine and files that contain data
> > source for external tables on another.
> > If FTP or NFS mounting are not recommended, what other options do I
> > have?
> > My understanding is that DBMS_FILE_TRANSFER is for transferring files
> > between Oracle databases. Can it also be used for moving ASCII files
> > from a non-Oracle machine?

>

I must be missing something.

  DBMS_FILE_TRANSFER.GET_FILE(

   source_directory_object      => 'DB_FILES_DIR1',
   source_file_name             => 'USERS01.DBF',
   source_database              => 'REMOTE',
   destination_directory_object => 'DB_FILES_DIR2',
   destination_file_name        => 'USERS01.DBF');

In all examples that I have seen the remote location was identified as a dblink to the remote database as source_database and a directory object on that database as source_directory_object. This implies that the remote location (where the source file lives) must have an Oracle instance running.
What am I missing? Received on Sat Aug 19 2006 - 16:20:56 CDT