X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

How to build Fusion Apps Security in BI Apps

General guidelines for Customization,Configuration and Administration

 

SSO Authentication

Implementers can refer to the steps to integrate SSO with IDM as explained in ‘Security Guide for Oracle Business Intelligence Enterprise Edition’.

FA Security Metadata Extraction steps

Identify the Data Objects to secure

e.g.

Supervisor Person, Department, Position, etc are typical Data security objects

HR Line Manager, Payroll Administrator, etc are typical Object Security Roles*.

Fusion Apps (FA) Source Objects

Identify the Data Security Roles and Object Security Roles (AoR or Area of responsibility roles) associated to these objects.

For data security implementer requires to identify tables containing User to Data Object ID mapping.

Finalize SQLs list for the above requirements.

Form SQL’s to extract data which can be extracted from source for Data and Object Security. Example target format in next section (Creating DWH side security metadata Table inherited from FA)

Security Table Object List

Some of Fusion Apps security tables and SQLs to extract security information are provided in appendix. Additional data objects can be obtained from Fusion Apps implementation team as per requirement.

 

*Note, OLAP and OLTP roles may not necessarily converge on most occasions. Extract only those roles, which makes business sense for BI, and not just every role present in Fusion Apps.

 

FA Security Metadata ETL to Warehouse

 Creating warehouse side security metadata Table extracted from FA

Data extracted from FA security tables must be loaded in certain format for ease of query by the RPD init blocks.

 

·         As an example, for on-prem customers we could possibly use 3 tables to store data effectively . Table structure is as follows. The columns and tables can be modified or extended based on additional requirements.

o   Roles Table (Role type : Data Security or Object Security) : w_sec_roles

Attributes: Role Name, Role Description, Role Type

Role Name

Role Description

Role Type

DSN

DEPT_LINE_MGR

Department Line Manager

Area of Responsibility(AOR)

210

DEPT_TOP_MGR

Department Top Manager

Data Security(DS)

210

 

o   Roles+User Table : w_sec_role_users

Attributes: Username, Role Name, DSN

USERNAME

Role Name

DSN

JOHNDOE

DEPT_LINE_MGR

210

GWILLIAMS

DEPT_TOP_MGR

210

 

o   User+Data Object Table : w_sec_user_objects

Attributes: Username, Object Name, Object ID,DSN

USERNAME

Object Name

Object ID

DSN

JOHNDOE

DEPARTMENT

1002

210

GWILLIAMS

SUPERVISOR

31052

210

Note here that Object refers to Securing Object to be used in BI side.

 

o   Useful SQL’s based above tables containing extracted security data

SQL Description

Physical SQL

User to Data Security Role

Select username,role_description

from w_sec_roles rol, w_sec_role_users rol_usr

where rol.role_name = rol_usr. role_name

and rol_usr.dsn = rol.dsn

and role_type=’DS’

 

User to Data AOR  Role

Select rol_usr.username,rol.role_description

from w_sec_roles rol, w_sec_role_users rol_usr

where rol.role_name = rol. role_name

and rol_usr.dsn = obj.dsn

and role_type=’AOR’

 

User to Object(Supervisor)

select username,object_id

from w_sec_role_users rol_usr,w_sec_user_objects obj

where rol_usr.username = obj.username

and rol_usr.dsn = obj.dsn

and object_name = ‘SUPERVISOR’

 

 

User to Object(Department)

select username,object_id

from w_sec_role_users rol_usr,w_sec_user_objects obj

where rol_usr.username = obj.username

and rol_usr.dsn = obj.dsn

and object_name = ‘DEPARTMENT’

 

 

 

o   NOTE

Data Security objects like Department and Supervisor are hierarchical objects.

OBIA expects the following in w_sec_user_objects table:

·         Provide top and all its child Dept ids for assigned user. Only top dept ID for an user not sufficient.

·         Provide top Supervisor Person ID,that is sufficient for Supervisor security for assigned user

 

·         Indexing table for quicker access

o   The table which is expected to be used most frequently must be indexed.

