Simulate Oracle eBS to Oracle BI login via SQL

There is an interesting (for me at least) topic on OTN about integrating Oracle eBS security into Oracle BI. It is a topic about getting Oracle eBS HR-Security to work in Oracle BI. Robin Moffat has a blogpost about;  ‘Validating EBS-BI authentication, without BI’. He refers to an My Oracle Support article (758392.1), which provides some (additional) troubleshooting details.

When I want to simulate Oracle eBS to Oracle BI login via SQL, I use some SQL-statements / scripts, to see what happens or at least should happen. If I need to know which HR-Orgs I could expect based on the Oracle eBS HR-Security I use the following scripts;

Query User – Responsibility — Rol

Check which responsibilities a user has within Oracle eBS.

select fu.user_id
 , frv.responsibility_id
 , fa.application_id
 , frv.responsibility_key
 , frv.responsibility_name
 from apps.fnd_responsibility_vl frv
 , applsys.fnd_application fa
 , applsys.fnd_request_groups frg
 , apps.fnd_user_resp_groups_all furga
 , applsys.fnd_user fu
 where fa.application_id = frv.application_id
 and TRUNC ( SYSDATE ) between fu.start_date
 and NVL ( fu.end_date
 , to_date ( '31-DEC-4712'
 , 'DD-MON-YYYY' ) )
 and fu.user_id = furga.user_id
 and TRUNC ( SYSDATE ) between furga.start_date
 and NVL ( furga.end_date
 , to_date ( '31-DEC-4712'
 , 'DD-MON-YYYY' ) )
 and furga.responsibility_id = frv.responsibility_id
 and frv.request_group_id = frg.request_group_id(+)
 and fu.user_name like :p_user_name
 and frv.responsibility_name like :p_responsibility_name
order by frv.responsibility_name

Excecute Apps Initialize

Run the Apps Initialize script to get the session in context of the user / responsibility combination of your choice, based on the previous query.

BEGIN
apps.fnd_global.apps_initialize(user_id, resposnsibility_id, resposnsibility_application_id, security_group_id);
END;

Validate the Context

Check the output of the previous step

select fnd_global.resp_id
 , fnd_global.resp_appl_id
 , fnd_global.security_group_id
 , fnd_global.resp_name
 , fnd_global.user_id
 , fnd_global.employee_id
 , fnd_global.user_name
 , ( select distinct responsibility_key
 from fnd_responsibility
 where responsibility_id = fnd_global.resp_id )
 responsibility_key
 from DUAL

Validate HR-Security

If all the above steps have completed like expected, you are able to check the query within the Oracle BI Initialization Block (IB) related to HR-Security. In my case it’s IB; ‘HR Organization’. This IB has the following query;

SELECT
 DISTINCT 'HR_ORG'
,TO_CHAR(SEC_DET.ORGANIZATION_ID)
FROM
(
SELECT
 'HR_ORG',
 ASG.ORGANIZATION_ID
FROM
 FND_USER_RESP_GROUPS URP
,FND_USER USR
,PER_SECURITY_PROFILES PSEC
,PER_PERSON_LIST PER
,PER_ALL_ASSIGNMENTS_F ASG
WHERE
 URP.START_DATE < TRUNC(SYSDATE)
AND (CASE WHEN URP.END_DATE IS NULL THEN TRUNC(SYSDATE) ELSE TO_DATE(URP.END_DATE) END) >= TRUNC(SYSDATE)
AND USR.USER_NAME = 'VALUEOF(NQ_SESSION.USER)'
AND USR.USER_ID = URP.USER_ID
AND TRUNC(SYSDATE)
 BETWEEN URP.START_DATE AND NVL(URP.END_DATE, HR_GENERAL.END_OF_TIME)
AND PSEC.SECURITY_PROFILE_ID = FND_PROFILE.VALUE_SPECIFIC('PER_SECURITY_PROFILE_ID', URP.USER_ID, URP.RESPONSIBILITY_ID, URP.RESPONSIBILITY_APPLICATION_ID)
AND PER.SECURITY_PROFILE_ID = PSEC.SECURITY_PROFILE_ID
AND PER.PERSON_ID = ASG.PERSON_ID
AND ASG.PERSON_ID = USR.EMPLOYEE_ID
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND URP.RESPONSIBILITY_ID = DECODE(FND_GLOBAL.RESP_ID,
 -1, URP.RESPONSIBILITY_ID,
 NULL, URP.RESPONSIBILITY_ID,
 FND_GLOBAL.RESP_ID)
UNION
SELECT DISTINCT 'HR_ORG',
 ORGANIZATION_ID
FROM PER_ALL_ASSIGNMENTS_F ASG,
 FND_USER USR
WHERE ASG.PERSON_ID = USR.EMPLOYEE_ID
AND USR.USER_NAME = 'VALUEOF(NQ_SESSION.USER)'
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND ASG.PRIMARY_FLAG = 'Y'
) SEC_DET

