I recently asked myself this question after having been confronted with a quite challenging situation, which will be been dissected in this post. Indeed, most of the DBA are aware about several mechanism offered by the Oracle database engine to call external programs.
However, we rarely have the opportunity to get deep understanding of all of them due to the broad skills required.

A couple of days ago, we have been asked a by an IT architect to have a look at some SHELL scripts & C programs executed on a database server by database processes; It all began with “strange” UNIX file permissions such as:

[oracle@svl-oat ~]$ ls -l /opt/oracle_scripts/run_prog.sh
-rwxr-sr-x 1 oracle oinstall 68 Mar 12 19:28 /opt/oracle_scripts/run_prog.sh

Interesting isn’t it ? SETUID & SETGID have been set on the scripts to probably gain elevated privileges ? But, for which purpose?

Which methods do we have to start external jobs from Oracle (pluggable) database?

Before moving into all the beloved technical details and reverse engineering. Let’s quickly summarize all options Oracle RBDMS offers.

DBMS_SCHEDULER jobs

According to the DBMS_SCHEDULER sub-program CREATE_JOB. Oracle supports several types for calling external programs:

TypeDescription
STORED_PROCEDURECalling external C/C++ subprogram or Java Stored procedure
EXECUTABLECalling anything that can be executed from the command line of the operating system
EXTERNAL_SCRIPTCalling an external script that uses the command shell of the computer running the job

Please refer to the DBMS_SCHEDULER package for details.

Java Stored procedure

Indeed, Oracle RBDMS can spawn a dedicated server process on the database server using Java stored procedure and call a SHELL script.


The following Java program source code has been merged from two different code base:

  • https://oracle-base.com/articles/8i/shell-commands-from-plsql
  • https://innerlife.io/oracle-db-cmd-java
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "JavaExecHostCmd" AS
import java.io.*;
public
class JavaExecHostCmd {
 public
  static String executeCommand(String command) {
    String buff = null;
    String output = "";
    try {
      String[] finalCommand;
      finalCommand = new String[3];
      finalCommand[0] = "/bin/bash";
      // finalCommand[0] = "";
      finalCommand[1] = "-c";
      finalCommand[2] = command;

      // Execute the command...
      final Process pr = Runtime.getRuntime().exec(finalCommand);

      // Capture output from STDOUT...
      BufferedReader br_in = null;
      try {
        br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
        output = output + "STDOUT: " + "\n";
        while ((buff = br_in.readLine()) != null) {
          // System.out.println("stdout: " + buff);
          output = output + buff + "\n";
          try {
            Thread.sleep(100);
          } catch (Exception e) {
          }
        }
        br_in.close();
      } catch (IOException ioe) {
        // System.out.println("Error printing process output.");
        output = output + "Error printing process output." + "\n";
        // ioe.printStackTrace();
      } finally {
        try {
          br_in.close();
        } catch (Exception ex) {
        }
      }

      // Capture output from STDERR...
      BufferedReader br_err = null;
      try {
        br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
        output = output + "\n" + "STDERR: " + "\n";
        while ((buff = br_err.readLine()) != null) {
          // System.out.println("stderr: " + buff);
          output = output + buff + "\n";
          try {
            Thread.sleep(100);
          } catch (Exception e) {
          }
        }
        br_err.close();
        return output;
      } catch (IOException ioe) {
        // System.out.println("Error printing execution errors.");
        output = output + "Error printing execution errors." + "\n";
        // ioe.printStackTrace();
      } finally {
        try {
          br_err.close();
        } catch (Exception ex) {
        }
      }
    } catch (Exception ex) {
      output = output + ex.getLocalizedMessage() + "\n";
    }
    return output;
  }
};
/

Basically, the program executes a “/bin/bash -c <Command>” and returns its result.

To interface the Java program a simple PL/SQL function is required:

CREATE OR REPLACE FUNCTION run_shell_cmd_with_java (p_command  IN  VARCHAR2) 
   RETURN  VARCHAR2 
   AS LANGUAGE JAVA 
   NAME 'JavaExecHostCmd.executeCommand (java.lang.String) return String';
/

Finally, we need to grant access to the Oracle OJVM for desired database schema:

DECLARE
   l_schema VARCHAR2(30) := 'JEW'; -- Adjust as required.
BEGIN
   DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
   DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
   DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/

Safe Harbor statement: Please ensure you do not blindly apply the same java.io.FilePermission which basically allows to run anything on the database server 🙂

