<

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

Published on
9,847 Points
3,247 Views
6 Endorsements
Last Modified:
Approved
Community Pick
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
Comment
  • 4
  • 3
7 Comments
LVL 5

Expert Comment

by:Sanjeev Labh
Nice article. Would it be possible for you to share the code of the function "sysrun" used in your query.
0
LVL 7

Author Comment

by:Javier Morales
Hi Sanjeev, thanks a lot for your comment.

The code function is really simple... it's just a call to "shell" procedure.

create or replace function sysrun (command_shell varchar2) return number
is
     shell (command_shell);   -- that's the external procedure
     return 0;
end;
/

Open in new window


Take care! it can be really dangerous!
0
LVL 5

Expert Comment

by:Sanjeev Labh
Thanks Javier. Sure this is really dangerous but is a good alternative in some use cases. Will definitely take care.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LVL 5

Expert Comment

by:Sanjeev Labh
Hi Javier,

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

Author Comment

by:Javier Morales
I used Borland c++ compiler (free)

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

Hope it works now :)
0
LVL 5

Expert Comment

by:Sanjeev Labh
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.
0
LVL 7

Author Comment

by:Javier Morales
Mmmm yes ! you're right ! :)

Thanks for the solution! :)
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month