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 :

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

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

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 :

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

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 message.sh :

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

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 :

…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…

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

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 :

… and now we can run it :

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 :

Then we can just read the file :