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