Yep! Tom you have solved the problem, Thanks very much !

Tom,

Is it possible to use a similar java procedure to copy files using UNC paths ?. If so can you give an example. I used the same sp as in this example and although it allows to copy files from the same server it doesn't seem to be able to use UNC paths.

Rgds, Chris

This example will be very useful for us for running a OS command with in a plsql program.

I followed the example and created all the java stored procedure and functions successfully. Also I have given the permissions as advised in the note. But on the last step I got error:

=====================================================
legd> !cat /tmp/zl.sh
#!/bin/ksh
/usr/bin/ipcs -a | /usr/ucb/head >/tmp/ipcs.out

legd> !ls -l /tmp/ipcs.out
/tmp/ipcs.out: No such file or directory

legd> exec dbms_output.put_line(run_cmd('/tmp/zl.sh'));
java.lang.ArrayIndexOutOfBoundsException
at MyCommand.RunThis(MyCommand:13)
-1
=====================================================
My MyCommand java stored procedure is exactly same as the example as below:
--------------------------------------------------
CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "MyCommand"
AS
import java.io.*;
import java.lang.*;

public class MyCommand extends Object
{

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

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

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 line 13 is ==> int rc = -1;

Please advise.

Thank you

Zhuang

Followup to previous question.

How would you pass parameters to the shell script.

Hi Tom,
I am having problem with executing OS command.

gaurang@oracle9>EXEC RC ('exp userid=gaurang/gaurang tables=external_table file=E:\ADMIN');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util.java:11)

PL/SQL procedure successfully completed.



I gave user GAURANG all required permission on server.

EXEC DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ADMIN','read,write');

EXEC DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ADMIN','read,write');

EXEC dbms_java.grant_permission(USER, 'java.lang.RuntimePermission','*', 'writeFileDescriptor' );

We have ORACLE HOME directory E:\ORACLE on server

I have checked DBA_JAVA_POLICY table and it is showing me all permission.

Please advice.

Thanks

Tom ,

I forgot to add one permission

EXEC DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ORACLE\BIN\EXP.EXE,'EXECUTE');

Thanks,

Hi Tom is a way to give permission to any file and directory, instead of file by file
Instead

EXEC
DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','E:\ORACLE\BIN\EXP.EXE'
EXECUTE');

