Oracle E-Business Suite Row Level Security in OBIA (BI APPS)

There are a lot of documents already about OBIA and E-Business suite integration, however I could not find much information on the real specifics i.e. How it actually works once EBS single sign-on has been configured! Therefore, I've written this post for myself really - to keep fresh in my mind the architecture.

I hope you find it useful too :)

EBS and OBIEE / OBIA: Row Level Security

Row-Level Security enables us to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

In the context of Oracle E-Business Suite (EBS) Financial Analytics and Procurement Analytics, row level security is enforced for users based upon the EBS responsibility that they use to connect to EBS. The Oracle EBS responsibility has an associated fixed assets book, GL ledger, operating unit, inventory organization etc.

In the context of OBIA, row level security is enforced using Initialisation blocks that are executed when a user connects to OBIEE. These initialization blocks set variable values defined in the RPD (such as ledger, OU_Org etc). These variables are then used as data filters in the RPD to limit the data returned when a BI User queries a subject area e.g.

• BI User connects to OBIEE using the responsibility ‘Financial Analyst’
• During the connection, an initialization block runs in OBIEE that sets a session variable ‘LEDGER’.
• This variable has been used in the RPD ‘data filters’ to add an additional filter (WHERE gl_ledger_id = LEDGER variable) to the analyses created by the User.

Looking at the standard Oracle OBIA solution for Oracle EBS ‘Operating Unit’ security in more detail:




 ‘Out of the box’ OBIA Row level security model

OBIA has a number of seeded Application roles that are granted to Users to access the system e.g. 

• Role ‘Purchasing Buyer’ has access to the Purchasing Subject Areas. 
• Role ‘Financial Analyst’ has access to the Financials Subject Areas

These application roles have:

• Permissions to open and view subject areas.
• Associated data filters that add SQL where clauses to any query executed using the application role. These data filters are used in OBIA to apply the ledger or fa book or inventory org or operating unit security to Users requests in OBIEE.

and also

• Can be members of other application roles and inherit the permissions and data filters from these parent members.

To view application roles, open the RPD and navigate to ‘Manage, Identity’.

Role: Purchasing Buyer

NOTE: In this example, role ‘Purchasing Buyer’ is also a member of two other roles:

• Operating Unit Org-based Security
• OBIA_PROCUREMENT_CURRENCY_PREFERENCES

Click on Permissions:

Under Object Permissions, role ‘Purchasing Buyer’ has been granted direct access to read the list of Procurement subject areas. 

Therefore, if we create a new Purchasing responsibility and make it a member of ‘Purchasing Buyer’, our new role would also inherit this subject area access.

Click on the Data Filters tab for the Purchasing Buyer application role.

Note – this this is empty. Role ‘Purchasing Buyer’ does not have any data filters itself.

To repeat the key point here! ‘Purchasing Buyer’ is a member of the role ‘Operating Unit Org Based Security’ – so will inherit any permissions that have been created for role ‘Operating Unit Org Based Security’

Opening the permissions of ‘Operating Unit Org Based Security’, we see that this application role has no object permissions. However, it has a large list of data filters:

Therefore, when a BI Users accesses OBIEE using a role (such as Purchasing Buyer) that is a member of the role ‘Operating Unit Org Based Security’, any queries they generate will automatically have these data filters applied to them to restrict the records returned.

There are similar application roles for the other EBS row level constraints (Fixed Assets Book, GL Ledger, Inventory Organization etc) that have the required data filters to enforce the appropriate security on Users analyses, dashboards and queries.

In the case of the ‘Operating Unit Org Based Security’, the data filter restricts the records returned to Users by variable ‘OU_ORG’ e.g.

"Core"."Dim - Business Unit"."Business Unit Number" =  VALUEOF(NQ_SESSION."OU_ORG")

This filter using OU_ORG is added as extra filter to any analysis created by the BI User.

So what is OU_ORG?

OU_ORG is a variable that stores the list of operating units that the user is allowed to access. This variable is populated by an OBIEE Initialisation block called ‘Operating Unit Organizations EBS’ that is fired automatically when the user connects to OBIEE.

Initialisation Blocks:

Initialization blocks are used to initialize dynamic repository variables, system session variables, and nonsystem session variables.

To view the initialization blocks: Open the RPD and navigate to ‘Manage’,’Variables’:

Looking at the SQL code in the initialization block that populates the variable 'OU_ORG, we can see how it selects the operating unit (org_id) directly from Oracle EBS:

Note: Apologies for the SHOUTING in the SQL - that is the way it is 'out of the box' :)

SELECT DISTINCT 'OU_ORG____EBS', COALESCE (TO_CHAR(PER_ORGANIZATION_LIST.ORGANIZATION_ID) ,'-999999')
FROM PER_ORGANIZATION_LIST,
(SELECT FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) PROFILE_ID
FROM
(SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER') END)
AND RESPONSIBILITY_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)
)
)
WHERE
PER_ORGANIZATION_LIST.SECURITY_PROFILE_ID = PROFILE_ID

UNION

SELECT
DISTINCT 'OU_ORG____EBS', COALESCE (FND_PROFILE.VALUE_SPECIFIC('ORG_ID', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID)  ,'-999999') ORGANIZATION_ID
FROM
(SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER') END)
AND RESPONSIBILITY_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)
)
WHERE NOT EXISTS
(SELECT DISTINCT 'OU_ORG____EBS', COALESCE (TO_CHAR(PER_ORGANIZATION_LIST.ORGANIZATION_ID) ,'-999999')
FROM PER_ORGANIZATION_LIST,
(SELECT FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) PROFILE_ID
FROM
(SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER') END)
AND RESPONSIBILITY_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)
)
)
WHERE
PER_ORGANIZATION_LIST.SECURITY_PROFILE_ID = PROFILE_ID
)

The following initialization blocks are key in driving the row-level security integration with Oracle EBS Financials:

Initialisation Block	Description
Ledgers EBS12	Populates the variable ‘LEDGER’ with the list of ledgers that the BI User is authorized to access in EBS
Inventory Organizations EBS	Populates the variable ‘INV ORG’ with the list of inventory organizations that the BI User is authorized to access in EBS
Operating Unit Organizations EBS	Populates the variable ‘OU ORG’ with the list of operating units that the BI User is authorized to access in EBS
Fixed Asset Book EBS	Populates the variable ‘ASSET BOOK CODE’ with the list of fixed asset books that the BI User is authorized to access in EBS
EBS Single Sign-on integration	Required for the single sign on for users from EBS
EBS Security context	Sets the EBS security context and cookie in memory

 That's the end of my summary :)