Oracle's UTL_FILE when connected locally or through listener has different behaviour

Question

We have a procedure which creates a CSV file with UTL_FILE. (env is Oracle 11.2.0.1 and RHEL 6.4) Until now, we were storing that CSV file into system oracle (the oracle owner) user home (/home/oracle/csv/). It works fine, but now we're required to store the CSV file into a different system user home (eg. reports system user home (/home/reports/csv/))

So we added the oracle user to the group reports as its secondary group, and then change reports home permissions to be accesible to him and all his group.

# id oracle
uid=500(oracle) gid=500(oracle) grupos=500(oracle),502(reports)
# id reports
uid=502(reports) gid=502(reports) grupos=502(reports)
# chmod 770 /home/reports
# ls -la /home/reports/
total 52
drwxrwx---  8 reports reports 4096 oct  3 12:58 .
drwxr-xr-x. 5 root    root    4096 oct  2 11:05 ..
drwxrwxrwx  2 reports reports 4096 oct  3 12:59 csv

With this, logging into system as oracle I can write, read and execute files into reports's home.

# su - oracle
oracle ~$ touch /home/reports/csv/test.txt
oracle ~$ ls -la /home/reports/csv/test.txt
total 8
-rw-rw-r-- 1 oracle          oracle             0 oct  3 17:51 test.txt

And now , as far as I know (and Oracle's documentation says), this should works, but it doesn't .. not at all. If I connect locally with sqlplus, It works. But if I connect from a remote machine or through the listener, I doesn't!

I'll show you: Local connection with sqlplus:

oracle ~$ export ORACLE_SID=MYDB
oracle ~$ sqlplus -S informes
Introduzca la contraseña: 
select a.directory_name, 
       a.directory_path,
       b.grantee,
       b.privilege
from all_directories a, 
     all_tab_privs b 
where a.directory_name = b.table_name
  and DIRECTORY_NAME='CSVFOLDER';

DIRECTORY_NAME                 DIRECTORY_PATH       GRANTEE   PRIVILEGE
------------------------------ -------------------- --------- ---------
CSVFOLDER                       /home/reports/csv   INFORMES  READ
CSVFOLDER                       /home/reports/csv   INFORMES  WRITE

show user
USER es "INFORMES"

declare
  output_fich utl_file.file_type;
begin
  output_fich := utl_file.fopen('CSVFOLDER','testfile.csv','W');
  utl_file.put_line (output_fich, 'test line');
  utl_file.fclose(output_fich);
end;
/

Procedimiento PL/SQL terminado correctamente.

host ls -la /home/reports/csv/testfile.csv
-rw-rw-r-- 1 oracle oracle 10 oct  3 18:20 /home/informesestados/tmp/testfile.csv

host rm /home/reports/csv/testfile.csv

Now, lets try it again connecting through the listener Let's see where is my tnsnames pointing to:

oracle ~$ cat $ORACLE_HOME/network/admin/tnsnames.ora | grep MYDB
MYDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = FR-BD1-tmp)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = MYDB)))

oracle ~$ ping FR-BD1-tmp
PING fr-bd1-tmp (192.168.78.3) 56(84) bytes of data.
64 bytes from fr-bd1-tmp (192.168.78.3): icmp_seq=1 ttl=64 time=0.047 ms
64 bytes from fr-bd1-tmp (192.168.78.3): icmp_seq=2 ttl=64 time=0.025 ms
^C
--- fr-bd1-tmp ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1386ms
rtt min/avg/max/mdev = 0.025/0.036/0.047/0.011 ms

oracle ~$ /sbin/ifconfig | grep "inet addr"
          inet addr:192.168.78.3  Bcast:192.168.78.255  Mask:255.255.255.0

oracle ~$ lsnrctl services LISTENER_MYBD

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2013 18:33:04

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Conectándose a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fr-bd1-tmp)(PORT=1522)))
Resumen de Servicios...
El servicio "mydb" tiene 1 instancia(s).
  La instancia "mydb", con estado READY, tiene 1 manejador(es) para este servicio...
    Manejador(es):
      "DEDICATED" establecido:45 rechazado:0
         LOCAL SERVER
El servicio "mydb_node1" tiene 1 instancia(s).
  La instancia "mydb", con estado READY, tiene 1 manejador(es) para este servicio...
    Manejador(es):
      "DEDICATED" establecido:3 rechazado:0 estado:ready
         LOCAL SERVER
El comando ha terminado correctamente

oracle ~$ sqlplus -S informes@mydb

Introduzca la contraseña:
select a.directory_name, 
       a.directory_path,
       b.grantee,
       b.privilege
