RE: java package to run OS command

Dick, harsh words, hmmm? Powerful tools can also be powerful weapons in the wrong hands. But don't blame the toolmaker.

John, the reason that running an OS command is such a hassle is that it can be horribly destructive to your server. An OS command that runs from a Java Stored Procedure such as the one from www.oracle-base.com that I gave you or the one that Tom Kyte wrote and published on Ask Tom will have all the permissions of the oracle database. Which means that it can be abused to absolutely destroy the database, just as in Dick's example.

Doing this with an external procedure as we do is also dangerous. If you use a separate Oracle Net listener for them, instead of LISTENER, and have another user besides the database owner (usually oracle) start that listener, and password protect the listener, you can at least have some control over the permissions, which will be those of the user that starts the listener. People who run Oracle under Windows may be out of luck here - it is harder to get this running under a less privileged account in Windows.

We do one more thing for security. We have a special schema in the database called COMMON that owns tables and stored procedures that are usable by all applications. We put the stub program for the external procedure that executes OS commands in a package as a private procedure. The public procedure that calls this private procedure can examine the OS command first. Certain commands, like "rm -fr" are absolutely forbidden, and raise an exception. Other commands are checked against the PRODUCT_PROFILE table which we set up much as for restrictions for what commands certain users may run in SQL*Plus. If the current user (or schema) does not have the explicit privilege to run that OS command, we raise an exception.

You could easily put a similar protective shell around the Java version of the same thing.

-----Original Message-----

Sent: Thursday, December 04, 2003 12:29 PM To: Multiple recipients of list ORACLE-L

John,

        I agree and am encouraging the external C procedures since their simpler, and can create log files as well. BTW: Whoever authored the Java procedure on that web page should be shot. I can just see someone passing 'rm -fr $ORACLE_HOME' to it.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----

Sent: Thursday, December 04, 2003 12:00 PM To: Multiple recipients of list ORACLE-L

yeah, I'm trying to get away from C external procedures ...but java is beginning to look just as much a hassle.

Why is running a OS command such hassle?

-----Original Message-----

Sent: 04 December 2003 15:40
To: Multiple recipients of list ORACLE-L

Here's another link to a good Java Stored Procedure for this: http://www.oracle-base.com/Articles/8i/ShellCommandsFromPLSQL.asp

But the same caveats apply. We're using an external procedure written in C for this instead of Java, and I use a shell that checks PRODUCT_PROFILE for authority to run the command, before it will call the extproc. I'm happy to share source code with anyone interested it doing it this way, but external procedures are a bit harder to set up than Java Stored Procedures and open you to a few security hazards.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  INET: JohnF_at_smdi.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).