Community Pick: Many members of our community have endorsed this article.

The query that rebooted the server - or - joys and dangers of using RPC to invoke OS commands.

Javier MoralesOwner
CERTIFIED EXPERT
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".
Published:
Updated:
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.

LISTENER.ORA
LISTENER =
                        (DESCRIPTION_LIST =
                          (DESCRIPTION =
                            (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
                            )
                          )
                        )
                      SID_LIST_LISTENER =
                        (SID_LIST =
                          (SID_DESC =
                      [b]      (SID_NAME = extproc_agent)
                            (ORACLE_HOME = E:\oracle\ora92)
                            (PROGRAM = extproc)
                            (ENVS = "EXTPROC_DLLS=ANY")[/b]
                          )
                        )

Open in new window


TNSNAMES.ORA
EXTPROC_CONNECTION_DATA =
                        (DESCRIPTION =
                          (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC))
                          )
                          (CONNECT_DATA =
                            (SID = extproc_agent)
                          )
                        )

Open in new window


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:


shell.c code
   #include <windows.h>     
                         #include <stdio.h>     
                         #include <stdlib.h>      
                      
                         void __declspec(dllexport) sh(char *);     
                         void sh(char *cmd)     
                         {     
                             system(cmd);     
                         }

Open in new window


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    

Open in new window


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.

Open in new window


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

Open in new window


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.
6
3,842 Views
Javier MoralesOwner
CERTIFIED EXPERT
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".

Comments (7)

Sanjeev LabhDatabase Consultant

Commented:
Thanks Javier. Sure this is really dangerous but is a good alternative in some use cases. Will definitely take care.
Sanjeev LabhDatabase Consultant

Commented:
Hi Javier,

Do you have a Linux version of shell.c. As I am facing some compilations issues with the one provided by you.
CERTIFIED EXPERT

Author

Commented:
I used Borland c++ compiler (free)

http://forms.embarcadero.com/forms/BCC32CompilerDownload

Hope it works now :)
Sanjeev LabhDatabase Consultant

Commented:
No I think there is supposed to be some code change also. Proabably in the Linux version the code does not require the below lines.

#include <windows.h>    
void __declspec(dllexport) sh(char *);  

These lines are probably related to generation of DLLs for windows which might not be the case for Linux.
CERTIFIED EXPERT

Author

Commented:
Mmmm yes ! you're right ! :)

Thanks for the solution! :)

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.