Skip to Main Content
  • Questions

  • DBMS_FILE_TRANSFER unix permissions settings

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, VIDHYA.

Asked: August 24, 2017 - 4:35 am UTC

Last updated: November 02, 2022 - 5:01 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi Tom,
We are having different unix development environment .when I'm using DBMS_FILETRANSFER.Fcopy in one environment it is creating the file with
Rw permission to oracle used only.
In another environment it is creating the file rw to user oracle and rw to group as well .
Env 1 :
RW- ___-___ oracle Dba filename
Env 2 :

Rw - Rw - ___ oracle Dba filename .

I want the file to be created with rw to user and group .
Pls guide me where to check and change the permission .


Thanks
Vidhya

and Connor said...

It can be impacted by the settings as the OS level. For example

$ mkdir /tmp/test1 /tmp/test2
$ setfacl -d -m g::rwx /tmp/test1

SQL> create or replace directory DEST as '/tmp/test1';

Directory created.

SQL> BEGIN
  2    DBMS_FILE_TRANSFER.copy_file(
  3     source_directory_object      => 'TEMP',
  4     source_file_name             => 'file1.dbf',
  5     destination_directory_object => 'DEST',
  6     destination_file_name        => 'file2.dbf');
  7  END;
  8  /

PL/SQL procedure successfully completed.


SQL> create or replace directory DEST as '/tmp/test2';

Directory created.

SQL> BEGIN
  2    DBMS_FILE_TRANSFER.copy_file(
  3     source_directory_object      => 'TEMP',
  4     source_file_name             => 'file1.dbf',
  5     destination_directory_object => 'DEST',
  6     destination_file_name        => 'file2.dbf');
  7  END;
  8  /

PL/SQL procedure successfully completed.


$ ls -l /tmp/test*/file*
-rw-r----- 1 oracle oinstall 309338112 Aug 25 01:15 /tmp/test2/file2.dbf
-rw-rw---- 1 oracle oinstall 309338112 Aug 25 01:14 /tmp/test/file2.dbf


Take a look here for some OS permission information

https://www.linuxquestions.org/questions/linux-desktop-74/applying-default-permissions-for-newly-created-files-within-a-specific-folder-605129/


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This doesn't seem to work

Luis, November 01, 2022 - 1:17 pm UTC

Hi,

I am posting this reply as I got referred to this suggestion on a support SR, and after testing the ACL it does not seem to work for DBMS_FILE_TRANSFER.

$ setfacl -d -m o::r .
$ getfacl .
# file: .
# owner: oradb01
# group: oinstall
user::rwx
group::r-x
other::r-x
default:user::rwx
default:group::r-x
default:other::r--

$ rm copia.txt

-- Ran the copy a SQL session
CREATE OR REPLACE DIRECTORY teste03 AS
'/home/oradb01/teste3';

begin
DBMS_FILE_TRANSFER.COPY_FILE(
'TESTE02','teste.txt','TESTE03','copia.txt');
END;


$ ls -l
total 0
-rw-r----- 1 oradb01 oinstall 0 Nov 1 09:42 copia.txt
$

Regards,

Luis
Connor McDonald
November 02, 2022 - 5:01 am UTC

Is the file an exact multiple of 512bytes?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library