<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
9,938 Points
3,338 Views
6 Endorsements
Last Modified:
Approved
Community Pick
Javier Morales
Oracle DBA teacher and consultant for almost 20 years.
Oracle Certified Professional. Writer of “Optimización SQL en Oracle".
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
  • 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month