o   User+Data Object table will get accessed each time an user runs a report.

o   This table must be indexed for optimal query for security “where clauses”.

o   Use BI Publisher to extract security metadata in csv format using SQL based reports from Fusion Apps security tables. (*)

§  Log in to BI Publisher using a Fusion Apps super user having access to “all” Security data sources that are required for the customer’s security model. Its possible that ETL user used for data extraction won’t have access to the FA security tables required, so those users may not work. Work with Fusion Apps administrator to create such an user.

§  Schedule a BI publisher job for each SQL report using the link https://docs.oracle.com/cd/E28280_01/bi.1111/e22257/create_rpt_jobs.htm#BIPUG213

§  The report outputs in csv format must be uploaded to ftp location of the ETL server for ODI to consume.

https://docs.oracle.com/middleware/12211/bip/BIPAD/GUID-52398FBA-CB92-4A8D-9187-770A98C657E4.htm#BIPAD306

 

o   Use the csv output file to load warehouse table via ODI.(Runs in full mode always, i.e. truncate and load).

* Note: The customer will have to ensure that access to Fusion Apps security data table is always available for BI publisher to query. While getting access ensure that security policies are adhered to.

·        OBIA Metadata configuration

 Identify Security Objects to be implemented (Supervisor Person, Department, Position, etc.)

 Configure LDAP as Authentication Provider to Store extracted Fusion Apps Roles as LDAP Groups, refer to link below.

https://docs.oracle.com/cd/E23943_01/bi.1111/e10543/privileges.htm#CJAFBFID

 

§  Use weblogic security provider BISQLGroupProvider (installed and configured on BI Server) to get Roles from DB to weblogic.

§  Create new Role or use existing pre-shipped OBIA roles as parents of these DB imported groups and link with Fusion Apps roles procured via LDAP. Refer to the flow below:

§  The FA roles extracted must have Object Role as well as Data security Role mapping done in Enterprise Manager. Example provided below.

FA Role

Existing WLS Role/OBIA Role

Type

HR Line Manager

BI Author

Object Security(AOR)

HR Line Manager

OBIA HR Data Sec Role(Dept)

Data Security

 

o   Seed Init blocks/variables in rpd or modify existing ones. https://docs.oracle.com/middleware/11119/biee/BIEMG/variables.htm#BIEMG373

 

o   Data Security uptake steps in RPD

 

§  Create init blocks in rpd to read w_sec_user_objects table for each user. An example to create init block and associated variable is shown below

Example query for init block for the data security session variable could be as follows:

select object_id

from w_sec_role_users rol_usr,w_sec_user_objects obj

where rol_usr.username = obj.username

and rol_usr.dsn = obj.dsn

and object_name = ‘DEPARTMENT’

and upper(username) = upper(‘:USER’) 

 

§  Use existing RPD Role filters for logged in user using init blocks/variables or create new role filters if new roles were added in EM to extend OBIA security Role list. Example below shows use of an existing Data Security role to add a data security filter for a Subject Area.

 

§  Use the User to Data Object filter directly to control data filter in physical layer. This can be done for existing or new Roles extracted from Fusion Apps.

 

o   Data Security Roles in physical SQL(rpd access needed for this, for on prem customers)

§  Roles apply data filters dynamically

§  If a user has 2 roles ROLE_HR_LOC and ROLE_HR_PAYROLL, the filters are appended to queries using  OR not AND

·         E.g. if user has access to Location ID : 100,101,102 and Payroll ID 2000,2001 then filter will be where location_id in (100,101,102) OR payroll_id in (2000,2001)

 

o   Object Security Roles (rpd access needed for this, for on prem customers)

§  Object Security roles extracted from FA (also called AoR in Fusion Apps) as groups into weblogic,must be either be:

·         Mapped to BIAuthor,BIAdministrator or BIConsumer roles which are preshipped BI data security roles

OR

·         Mapped to newly created custom Roles in Enterprise manager.

 

Object Permissions can be changed for those roles as follows.

 

