Several times during my career, I have encountered the need or the question of how to run operating systems commands from the Oracle Database. Doing research on the topic – there are two main ways to accomplish this task.
Java or DBMS_SCHEDULER
Once Oracle included JAVA in the database (v8 + review the version features for each edition), it is possible to execute Java based commands that can reach all the way back to the file system. My first encounter of this was in an Ask Tom post by Tom Kyte.
Since that time, there have been several other documented ways to use the same general Java method. As versions of the database have improved and added security features, not all of the historically documented techniques work as they did. A very good project that is maintained by Carsten Czarski has kept up with some of those features. The project is named Oracle PL/SQL executing OS Commands.
Since Oracle 10g (and probably earlier) there has been the capability to create scheduler jobs that can execute a job type of executable. With the right grants, you can effectively identify a command that the Oracle database will execute. In 11g this was improved to include a Credential parameter option to allow the job to be run with a user that is less privileged than the full oracle engine user. NOTE: I have had reports of this being challenging for individuals using LDAP on their Linux boxes as the credential as not validated via the OS.
There is a whole host of pros and cons and other considerations for both of these methods like:
* Too powerful a user to run os commands
* Limiting the commands that can be run
* RAC configurations
This brings us to the huge warning. The reason this feature makes some developers and security professionals worry is because of the RISK involved. Once you open the door to the operating system, many boundaries to security become questioned. The JAVA method runs as the same user the database is running as. The DBMS_SCHEDULER method can run as the Oracle user or any other user defined. OS commands with the correct permissions can cross all schemas, logs, and even the entire network. ANY hooks to the operating system must be done with extreme care. Research the security features around both the Java and DBMS_SCHEDULER methods. They both have options to mitigate risk. This is a very sharp tool that can cut you badly if you are not careful.
If you have other good examples or notes on executing host commands from the database, please share by leaving a comment below.