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.
- 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
)
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 :)
