3 Replies Latest reply: Mar 21, 2013 11:12 AM by Turbokat RSS

    Seeting up position based security in OBI 10.1.3.4

    798959
      Hi Gurus,
      I have a requirement to enable the security for managers,so that when manager logs in he should be able to see all the information of his subordinates, the subordinates are not just restricted to immediate reports but also indirect reports
      eg: A reports to B and B reports to C
      So when B logs in he should have access to A data and when C logs in he should have access to B & C's data.
      I checked through the Oracle docs and found that, this can be accomplished by assigning users to a group ABC which has a filter set as
      Core."Dim - Position Security"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER")

      and Hierarchy column is calculated as
      Position Security"."Hierarchy Based Column"
      CASE VALUEOF(NQ_SESSION."HIER_LEVEL") WHEN 0 THEN Core."Dim - Position Security"."Current Base Level Login" WHEN 1 THEN Core."Dim - Position Security"."Current Level 1 Login" WHEN 2 THEN Core."Dim - Position Security"."Current Level 2 Login" WHEN 3 THEN Core."Dim - Position Security"."Current Level 3 Login" WHEN 4 THEN Core."Dim - Position Security"."Current Level 4 Login" WHEN 5 THEN Core."Dim - Position Security"."Current Level 5 Login" WHEN 6 THEN Core."Dim - Position Security"."Current Level 6 Login" WHEN 7 THEN Core."Dim - Position Security"."Current Level 7 Login" WHEN 8 THEN Core."Dim - Position Security"."Current Level 8 Login" WHEN 9 THEN Core."Dim - Position Security"."Current Level 9 Login" WHEN 10 THEN Core."Dim - Position Security"."Current Level 10 Login" WHEN 11 THEN Core."Dim - Position Security"."Current Level 11 Login" WHEN 12 THEN Core."Dim - Position Security"."Current Level 12 Login" WHEN 13 THEN Core."Dim - Position Security"."Current Level 13 Login" WHEN 14 THEN Core."Dim - Position Security"."Current Level 14 Login" WHEN 15 THEN Core."Dim - Position Security"."Current Level 15 Login" WHEN 16 THEN Core."Dim - Position Security"."Current Level 16 Login" WHEN 17 THEN Core."Dim - Position Security"."Current Top Level Login" END

      where Hier level variable logic is

      select round(FIXED_HIER_LEVEL) FROM VALUEOF(OLAPTBO).W_POSITION_DH WHERE BASE_LOGIN= ':USER' AND CURRENT_FLG='Y'

      When I checked the data in W_POSITON_DH table, if for a particualr employee the curent level is 10 then all current_level coulms above level 10 are populated with his supervisor details. My only concern over here is if I enable this group on my fact and dimensions, will I be able to accomplish the direct and indirect reportees data requirement as well..

      Please advise as this is required urgently.
        • 1. Re: Seeting up position based security in OBI 10.1.3.4
          Srini VEERAVALLI
          hmm
          It should be something like as in point 11 in http://www.rittmanmead.com/2012/03/obiee-11g-security-week-row-level-security/

          If I find will let you know any other link.

          If helps mark
          • 2. Re: Seeting up position based security in OBI 10.1.3.4
            998387
            Thanks for the link ...I will check on it
            • 3. Re: Seeting up position based security in OBI 10.1.3.4
              Turbokat
              What is your source application . ?Siebel CRM , Oracle EBS .? it depends on that so lets say for Oracle EBS. BTW use index col instead of CASE WHEN .. :)

              This will enable primary position based hierarchy which means that if user is a manager , this user should be able to see all employees data that are reporting under him/her.

              Below in the info per guide. Hope this helps.

              Session variable: HIER_LEVEL. It is populated by the Initialization block 'User Hierarchy Level' using the following SQL:

              Select round(FIXED_HIER_LEVEL) FROM VALUEOF(OLAPTBO).W_POSITION_DH WHERE BASE_LOGIN= ':USER' AND CURRENT_FLG='Y'

              HIER_LEVEL value will take a number between 0 and 9, and will designate the level of the user in the Position (or Employee) hierarchy of the company. For example the CEO of the company is the only Employee who's HIER_LEVEL takes the value 9, if the employee hierarchy is a full tree.

              A logical dimension 'Dim - Security' joined to the supported facts with Primary Position/Employee - based security. This logical dimension is defined using on the physical table W_POSITION_DH.

              A logical column in this dimension, called 'Hierarchy Based Column', and defined as follows:

              "INDEXCOL(VALUEOF(NQ_SESSION."HIER_LEVEL"), "Core"."Dim - Security Dimension"."Current Base Level Login", "Core"."Dim - Security Dimension"."Current Level 1 Login", "Core"."Dim - Security Dimension"."Current Level 2 Login", "Core"."Dim - Security Dimension"."Current Level 3 Login", "Core"."Dim - Security Dimension"."Current Level 4 Login", "Core"."Dim - Security Dimension"."Current Level 5 Login", "Core"."Dim - Security Dimension"."Current Level 6 Login", "Core"."Dim - Security Dimension"."Current Level 7 Login", "Core"."Dim - Security Dimension"."Current Level 8 Login", "Core"."Dim - Security Dimension"."Current Top Level Login")".

              The IndexCol function in this definition makes the Hierarchy Based Column default to one of the logical columns in the list based on the value of HIER_LEVEL. So if HIER_LEVEL is 0, the new column will default to the first column in the list, etc.

              A filter in the security group 'Primary Employee/Position Hierarchy-based Security' defined as follows: ("Core"."Dim - Security Dimension"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER")),

              A user needs to be a member of the security group 'Primary Employee/Position Hierarchy-based Security', through one of his responsibilities (for Siebel and Oracle EBS Applications) and Roles (for PeopleSoft application), for the data security filters to apply. Users are assigned to this security group based on their responsibilities, using the Initialization block: Authorization, defined in Section 6.6.3, "Initialization Blocks Used For Security Oracle Business Intelligence Applications". Out of the box, this initialization block is populated using the following SQL:

              select 'GROUP', CASE VALUEOF(NQ_SESSION.HIER_LEVEL)

              WHEN 0 THEN 'Hierarchy Level (Base)'

              when 1 then 'Hierarchy Level 1'

              when 2 then 'Hierarchy Level 2'

              when 3 then 'Hierarchy Level 3'

              when 4 then 'Hierarchy Level 4'

              when 5 then 'Hierarchy Level 5'

              when 6 then 'Hierarchy Level 6'

              when 7 then 'Hierarchy Level 7'

              when 8 then 'Hierarchy Level 8'

              When 9 then 'Hierarchy Level (Top)'

              ELSE 'NOGROUP' END from VALUEOF(TBO).S_DUAL

              To add security support to a fact

              Join the underlying physical table to Dim_W_POSITION_DH_Position_Hierarchy.

              This assumes you already created the appropriate foreign key in the fact table, and populated it correctly.

              Join the logical table to the Dim - Security Dimension.

              Open the security group screen using Manage/Security in Oracle BI Administrator.

              Right-click the group 'Primary Employee/Position Hierarchy-based Security', and choose Properties.

              In the Properties dialog, click the Permissions box and select the Filter tab.

              To add a new filter, click on the Add button.

              In the new dialog, select the Business Model tab, and find the logical table: Dim - Agreement.

              A new record will be added to the list of Filters automatically.

              Click on the ellipsis box, and add the condition "Core"."Dim - Security Dimension"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER") in the Security Filter Expression Builder and click OK.

              Mark if helps.!

              Let us know if you have any questions.

              Thanks,
              SVS