Database Software

Hi,

Do you know why a user who can access a job of another user cannot see it in the ALL_OBJECTS view?

My DB is 11.2.0.4 SE.

Here is a test case to reproduce:

-- 1°) Create 2 users: one who's going to be the job's owner, one who's going to be the job's caller

--     So grant to both users the CREATE SESSION privileges, and the CREATE JOB privilege to JOB_OWNER

Connecte a :

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

With the Automatic Storage Management option

SQL> show user

USER est "SYSTEM"

SQL>

SQL> CREATE USER job_owner IDENTIFIED by job_owner;

Utilisateur cree.

SQL> GRANT CREATE SESSION, CREATE JOB TO job_owner;

Autorisation de privileges (GRANT) acceptee.

SQL> CREATE USER job_caller IDENTIFIED by job_caller;

Utilisateur cree.

-- 2°) Connect as JOB_OWNER and create a dummy job

SQL> CONNECT job_owner/job_owner

Connecte.

SQL>

SQL> EXEC DBMS_SCHEDULER.Create_job(job_name => 'TESTJOB', job_type => 'PLSQL_BLOCK', job_action => 'NULL;');

Procedure PL/SQL terminee avec succes.

-- 3°) Try to run the job with its owner : no problem

SQL>  EXEC DBMS_SCHEDULER.Run_job('TESTJOB');

Procedure PL/SQL terminee avec succes.

-- 4 °) Check in the USER_SCHEDULER_JOBS view that the job is shown

SQL> SELECT job_name, state FROM USER_SCHEDULER_JOBS;

JOB_NAME                       STATE

------------------------------ ---------------

TESTJOB                        DISABLED

SQL>

-- 5°) Connect as JOB_CALLER to run the same query against ALL_SCHEDULER_JOBS : nothing is shown as expected

SQL>

SQL> CONNECT job_caller/job_caller

Connecte.

SQL>

SQL> SELECT job_name, state FROM ALL_SCHEDULER_JOBS;

aucune ligne selectionnee

SQL>

-- 6°) Now connect as JOB_OWNER and grant ALTER on its job to JOB_CALLER

SQL> CONNECT job_owner/job_owner

Connecte.

SQL>

SQL> GRANT ALTER ON testjob TO job_caller;

Autorisation de privileges (GRANT) acceptee.

SQL>

-- 7°) Connect back as JOB_CALLER and run the same query : now the job is shown since JOB_CALLER can alter it

SQL> CONNECT job_caller/job_caller

Connecte.

SQL>

SQL> SELECT job_name, state FROM ALL_SCHEDULER_JOBS;

JOB_NAME                       STATE

------------------------------ ---------------

TESTJOB                        DISABLED

SQL>

-- 8°) But trying now to check the job from ALL_OBJECTS returns no rows:

SQL> select object_name, status FROM ALL_OBJECTS WHERE object_name = 'TESTJOB';

aucune ligne selectionnee

Since JOB_CALLER can alter the job owned by JOB_OWNER, why isn't it shown in ALL_OBJECTS?

Have I missed something?

Thanks