Granting correct permissions to the UTL_FILE method

Question

Say, I have an oracle directory and granted to user 'scott'

CREATE OR REPLACE DIRECTORY dataFolder AS '/data/';
GRANT READ, WRITE ON DIRECTORY dataFolder TO scott;    

Then, I have a shell script say ExtractData.sh which uses UTL_FILE to convert BLOB data from database to physical files stored in the above directory dataFolder.

However, due to security concern in server, this /data/ directory is only given 770 permission, hence causing my script fails to write file into the directory.

But, when I change the permission to 777, script successfully writes file.

How to solve this by not giving 777 permission?

Answer 1

You failed to mention the owner of the directory.

Use a directory owned by oracle or in the osoper or dba group and you won't need 777 permissions. Apparently the directory is owned by root or some other user, so owner and group bits aren't helping you.

You can use chown to change ownership.

chown oracle:osoper /data

Just make sure you are aware of other programs accessing /data, if you change ownership make sure to adjust privs accordingly.