I have two schema "OWNER" and "USER".

I've created job in "OWNER" schema in PROD and we don't have access to login into this schema. Now I want to find a way to access these jobs in "USER" schema.

Below are the methods, I tried and did not work for me: 1)I created view in "OWNER" schema (create view test_view as select * from all_scheduler_jobs) and gave a grant "GRANT SELECT OWNER.test_view to USER". But still I did not find any records in USER schema.

2)Created a view as mentioned above and after that I created synonym in USER schema( create synonym USER.test_view for OWNER.test_view.

Please let me know if there is anything that I'm missing or is there any other way that I can implement.

CodePudding user response：

The ALL_SCHEDULER_JOBS view only lets you see jobs to which you already have access - essentially just your own. To see properties or output from scheduler jobs belonging to other schemas, USER must have the SELECT ANY DICTIONARY privilege, which would allow access to the DBA_SCHEDULER_JOBS view. Check with your DBA to see if you are allowed to have that privilege (it opens up access to a lot of other things, too), or if they would prefer that you have a custom role that just grants access to the various DBA_SCHEDULER_% views.

If USER needs to execute the job in another schema, then it will need the EXECUTE ANY JOB privilege, which would allow it to run any job in any schema. There's no way to make that more fine-grained at this time.

CodePudding user response：

You can try below SQL.

SCHEMA: OWNER

commit;

SCHEMA: USER

select * from OWNER.test_view;