Something like
EXEC
DBMS_JAVA.GRANT_PERMISSION(USER,'java.io.FilePermission','%'
EXECUTE');

Thank you

Yes that is what I wanted to know

Hi Tom,

Thank you very much for your previous help.

Regarding your wonderful RC routine (java stored procedure) to run OS command in your book. I have deployed all related objects and everything run perfectly.

Due to security concern, we would like to use dbms_java.grant_permission as secured as possible,

For moving files from one directory to the other, we only talked about move file here,
begin
rc('c:\winnt\system32\cmd.exe /c move c:\from\fromtest1.xml
c:\to\fromtest1.xml');
end;

What will be most secured way to do DBMS_JAVA.GRANT_PERMISSION
instead of
BEGIN
DBMS_JAVA.GRANT_PERMISSION
('USER',
'java.io.FilePermission',
'c:\winnt\system32\cmd.exe,
'execute');
END;

For example, can we do

BEGIN
DBMS_JAVA.GRANT_PERMISSION
('USER',
'java.io.FilePermission',
'c:\winnt\system32\cmd.exe /c move c:\from\*.xml :\to\*.xml,
'execute');
END;

Please advise the most secured way...

Thanks for help.

Here is my code some how it doesn't work either on 8.1.7 or 9.2.0.4. I got error message "java.io.FileNotFoundException: Permission denied". Any ideas why grant_permission doesn't work.

Thanks in advance.

CREATE OR REPLACE and COMPILE JAVA SOURCE
NAMED "Oscar"
AS
import java.io.*;
import java.lang.*;
public class Oscar
{
// return a quotation from Oscar Wilde
public static String quote() {
FileOutputStream out; // declare a file output object
PrintStream p; // declare a print stream object
try
{
// Create a new file output stream
// connected to "myfile.txt"
out = new FileOutputStream("/opt/mis/apps/oraApps/OAKWCAP2/javatst.txt");
// Connect print stream to the output stream
p = new PrintStream( out );
p.println ("This is written to a file");
p.close();
out.close();
}
catch (Exception e)
{
System.err.println (e);
}
System.out.println ("Testing.....");
return "I can resist everything except temptation.";
}
}
/

CREATE or REPLACE FUNCTION oscar_quote RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Oscar.quote() return java.lang.String';
/

begin
dbms_java.grant_permission
( 'APPS',
'java.io.FilePermission',
'/opt/mis/apps/oraApps/OAKWCAP2/-',
'write' );
end;
/
declare
x varchar2(100);
begin
dbms_java.set_output(2000);
x := oscar_quote();
dbms_output.put_line(x);
end;
/

works fine for me.  perhaps the Oracle software owner does not have privs to write to that directory (it runs as "oracle", not as "you")

when i run this against a directory ORACLE (that account running my software) is not allowed to write to, I do get:

PL/SQL procedure successfully completed.
 
<b>java.io.FileNotFoundException: Permission denied</b>
Testing.....
I can resist everything except temptation.


which should be expected.



ops$tkyte@ORA9IR2> crEATE OR REPLACE and COMPILE JAVA SOURCE
  2  NAMED "Oscar"
  3  AS
  4  import java.io.*;
  5  import java.lang.*;
  6  public class Oscar
  7  {
  8    // return a quotation from Oscar Wilde
  9    public static String quote() {
 10      FileOutputStream out; // declare a file output object
 11                  PrintStream p; // declare a print stream object
 12                  try
 13                  {
 14                          // Create a new file output stream
 15                          // connected to "myfile.txt"
 16                          out = new
 17  FileOutputStream("/tmp/javatst.txt");
 18                          // Connect print stream to the output stream
 19                          p = new PrintStream( out );
 20                          p.println ("This is written to a file");
 21                          p.close();
 22              out.close();
 23                  }
 24                  catch (Exception e)
 25                  {
 26                          System.err.println (e);
 27                  }
 28                                  System.out.println ("Testing.....");
 29      return "I can resist everything except temptation.";
 30     }
 31  }
 32  /
 
Java created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE or REPLACE FUNCTION oscar_quote RETURN VARCHAR2
  2  AS LANGUAGE JAVA
  3  NAME 'Oscar.quote() return java.lang.String';
  4  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2              dbms_java.grant_permission
  3              ( USER,
  4               'java.io.FilePermission',
  5               '/tmp/-',
  6               'write' );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2  x varchar2(100);
  3  begin
  4  dbms_java.set_output(2000);
  5  x := oscar_quote();
  6  dbms_output.put_line(x);
  7  end;
  8  /
Testing.....
I can resist everything except temptation.
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> !ls -l /tmp/java*
-rw-rw-r--    1 ora9ir2  ora9ir2        26 May 11 07:53 /tmp/javatst.txt
 

Thank you Tom, I have changed the directory where the user account "oracle" has write privileges and it worked just fine. These are my observations..

When I picked the directory that has privileges drwxrwxr-x, which was created by user account: applmgr but this user belongs to the group "DBA" and so the "oracle" user account as well. When you look at the permissions it does say groups does have write access to this directory but why does it say permission denied.

How can we see a list of privileges granted to a user from dbms_java.grant_permission?

Thanks!

scott@LTST.NERI.ORG> exec rc('exp userid=scott/password TABLES=(emp, dept) rows=y file=d:\oracle\test_exp.dmp');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
has not been granted by dbms_java.grant_permission to
SchemaProtectionDomain(SCOTT|PolicyTableProxy(SCOTT))
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util:14)

smccarthy@LTST.NERI.ORG> l
1* select * from dba_java_policy where grantee = 'SCOTT'
smccarthy@LTST.NERI.ORG> /

KIND GRANTEE TYPE_ TYPE_NAME NAME ACTION ENABLED
-------- ---------- ----- ------------------------------ -------------------- -------------------- -
GRANT SCOTT SYS java.io.FilePermission d:\oracle\ORA817\bin execute ENABLED
\exp.exe

GRANT SCOTT SYS java.lang.RuntimePermission * writeFileDescriptor ENABLED

smccarthy@LTST.NERI.ORG>

Thanks Tom.

Tom,

I am trying to execute the following:
exec rc('c:\windows\system32\cmd.exe /c C:\PROGRA~1\Java\jdk1.5.0_10\bin\java.exe ButtonEncryption C:\OpenSSL\bin\my-pubcert.pem C:\OpenSSL\bin\my-prvkey.p12 C:\OpenSSL\bin\paypal_cert_pem.txt password "cmd=_cart,upload=1,business=abc@abc.com,item_name_1=cool item 1,item_number_1=123456,quantity_1=1,amount_1=29.99,currency_code=USD,shipping=10.95,cert_id=ABCDEFGHIJ,cancel_return= http://www.abc.com/a?b=1&c=1" C:\test\java_bn.html');

However nothing really gets executed.

At C Prompt:

C:\PROGRA~1\Java\jdk1.5.0_10\bin\java.exe ButtonEncryption C:\OpenSSL\bin\my-pubcert.pem C:\OpenSSL\bin\my-prvkey.p12 C:\OpenSSL\bin\paypal_cert_pem.txt password "cmd=_cart,upload=1,business=abc@abc.com,item_name_1=cool item 1,item_number_1=123456,quantity_1=1,amount_1=29.99,currency_code=USD,shipping=10.95,cert_id=ABCDEFGHIJ,cancel_return= http://www.abc.com/a?b=1&c=1" C:\test\java_bn.html

runs fine.

Please advise.

Thanks

Found the problem to my earlier question.
The classpath was not getting set.

I am able to execute the script from PL/SQL however that script has to execute a following command
su - oracle -c 'emctl start blackout bkp1' everything in script is executing except this command..
Please help me..

That's what exactly my problem is. I am not getting any error message any where. I have a grid agent installed under os user named "oragc" and we have databases installed under os user named "oradb". I am trying to execute "emctl status agent" command from pl/sql. I have this command written in my shell script which is owned by oragc user as "sudo -u oragc emctl status agent". I got password less sudo set. and I have 775 privileges on the script at os level. the script has other commands like ls, pwd etc. all are executing and I can see output on sqlplus prompt but for this sudo command. no errors nothing.
On another test server I had agent and database installed under same OS user and script works fine. because of no error message in above mentioned scenario, I am directionless. I tried setting suid bit also, but it didn't work out.

Is there any data dictionary to view whether permission is granted or not for dbms_java.grant_permission.

Hello All,

I want to run copy command (Unix) from PL SQL.
So far I did follwoing:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED WASABI."Util" as import java.io.*; --- No change in it


CREATE OR REPLACE function WASABI.EXECUTE_SYSTEM_COMMAND ( cmd in varchar2 )
return number
as
language java

-- name 'Util.RunThis( java.lang.String ) return integer' ;
name 'org/apache/regexp/REUtil.RunThis( java.lang.String ) return integer' ;
/

Here is my PL SQL script which call copy command as Follow:

Declare

COMMAND_LINE varchar2(32767) ;
RETURN_CODE integer := 0;

begin

COMMAND_LINE := '/bin/cp test.txt test.out';

RETURN_CODE := EXECUTE_SYSTEM_COMMAND( COMMAND_LINE ) ;
DBMS_OUTPUT.PUT_LINE( 'Return Code is ' || RETURN_CODE) ;
if ( RETURN_CODE = 100 ) then
DBMS_OUTPUT.PUT_LINE( 'Return Code is a warning - review ' || RETURN_CODE) ;
elsif ( RETURN_CODE <> 0 ) then
DBMS_OUTPUT.PUT_LINE( 'ERROR - encountered when executing ; exit code = [' || RETURN_CODE || ']' ) ;
end if ;


EXCEPTION
when others then

DBMS_OUTPUT.PUT_LINE('Unix copy command failed: ' ||sqlerrm);

end;
/

I get message
PL/SQL procedure successfully completed.
but did not copy that file and also its return code is -1.

When I try
COMMAND_LINE := '/usr/bin/sh testpl.sh';

Here is testpl.sh file.

#!/bin/sh
set -x
cp test.txt test.out

Please let me know if I miss something or doing wrong.

Thanks,

EXCEPTION
   when others then

      DBMS_OUTPUT.PUT_LINE('Unix copy command failed: ' ||sqlerrm);

Hi Tom, I am trying to execute a shell script on the OS using Runtime.getRuntime().exec.

---------------------
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 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. 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;01: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:*.7z=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;35:*.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:*.aac=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/rdbms/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?

BTW, the same code ran fine on a single instance database with no grid infra (datafiles on FS). After we migrated the database to ASM, we started facing this problem.

Tom, I figured what was happening. The execution was going through EXTPROC, and thus through the Grid Listener as grid user. Creating a second listener on the DB HOME fixed the problem, now I just have to figure out if I keep the second listener, or change the FS permissions.

Kind regards,
Daniel Stolf

Calling Stack:
PL/SQL Block
-> Java Runtime.Exec
--> Shell Script
---> imp job with ignore = y


Question: 
Why can the PL/SQL Block see the value (1) loaded by imp? It should see the old value (999) due to read consistency = beginning of PL/SQL block, no?


Code:
select * from t_import;
update t_import set col1 = 999;
commit;
select * from t_import;

prompt ====== start block ======
declare
  ret number:=0;
begin
  dbms_java.set_output(10000);
  dbms_output.put_line ('IN BLOCK: BEFORE IMPORT');

  -- Import starten
  ret:=kutil.run_prog(program_name => '/tmp/imp_zvo_sync '||
                                      'rt/rt@db01 '||
                                      '/tmp/t_import.dmp',
                      arguments    => '',
                      sync         => true);
  dbms_output.put_line('IN BLOCK: RETURN VALUE: '||to_char(ret));

  -- Zugriff auf t_import im selben block
  select min(col1)
  into ret
  from t_import;

  dbms_output.put_line('IN BLOCK: Col1 Value: '||to_char(ret));
end;
/
prompt ====== end block ======

-- Zugriff auf t_import ausserhalb des Blocks
select * from t_import;

commit;

-- Zugriff auf t_import in einer neuen Transaktion
select * from t_import;



Result:
SQL> @test4.sql

      COL1
----------
         1


1 row updated.


Commit complete.


      COL1
----------
       999

====== start block ======
IN BLOCK: BEFORE IMPORT
IN BLOCK: RETURN VALUE: 0
IN BLOCK: Col1 Value: 1

PL/SQL procedure successfully completed.

====== end block ======

      COL1
----------
       999
         1


Commit complete.


      COL1
----------
       999
         1