Secured BI Publisher with asignment based AoR Security

Question

Hi,

I created a BI Publisher report that are based on the secured view PER_ASSIGNMENT_SECURED_LIST_V and assigned the privilege "Report Assignment" to the user's custom role.  The data security should be based on the User AoR that is applied to the assignment level.  But for workers with multi-assignments, all the assignments numbers are returned and not only the one the user should have access to.

How I can secure my report to return only the assignment that the user have access to (like in the Oracle HCM Cloud page) ?  Maybe I forgot to setup something in the Oracle HCM Cloud ?

Here an example of the query:

SELECT assign.*

FROM per_assignment_secured_list_v assign

WHERE assign.assignment_type IN ('E', 'C')

AND assign.primary_flag = 'Y'

AND assign.assignment_status_type = 'ACTIVE'

AND TRUNC(sysdate) BETWEEN assign.effective_start_date AND assign.effective_end_date

MandeepGupta · Answer

https://community.oracle.com/products/oracleanalytics/discussion/comment/73965#Comment_73965

Yes, using the secured view will be sufficient. You can replace the table with secured view.

Thanks.

Consultant Ian · Answer

Hello MandeepGupta

Why the need to join per_all_assignments with per_assignment_secured_list_v ? Should using the secure view be sufficient?

MandeepGupta · Answer

https://community.oracle.com/products/oracleanalytics/discussion/comment/69511#Comment_69511

I see you are using person_id join, this might be the cause. Can you add per_all_assignments_m table also in your query and join the secured view assignment_id with paam.assignment_id and then check?

Thanks.