SQL – A shell script is invoked from PL/SQL, but the shell executes as a grid user instead of an oracle

A shell script is invoked from PL/SQL, but the shell executes as a grid user instead of an oracle… here is a solution to the problem.

A shell script is invoked from PL/SQL, but the shell executes as a grid user instead of an oracle

I’m trying to execute a shell script from inside an 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;
  }
  }
}
/

Grant db user SCOTT permissions on java:


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

shell script executa.sh, this is the script I want to execute:


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

Directory permissions:


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 problem is that when I execute procedure pr_executa_host, it runs shell scripts in grid OS
Users, not Oracle! (Although it retains the oracle environment variable, like it did ‘su grid -m.’)
Before executing the shell script).

Because grid does not have write access to directories and files, scripts do not
Do everything and the test file remains the same. 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 Java in the database using the grid user to invoke Unix commands instead of Oracle?

Thank you very much for your help,
Stole away

Solution

As noted in the comment, the problem is that Runtime.getRuntime().exec runs through EXTPROC and therefore via Grid Listener. Since we have OS user isolation between DB and GRID on the new configuration, this raises permissions issues on FS.

One of the ways to solve this problem is:

  • Fix the FS permissions to have the grid user write to the file and change umask to something like 774 or 664 so that both the grid and oracle users can modify the file later;

  • Change the sudoers file and allow the grid to execute the required commands like oracle without a password, and change the 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. Extproc will then execute as the OS user oracle. YOU WILL HAVE TO MANUALLY EDIT THE LISTENER ON $OH. ORA and start it with lsnrctl because listeners registered with srvctl will always be started by grid;

  • Change the primary listener to the database home page. I do not recommend this (see above).

[edit]
As @AlexPoole and @jonearles point out, there are two other options that don’t work for my situation, but might work for others:

  • If you run scripts locally on sqlplus, set up the ORACLE_SID AND FS ACCESS WILL BE MADE BY THE OPERATING SYSTEM USER RUNNING SQLPLUS. Therefore, you can run and repair FS permissions as oracle or another user;
  • If you schedule a job on dbms_job scheduler as SYS, the task will be performed by Oracle (this behavior may depend on the version and therefore requires further testing).

Greetings,

Daniel Stoff

Related Problems and Solutions