从PL/SQL调用Shell脚本，但是Shell以网格用户而不是oracle的身份执行 [英] Calling shell script from PL/SQL, but shell gets executed as grid user, not oracle
问题描述
我正在尝试使用Runtime.getRuntime().exec从Oracle数据库内部执行Shell脚本.
I am trying to execute a shell script from inside the Oracle database using Runtime.getRuntime().exec.
在Red Hat 5.5上运行的Oracle 11.2.0.4 EE
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;
}
}
}
/
在Java上授予数据库用户SCOTT的权限:
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
shell脚本executa.sh，这是我要执行的脚本:
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 .
问题是，当我执行过程pr_executa_host时，它将外壳脚本作为网格os运行 用户，不是oracle！ (尽管它保留oracle环境变量，就像执行'su grid -m'一样 在执行Shell脚本之前)
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
为什么数据库内部的Java以网格用户而不是Oracle的身份调用unix命令?
Why is the java inside the database calling unix commands as grid user, not oracle?
非常感谢您的帮助， 斯托尔夫
Thanks a lot for your help, Stolf
推荐答案
注释中指出，问题在于Runtime.getRuntime().exec在EXTPROC中运行，因此在Grid Listener中运行.由于我们在新配置中在DB和GRID之间隔离了OS用户，因此这在FS上引起了权限问题.
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:
-
修复FS权限，以允许网格用户写入文件并将umask更改为774或664之类的内容，因此网格和oracle用户都将能够在以后修改文件；
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;
更改sudoers文件，并允许网格在不使用密码的情况下执行oracle所需的命令，并更改shell脚本以包含sudo;
change sudoers file and allow grid to execute the commands needed as oracle without password and change shell script to include sudo;
在DB Home上的另一个端口上创建一个新的侦听器，并将TNSNAMES.ORA条目更改为指向新端口.然后，将以操作系统用户oracle的身份执行extproc.您将必须在$ OH上手动编辑LISTENER.ORA并以lsnrctl开头，因为向srvctl注册的侦听器将始终由grid;
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).
正如@AlexPoole和@jonearles指出的那样，还有其他两个选项不适合我的情况，但可能适合其他情况:
As pointed out by @AlexPoole and @jonearles, there are two other options that weren't fit for my case, but might be for others:
- 如果您在sqlplus上本地运行脚本，并设置ORACLE_SID，则FS访问将由运行sqlplus的OS用户进行.因此，您可以以oracle或其他用户身份运行，并修复FS权限；
- 如果您在dbms_job调度程序上将作业调度为SYS，则该任务将由oracle执行(此行为可能取决于版本，因此需要进一步测试).
此致
丹尼尔·斯托尔夫
这篇关于从PL/SQL调用Shell脚本，但是Shell以网格用户而不是oracle的身份执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
