Monday, May 25, 2020

DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

Database Tutorial and Material, Database Guides, Database Learning, Database Certification, Database Exam Prep

The DBMS_JOB package has been deprecated since 12cR2. Oracle 19c takes the demise of the DBMS_JOB package a step further by converting any DBMS_JOB jobs to DBMS_SCHEDULER jobs.

◉ Create a Job Using DBMS_JOB


In Oracle 19c jobs created using the DBMS_JOB package are implemented as DBMS_SCHEDULER jobs, as demonstrated below.

We can see from the output below we don't have any jobs for this user.

CONN test/test@pdb1

COLUMN what FORMAT A30

SELECT job, what FROM user_jobs;

0 rows selected.

SQL>

COLUMN job_name FORMAT A30
COLUMN job_action FORMAT A30

SELECT job_name, job_action FROM user_scheduler_jobs;

0 rows selected.

SQL>

We create a job using the DBMS_JOB.SUBMIT procedure, but we are not going to issue a COMMIT statement.

DECLARE
  l_job  PLS_INTEGER;
BEGIN
  DBMS_JOB.submit (
    job       => l_job,
    what      => 'BEGIN NULL; END;',
    next_date => TRUNC(SYSDATE)+1,
    interval  => 'TRUNC(SYSDATE)+1'
  );
END;
/

We can see the job is listed in the USER_JOBS and USER_SCHEDULER_JOBS views.

SELECT job, what FROM user_jobs;

       JOB WHAT
---------- ------------------------------
         1 BEGIN NULL; END;

1 row selected.

SQL>

SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                        JOB_ACTION
------------------------------ ------------------------------
DBMS_JOB$_1                  BEGIN NULL; END;

1 row selected.

SQL>

Notice the JOB_NAME of "DBMS_JOB$_?" for the DBMS_SCHEDULER job that has been generated.

◉ Transactional Jobs


One of the reasons people still use the DBMS_JOB package is it allows you to create jobs that are part of a bigger transaction. If a failure causes an exception, all the current work along with the jobs defined as part of it can be rolled back. We can demonstrate this using the job created above. Remember, we didn't issue a COMMIT, so the job is not visible from another session connected to the same user.

Without closing the original session, open a new connection and check for the jobs.

CONN test/test@pdb1

SELECT job, what FROM user_jobs;

0 rows selected.

SQL>

SELECT job_name, job_action FROM user_scheduler_jobs;

0 rows selected.

SQL>

Now return to the original session and the jobs are still visible.

SELECT job, what FROM user_jobs;

       JOB   WHAT
---------- ------------------------------
         1   BEGIN NULL; END;

1 row selected.

SQL>

SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                         JOB_ACTION
------------------------------ ------------------------------
DBMS_JOB$_1                   BEGIN NULL; END;

1 row selected.

SQL>

Issue a ROLLBACK, and the job definition will be removed.

ROLLBACK;

SELECT job, what FROM user_jobs;

0 rows selected.

SQL>

SELECT job_name, job_action FROM user_scheduler_jobs;

0 rows selected.

SQL>

As a result, the DBMS_JOB package can still be used to create transactional jobs, that are implemented using the DBMS_SCHEDULER scheduler. This also provides backwards compatibility.

◉ Materialized View Refresh Groups


Up to and including Oracle 18c, materialized view refresh groups were implemented using the kernel APIs exposed by the old DBMS_JOB package. In Oracle 19c things look a little different.

Create a table, materialized and refresh group including that materialized view.

CREATE TABLE t1 (id NUMBER);

CREATE MATERIALIZED VIEW t1_mv
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM t1;