o   Web catalog Object security can be done for all object security roles, extracted/pre-seeded as follows.

 

 

·        APPENDIX of some useful Fusion Apps security tables

User assignments

·         PER_ASG_RESPONSIBILITIES

Person,User

·         PER_USERS

·         PER_PERSON_NAMES_F

·         PER_ALL_PEOPLE_F

Org,Department

·         FND_TREE_VERSION

·         PER_ORG_TREE_NODE_RF

·         PER_ORG_TREE_NODE_CF

·         HR_ORGANIZATION_UNITS 

Roles

·         PER_USER_ROLES

·         PER_GENERATED_DATA_ROLES

·         PER_ROLES_DN

FND table for deriving Security clause

·         FND_GRANTS

·         FND_OBJECT_INSTANCE_SETS_VL

·         FND_COMPILED_MENU_FUNCTIONS

·         FND_FORM_FUNCTIONS_VL

·         FND_OBJECTS

More table and columns information in this link: https://docs.oracle.com/cloud/latest/globalcs_gs/OEDMH/TablesOverview.htm#TablesOverview

Note:If customers require additional security predicates or objects, the same must be discussed with the Fusion Apps source team to identify the underlying tables and the corresponding SQL’s.

 

·        APPENDIX for some of the useful SQL’s

User to Person ID/Supervisor ID link

select distinct username ,   prsn.person_id from  per_users usr,per_person_names_f prsn

where  usr.person_id = prsn.person_id

and username = ‘:USER’

User to Top Org ID

select distinct pu.username,par.top_organization_id

from per_all_people_f papf,

per_users pu,

per_person_names_f ppnf,

per_asg_responsibilities par

where pu.username = ‘:USER’

and PAR.person_id = PAPF.person_id

and PAPF.Person_id = PU.person_id

and PAPF.person_id = PPNF.person_id

 

 

Job Roles

select distinct per_generated_data_roles.base_role_id

    ,per_roles_dn_base_role.role_common_name job_role_common_name

    ,per_roles_dn_tl.role_name job_role_name

    ,per_roles_dn_tl.description job_role_desc

from per_generated_data_roles  per_generated_data_roles ,per_roles_dn per_roles_dn_base_role   , per_roles_dn_tl

where per_roles_dn_base_role.role_id = per_generated_data_roles.base_role_id

and per_generated_data_roles.base_role_id = per_roles_dn_tl.role_id

and  per_roles_dn_tl.language = 'US'

Job Data Roles

 

select distinct per_user_roles.user_id

    ,per_users.username

    ,per_person_names_f.first_name

    ,per_person_names_f.last_name

    ,per_generated_data_roles.base_role_id

    ,per_roles_dn_base_role.role_common_name job_role_common_name

    ,per_roles_dn_tl.role_name job_role_name

    ,per_roles_dn_tl.description job_role_desc

from per_user_roles  ,  per_users , per_person_names_f  , per_generated_data_roles ,per_roles_dn per_roles_dn_base_role , per_roles_dn_tl

where per_user_roles.user_id = per_users.user_id

and   per_person_names_f.person_id = per_users.person_id

and   per_generated_data_roles.data_role_id = per_user_roles.role_id

and   per_roles_dn_base_role.role_id = per_generated_data_roles.base_role_id

and    per_generated_data_roles.base_role_id = per_roles_dn_tl.role_id

and   per_roles_dn_tl.language = 'us'

Table level data Filters along with roles

SELECT o.obj_name table_name,

  g.grant_type,

  g.instance_type,

  g.role_name,

  s.instance_set_id,

  f.function_name,

  s.predicate filter_clause

FROM fnd_grants g,

     fnd_object_instance_sets_vl s,

     fnd_compiled_menu_functions m,

     fnd_form_functions_vl f,

     fnd_objects o

WHERE

o.object_id       = s.object_id

AND g.instance_set_id = s.instance_set_id

AND g.menu_id         = m.menu_id

AND m.function_id     = f.function_id

order by g.role_name