from all_directories a, 
     all_tab_privs b 
where a.directory_name = b.table_name
  and DIRECTORY_NAME='CSVFOLDER';

DIRECTORY_NAME                 DIRECTORY_PATH       GRANTEE   PRIVILEGE
------------------------------ -------------------- --------- ---------
CSVFOLDER                       /home/reports/csv   INFORMES  READ
CSVFOLDER                       /home/reports/csv   INFORMES  WRITE

show user
USER es "INFORMES"

declare
  output_fich utl_file.file_type;
begin
  output_fich := utl_file.fopen('INFORMES','testfile.csv','W');
  utl_file.put_line (output_fich, 'test line');
  utl_file.fclose(output_fich);
end;
/
declare
*
ERROR en línea 1:
ORA-29283: operación de archivo no válida
ORA-06512: en "SYS.UTL_FILE", línea 536
ORA-29283: operación de archivo no válida
ORA-06512: en línea 4

Now, If I change reports's home permissions to be accesible to all, the UTL_FILE procedure through listener conenction works!

# chmod 777 /home/reports
# ls -la /home/reports/
total 52
drwxrwxrwx  8 reports reports 4096 oct  3 12:58 .
drwxr-xr-x. 5 root    root    4096 oct  2 11:05 ..
drwxrwxrwx  2 reports reports 4096 oct  3 12:59 csv

# su - oracle
oracle ~$ sqlplus -S informes@mydb
Introduzca la contraseña:
declare
  output_fich utl_file.file_type;
begin
  output_fich := utl_file.fopen('CSVFOLDER','testfile.csv','W');
  utl_file.put_line (output_fich, 'test line');
  utl_file.fclose(output_fich);
end;
/

Procedimiento PL/SQL terminado correctamente.

host ls -la /home/reports/csv/testfile.csv
-rw-rw-r-- 1 oracle oracle 10 oct  3 18:59 /home/informesestados/tmp/testfile.csv

???!!

I don't get why this behaviour. As Oracle's documentation says:

(http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_file.htm)

On UNIX systems, the owner of a file created by the FOPEN function is the owner of the shadow process running the instance

The owner of the shadow process its the same in both methods (i.e. ''oracle'' system user), so,.. why this?

Anybody has a clue? Am I missing something?

Answer 1

This is the expected behaviour on a unix system. either a process' effective user id allows to read/write a file or itseffective primary or its effective secondary group ids allow to read/write the files

If you log on to a unix system the effective user id , the effective group id and the effective secondary group ids are the ids defined for your login user on this system (in /etc/passwd or anywhere). if a unix process creates a new process the new process inherits this ids from the creating process.

The Oracle shadow process is the unix process that corresponds to an oracle session. This shadow process is created by the listener process if you log into the database using a network connection. If you add a new secondary group id to the oracle unix user this does not change the effective ids of the running listener process and therefore all newly created processs have the same effective user/group ids as the processes created before the adding of the new secondary group.

But if you log into the system after you added the new secondary group id to the oracle user, your login session will have this additional effective secondary group id and if you stop and start the listener process it will have the additional effective secondary group id too. So will have the shadow processes created by the new listener process.

If the listener process ist stared an stopped by another process after adding the new secondary group id but this other process was not restarted after adding the new secondary group id, the listener will not have the new secondary group id as effective secondary group id even if he is restarted because the process starting the listener process did not have the new secondary id as effective secondary id. This can happen if you restart the listener process by some cluster process. In this case this cluster process must be restarted before it restarts the listener process for the newly added secondary group id to take in effect.

If you make a local connection using sqlplus then the shadow process is spawned by sqlplus and not by a listener process. but the sqlplus process has the effective user/group ids inherited by your login session. so is you logged in after you added the new secondary group then the shadow process will have this secondary group id. If your login was already established before the secondary group was added to the oracle user then also all new local sqlplus connection (= shadow processes) of your unix session will not have this additional secondary group id as effective secondary group id.

Answer 2

As Colin 't Hart and Phil suggested in comments, I had to restart the listener. That was the problem!

New db connections via the listener inherit the env variables, groups etc that the listener was started with because a new Oracle process is fork()ed from the listener. Group membership is cached at process startup on Linux

Thanks a lot, I was stuck on this all day long.

Regards

UPDATE

Recently I've faced a similar situation with a procedure executed with the Scheduler. In this case, the shadow process is spawned by the job coordinator process (CJQ0). Here You will need to restart the whole database (you can kill the CJQ0, but then it is spawned by the process monitor, having the same problem).

As a workaround, you can use linux access control lists setfacl. With it the process doesnt need to restart to reload group membership .