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 :
For this exercise, I’m using my trusty 18cXE database running on CentOS…
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 message.sh :
#!/bin/sh /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/message.sh -rwxrw-r-- 1 xerunner xerunner 114 May 24 21:42 /home/xerunner/message.sh
Now we’ve completed the setup on the OS, we need to return to the database.
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 :
begin dbms_credential.create_credential( credential_name => 'myapp_ext_jobs', username => 'xerunner', password => 'super secret password'); end; /
…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…
begin dbms_credential.update_credential( credential_name => 'myapp_ext_jobs', attribute => 'comments', value => 'OS User to execute shell scripts from scheduler jobs'); end; /
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.
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 :
begin dbms_scheduler.create_job( job_name => 'my_external_job', job_type => 'EXECUTABLE', job_action => '/home/xerunner/message.sh', credential_name => 'MYAPP_EXT_JOBS', auto_drop => false, enabled => true); end; /
… and now we can run it :
begin dbms_scheduler.run_job( 'my_external_job'); end; /
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.
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 :
declare 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; begin -- 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); begin -- Read and print each line in a nested block. -- NO_DATA_FOUND will be raised when we hit the end of the file loop utl_file.get_line(fh, buf, 32767); dbms_output.put_line(buf); end loop; exception when no_data_found then dbms_output.put_line('End of file reached'); utl_file.fclose(fh); end; end; / Help ! I'm trapped inside this database ! End of file reached PL/SQL procedure successfully completed.