All files generated via a spool in sqlplus are given the oracle:dba ownership instead of the OS user that launched sqlplus and executed the script. We need to spool a file with the OS user ownership.
Script launched as OS user "A" on the "A" server that then launches sqlplus and connects to a remote database via a service from tnsnames.ora as another database user and the results of this script are spooled out to the "A" server.
The spooled file on the old server is written with rw-rw-r: user read write, group read write, public read and the ownership A:A which is the OS user. The spooled file on the new server is written rw-r-r: user read write, group read, public read and the ownership oracle:dba.
The "A" user then has no privileges to modify the file and continue with the process of transmitting the file, editing and removing the file for the next round of orders. This will keep us from being able to migrate to the new server as we cannot process orders.
$ORACLE_HOME/bin/sqlplus -s user/password@prd1 @./script/CustomScript/R12_OM_UFPC-oracle.sql
Background: Old server running OEL 4.8, Oracle 10.2.0.4
New server running OEL 6.5, Oracle 11.2.0.4
Tested so far:
Updated umask to 0002 instead of 0022 and file is now generated as rw-r-r. However, the ownership is still oracle:dba.
Updated ownershipt of $ORACLE_HOME/bin/oracle.exe to oracle:oracle (added edi user to oracle group) and chmod 6751 oracle.exe. This created multiple issues where crucial scripts, ie sqlplus failed to run.
Added !chmod 755 output.file.name - "A" OS user doesn't have permission to change mod.
Added !cp command to mv the output file and this generated the file as OS user:oracle. This is a potential work around in an emergency but the developer would be forced to rewrite countless scripts.
Any ideas?