This discussion is archived
13 Replies Latest reply: Dec 10, 2012 9:08 AM by Vigneswar Battu RSS

Security in Reporting

christm31 Newbie
Currently Being Moderated
Hi

I have setup a new responsiblity which has a security profile attached to it to restrict the employees that can be seen by that responsiblity to a certain section of people.
This is all working fine.

However, I now need a report within this reponsiblity (and other similar secured responsiblities) which will only bring back the same employees that the Enter and Maintain form does - i.e the report takes into account the security profile associated to the responsiblity running the report.

Is this possible? As far as I can see, if I put in a report into this responsibility, it brings back all employees and doesn't automatically look for the security profile.

Thanks

Martin
  • 1. Re: Security in Reporting
    Vigneswar Battu Guru
    Currently Being Moderated
    Hi Martin,

    When you say a Report, what exactly do you mean ?

    The security works via database views.
    So, if you use proper views instead of actual tables in your reports, it will ensure that the data is filtered as per the responsibility(security profile).

    So in your reports you should use -
    per_people_f instead of per_all_people_f
    per_assignments_f instead of per_all_assignments_f
    and so on..

    Hope that helps,

    Cheers,
    Vignesh
  • 2. Re: Security in Reporting
    christm31 Newbie
    Currently Being Moderated
    OK Thanks, I'll try that.
    I mean a Reports 6i report which is based on HR tables but as you point out - it's based on per_all_people_f, so I will rewrite it to be based on per_people_f

    Thanks

    Martin
  • 3. Re: Security in Reporting
    christm31 Newbie
    Currently Being Moderated
    Hi

    I have rewritten my report query as outlined above.
    It now is the following:
    select per.employee_number, per.first_name, per.last_name, per.known_as, pos.name sys_job_title, org.name organization
    from per_people_f per, per_assignments_f asg, per_all_positions pos, hr_organization_units org
    where sysdate between per.effective_start_date and per.effective_end_date
    and person_type_id = '125'
    and per.person_id = asg.person_id
    and sysdate between asg.effective_start_date and asg.effective_end_date
    and asg.position_id = pos.position_id
    and asg.organization_id = org.organization_id

    However when I run the report from a responsiblity with a Security Profile attached to it, it is still bringing back all employees, rather than just the ones which meet the security criteria.

    Any suggestions?

    Thanks

    Martin
  • 4. Re: Security in Reporting
    975131 Newbie
    Currently Being Moderated
    Hi,
    Basically, PER_PEOPLE_F & PER_ASSIGNMENT_F are used for security profile control to select data.
    But there are some problem for customized report & form.
    You need to initiate system executing IDs by your self, including user_id, resp_id, resp_appl_id.
    If you need to work on report, Here are suggestions:
    1.Trigger: Before Parameter Form
    function BeforePForm return boolean is
    LN_USER_ID NUMBER := FND_PROFILE.VALUE('USER_ID');
    LN_RESP_ID NUMBER := FND_PROFILE.VALUE('RESP_ID');
    LN_RESP_APPL_ID NUMBER := FND_PROFILE.VALUE('RESP_APPL_ID');
    LN_SECURITY_GROUP_ID NUMBER := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
    --LN_SESSION_ID             NUMBER := USERENV('SESSIONID');
    --LD_SESSION_EFFECTIVE_DATE DATE;
    begin
    --Security Control setting
    FND_GLOBAL.APPS_INITIALIZE(LN_USER_ID --user_id in number
    ,LN_RESP_ID --resp_id in number
    ,LN_RESP_APPL_ID --resp_appl_id in number
    ,LN_SECURITY_GROUP_ID); --security_group_id in number default 0, server_id in number default -1);

    --Session
    --IF it doesn't work, you need to insert into FND_SESSIONS manually
    return (TRUE);
    end;

    2.Trigger: After Report
    function AfterReport return boolean is
    begin
    --Session
    --IF you has insert into FND_SESSIONS manually, you need to delete record that you inserted manually.
    return (TRUE);
    end;

    Best Regards,
    Changhua
  • 5. Re: Security in Reporting
    Vigneswar Battu Guru
    Currently Being Moderated
    Initialise your session and see what the views return
    alter session set nls_language = 'AMERICAN' ;
     
    begin
     apps.fnd_global.apps_initialize(12447,20536,800);  -- user_id, responsibility_id, application_id
     end;
     
    INSERT into fnd_sessions 
               (session_id, effective_date) 
    SELECT userenv('sessionid'), sysdate 
       FROM sys.dual;
    Just select from per_people_f after you initialise the session and see if the data is getting filtered.

    Cheers,
    Vignesh
  • 6. Re: Security in Reporting
    christm31 Newbie
    Currently Being Moderated
    Just attacking these replies in order.
    I have done the following in my BeforePForm trigger:
    function BeforePForm return boolean is
    LN_USER_ID NUMBER := FND_PROFILE.VALUE('USER_ID');
    LN_RESP_ID NUMBER := FND_PROFILE.VALUE('RESP_ID');
    LN_RESP_APPL_ID NUMBER := FND_PROFILE.VALUE('RESP_APPL_ID');
    LN_SECURITY_GROUP_ID NUMBER := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
    --LN_SESSION_ID NUMBER := USERENV('SESSIONID');
    --LD_SESSION_EFFECTIVE_DATE DATE;
    begin
    --Security Control setting
    FND_GLOBAL.APPS_INITIALIZE(LN_USER_ID --user_id in number
    ,LN_RESP_ID --resp_id in number
    ,LN_RESP_APPL_ID --resp_appl_id in number
    ,LN_SECURITY_GROUP_ID); --security_group_id in number default 0, server_id in number default -1);
    RETURN (TRUE);
    end;

    When I run the report in Reports Builder, it is fine but when I run it through the application, it's failing with REP-1401: 'beforepform': Fatal PL/SQL error occurred.

    Any ideas why?

    Thanks

    Martin
  • 7. Re: Security in Reporting
    975131 Newbie
    Currently Being Moderated
    Hi,
    Please check the profile parameters strings.
    Follow steps below:
    1.Run a Form Program
    2.Run Menu: Help->Diagnostics->Examine
    3.Select Block = $PROFILES$, and find Fiels in ('USER_ID', 'RESP_ID', 'RESP_APPL_ID', 'SECURITY_GROUP_ID') =>Check these string is correct or not.

    If those strings are correct, please delete that procedure and re-create.
    Using mouse device to point at "Report Trigger->BEFORE PARAMETER FORM" and click right key, then choosing "PL/SQL Editor" to open editor.
    Just copy those variables and statements into existing procedure area. Do not copy whole procedure and paste directly.
    Trying this way to re-build procedure.

    Best Regard's,
    Changhua
  • 8. Re: Security in Reporting
    christm31 Newbie
    Currently Being Moderated
    Hi

    When I check the values in Examine I'm getting the following:
    'USER_ID' - 1077
    'RESP_ID' - 50862
    'RESP_APPL_ID' - 800
    'SECURITY_GROUP_ID' - 0

    I assume this means it's all working - I'm not sure what I'm meant to do now with this information

    Thanks

    Martin
  • 9. Re: Security in Reporting
    975131 Newbie
    Currently Being Moderated
    Hi,
    Could you try to remove those script then submit report again?
    Most REP-1401 error occurred on formula or compute column has wrong data type or PL/SQL scripts has assign wrong data type variables.
    Try to find out whether any ORA-xxxxx error code.

    Best Regard's,
    Changhua
  • 10. Re: Security in Reporting
    christm31 Newbie
    Currently Being Moderated
    Hi

    If I remove the code in the BeforePform trigger, the report runs successfully but brings back all employees, rather than only those visible for the responsibilty.

    Thanks

    Martin
  • 11. Re: Security in Reporting
    user122479 Newbie
    Currently Being Moderated
    Did you put delete record in after report trigger?

    After Report
    function AfterReport return boolean is
    begin
    --Session
    --IF you has insert into FND_SESSIONS manually, you need to delete record that you inserted manually.
    return (TRUE);
    end;

    1. Delete manually your session record ind fnd_session table
    2. Add after report trigger
    3. Try it again in App

    Thanks
    Hieu
  • 12. Re: Security in Reporting
    christm31 Newbie
    Currently Being Moderated
    Hi

    I didn't do a manual insert into FND_SESSIONS - therefore I didn't put in a delete.

    Should I have done that?

    I assumed this would be just a case of pointing at the per_person_f and the application would automatically filter data.

    I must be missing a step somewhere. Sorry for delay, got pulled off this to work on something else, just going back onto it today.

    Thanks

    Martin
  • 13. Re: Security in Reporting
    Vigneswar Battu Guru
    Currently Being Moderated
    Hi Martin,

    Did you check the previous post -
    How do you make a concurrent report use security profiles?

    Basically -
    How To Enable Hr Security on Custom Reports? [ID 369345.1]

    Cheers,
    Vignesh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points