running script in oracle from external directory

something like this?

also, where does Oracle come into this? looks like a unix scripting question

#!/bin/sh
/home/hbc_data/juggler/juggler.sh

Select allOpen in new window

since juggler.sh is already a script, I'm not sure what the added benefit is in creating another script just to call it.

maybe I'm misunderstanding your question

I want a procedure kind of thing in oracle with a script that goes to this directory and runs the juggler.sh

right now juggler.sh is run from an old system called avion, which I am converting to oracle.

You cannot run a script from inside the database until 10g and dbms_scheduler.

I believe you are on 9i so you are stuck with a Java Stored Procedure as a wrapper.

You can always set up Oracle as a cron user and use cron to run the script at predetermined times.

I believe you are on 9i so you are stuck with a Java Stored Procedure as a wrapper.

Any examples on that that I can use?

I would seriously look at cron.  It is much easier assuming you do not ned a triggering event.

The best sample of the Java approach was from Tom Kyte:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241

Any oracle forms experts have run the HOST built in ( in the forms. )

My forms is very old and rusty.  Didn't know it had a host command.

You'll need to wait and see if any other Experts have ever used it.

You cannot run a script from inside the database until 10g and dbms_scheduler.

I believe you are on 9i so you are stuck with a Java Stored Procedure as a wrapper.

Any examples that I can take a look?

https://www.experts-exchange.com/questions/21165304/Execute-Unix-command-from-a-pl-sql-procedure.html

I looked at this but it seems to be a very old one. Any recent examples?

>>Any examples that I can take a look?

re: http:#a38168074 ?

>>Any recent examples?

No.  Hardly anyone still uses 9i and this isn't necessary in 10g.  Any example will be old.  The Java wrapper hasn't changed.

You can always set up Oracle as a cron user and use cron to run the script at predetermined times

I would seriously look at cron.  It is much easier assuming you do not ned a triggering event

The this is here the user wants the ability to run that script. And I do not know how to do if I use cron

cron is for scheduled events.  If they want something more even-driven, like 'Click here to run', then it will not work.

The oracle forms host built_in in combination with a repeating timer could do the job, provided the application server is on the host where the juggler.sh is and you can manage that there is always 1 such oracle forms session running.

I would however prefer the cron option slightwv mentioned.
Both methods execute the command periodically.

If you  want to trigger the action  on uploading of juggler.upl  you will need the java-procedure and some database/unix security settings (i didn't look into them because we use a job-scheduling system that can be triggered from an oracle procedure) unless the application that does the uploading can also trigger  the command.
What application is used for the upload ?

The best sample of the Java approach was from Tom Kyte:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 

Java Result:

SQL> exec rc('/usr/bin/ps -ef');

java.lang.ArrayIndexOutOfBoundsException
  at Util.RunThis(Util.java:14)

PL/SQL procedure successfully completed

Any idea experts?

It appears there was a change from 8i.

Tom posted a fix here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

I went through the whole thing. But not sure where is the fix. Any help?

BEGIN
DBMS_JAVA.GRANT_PERMISSION
   ('HBC_DATA',
    'java.io.FilePermission',
      'c:\windows\system32\find.exe',
      'execute');
      
      dbms_java.grant_permission
        ('HBC_DATA',
        'java.lang.RuntimePermission',
        '*',
        'writeFileDescriptor' );
END;
/

create or replace and compile
  java source named "Util"
  as
  import java.io.*;
  import java.lang.*;
 
  public class Util extends Object
  {
  public static int RunThis(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;
 }
 }
 }
 /
All successful


create or replace
  function RUN_CMD(p_cmd in varchar2) return number
  as
  language java
  name 'Util.RunThis(java.lang.String) return integer';
/


create or replace procedure RC(p_cmd in varchar2)
  as
  x number;
  begin
  x := run_cmd(p_cmd);
  dbms_output.put_line('Got: ' || x );
  end;
/
ALL OK and Valid.

SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL> exec rc('c:\windows\system32\find.exe /?');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
has not been granted to HBC_DATA. The PL/SQL to grant this is dbms_java.grant_permission(
'HBC_DATA', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execu
te' )
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:13)
Got: -1

PL/SQL procedure successfully completed.

Can we change this to 500 instead of 250? I mean points

>>java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)

post the results of:
select * from dba_java_policy where grantee = 'HBC_DATA';

