Calling shell script from PL/SQL, but shell gets e

Question

I am trying to execute a shell script from inside the Oracle database using Runtime.getRuntime().exec.

Oracle 11.2.0.4 EE running on Red Hat 5.5

---

CREATE OR REPLACE procedure pr_executa_host(p_cmd varchar2)
    as language java name 'Util.RunThis(java.lang.String)';
/

---

public class Util extends Object
{

  public static int RunThis(java.lang.String args)
  {
  Runtime rt = Runtime.getRuntime();
  int        rc = -1;

  try
  {
     Process p = rt.exec(args);

     int bufSize = 4096;
     BufferedInputStream bis =
      new BufferedInputStream(p.getInputStream(), bufSize);
     int len;
     byte buffer[] = new byte[bufSize];

     // Echo back what the program spit out
     while ((len = bis.read(buffer, 0, bufSize)) != -1)
        System.out.write(buffer, 0, len);

     rc = p.waitFor();
  }
  catch (Exception e)
  {
     e.printStackTrace();
     rc = -1;
  }
  finally
  {
     return rc;
  }
  }
}
/

---

The permissions granted on java to db user SCOTT:

---

kind    grantee    type    name    action

GRANT    SCOTT    java.io.FilePermission    /webstart/mn500/*    readFileDescriptor
GRANT    SCOTT    java.io.FilePermission    /webstart/mn500/*    read,write,execute
GRANT    SCOTT    java.io.FilePermission    /webstart/mn500/*    writeFileDescriptor
GRANT    SCOTT    java.io.FilePermission  /webstart/mn500/CONCLUIDO/MN457560/executa.sh execute
GRANT    SCOTT    java.lang.RuntimePermission    *    writeFileDescriptor
GRANT    SCOTT    java.lang.RuntimePermission /webstart/mn500/CONCLUIDO/MN457560/executa.sh    execute

---

The shell script executa.sh, which is the one I'm trying to execute:

---

#!/bin/sh
echo i am `/usr/bin/whoami`
echo environment `/bin/env`
/bin/date>>/webstart/mn500/CONCLUIDO/MN457560/test.txt

---

The permissions on the directory:

---

p08[oracle] $ ls -larth /webstart/mn500/CONCLUIDO/MN457560
-rw-r--r--   1 oracle oinstall    1 Jul 29 12:03 test.txt
-rwxr-xr-x   1 oracle orafiles  430 Jul 29 12:04 executa.sh
drwxr-xr-x   2 oracle orafiles 4.0K Jul 29 12:04 .

---

The thing is, when I execute the procedure pr_executa_host, it runs the shell script as grid os user, not oracle! (although it keeps oracle environment variables, like it did a 'su grid -m' before executing the shell script)

Since grid doesn't have write privileges on neither the directory, nor the file, the script doesn't do anything, the test file stays unaltered. Take a look:

---

begin
  dbms_java.set_output(1000000);
  pr_executa_host('/webstart/mn500/CONCLUIDO/MN457560/executa.sh');
  dbms_lock.sleep(2);
end;
/

i am grid
environment HOSTNAME=p08.XXXXXXXXXXXX.com.br SHELL=/bin/bash TERM=xterm HISTSIZE=1000 
SSH_CLIENT=10.141.112.28 56029 22 NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 QTDIR=/usr/lib64/qt-3.3 
QTINC=/usr/lib64/qt-3.3/include SSH_TTY=/dev/pts/0 USER=oracle 
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;0
1:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01
;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*
.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.tbz=01;31:*.tbz2=01;31:*.bz=01;31
:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7
z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01
;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;3
5:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*
.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=
01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35
:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.a
ac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=
01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36: ORACLE_SID=sigepshm 
ORACLE_BASE=/oracle ORACLE_HOSTNAME=P08 PATH= MAIL=/var/spool/mail/oracle 
TNS_ADMIN=/grid/product/11.2.0/grid/network/admin PWD=/oracle/product/11.2.0/db/dbs 
KDE_IS_PRELINKED=1 LANG=en_US.UTF-8 ORA_NET2_DESC=27,30 KDEDIRS=/usr ORACLE_TERM=xterm 
ORACLE_SPAWNED_PROCESS=1 HISTCONTROL=ignoredups SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass 
HOME=/home/oracle SHLVL=2 GRID_HOME=/oracle/product/11.2.0/grid LOGNAME=oracle CVS_RSH=ssh 
QTLIB=/usr/lib64/qt-3.3/lib SSH_CONNECTION=10.141.112.28 56029 10.147.0.8 22 
CLASSPATH=/oracle/product/11.2.0/db/JRE:/oracle/product/11.2.0/db/jlib:/oracle/product/11.2.0/db/rdb
ms/jlib LESSOPEN=|/usr/bin/lesspipe.sh %s DISPLAY=localhost:10.0 
ORACLE_HOME=/oracle/product/11.2.0/db G_BROKEN_FILENAMES=1 _=/bin/env

---

Why is the java inside the database calling unix commands as grid user, not oracle?

Thanks a lot for your help, Stolf

Answer 1

The issue, as pointed out in the comments, is that Runtime.getRuntime().exec runs throught EXTPROC, and thus through the Grid Listener. Since we have OS user isolation between DB and GRID on our new configuration, this raised a permission problem on the FS.

The solution to this is one of the bellow:

• Fix FS permission to let grid user write the files and change umask to something like 774 or 664, so both grid and oracle users will be able to modify the files later;

• change sudoers file and allow grid to execute the commands needed as oracle without password and change shell script to include sudo;

• create a new listener on DB Home on another port and change TNSNAMES.ORA entry to point to the new port. Then extproc will be executed as OS user oracle. You will have to manually edit LISTENER.ORA on $OH and start it with lsnrctl, because listeners registered with srvctl will always be started by grid ;

• change main listener to the db home. I don't recommend that (see item above).

[EDIT] As pointed out by @AlexPoole and @jonearles, there are two other options that weren't fit for my case, but might be for others:

• if you run the script locally on sqlplus, setting ORACLE_SID, the FS access will be made by the OS user running sqlplus. So you can run as oracle, or some other user and fix the FS permissions;
• if you schedule a job on dbms_job scheduler as SYS, the task will be executed by oracle (this behavior may be version dependent, so further testing is needed).

Regards,

Daniel Stolf

Answer 2

On further investigation it runs the script as the OS user that started the session; but the server user, not the client osuser seen in v$session.

If you connect locally through SQL*Plus, without going through SQL*Net, the shell script runs as your own OS user, not grid or oracle, unless you're logged into the box as one of those. So when I execute the procedure as myself, the script reports i am apoole.

When you run remotely though, the OS user for the session is the listener owner, which by default in a Grid environment is going to be grid. And you see the environment of the grid user when the listener was started.

So if you're going to be executing this manually, remotely through a SQL*Net-connected client then the options in your own answer are valid. You can move the DB listener to run under the oracle account, or create a new listener under that account, and connect via that. Then the script will execute as oracle when invoked from any session connected via that listener. Or make the OS permissions/sudo work for you.

If you will or might execute it from a local session without going through SQL*Net then you'd need to make the OS permissions valid for any user that might invoke it - assuming you won't be running it from SQL*Plus launched from the oracle account. The listener isn't part of the picture, so the grid user isn't a factor.

---

This is when it's run as an anonymous block; as @jonearles pointed out in a comment on the question, the behaviour for scheduled jobs is different. By default it would execute the script as nobody, which would mean you'd have to relax the OS permissions even more.