You should replace the ‘VALUEOF(NQ_SESSION.USER)’ part with either the user or fnd_global.user_name.

Now you are able to see whether the Initialization Block is working like expected and retrieves the same values as it does in Oracle eBS. If you can conform these steps and the the Security isn’t working, it’s probably because one of the related IB’s doesn’t retrieve (all) the necessary values.

This whole post assumes that the integration is setup correctly.

Good Luck.

Integrating Oracle eBS R12 and Oracle BI 11g

I have made a blogpost in the past about integrating Oracle eBS R12 and Oracle BI 10g. In the course of an upgrade of Oracle BI Applications (OBIA) to OBIA 7.9.6.3, I came to the subject of integrating  Oracle eBS R12 and Oracle BI 11g. Of course you should start with the documentation. Next to that, Oracle provides a note on Oracle Support (ID 1343143.1). A lot of integration steps are equal to the 10g version.

In short:

There are two sides (Oracle eBS & Oracle BI) where you need to make some preparations.

Oracle eBS:

You need to define the link from to Oracle eBS to Oracle BI. This functionality hasn’t changed and I have described that process here. Combined with the ‘FND: Oracle Business Intelligence Suite EE base URL’-profile option in Oracle eBS, you now are ready to navigate from Oracle eBS to Oracle BI.

Oracle BI:

Now the Oracle eBS side is ready, you’ll have to prepare Oracle BI for accepting login requests from Oracle eBS. This parts differs a little from 10g. The changes in the Repository are still the same and consist of validation of the ICX-cookie in the Oracle eBS Connection Pool and the Authentication via Session variables. You can choose to either authenticate via the GROUP- system variable or directly via the new 11g ROLES-system variable.

After that you need to change the Oracle BI configuration;

  • authenticationschemas.xml (ORACLE_HOME/bifoundation/web/display)

authenticationschemas.xml (SchemaKeyVariable)

authenticationschemas.xml

  • instanceconfig.xml (ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1)

instanceconfig.xml

Note: Don’t get mislead by the following sentence; ‘<!–This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control–>’. You must adjust these settings directly in the instanceconfig.xml itself.

This should (all) be sufficient to log into Oracle BI via a selected responsibility in Oracle.

In a following post I will cover the subject of applying Data Security in Oracle BI, based on the Oracle eBS Responsibility.

Automatic login to Oracle BI via Oracle eBS

I have been blogging about the integration between Oracle eBS and Oracle BI. It’s possible to login to Oracle BI via Oracle eBS. In these situations it’s necessarily to have an account and a responsibility in Oracle eBS.

Some people make use of Oracle BI and not Oracle eBS. Normally the wouldn’t need an Oracle eBS account.

In cases of an integration between the two systems you have to login to Oracle eBS, select a responsibility and select a link to Oracle BI.

For some people these are to many steps to get to Oracle BI.

It’s possible to set a default start page when you login to Oracle eBS. You could eg. choose ‘Oracle BI Answers’ as your default start page.

