Oracle Scheduler External Jobs and Credentials

A few months ago I wrote a blog post - Kill stuck Oracle process from OS using DBMS_SCHEDULER? We had some problems with stuck Oracle processes and I have created external job to kill stuck processes for a quick workaround.

At the end of that post I wrote:

When dbms_scheduler is executing external (operating system) jobs it is using lowly privileged user and group for your platform. In my environment (OEL 5.8) this is “nobody” and “nobody”. As you must kill OS process you must specify more privileged user. There is “externaljob.ora” configuration file in “$ORACLE_HOME/rdbms/admin”. Edit this file as root and specify privileged user and group.

During my preparation for “Oracle Database 11g: New Features for Administrators” I’ve learned that this is not the best way to grant privileges to external jobs.

In 11g, to improve security, Oracle strongly recommends to assign a credential to a external job for this task. Default credentials are included only for backward compatibility and may be deprecated in future releases.


This will be very simple example to demonstrate how to use credentials.


Let’s create simple script:

$ cat test_script.sh
#!/bin/bash

/bin/echo "TEST SCRIPT!" > /home/oracle/test_script.out

Grant execute privileges to OS user:

$ chmod u+x test_script.sh

Create test user and grant necessary privileges. To execute external jobs you will need "CREATE JOB" and "CREATE EXTERNAL JOB" privileges.

SQL> create user testusr identified by testusr;

User created.

SQL> grant create session to testusr;

Grant succeeded.

SQL> grant create job to testusr;

Grant succeeded.

SQL> grant create external job to testusr;

Grant succeeded.

Create simple job.

SQL> conn testusr/testusr
Connected.
SQL>
begin
   dbms_scheduler.create_job(
   job_name => 'test_script_job',
   job_type => 'EXECUTABLE',
   job_action => '/home/oracle/test_script.sh',
   start_date => SYSDATE,
   enabled => FALSE,
   repeat_interval => NULL);
   end;
 10  /

PL/SQL procedure successfully completed.

Try to run job without assigning credentials.

SQL> begin
  2  dbms_scheduler.run_job(job_name => 'test_script_job', use_current_session=> TRUE);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: 274662
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

Now to create credential using CREATE_CREDENTIAL procedure in the DBMS_SCHEDULER package.
OS user specified in credential must have necessary privileges to run script.

SQL> begin
  2  dbms_scheduler.create_credential(
  3  credential_name => 'ORACLE_CRED',
  4  username => 'oracle',
  5  password => ‘os_password’);
  6  end;
  7  /

PL/SQL procedure successfully completed.

BE AWARE!

It is noted in documentation, passwords are stored obfuscated, and are not displayed in the *_SCHEDULER_CREDENTIALS views. But as SYS user you could extract password with DBMS_ISCHED.GET_CREDENTIAL_PASSWORD function.

SQL>
SELECT u.name CREDENTIAL_OWNER, O.NAME CREDENTIAL_NAME, C.USERNAME,
  DBMS_ISCHED.GET_CREDENTIAL_PASSWORD(O.NAME, u.name) pwd
FROM SYS.SCHEDULER$_CREDENTIAL C, SYS.OBJ$ O, SYS.USER$ U
WHERE U.USER# = O.OWNER#
  5    AND C.OBJ#  = O.OBJ# ;

CREDENTIAL_OWNER     CREDENTIAL_NAME                USERNAME             PWD
-------------------- ------------------------------ -------------------- ----------
TESTUSR              ORACLE_CRED                    oracle               os_password

Thanks Martin Berger for this information: http://berxblog.blogspot.com/2012/02/restore-dbmsschedulercreatecredential.html

You already know that SYS user is very powerful and should be well protected - this is just another reason.


As I have created credential lets assign it to my external job.

SQL> begin
  2  dbms_scheduler.set_attribute('test_script_job','credential_name','ORACLE_CRED');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Everything should work fine now.

SQL> begin
  2  dbms_scheduler.run_job(job_name => 'test_script_job', use_current_session=> TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

Quick check.

SQL> !cat /home/oracle/test_script.out
TEST SCRIPT!

Cleanup:

SQL> drop user testusr cascade;

User dropped.


$ rm /home/oracle/test_script.sh /home/oracle/test_script.out

REFERENCES:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/schedover004.htm#BGBHJCHH