Re: External tables. Security concerns.

From: <artmt_at_hotmail.com>
Date: 20 Aug 2006 09:15:20 -0700
Message-ID: <1156090520.360431.99540@m79g2000cwm.googlegroups.com>

DA Morgan wrote:
> artmt_at_hotmail.com wrote:
> > 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?
> >> DBMS_FILE_TRANSFER can be used to transfer your mother's cookie
> >> recipies. A file is a file is a file.
> >
> > 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?

>

> A directory object points to a location on the file system. That
> location can be c:\temp or /home/oracle. It has nothing to do with
> the database. Source object is a file name.

>

> Look at the demo in Morgan's library. I am copying from:
> 'c:\temp\source'
> to:
> 'c:\temp\dest'

>

> None of these has anything to do with a database.
> Try it without using a .dbf file.

I am confused.
If my source directory is on a remote machine, doesn't it have to be NFS mounted? Received on Sun Aug 20 2006 - 11:15:20 CDT