External C/C++ programs aka “extproc”

That’s probably one of the least known option but as old as the Oracle JVM both introduced in 1999 with Oracle 8i.

The following source code was mostly generated with the support of a LLM but slightly adapted to mimic the behavior as the Java program:

cat <<EOF_> /opt/oracle_scripts/c_extproc.c
// Source: ChatGPT
#include <stdio.h>
#include <stdlib.h>
#include <string.h>  // Include the string.h header to use strcpy and strlen

#define MAX_BUFFER 1024

// Function to execute a Linux command and return the output
char* execute_command(const char *command) {
    FILE *fp;
    char *output = (char*)malloc(MAX_BUFFER * sizeof(char));  // Allocate memory for the output
    if (output == NULL) {
        perror("malloc failed");
        return NULL;
    }

    fp = popen(command, "r");  // Open a pipe to the command
    if (fp == NULL) {
        perror("popen failed");
        free(output);
        return NULL;
    }

    // Buffer to accumulate the command output
    char buffer[MAX_BUFFER];
    size_t output_size = 0;

    // Read the output line by line and accumulate it in 'output'
    while (fgets(buffer, sizeof(buffer), fp) != NULL) {
        size_t len = strlen(buffer);

        // Reallocate memory if necessary
        if (output_size + len >= MAX_BUFFER) {
            output = realloc(output, output_size + len + 1);  // Add space for the new content
            if (output == NULL) {
                perror("realloc failed");
                fclose(fp);
                return NULL;
            }
        }

        // Append the line to the output
        strcpy(output + output_size, buffer);
        output_size += len;
    }

    fclose(fp);
    return output;
}
EOF_

Compile the source using GNU compiler and finally resolve the library dependencies (combine links):

gcc -fPIC -c /opt/oracle_scripts/c_extproc.c -o /opt/oracle_scripts/c_extproc.o
ld -shared -o /opt/oracle_scripts/c_extproc.so /opt/oracle_scripts/c_extproc.o

In our case, the program does not contain any “call to the function – aka. main progtam”, as such it’s just a library.

In the same way as we interfaced the Java program, we need to bind the external library with a PL/SQL function:

CREATE OR REPLACE LIBRARY shell_c_lib is '/opt/oracle_scripts/c_extproc.so';
/
CREATE OR REPLACE FUNCTION run_shell_cmd_with_c (command IN VARCHAR2)  
 RETURN  VARCHAR2  
 AS  EXTERNAL
 NAME "execute_command"  
 LIBRARY  shell_c_lib  
 LANGUAGE C  
 PARAMETERS (command string, RETURN string);  
/

Hold on a second, for Java we had to allow the Oracle JVM to access the file sytem to execute an Operating System command. Is anything required for external C/C++ programs ?

Yes, usually ISVs recommend to configure EXTPROC_DLLS parameter within the Oracle Listener. However, in our Oracle Cloud LAB we used an unsafe shortcut which consists to whitelist any library present on the database server filesystem by editing following Oracle Home configuration file:

[oracle@svl-ora-t ~]$ grep '^SET EXTPROC_DLLS=ANY' ${ORACLE_HOME}/hs/admin/extproc.ora
SET EXTPROC_DLLS=ANY

Safe Harbor statement: Please ensure you do not blindly follow this post for your own safety 😉

HOST command from PL/SQL

Uh? Does it exists? LLM training on the job! Of course, not 😉

DBMS_SCHEDULER

The Oracle scheduler support several job types when working with external programs:

EXECUTABLE and EXTERNAL_SCRIPT

Create credentials for jobs from type EXTERNAL_SCRIPT (aka. existing local Operating System user credentials):

BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL(
    credential_name => 'test_creds',
    username        => 'test',
    password        => 'test');
END;
/

Once, created if needed the credentials can be applied at pluggable database level only though init. parameter PDB_OS_CREDENTIAL.

Create the scheduler jobs:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
     job_name=>'EXEC_SHELL_CMD_AS_EXECUTABLE',
     job_type=>'EXECUTABLE',
     job_action=>'/opt/oracle_scripts/run_prog.sh',
     enabled=>TRUE,
     auto_drop=>FALSE,
     repeat_interval => 'freq=secondly;bysecond=5;'
     );

	-- EXTERNAL_SCRIPT requires credentials ! 
	-- Please Refer to DBMS_SCHEDULER package documentation for details
  DBMS_SCHEDULER.CREATE_JOB(
     job_name=>'EXEC_SHELL_CMD_AS_EXTERNAL_SCRIPT',
     job_type=>'EXTERNAL_SCRIPT',
     job_action=>'/opt/oracle_scripts/run_prog.sh',
     credential_name=>'test_creds',
     enabled=>TRUE,
     auto_drop=>FALSE,
     repeat_interval => 'freq=secondly;bysecond=15;'
     );   