This could be set per user via the Preferences of the user who has logged in.

Here you could set the start page

Be careful setting this option, because once you set it, you wont be able to return to Oracle eBS. In such a case when a user wants to get rid of this startpage use the Oracle eBS Profile Option; ‘Applications Start page’. This option could be set for a specific user or a responsibility via the ‘System Administrator’-responsibility.

Via ‘Profile’, ‘System’, you can find the specific ‘System Profile Value’

For the user; ‘DBAKBOORD’ the value of this profile is;

This value can be retrieved from the database via the following query;


select fpov.level_value user_id

, fu.user_name

, fpov.profile_option_value

from applsys.fnd_profile_option_values fpov

, apps.fnd_profile_options_vl fpo

, applsys.fnd_user fu

where fpo.profile_option_id = fpov.profile_option_id

and fpo.application_id = fpov.application_id

and fu.user_id = fpov.level_value

and fpo.profile_option_name = 'APPLICATIONS_START_PAGE'

and fpov.level_id = 10004

The output of this query is the following;

Using the Oracle eBS Profile Option; ‘Applications Start page’ could also be an easy solution to set a startpage for a whole group of user. After setting this option they all will login to Oracle BI Answers directly without selecting the menuoption in Oracle eBS first.

SLS: Subledger Security

I am in the proces of implementing security for Oracle BI Apps 7.9.6 in a Oracle eBS R12 environment. One of the requirements is Subledger Security (User Guide, My Oracle Support).

As per the documentation; Subledger Security is an extension to Oracle Financials that enables the user to selectively partition data within a single install of Oracle Financials. Subledger Security provides a system where all business units can access their own financial information only.

In my clients case it makes it possible to secure parties, customers and suppliers. Certain customers are only visible for selected responsibilities within the same operating unit.

For implementing the security I am interested in the database-implementation of SLS. Check the following diagram:

For all the Subledger Security tables you have to refer to the IGI-scheme in Oracle eBS:

Whether Subledger Security is applied depends on two profiles;

  • ‘Subledger Security : Security Group’
  • ‘Subledger Security : SLS Responsibility’

A query to retrieve the values of these profiles could be;


SELECT r.responsibility_id ,
  r.responsibility_key ,
  r.responsibility_name ,
  nvl(sr.profile_option_value, 'N') sls_responsibility ,
  nvl(sg.profile_option_value, 'No SLS Responsibility') sls_security_group ,
  nvl(i.sls_group, 'No SLS Responsibility') sls_group
FROM
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : Security Group'
  AND t.language                 = 'US'
  ) sg ,
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : SLS Responsibility'
  AND t.language                 = 'US'
  ) sr ,
  igi_sls_groups i,
  fnd_responsibility_vl r
WHERE sr.responsibility_id = sg.responsibility_id (+)
AND sr.responsibility_id (+)  = r.responsibility_id
AND i.sls_group (+)        = sg.profile_option_value ;

Other queries to find out which data a responsibility is allowed to view:

The query below gives insight in the available SLS Security Groups, eg”.: ‘AR Security for Dept A’

SELECT sls_groups --'AR Security for Dept A' 
FROM   igi_sls_groups
WHERE  sls_group_type = 'S';

The query below gives insight in which tables are ‘SLS Security’-enabled within a certain SLS Security Group.

SELECT sls_group --'AR Security for Dept A'
,      table_name -- HZ_PARTIES 
FROM   igi_sls_enabled_alloc_v;

The query below gives insight in which ‘IG_SLS_#’-table the ‘SLS-Security’-details are stored. Depending on the configuration, multiple ‘IG_SLS_#’-tables  (IG_SLS_1, IG_SLS_2, IG_SLS_3, IG_SLS_4, IG_SLS_5, etc.) can exist.

SELECT owner -- AR
,      table_name -- HZ_PARTIES
,      sls_table_name -- IG_SLS_1 
FROM igi_sls_secure_tables;

