Custom Job Role to View Output of Specific ESS Job Submitted By Another User Not Functioning

Eliran Hadad

Hi All,

I am facing this issue after creating a custom role to view the output of a specific ESS job submitted by another user.

I followed oracle Doc ID 1980772.1.

Also documentation:https://docs.oracle.com/en/cloud/saas/procurement/24a/oapro/assign-roles-for-access-to-manage-scheduled-processes.html#Access-for-All-Scheduled-Processes

I've defined:

1. Manage Database Resources - ESS_REQUEST_HISTORY (SQL predicate)
2. A new role assigned ESS Monitor Role
3. DSP- ESS_REQUEST_HISTORY, Actions: ESS_REQUEST_OUTPUT_READ; ESS_REQUEST_READ; READ

Still, we can't see any output.

Can anyone help here :-)?

BR

Eliran

Mallikarjuna Kuppauru-Oracle

Hi @Eliran Hadad

You question is more about Fusion application , you can use below forum to get the answer -

Welcome to Cloud Customer Connect

Regards,

Arjun

Liwen Chiu

Hi @Eliran Hadad ,

We have this customized role as depicted in the followings works for our company's implementation, give it a try? If you have other findings please let me know as well, thank you!

Role Category: Common - Abstract Roles

Function Security Policies: (All inherited from Role)
-Admin privilege all ESS metadata, inherited from "ESS Administrator Role"
-All privileges on all ESS Request of all tenant, inherited from "ESS Administrator Role"
-READ privilege on all ESS Request of all tenant, inherited from "ESS Monitor Role"
-Read and submit privilege on ESS health check jobs, inherited from "ESS Monitor Role"
-Read privilege on all ESS metadata, inherited from "ESS Monitor Role"

Data Security Policies: there's a customized policy of "xxx_ESS_REQUEST_HISTORY", assuming that's what suggested in Doc ID 1980772.1 as you menationed.

Role Hierarchy: (three roles here)
-ESS Monitor Role
-ESS Administrator Role
-ESS Operator Role

As a result, the overview of our customized role on the privileges are:

See if it works for you, thank you.

Liwen

Eliran Hadad

Hi @Liwen Chiu,

Thank you for your comment, I've created a custom role that contains all role hierarchies with their privileges:

-ESS Monitor Role

-ESS Administrator Role

-ESS Operator Role

Function Security Policies: (All inherited from Role)

-Admin privilege all ESS metadata, inherited from "ESS Administrator Role"

-All privileges on all ESS Request of all tenant, inherited from "ESS Administrator Role"

-READ privilege on all ESS Request of all tenant, inherited from "ESS Monitor Role"

-Read and submit privilege on ESS health check jobs, inherited from "ESS Monitor Role"

-Read privilege on all ESS metadata, inherited from "ESS Monitor Role"

About the DSP- ESS_REQUEST_HISTORY:

New policy "View WSS output files":

Testing- No option to view the output:

BR

Eliran Hadad

Hi @Liwen Chiu,

Thank you for your comment, I've created a custom role with all role hierarchies:

Concerning Data Security Policies definition ESS_REQUEST_HISTORY:

Adding a DSP to the custom role:

After testing I can't view the output:

BR

Liwen Chiu

Hi @Eliran Hadad ,

I am assuming the difference is on the customized Data Security Policies definition ESS_REQUEST_HISTORYthat you have.

As I spot that you have a SQL on "JobDefinition://oracle/apps/ess/hcm/users/SyncRolesJob", and I found this code snippet online and tried at our TEST environment. The result shows that I only have a very limited set of reports to view:

select r.requestid,absparentid parent_id,r.application,r.username, r.TYPE,
r.definition,r.executable_status,v.value reportname,
to_timestamp(to_char(r.processstart, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') process_start,
to_timestamp(to_char(r.processend,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') process_end,
to_timestamp(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') system_date,
to_timestamp(to_char(scheduled,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') scheduled_time

from ess_request_history r, fusion.ess_request_property v

where 1=1 AND r.requestid = v.requestid
and r.definition = 'JobDefinition://oracle/apps/ess/hcm/users/SyncRolesJob'
and to_timestamp(to_char(r.processstart, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= to_date(to_char('2024-10-01'), 'yyyy-mm-dd')
and r.username='xxx' /* please put your user name here */

With the above code, I put that into a Data Model to check the result:

In our implementation, we don't have that ESS_REQUEST_HISTORY customized, we are using a Oracle factory-built one.

Hope it helps.

Liwen

Eliran Hadad

Hi @Liwen Chiu,

Thanks, I'll check that, after following Doc ID 1980772.1 it seems like adding a custom condition to ESS_REQUEST_HISTORY is unnecessary because I'm aiming to see the output of all the ESS job processes.But still, the configuration doesn't work.

Eliran Hadad

BR

Vmp

Hi ,

I have added DSP on the ESS_Monitor role by creating a condition on ESS_REQUEST_HISTORY Table . I couldn't view the output (Republish button) . Have u found out the solution ?

Eliran Hadad

Hi @Vmp,

According to Oracle support: "BI jobs are different from normal ESS jobs. As per the current functionality, BI Admin privilege is needed to view the BI report output" There is a Service Requests tab:

Ability to view output of others ESS jobs without BI Administrator role

huronstevesmith

Feb 1, 2021

Organization Name

Rotary

Description

We have followed the detailed instructions in:

How to View the Output of an ESS Jobs Submitted By Another User Based on Role? ( Doc ID 1980772.1 )

• We created a condition that allows supervisors to view subordinates requests.
  • Supervisors can view output/logs/cancel jobs...
    • This works only for jobs submitted via tools/scheduled processes when the output is under attachments. 
    • This does not work for jobs submitted via tools/reports and analytics or when output is displayed as as a link (see attached screenshot)
• We created SR 3-24761920631: I can view subordinates ESS jobs, and attachments but cannot view output.  This SR is now closed
  • ​This only solution was to also grant "BI Administrator" role to every Supervisor as instructed in "How to Add the BI Administrator Role to a user. ( Doc ID 2238277.1 )".
  • Having "BI Administrator" role gives too much access so it is not a solution for us.
  • We explored removing roles inside the overall BI Administrator role but we could not find a combination that gave us what we wanted without additional access we did not want.  It appeared to be all or nothing.

Use Case and Business Need

Having the ability of supervisors to view their subordinates requests:

1. Allows both supervisor and subordinate to see the same output without saving a file or emailing output to each other
2. Allows both supervisor and subordinate to see the same error and work through the resolution together.
3. Allows supervisor to view output from subordinate if they are out of office.

More details

1. Both supervisor and subordinate cannot see the same output without saving a file or emailing output to each other
2. Both supervisor and subordinate cannot see the same error and work through the resolution together.
3. Supervisor cannot view output from subordinate if they are out of office.

Original Idea Number: e2e530e5b4

---

View Post

this guideline is only for seeded ESSDoc ID 1980772, you can see there is also a reference BIP report,

BR