END;
/

Nota Bene: DBMS_SCHEDULER job_type EXECUTABLE do not capture STDOUT as long as no credentials are applied (created with DBMS_CREDENTIALS)! at least this was the case on my Oracle 19c Release Update 23 database

STORED_PROCEDURE and PLSQL_BLOCK (Java and External C program)

Functions are not supported by the Oracle Scheduler. Thus, we created a simple instrumentation table:

DROP TABLE debug;
CREATE TABLE debug 
   (EXECUTED_DATE TIMESTAMP default SYSTIMESTAMP, 
    use_case VARCHAR2(20), 
    output VARCHAR2(1024)
   );

And finally, created on top of the functions for the e Java and C program stored procedures:

CREATE OR REPLACE  PROCEDURE runproc_shell_cmd_with_c  AS
   l_output VARCHAR2(100); 
BEGIN  
   l_output := run_shell_cmd_with_c('/usr/bin/whoami'); 
   INSERT INTO debug(use_case, output) VALUES('c_program', l_output);
 END;
 /

CREATE OR REPLACE  PROCEDURE runproc_shell_cmd_with_java  AS
   l_output VARCHAR2(100); 
BEGIN  
   l_output := run_shell_cmd_with_java('/usr/bin/whoami'); 
   INSERT INTO debug(use_case, output) VALUES('java_program', l_output);
 END;
 /

Create the scheduler jobs:

-- Once executed as STORED_PROCEDURE
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
     job_name=>'EXEC_SHELL_CMD_USING_C_STOREDPROC',
     job_type=>'STORED_PROCEDURE',
     job_action=>'runproc_shell_cmd_with_c',
     enabled=>TRUE,
     auto_drop=>FALSE,
     repeat_interval => 'freq=secondly;bysecond=5;'
     );
    
  DBMS_SCHEDULER.CREATE_JOB(
     job_name=>'EXEC_SHELL_CMD_USING_JAVA_STOREDPROC',
     job_type=>'STORED_PROCEDURE',
     job_action=>'runproc_shell_cmd_with_java',
     enabled=>TRUE,
     auto_drop=>FALSE,
     repeat_interval => 'freq=secondly;bysecond=5;'
     );
END;
/

-- Once executed as anonymous PL/SQL block
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
     job_name=>'EXEC_SHELL_CMD_USING_C_PLSQLBLOCK',
     job_type=>'PLSQL_BLOCK',
     job_action=>'runproc_shell_cmd_with_c;',
     enabled=>TRUE,
     auto_drop=>FALSE,
     repeat_interval => 'freq=secondly;bysecond=5;'
     );
    
  DBMS_SCHEDULER.CREATE_JOB(
     job_name=>'EXEC_SHELL_CMD_USING_JAVA_PLSQLBLOCK',
     job_type=>'PLSQL_BLOCK',
     job_action=>'runproc_shell_cmd_with_java;',
     enabled=>TRUE,
     auto_drop=>FALSE,
     repeat_interval => 'freq=secondly;bysecond=15;'
     );
END;
/

Test cases

So any guess about old Unix command that prints the effective user id ? whoami?

Execution methodType of database connectionwhoami ?
SELECT
run_shell_cmd_with_java(’/usr/bin/whoami’) AS stdout
FROM DUAL;		Connected to PDB over SQL*NETgrid
Connected to PDB over BEQUEATH protocoloracle
SELECT
run_shell_cmd_with_c(’/usr/bin/whoami’) AS stdout
FROM DUAL;		Connected to PDB over SQL*NETgrid
Connected to PDB over BEQUEATH protocoloracle
exec DBMS_SCHEDULER.RUN_JOB(‘EXEC_SHELL_CMD_AS_EXECUTABLE’);Database job executed manuallynobody
Database job schedulednobody
exec DBMS_SCHEDULER.RUN_JOB(‘EXEC_SHELL_CMD_AS_EXTERNAL_SCRIPT’);Database job executed manuallytest

