The conversion comes with rather interesting consequences.
The scheduler side of things is tighter. As Connor pointed out you now need the CREATE JOB privilege to use the DBMS_JOB package. That’s nice, but isn’t that the opposite of the title of this post? Yes, but…
The problem is the re-implementation of materialized refresh groups using the DBMS_REFRESH package didn’t seem to follow the same approach. You can create a refresh group, which creates a DBMS_SCHEDULER job, without needing the CREATE_JOB privilege. Once you own a job, you can amend it, which means there is now a method to create DBMS_SCHEDULER jobs without needing the CREATE JOB privilege. Doh! You can see an example of it in 19.3 here.
It’s clearly a bug, and I’m sure it will be picked up in a future release.
Although it seems pretty bad, remember the DBMS_REFRESH and DBMS_JOB packages are available by default and in previous releases you didn’t need the CREATE JOB privilege to them.
If this is a problem you can revoke execute on DBMS_REFRESH from PUBLIC, like you may have been doing for DBMS_JOB already.
PS. SR raised.
SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege.
PPS. This is now Bug 30357828 and is being worked on.
It’s more of an opinion piece, so it started as a blog post, but it got too big. Not really what I usually put on the website, but I figured if I put it on the blog it would get in the way of the movie and book reviews and that simply wouldn’t do… 🙂
I am still worried about feature creep turning SQL Developer into the new TOAD, but so far so good. I’m liking it more and more with each release.