BEGIN
   DBMS_REFRESH.make(
     name                 => 'MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

BEGIN
   DBMS_REFRESH.add(
     name => 'MINUTE_REFRESH',
     list => 'T1_MV',
     lax  => TRUE);
END;
/

We don't see a job in the USER_JOBS view, but we do see one in the USER_SCHEDULER_JOBS view.

SELECT job, what FROM user_jobs;

0 rows selected.

SQL>


SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
MV_RF$J_0_S_210           dbms_refresh.refresh('"TEST"." MINUTE_REFRESH"');

1 row selected.

SQL>

But this job is transactional, in that a ROLLBACK will remove the job, along with the refresh group definition.

ROLLBACK;

SELECT job, what FROM user_jobs;

0 rows selected.

SQL>


SELECT job_name, job_action FROM user_scheduler_jobs;

0 rows selected.

SQL>

It would appear the refresh group functionality has been re-implemented using the kernel APIs that sit under the DBMS_SCHEDULER package, but without the implicit commit. Similar to the way the DBMS_JOB interface has been re-implemented. This is not 100% backwards compatible, as the associated job is not visible in the USER_JOBS view. If you have any functionality that relies on the link between the refresh groups and the old scheduler, it will need revisiting. I can't imagine that will be a problem for most people.

You can clean up the test table and materialized view using these commands.

DROP MATERIALIZED VIEW t1_mv;
DROP TABLE t1 PURGE;

◉ Security : The CREATE JOB Privilege is Required?


At first glance the loophole discussed here sounds really bad, but remember that even in Oracle 18c, any user connected to the database could create a job using the DBMS_JOB interface, so this loophole is no worse than what came before. It just breaks the DBMS_SCHEDULER security.

As Connor McDonald pointed out, the conversion means users require the CREATE JOB privilege to allow them to create jobs using the DBMS_JOB package, where previously they didn't. We can see this if we create a user with just the CREATE SESSION privilege and attempt to create a job.

CREATE USER test2 IDENTIFIED BY test2;
GRANT CREATE SESSION TO test2;

CONN test2/test2@pdb1

DECLARE
  l_job  PLS_INTEGER;
BEGIN
  DBMS_JOB.submit (
    job       => l_job,
    what      => 'BEGIN NULL; END;',
    next_date => TRUNC(SYSDATE)+1,
    interval  => 'TRUNC(SYSDATE)+1'
  );
END;
/

Error report -
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9387
ORA-06512: at "SYS.DBMS_ISCHED", line 9376
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_ISCHED", line 9302
ORA-06512: at "SYS.DBMS_IJOB", line 196
ORA-06512: at "SYS.DBMS_JOB", line 168
ORA-06512: at line 4
27486. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a scheduler operation without the
           required privileges.
*Action:   Ask a sufficiently privileged user to perform the requested
           operation, or grant the required privileges to the proper user(s).
SQL>

There is a loophole caused by the refresh group implementation. If we repeat the previous refresh group example, we can see we are able to create a job without the CREATE JOB privilege.

BEGIN
   DBMS_REFRESH.make(
     name                 => 'MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                       JOB_ACTION
------------------------------ ------------------------------
MV_RF$J_0_S_242         dbms_refresh.refresh('"TEST2" "MINUTE_REFRESH"');


1 row selected.

SQL>

That in itself is not devastating because it's for a very specific purpose, but most of Oracle's security is based on you being able to do whatever you want with objects you already own, so what happens if we try to change the attributes?

BEGIN
  DBMS_SCHEDULER.set_attribute (
    name      => 'MV_RF$J_0_S_242',
    attribute => 'job_action',
    value     => 'BEGIN NULL; END;'
  );
END;
/

SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                         JOB_ACTION
------------------------------ -----------------------------
MV_RF$J_0_S_242          BEGIN NULL; END;

1 row selected.

SQL>

So we can create a job using the DBMS_REFRESH package, then alter it to suit our purpose, giving us the ability to create a job without the need for the CREATE JOB privilege.

It would appear the re-implementation of the DBMS_REFRESH package has not followed the same security rules as that used by the other scheduler implementations. I'm sure this will get fixed in a future release.

Until this issue is resolved, you should probably revoke EXECUTE on the DBMS_REFRESH package from PUBLIC, as you may already do for the DBMS_JOB package.

Note. I raised this issue as "SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege". This is now Bug 30357828 and is being worked on.

Related Posts

0 comments:

Post a Comment