Customer wants to secure their data based on things like Ledger, Company, Operating Unit, etc. All users who login via Oracle eBS should inherit (based on the current responsibility) the same security settings in Oracle BI. The following is a possible solution.
- Oracle eBS R12.1.1
- Oracle BI EE 220.127.116.11.0
- Oracle BIA 18.104.22.168
The setup consists of a few different parts
- Integration Oracle eBS and Oracle BI
- Data Security
The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in Oracle eBS
- Create ‘BI Type User’-profile option
- Assign ‘BI Type User’-profile option to Responsibility
- Assign Responsibility to User
Each Responsibility has either a specific ‘BI Type User’-profile option or a ‘BI Type User’-profile option on Site level. A view (xx_obia_user_groups_v) in Oracle eBS ‘holds’ the profile option information.
select fpov.level_value responsibility_id , fpov.level_value_application_id application_id , 'OBIA ' || fpov.profile_option_value autorisatierol_code , fl.meaning autorisatierol from applsys.fnd_profile_option_values fpov , apps.fnd_profile_options_vl fpo , apps.fnd_lookups fl where fpo.profile_option_id = fpov.profile_option_id and fpo.application_id = fpov.application_id and fpov.profile_option_value = fl.lookup_code and fl.lookup_type = 'BI_TYPE_GEBRUIKER' and fpo.profile_option_name = 'XXBI_TYPE_GEBRUIKER' and fpov.level_id = 10003
In Oracle BI, there is a Initialization Block which populates the; ROLES Session Variable
select ( select sector from apps.xx_obia_user_groups_v where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)' and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) ) || ';' || ( select autorisatierol_code "ROLES" from apps.xx_obia_user_groups_v where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)' and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) ) || ';' || ( select responsibility_key "ROLES" from apps.xx_obia_user_groups_v where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)' and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) ) "ROLES" from DUAL
Oracle Enterprise Manager (EM)
In the EM ‘all’ the different Application Roles, related to the Data Security are created.
There are a few different Application Roles;
- Out-of the box
- Data Security
- eBS Profile
- Administration privileges.
- Create, use or consume content.
- Use / consume content,
- Every authenticated user.
- Component connections between products.
Data Security & eBS Profile
Oracle BI Administrator (Identity Manager)
In the Identity Manager, the Business Model Filters are applied to the Data Security Application Roles
The Business Model Filters are based on the Initialization Blocks. Some out-of-the-box, others custom.
This setup should be sufficient to apply Data Security to all queries, which query the Logical Tables with the Business Model Filters applied to it.
Note: Application roles data filters won’t apply for users with BI Administrator role.
By definition the BIAdministrator application role is granted the “oracle.bi.server.manageRepositories” permission, which is equivalent to the 10g “Administrator” user who also had unrestricted access. Hence, data filters won’t affect users with BIAdministrator Role. (source: Oracle Support)
- Integrating Oracle eBS R12 and Oracle BI 11g (obibb.wordpress.com)