Running a shell script from inside an Oracle database using DBMS_SCHEDULER

As I write, various parts of the United Kingdom are still under variations of the theme of “lockdown”. Not that I’m stir crazy or anything but I’ve now decided it’s time to explore one way of escaping, from the confinements of my database, at least.

Specifically I’m going to :

  • create an OS user on the database server
  • create an OS group to allow both the new user and the oracle user to access to a common location
  • create a shell script owned by the OS user which writes to that common location
  • create a credential to access that user from inside the database itself
  • setup and run a scheduler job to execute a shell script as the new OS user
  • read the output file generated by the shell script from inside the database

For this exercise, I’m using my trusty 18cXE database running on CentOS…

Setting up the Linux User

Note that my database settings are defaulted so that, when my application needs to do anything on the OS ( e.g. read a file via an external table), it will do so as the OS user oracle .

This may be different on your database, especially if someone has specified a different user in the PDB_OS_CREDENTIAL parameter .

First of all, I’m going to create a group :

sudo groupadd app_out

…and then the OS user (setting a password) …

useradd -m xerunner
passwd xerunner (xerunner)

…before assigning both the new xerunner and existing oracle users to the new group :

usermod -G app_out xerunner
usermod -G app_out oracle

I’ll now create a directory under $ORACLE_BASE and change the directory’s group to app_out , and ensure that group members have full access to the directory :

sudo su oracle
mkdir app_script_output

chgrp app_out app_script_output
chmod g+w app_script_output
ls -ld app_script_output
drwxrwxr-x 2 oracle app_out 6 May 24 21:32 app_script_output/

To test this, we can connect as the new xerunner user :

sudo su xerunner
touch /opt/oracle/app_script_otuput/silly.txt
ls -l /opt/oracle/app_script_output/silly.txt

-rw-rw-r-- 1 xerunner xerunner 0 May 24 21:36 silly.txt

Now for the shell script we’re going to call from inside the database. Just to demonstrate that the oracle user does not require execute permissions on the script, we’ll create it in as xerunner in that user’s home directory. The script is called :

/bin/echo "Help, I'm trapped inside this database!" >/opt/oracle/app_script_output/message.txt
exit 0

We can see that permissions are fairly restricted, with others (which includes oracle ), having only read access :

ls -l /home/xerunner/ 
-rwxrw-r-- 1 xerunner xerunner 114 May 24 21:42 /home/xerunner/

Now we’ve completed the setup on the OS, we need to return to the database.

Creating a credential

We need to create a Credential object in Oracle. This will allow us to connect to the database server as xerunner without having to know any of the gory details ( such as the user’s password).

Back in the olden days (when only cats did social distancing), credential management was handled in the DBMS_SCHEDULER package.

Since 12c, the DBMS_CREDENTIAL package has assumed this role.

First up, let’s create our credential :

        credential_name => 'myapp_ext_jobs',
        username => 'xerunner',
        password => 'super secret password');

…where super secret password is the password for the xerunner user on the OS.

You can include a comment when creating a credential. In hindsight, that might have been a good idea.

Fortunately, I can edit the credential rather than having to drop and re-create it…

        credential_name => 'myapp_ext_jobs', 
        attribute => 'comments', 
        value => 'OS User to execute shell scripts from scheduler jobs');

I can see details of my credentials by querying the appropriate data dictionary view :

select username, comments
from user_credentials
where credential_name = 'MYAPP_EXT_JOBS';

USERNAME        COMMENTS                                               
--------------- -------------------------------------------------------
xerunner        OS User to execute shell scripts from scheduler jobs

One other point to note, in the aforementioned olden days, it was possible to retrieve the password of a credential by using DBMS_ISCHED.GET_CREDENTIAL_PASSWORD. See Martin Berger’s explanation here for details .

In 18c however, this is no longer the case.

Running the shell script from a Scheduler Job

Scheduler jobs are extremely useful for doing all sorts of things, not just running – well – scheduled jobs.

In this case we want to run the shell script. To do this, we first create a job :

        job_name => 'my_external_job',
        job_type => 'EXECUTABLE',
        job_action => '/home/xerunner/',
        credential_name => 'MYAPP_EXT_JOBS',
        auto_drop => false,
        enabled => true);

… and now we can run it :

    dbms_scheduler.run_job( 'my_external_job');

Note that, by default, RUN_JOB executes the specified job in the current session – i.e. in the foreground.

We can check that the job has been successful by connecting to the OS and looking in the appropriate directory….but that’s boring. Let’s have a look without leaving the comfort of our database.

Reading the file

First, we need to create a directory object for the OS directory in question. This shouldn’t be a problem because we already know that the oracle OS user has permissions on the directory on the OS :

create directory app_script_out
    as '/opt/oracle/app_script_output'

Then we can just read the file :

    fdir all_directories.directory_name%type := 'APP_SCRIPT_OUT';
    fname varchar2(128) := 'message.txt';
    fh utl_file.file_type;
    buf varchar2(32767);
    fexists boolean;
    flen number;
    bsize number;
    -- Make sure the file is where we think it is before we try to open it...
    utl_file.fgetattr( fdir, fname, fexists, flen, bsize);
    if not fexists then
        raise_application_error(-20000, q'[Hold up, the file isn't there]');
    end if;
    fh := utl_file.fopen(fdir, fname, 'r', 32767);
        -- Read and print each line in a nested block.
        -- NO_DATA_FOUND will be raised when we hit the end of the file
            utl_file.get_line(fh, buf, 32767);
        end loop;
    exception when no_data_found then
        dbms_output.put_line('End of file reached');

Help ! I'm trapped inside this database !
End of file reached

PL/SQL procedure successfully completed.