The query below gives insight in the actual id’s which are secured by the ‘SLS-Security’-settings. The rowid of the source table is stored in the ‘IG_SLS_#’-table.

SELECT sls1.sls_rowid    
,      sls1.sls_sec_grp    
,      p1.party_id    
,      p1.party_number    
,      p1.party_name 
FROM   igi_sls_1 sls1 -- HZ_PARTIES    
,      hz_parties p1
WHERE  p1.ROWID = sls1.sls_rowid;

Using these queries, you should be able to select which id’s are available to a selected responsibility. Within the Oracle eBS R12 application, this is managed by policies (VPD – Virtual Private Database, RLS – Row Level Security)

Check the following query, to see how it’s setup;

select object_owner --'AR'
,      object_name  -- 'HZ_PARTIES'
,      policy_name  -- 'IGI_SLS_3_POL'
,      pf_owner     -- 'APPS'
,      function     --'IGI_SLS_1_FUN'
from   dba_policies
where  policy_name like 'IGI%'

With this information I am able to built ‘SLS Security’ into Oracle BI Applications.

Oracle BI Applications – Security

I recently had to digg into the standard Oracle BI Applications Security Oracle delivers out of the box. The clients had two security requirements.

The first one was a Data Security requirement. When a user logs in he is presented with his / her organization’s data only. So a user from organization ‘001’  only sees data from organization ‘001’ . Organization user ‘002’ only sees organization ‘002’ and so on.

The second requirement was Object Security. Each function has access to a group of objects, regardless of their organization. So all ‘General Ledger Super Users’ have access to the same objects whether they are in organization ‘001’ or ‘002’

 

The client has the following installation:

  • Oracle eBS R12 (12.1.1)
  • Oracle BI Apps  (7.9.6)
  • Oracle BI EE (10.1.3.4.1)

In general the standard Oracle BI Applications security solution is built around;

  • Groups (Repository, Web Catalog)
  • Session Variables
  • Business Model Filters
  • Permissions
  • Priviliges

In more detail the following steps have to be performed:

Set the application context

The Oracle BI Applications session should get the same security context as Oracle eBS, where you navigate from.

 During logon the ‘EBS Security Context’-Initialization Block is called and executed. The Oracle eBS session cookie is used to set the context. The Initialization block ‘fills’ the variables with information about which user / responsibility combination is logged on. These variable will be used in other Initialiation Blocks along the road.

call APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')

 If all goes well, the Oracle BI Apps session will get the same context as Oracle eBS. Otherwisse there are 3 options :

  • SESSION_DOES_NOT_EXIST,
  • SESSION_NOT_VALID,
  • SESSION_EXPIRED.

Repository Groups

There are two important Groups;

  • Responsibility Groups (Should the Responsibilities in Oracle eBS)
  • Security Groups (These will be used for the Data Security)  
    • –> Examples:
    • Ledger-based Security
    • Inventory Org-based Security
    • Operating Unit Org-based Security

 Variables

The security group someone belongs to is detemined by session variables, which are set during logon.

Initialization Blocks and Variables are the necessary objects to examine. If we relate to the example in the previous section, we could state that the following three Initialization Blocks are most important;

  • Ledgers
  • Inventory Organizations
  • Operating Unit Organizations
  •  

    Data Security

    Data Security is being set up via, Security Groups and Business Model Filters.

     

    As from now on, each query which is composited with a table linked to a Security Group a “Where-clause” is added.

    Presentation Catalog Groups

    The groups as they are created in the repository should also be created in the Web Catalog.

     

     Object Security

    You can use the Catalog Groups to grant or revoke acces to certain objects (Folders, Answers, Dashboards, etc) in the Web Catalog. The same groups an be used to mange the privilliges within the Web Catalog. Use the Security Groups in the Repository to control the Access to the Subject Area’s in the Presentation Layer.

    Check my previous post about navigating from Oracle eBS to Oracle BI EE.