Database job scheduled		test
exec DBMS_SCHEDULER.RUN_JOB(‘EXEC_SHELL_CMD_USING_C_STOREDPROC’);Database job executed manuallygrid
Database job scheduledoracle
exec DBMS_SCHEDULER.RUN_JOB(‘EXEC_SHELL_CMD_USING_JAVA_STOREDPROC’);Database job executed manuallygrid
Database job scheduledoracle
exec DBMS_SCHEDULER.RUN_JOB(‘EXEC_SHELL_CMD_USING_C_PLSQLBLOCK’);Database job executed manuallygrid

Database job scheduled		oracle
exec DBMS_SCHEDULER.RUN_JOB(‘EXEC_SHELL_CMD_USING_JAVA_PLSQLBLOCK’);Database job executed manuallygrid
Database job scheduledoracle

Comments:

  • With Oracle Grid Infrastructure the entire Single Client Access Name (SCAN) listeners belong to Operating System User grid
  • BEQUEATH Protocol is local Operating System authentication method only based on the traditional OSDBA, OSOPER. etc .. O.S group mapping
  • Oracle Scheduler job coordination CJQn processes and workers Jnn processes are spawned when database starts.

Additional background information

Oracle Cloud database home specificity

On our LAB we had to tweak the Oracle Home as documented per Oracle support note, for some reason on OCI the external job feature is not working by default and according to the note it’s a feature 🙂

DBMS_SCHEDULER Job (External Job) Failed With ORA-27300, ORA-27301, ORA-27302: failure occurred at: sjsec 6d [OCI DBCS] (Doc ID 2647889.1)

DBMS_SCHEDULER job type EXECUTABLE

By default any job is executed under the Unix pseudo user “nobody” which is a low-privileged Unix namespace and as such considered as secure. Oracle allows to tweak the behavior by adapting ${ORACLE_HOME}/rdbms/admin/externaljob.ora:

[oracle@svl-ora-t ~]$ grep '^run_' ${ORACLE_HOME}/rdbms/admin/externaljob.ora
run_user = nobody
run_group = nobody

DBMS_SCHEDULER & Linux PAM (Pluggable Authentication Modules) kernel module

Oracle Scheduler switch the user in background like you would run an “su” command. If this is not possible because of security reasons. the Job wont’ be executed at all

[oracle@svl-ora-t ~]$ su - test
Password:
su: Permission denied

Some hardened system on which we worked did not allow to “escape” from oracle O.S user and as such all Oracle Scheduler jobs failed due to the PAM config preventing switching to another user unless member of wheel O.S group:

# Uncomment the following line to require a user to be in the "wheel" group.
auth           required        pam_wheel.so use_uid

Oracle Support note which is still valid nowadays despite Oracle Linux 6 reference:

Dbms_scheduler Job Executes Shell Script Failed With Error: ORA-27369: job of type EXECUTABLE failed with exit code: 7 !@#–!@#7#@!–#@! (Doc ID 2312329.1)

Isolate Oracle JVM runtime execution to least privileged O.S user

Oracle delivers Oracle JVM isolation feature using a DBMS_JAVA sub-program to bind execution of program to a specific Operating System user. This setting requires to be conneted as SYS:

SQL> show user 

"SYS"

SQL> alter session set container=APP_001I;

Session altered.

SQL> exec dbms_java.set_runtime_exec_credentials(dbuser=>'JEW', osuser=>'test', ospass =>'test');

PL/SQL procedure successfully completed.

From now on, all sub-subsequent execution will be started under the Operating System user “test”. Unfortunately, all executions fail with following error message

Cannot run program "/bin/bash": cannot execute /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/jssu

Unfortunately, we were not able to fix this issue which has been well described in following post:
https://mvelikikh.blogspot.com/2020/04/secure-use-of-runtimeexec-functionality.html

Unbinding the OJVM runtime environment requires as well SYS:

SQL> alter session set container=APP_001I;

Session altered.

SQL> exec dbms_java.set_runtime_exec_credentials(dbuser=>'JEW', osuser=>null, ospass => null);

PL/SQL procedure successfully completed.

Summary

Well, there is no blueprint to deduct from this post which method suits the best for extermal programs, but at least it provides an overview and reproducible test cases to understand under which circumstances some files may or may not need special file system flags.

Indeed, the dissected use cases for several scripts / C programs were amended with SETUID, GID flag to mitigate some past issues nobody remember leaving the system an undesired state, hackers could potentially take advantage of.

Post Views: 842
Thumbnail [60x60]
by
Jérôme Witt