Our community of experts have been thoroughly vetted for their expertise and industry experience.
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".
Browse All Articles > The query that rebooted the server - or - joys and dangers of using RPC to invoke OS commands.
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.
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'; 2 /Biblioteca creada.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); 8 /Procedimiento creado.
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!SYSTEM.SYSRUN('SUDOREBOOT')--------------------------- 0
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.