attaching result as txt file.
query.txt

Just as a test (do not leave it this way):

grant javasyspriv to HBC_DATA;

grant succeeded. How can I take it off?

>>grant succeeded

The grant succeeded or the grant allowed the procedure to run?

revoke javasyspriv from HBC_DATA;

I will now run the procedure and let you know

same error

SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL> exec rc('c:\windows\system32\find.exe /?');
java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute)
has not been granted to HBC_DATA. The PL/SQL to grant this is dbms_java.grant_permission(
'HBC_DATA', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execu
te' )
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:13)
Got: -1

PL/SQL procedure successfully completed.

I suppose you can do what the error tells you to do:
exec dbms_java.grant_permission('HBC_DATA', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execu
te' );

I have gut feeling this opens up a pretty big security hole but I am not a Java person.

Try it as a test, if it works, then you can research what security issues '<<ALL FILES>>' opens up.

SQL> exec dbms_java.grant_permission('HBC_DATA', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'exe
cute' );

PL/SQL procedure successfully completed.

SQL> exec rc('c:\windows\system32\find.exe /?');
java.io.IOException: can't exec: c:\windows\system32\find.exe doesn't exist
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.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:13)
Got: -1

PL/SQL procedure successfully completed.

>>can't exec: c:\windows\system32\find.exe doesn't exist

Do you have find.exe on your system?  Is it in that folder?

If not, pick some exe file you have and execute that.  This is only a test for now.

Anything that echos something to the screen and is an executable will work.

I have : c:\windows\system32\find. But no idea why this error
screen-print.JPG

Is that screen shot from the database server?

No local PC

>>No local PC

Think about it...  how can the database server execute something on your local PC?

Anything the database executes, needs to be executed on the database server.

When you run that Java code, it is executing inside the database and is executed as the OS owner of the database.  On windows this is typically SYSTEM.

[oracle@hbc-maroon bin]$ pwd
/usr/bin

Can I do add .exe  to

BEGIN
DBMS_JAVA.GRANT_PERMISSION ('HBC_DATA', 'java.io.FilePermission', /usr/bin', 'execute');
END;

>>Can I do add .exe  to

You need to add the shell program you want to use.  

So if you want /usr/bin/sh, add that:
exec DBMS_JAVA.GRANT_PERMISSION ('HBC_DATA', 'java.io.FilePermission', /usr/bin/sh', 'execute');

Then what you want to run is:
exec rc('/bin/sh /home/hbc_data/juggler/juggler.sh');

this is the actual path

/home/hbc_data/juggler
$ ls
juggler.dat  juggler.log  juggler.sh   juggler.upl

before doing the sh I want to test something here. Can I have add any temp file here in this path to test?

>>this is the actual path

I know... isn't that what I posted in http:#a38175918 ?

>>Can I have add any temp file here in this path to test?

You can do whatever you want.

You need to understand the code you are executing and the permissions you are granting.

Once you understand what it is doing, creating test cases are simple.

for testing I did this

begin
   dbms_java.grant_permission
    ('HBC_DATA',
     'java.io.FilePermission',
     '/bin/ps',
     'execute');
   
     dbms_java.grant_permission
       ('HBC_DATA',
        'java.lang.RuntimePermission',
        '*',
        'writeFileDescriptor' );
end;

And ran the rest.  
Now
exec rc('/bin/sh');  will this be ok? to test?

>>will this be ok? to test?

Do you understand what the commands you are executing are doing?

In the first PL/SQL Block, you grant execute on '/bin/ps'.  

Now if you try: exec rc('/bin/sh');

If you don't grant execute on '/bin/sh', do you think you will be able to execute it?

That said: /bin/sh is just a shell.  It will do nothing.  You need to provide it something to do.

I did test

$ pwd
/bin
$ whoami
hbc_data
--------------------
begin
   dbms_java.grant_permission
    ('HBC_DATA',
     'java.io.FilePermission',
     '/bin/whoami',
     'execute');
   
     dbms_java.grant_permission
       ('HBC_DATA',
        'java.lang.RuntimePermission',
        '*',
        'writeFileDescriptor' );
end;

ALL the rest successful.

SQL> set serveroutput on size 1000000
SQL> exec dbms_java.set_output(1000000)

PL/SQL procedure successfully completed.

SQL> exec rc('/bin/whoami');
oracle
Got: 0

PL/SQL procedure successfully completed.

What do you think?

>>What do you think?

What do I think about what?
Did you get the results you were expecting?

This shows you can run 'whoami'.  Still not sure how this tests the ability to run your shell script.

Create a sample shell script that performs some task.  See if you can execute that.

for example: /tmp/testme.sh
echo Hello World > /tmp/testme.output

Then execute that from the stored procedure.  If you have a file in /tmp called testme.output with "Hello World" in it, then the shell script ran.


After you get done testing, don't forget to clean up all these Java permissions you are granting.

SQL> exec rc('/tmp/testme.sh');
java.io.IOException: can't exec: /tmp/testme.sh lacks world privilege
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.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:13)
Got: -1

PL/SQL procedure successfully completed.

SQL>

$ ls -l testme*
-rw-rw-rw-   1 hbc_data   users           18 Jul 11 13:37 testme.sh
$ pwd
/tmp
$ chmod 777 testme.sh
$ ls -l testme*
-rwxrwxrwx   1 hbc_data   users           18 Jul 11 13:37 testme.sh
$
SQL> exec rc('/tmp/testme.sh');
Got: 255

PL/SQL procedure successfully completed.

Did not give me HELLO WORLD

>>lacks world privilege

Error is self explanatory:  Does the oracle user have execute on it????????????

chmod 755 /tmp/testme.sh

chmod 755 /tmp/testme.sh

SQL> exec rc('/tmp/testme.sh');
Got: 255

PL/SQL procedure successfully completed.

What permission did you grant?

If you granted execute to /bin/sh, try:

exec rc('/bin/sh /tmp/testme.sh');

SQL> exec rc('/bin/sh /tmp/testme.sh');
HELLO WORLD
Got: 0

PL/SQL procedure successfully completed.

SQL>

So, are we done?

one more question.ow I want to try juggler.sh

Can I give execute to path like /home/hbc_data/juggler? or any other expansion

begin
   dbms_java.grant_permission
    ('HBC_DATA',
     'java.io.FilePermission',
     '/home/hbc_data/juggler/juggler.sh',
     'execute');
   
     dbms_java.grant_permission
       ('HBC_DATA',
        'java.lang.RuntimePermission',
        '*',
        'writeFileDescriptor' );
end;

>>Can I give execute to path like /home/hbc_data/juggler? or any other expansion

Are you not understanding what these commands are doing or are you just blindly running what I'm posting?

Again, think about what you are doing.

What did you just test?  was the command passed to rc
exec rc('/tmp/testme.sh');
or
exec rc('/bin/sh /tmp/testme.sh');

Which one worked?

The second.  You granted execute on '/bin/sh'  that executes the script.

All that said:
Do you need to grant execute on '/home/hbc_data/juggler/juggler.sh'?
Does granting execute on '/bin/sh' open up any security holes?  
   by the way, the answer to that last question is YES!!!

One last test:
make /tmp/testme.sh like:
#!/bin/sh
echo "Hello" > /tmp/testme.output


Then run:
 dbms_java.grant_permission
    ('HBC_DATA',
     'java.io.FilePermission',
    '/tmp/testme.sh',
     'execute');

exec rc('/tmp/testme.sh');

See if you have /tmp/testme.output

SQL> exec rc('/home/hbc_data/juggler/juggler.sh');
java.io.IOException: can't exec: /home/hbc_data/juggler/juggler.sh lacks world privilege
at oracle.aurora.java.lang.OracleProcess.create(OracleProcess.java)
at oracle.aurora.java.lang.OracleProcess.construct(OracleProcess.java)
at java.lang.Runtime.execInternal(Runtime.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:13)
Got: -1

PL/SQL procedure successfully completed.

I tried

$ chmod 755 /home/hbc_data/juggler/juggler/sh
chmod: can't access /home/hbc_data/juggler/juggler/sh

>>chmod 755 /home/hbc_data/juggler/juggler/sh

See your typo?  Look for it.

Is blindly changing permissions a good thing?
Should 'world' have execute on that script?

One last test:
make /tmp/testme.sh like:
#!/bin/sh
echo "Hello" > /tmp/testme.output


Then run:
 dbms_java.grant_permission
    ('HBC_DATA',
     'java.io.FilePermission',
    '/tmp/testme.sh',
     'execute');

exec rc('/tmp/testme.sh');

See if you have /tmp/testme.output


SQL> exec rc('/tmp/testme.sh');
Got: 0

PL/SQL procedure successfully completed.


vi testme.sh

#!/bin/sh
echo "Hello">/tmp/testme.output

You are not commenting on the understanding of what you are executing.

>>See if you have /tmp/testme.output

I don't see where you checked...

from sqlplus:

SQL> host rm /tmp/testme.output
SQL> exec rc('/tmp/testme.sh');
SQL> host cat /tmp/testme.output  | more


Do you see the output you expect?

Please do not post what you see.  I'm trying to get you to understand what you are doing.

Did it do what you expected?

I just see a window blink and go

>>I just see a window blink and go

Then maybe just look from a unix prompt in another window?

cat testme.output
Hello

Is this what you expected?
Are we done?

Don't forget to clean up all the test permisions that have been granted.

based on everything I did this

SQL> exec rc('/bin/sh /home/hbc_data/juggler/juggler.sh');
Got: 0

PL/SQL procedure successfully completed.

I thought it would run juggler.sh and the oputput would be juggler.dat

Remember what I asked above:  Is gratning execute on '/bin/sh' a good thing to do?

>>I thought it would run juggler.sh and the oputput would be juggler.dat

Goes back to understanding...

That would depend on what juggler.sh told it to do.
What command created the juggler.dat file and where did it create it?

juggler.sh creates juggler.dat and juggler.dat is created in /home/hbc_data/juggler

>>juggler.dat is created in /home/hbc_data/juggler

Are you sure the script tells the output to go there or just to that file?

You need to look at how that script actually creates the output file.  Does it tell the output where to  go or just the file to go into?

juggler.sh is basically some commands to edit juggler.upl and give the juggler.dat output.
Enclosing juggler.sh
juggler.sh.doc

$ cd $HOME
$ pwd
/home/hbc_data

>>$ cd $HOME

See if you can tell me why it isn't working.

What Unix user is actually executing the script?  re: http:#a38176391

$ ls -l juggler.sh
-rwxrwxrwx   1 hbc_data   users         3718 Feb 14 12:39 juggler.sh

That does not answer my question.

When you run that Java stored procedure, what is the unix user that is running it?  Look at what you posted in http:#a38176391 when the program returned the results of whoami.

its oracle. Any thing that I have to do from my side to make it work. When I run this wrapper that it gives me juggler.dat output?

>>its oracle

Correct.  Now do you understand why juggler.dat isn't in /home/hbc_data/juggler?

>>Any thing that I have to do from my side to make it work.

Change juggler.sh to not put the output in $HOME.  If you want it in a specific directory, tell the script to put it there.

Now I have changed the juggler.sh

> /home/hbc_data/juggler/juggler.dat
 I will try my script

$ pwd
/home/hbc_data/juggler
$ ls -l juggler*
-rwxrwxrwx   1 hbc_data   users         6392 Jul 12 08:41 juggler.dat
-rw-r-----   1 hbc_data   users        11040 Feb 14 12:24 juggler.log
-rwxrwxrwx   1 hbc_data   users         3752 Jul 12 08:37 juggler.sh
-rwxrwxrwx   1 hbc_data   users        58178 Feb 14 12:24 juggler.upl

It worked

>>rwxrwxrwx

I want to again point out that having world 'rwx' can be dangerous.

Now I have this complete script that is working. Since everything is in the database, now can I give a user a button where in I call my

exec rc('/bin/sh /home/hbc_data/juggler/juggler.sh');
juggler.txt

so what would be the appropriate permissions on juggler.sh?

>>exec rc('/bin/sh

You are not understadning what I am posting.

I have pointed out many times you should not grant execute on /bin/sh.  This is a pretty big security hole.  Any user connecting to the database can easily trash your system.

Do not use /bin/sh.  You have tested a work-around and showed it worked.

Add #/bin/sh to the top of juggler.sh like you did in testme.sh.  Grant java execute permissions in juggler.sh and just call it.

>>so what would be the appropriate permissions on juggler.sh?

Minimal appropriate for your system.  world is typically bad since ANY user on the system can run it.

Important users should also probably not be in the general users group.

Personally I would create a 'special' group for this application.  Then add the oracle unix user to that group.  Then set permissions on all the juggler stuff to 750.

thanks

I changed juggler.sh chmod 666. So its working fine.

666 gives world execute so any user on the server can run it.