Oracle database server does not allow PL/SQL code to execute shell scripts or OS commands for security basis. To solve needing for ftp content, mailing or write/read info through files, there are several supplied packages that allow you to do this (UTL_FILE, UTL_FTP, UTL_SMTP, and so on).
But once I needed to allow a remote execution of a binary file, and I had to configure a workaround to this security rule like this: Allowing PL/SQL to run external compiled C code as an external procedure.
The following steps show how to set it up. Which is a great thing (what joy). But as you will read further on, this can cause tears (not of joy). So be warned to be extremely careful about how, what and who gets access.
First of all, listener.ora and tnsnames.ora files need to include a naming resolution to invoce the program extproc and, through it, make the database able to run external libraries.
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
[b] (SID_NAME = extproc_agent)
(ORACLE_HOME = E:\oracle\ora92)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ANY")[/b]
(ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC))
(SID = extproc_agent)
So, Oracle could be able to find a service called "extproc_agent" in the listener, and call it through remote procedure calls in our PL/SQL code.
The next step will be implement a basic C code to execute a shell command like this:
void __declspec(dllexport) sh(char *);
void sh(char *cmd)
and, using a C compiler such as old Borland C compiller or similar and create a ddl for it
bcc32 -WD shell.c
implib shell.lib shell.dll
At this point we have the following:
1. A network configuration that allows the database to invoque execution of external libraries.
2. Our own OS library (in Unix, we would call it shell.so, as example) that executes the OS command passed through parameter.
The next step is to link a library from Oracle database to the compiled C code as ddl using the remote procedure calls through the listener, previously set up, and create a function/procedure that calls it as external procedure.
SQL> create library shell_lib is 'C:\Borland\BCC55\shell.dll';
SQL> create or replace procedure shell (
2 cmd IN varchar2)
3 as external
4 library shell_lib
5 name "_sh"
6 language C
7 parameters (cmd string);
Now, we have a PL/SQL procedure that will be able to execute the IN parameter to the OS level, as oracle linux user, with its account privileges.
Well, It was a good solution to execute code using the new "shell" procedure, but recently I've seen a similar implementation on a linux machine, using a function instead a procedure, and all oracle users in the database had privileges to execute it.
It revealed a potential danger to the system if this kind of process architecture is not under a strict control.
I made then just a simple test. Connected as an end user, I launched the following query and wonder why the server was being bounced ! I realized that oracle user was a non password sudoer and that the query that "bounce the server" exists.
SQL> select sysrun('sudo reboot') from dual;
Broadcast message from root (Thu Jan 27 13:16:34 2011):
The system is going down for reboot NOW!
So, I hit a myth. The query that bounce the server exists !
After attending to the production server startup, and apologizing for all the inconveniences, I've had to restrict access to this function, and check for all the dba_source procedures and start controling what was performing calls to OS through the database.
So, there we have it a fantastic and very useful little utility that can solve quite a few problems. But it can cause a lot of pain if not properly managed.
And so, you too have now been warned. There are always legitimate reasons to invoke external utilities and commands, and a lot of other Databases support the feature natively, but without exception, they do need to be managed very carefully.