◉ 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.
Posts
0 comments:
Post a Comment