Re: External tables. Security concerns.

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 19 Aug 2006 15:19:00 -0700
Message-ID: <1156025940.971213@bubbleator.drizzle.com>

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.

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

Received on Sat Aug 19 2006 - 17:19:00 CDT