SEC_SECURE_BY_OBJ_MEMBER_USER limitations

Lauriane Massin Whitaker · Mar 31, 2025 2:25PM

Hi,

I've been experiencing limitations on some common attributes like Legal Employer, and my client as well. I have another account user who is not experiencing these limitations at all and I don't know how to fix that ? Any idea or steps to take ?

Thank you.

Regards,

Lauriane

Sumanth V -Oracle · Apr 4, 2025 10:35AM

@Lauriane Massin Whitaker - The rules ensure that the most restrictive permissions are enforced, adhering to the principle of least privilege.

For detailed information on these behaviors, refer to Oracle's documentation on managing security for dashboards and analyses:
https://docs.oracle.com/middleware/12211/biee/BIEMG/GUID-E4B828EE-B906-432A-834D-A045BB6A51BD.htm

Sumanth V -Oracle · Mar 31, 2025 2:34PM

@Lauriane Massin Whitaker -The SEC_SECURE_BY_OBJ_MEMBER_USER table plays a key role in security by controlling row-level access based on object member security.

If you are unable to see the data, it is likely due to the row-level security defined in the system. Please validate this by comparing it with another user account that does not experience this issue.

Users with the Administrator role are exempt from row-level security restrictions.

Reference: FAW: How to Validate Data - Security Tables Are Not Accessible Anymore (Doc ID 2925072.1)

Lauriane Massin Whitaker · Apr 1, 2025 12:40PM

Thanks Sumanth.

However, I don't understand because I am an admin. In dev I can see the legal employers but not in Prod.. I asked 2 persons at my clients and they are experiencling the same issue. They are able to see the list for the business units and services but not for legal employers.

If I pull legal eployers only no data :

But asa I add business units for ex, they are displayed :

Sumanth V -Oracle · Apr 1, 2025 2:11PM

@Lauriane Massin Whitaker - Do you have administrative privileges in both environments? Try comparing the physical SQL generated in both environments and validate if they are the same.

Lauriane Massin Whitaker · Apr 3, 2025 8:57AM

Admin privileges in both HCM environments you mean ?

And about the SQL generated, shall I compare this tab for each environment FDI prod/dev ?

Sumanth V -Oracle · Apr 3, 2025 12:12PM

@Lauriane Massin Whitaker - Please check the execution log, it will have the physical SQL.

Lauriane Massin Whitaker · Apr 4, 2025 7:24AM

@Sumanth V -Oracle

From Dev :

[2025-04-04T07:22:35.861-00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: 44527816-4f6f-492a-854b-e2255f843d30-00043c31,0:2:2:3] [sik: bootstrap] [tid: 71ff5700] [messageId: USER-0] [requestid: 77620017] [sessionid: 77620000] [username: lauriane.massin@kertios.com] ############################################## [[-------------------- SQL Request, logical request hash:e6f533b9SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Visual Analyzer',SAW_SRC_PATH='{"viewID":"view!1","currentCanvas":"canvas!1"}',ENABLE_DIMENSIONALITY=1;SELECT0 s_0,"HCM - Workforce Core"."Legal Employer"."Legal Employer Name" s_1FROM "HCM - Workforce Core"ORDER BY 2 ASC NULLS LASTFETCH FIRST 500001 ROWS ONLY/* AdditionalDetail='VisualAnalyzer' */]][2025-04-04T07:22:35.861-00:00] [OBIS] [TRACE:2] [USER-23] [] [ecid: 44527816-4f6f-492a-854b-e2255f843d30-00043c31,0:2:2:3] [sik: bootstrap] [tid: 71ff5700] [messageId: USER-23] [requestid: 77620017] [sessionid: 77620000] [username: lauriane.massin@kertios.com] -------------------- General Query Info: [[Repository: bootstrap, Subject Area: Core, Presentation: HCM - Workforce Core]][2025-04-04T07:22:35.880-00:00] [OBIS] [TRACE:2] [USER-16] [] [ecid: 44527816-4f6f-492a-854b-e2255f843d30-00043c31,0:2:2:5] [sik: bootstrap] [tid: 71ff5700] [messageId: USER-16] [requestid: 77620017] [sessionid: 77620000] [username: lauriane.massin@kertios.com] -------------------- Execution plan: [[Child Nodes (RqCache): 1743751355:876:1771895483:0x7f9e71ff2800RqList <<10350099>> [for database 3023:2:Oracle_Data_Warehouse,78] distinct /* FETCH FIRST 500001 ROWS ONLY */ 0 as c1 GB [for database 3023:2:Oracle_Data_Warehouse,78],D1.c1 as c2 GB [for database 3023:2:Oracle_Data_Warehouse,78]Child Nodes (RqJoinSpec): <<10350152>> [for database 3023:2:Oracle_Data_Warehouse,78]RqJoinNode <<10350151>> [](RqList <<10350106>> [for database 3023:2:Oracle_Data_Warehouse,78]Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly.LEGAL_EMPLOYER_NAME as c1 GB [for database 3023:2:Oracle_Data_Warehouse,78]Child Nodes (RqJoinSpec): <<10350216>> [for database 3023:2:Oracle_Data_Warehouse,78]RqJoinNode <<10350135>> []DW_LEGAL_EMPLOYER_D_TL AS Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnlyDetailFilter: Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly.LANGUAGE = 'F' [for database 3023:2:Oracle_Data_Warehouse,78]) as D1OrderBy: c2 asc NULLS LAST [for database 3023:2:Oracle_Data_Warehouse,78]]][2025-04-04T07:22:35.881-00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: 44527816-4f6f-492a-854b-e2255f843d30-00043c31,0:2:2:5] [sik: bootstrap] [tid: 71ff5700] [messageId: USER-18] [requestid: 77620017] [sessionid: 77620000] [username: lauriane.massin@kertios.com] -------------------- Sending query to database named Oracle_Data_Warehouse (id: <<10350099>>), client type Oracle Call Interface (OCI), connection pool named Oracle_Data_Warehouse_Connection_Pool, logical request hash e6f533b9, physical request hash 710e3222: [[WITH SAWITH0 AS (select distinct T1697.LEGAL_EMPLOYER_NAME as c1from OAX$OAC.DW_LEGAL_EMPLOYER_D_TL T1697 /* Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly */ where ( T1697.LANGUAGE = 'F' ) )select D1.c1 as c1, D1.c2 as c2 from ( select 0 as c1,D1.c1 as c2from SAWITH0 D1order by c2 ) D1 where rownum <= 500001]][2025-04-04T07:22:35.973-00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: 44527816-4f6f-492a-854b-e2255f843d30-00043c31,0:2:2:5:1] [sik: bootstrap] [tid: e8864700] [messageId: USER-18] [requestid: 77620017] [sessionid: 77620000] [username: lauriane.massin@kertios.com] -------------------- Sending query to database named Oracle_Data_Warehouse (id: <<10350099>> pre query 0), client type Oracle Call Interface (OCI), connection pool named Oracle_Data_Warehouse_Connection_Pool, logical request hash e6f533b9, physical request hash 95ab3789: [[call FDI_SET_CONTEXT_PARAM('FDI_CONTEXT', 'USER', 'lauriane.massin@kertios.com');]][2025-04-04T07:22:35.976-00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: 44527816-4f6f-492a-854b-e2255f843d30-00043c31,0:2:2:5:1] [sik: bootstrap] [tid: e8864700] [messageId: USER-18] [requestid: 77620017] [sessionid: 77620000] [username: lauriane.massin@kertios.com] -------------------- Sending query to database named Oracle_Data_Warehouse (id: <<10350099>> pre query 1), client type Oracle Call Interface (OCI), connection pool named Oracle_Data_Warehouse_Connection_Pool, logical request hash e6f533b9, physical request hash f37bc835: [[call FDI_SET_CONTEXT_PARAM('FDI_CONTEXT', 'APP_ROLE', 'FAW Modeler Role;FAW Modeler Administrator Role;FAW Security Administrator Role;ServiceAdmin;OA4F_SECURITY_REPORTING_DUTY;FAW Licensed HCM Consumers Role;FAW Licensed HCM Authors Role;AuthenticatedUser;Modeler;ModelerAdmin;SecurityAdmin;Consumer;Author;FAW Service Administrator Role;OA4F_CONTENT_REPORTING_CONFIG;OA4F_HCM_BUSINESS_UNIT_DATA;FAW Functional Administrator Role;FunctionalAdmin');]]

From Prod :

[2025-04-04T07:23:19.405+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: e1557d81-c54f-4224-8a62-68e9e36318f7-0004ec22,0:2:2:3] [sik: bootstrap] [tid: 7bfd5700] [messageId: USER-0] [requestid: eb950017] [sessionid: eb950000] [username: lauriane.massin@kertios.com] ############################################## [[

-------------------- SQL Request, logical request hash:

e6f533b9

SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Visual Analyzer',SAW_SRC_PATH='{"viewID":"view!1","currentCanvas":"canvas!1"}',ENABLE_DIMENSIONALITY=1;SELECT

0 s_0,

"HCM - Workforce Core"."Legal Employer"."Legal Employer Name" s_1

FROM "HCM - Workforce Core"

ORDER BY 2 ASC NULLS LAST

FETCH FIRST 500001 ROWS ONLY

/* AdditionalDetail='VisualAnalyzer' */

]]

[2025-04-04T07:23:19.406+00:00] [OBIS] [TRACE:2] [USER-23] [] [ecid: e1557d81-c54f-4224-8a62-68e9e36318f7-0004ec22,0:2:2:3] [sik: bootstrap] [tid: 7bfd5700] [messageId: USER-23] [requestid: eb950017] [sessionid: eb950000] [username: lauriane.massin@kertios.com] -------------------- General Query Info: [[

Repository: bootstrap, Subject Area: Core, Presentation: HCM - Workforce Core

]]

[2025-04-04T07:23:19.421+00:00] [OBIS] [TRACE:2] [USER-16] [] [ecid: e1557d81-c54f-4224-8a62-68e9e36318f7-0004ec22,0:2:2:5] [sik: bootstrap] [tid: 7bfd5700] [messageId: USER-16] [requestid: eb950017] [sessionid: eb950000] [username: lauriane.massin@kertios.com] -------------------- Execution plan: [[

Child Nodes (RqCache): 1743751399:417:1709189539:0x7fe27bfd2800

RqList <<8916975>> [for database 3023:2:Oracle_Data_Warehouse,78] distinct /* FETCH FIRST 500001 ROWS ONLY */

0 as c1 GB [for database 3023:2:Oracle_Data_Warehouse,78],

D1.c1 as c2 GB [for database 3023:2:Oracle_Data_Warehouse,78]

Child Nodes (RqJoinSpec): <<8917028>> [for database 3023:2:Oracle_Data_Warehouse,78]

RqJoinNode <<8917027>> []

(

RqList <<8916982>> [for database 3023:2:Oracle_Data_Warehouse,78]

Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly.LEGAL_EMPLOYER_NAME as c1 GB [for database 3023:2:Oracle_Data_Warehouse,78]

Child Nodes (RqJoinSpec): <<8917012>> [for database 3023:2:Oracle_Data_Warehouse,78]

RqJoinNode <<8917010>> [(InNode:<<8917010>>) (OutNode:<<8917011>>) ]

[SELECT

USERNAME,

SEC_OBJ_MEMBER_VAL_NUMBER as SEC_OBJ_MEMBER_VAL,

SEC_OBJ_CODE

FROM

SEC_SECURE_BY_OBJ_MEMBER_USER

WHERE

USERNAME='VALUEOF(NQ_SESSION.USER)'

AND

SEC_OBJ_CODE='OA4F_SEC_HCM_LEGAL_EMPLOYER_LIST'] as T3979

RqJoinNode <<8917011>> [(InNode:<<8917010>>) (OutNode:<<8917011>>) ]

DW_LEGAL_EMPLOYER_D_TL AS Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly

DetailFilter: Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly.LEGAL_EMPLOYER_ID = SEC_SECURE_BY_OBJ_MEMBER_USER_HCM_LE_DimTL.SEC_OBJ_MEMBER_VAL and Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly.LANGUAGE = 'F' [for database 3023:2:Oracle_Data_Warehouse,78]

) as D1

OrderBy: c2 asc NULLS LAST [for database 3023:2:Oracle_Data_Warehouse,78]

]]

[2025-04-04T07:23:19.422+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e1557d81-c54f-4224-8a62-68e9e36318f7-0004ec22,0:2:2:5] [sik: bootstrap] [tid: 7bfd5700] [messageId: USER-18] [requestid: eb950017] [sessionid: eb950000] [username: lauriane.massin@kertios.com] -------------------- Sending query to database named Oracle_Data_Warehouse (id: <<8916975>>), client type Oracle Call Interface (OCI), connection pool named Oracle_Data_Warehouse_Connection_Pool, logical request hash e6f533b9, physical request hash 7bd777e3: [[

WITH

SAWITH0 AS (select distinct T1697.LEGAL_EMPLOYER_NAME as c1

from

(SELECT

USERNAME,

SEC_OBJ_MEMBER_VAL_NUMBER as SEC_OBJ_MEMBER_VAL,

SEC_OBJ_CODE

FROM

SEC_SECURE_BY_OBJ_MEMBER_USER

WHERE

USERNAME='lauriane.massin@kertios.com'

AND

SEC_OBJ_CODE='OA4F_SEC_HCM_LEGAL_EMPLOYER_LIST') T3979,

OAX$OAC.DW_LEGAL_EMPLOYER_D_TL T1697 /* Dim_DW_LEGAL_EMPLOYER_D_TL_DimOnly */

where ( T1697.LEGAL_EMPLOYER_ID = T3979.SEC_OBJ_MEMBER_VAL and T1697.LANGUAGE = 'F' ) )

select D1.c1 as c1, D1.c2 as c2 from ( select 0 as c1,

D1.c1 as c2

from

SAWITH0 D1

order by c2 ) D1 where rownum <= 500001

]]

[2025-04-04T07:23:19.521+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e1557d81-c54f-4224-8a62-68e9e36318f7-0004ec22,0:2:2:5:1] [sik: bootstrap] [tid: d729e700] [messageId: USER-18] [requestid: eb950017] [sessionid: eb950000] [username: lauriane.massin@kertios.com] -------------------- Sending query to database named Oracle_Data_Warehouse (id: <<8916975>> pre query 0), client type Oracle Call Interface (OCI), connection pool named Oracle_Data_Warehouse_Connection_Pool, logical request hash e6f533b9, physical request hash 95ab3789: [[

call FDI_SET_CONTEXT_PARAM('FDI_CONTEXT', 'USER', 'lauriane.massin@kertios.com');

]]

[2025-04-04T07:23:19.523+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e1557d81-c54f-4224-8a62-68e9e36318f7-0004ec22,0:2:2:5:1] [sik: bootstrap] [tid: d729e700] [messageId: USER-18] [requestid: eb950017] [sessionid: eb950000] [username: lauriane.massin@kertios.com] -------------------- Sending query to database named Oracle_Data_Warehouse (id: <<8916975>> pre query 1), client type Oracle Call Interface (OCI), connection pool named Oracle_Data_Warehouse_Connection_Pool, logical request hash e6f533b9, physical request hash 9c004a3: [[

call FDI_SET_CONTEXT_PARAM('FDI_CONTEXT', 'APP_ROLE', 'FAW Modeler Role;FAW Modeler Administrator Role;FAW Security Administrator Role;OA4F_HCM_WORKFORCE_CORE_ANALYSIS_DUTY;OA4F_HCM_VIEW_ALL_DATA;OA4F_HCM_WORKFORCE_CORE_ANALYSIS_CONTENT_DUTY;ServiceAdmin;OA4F_SECURITY_REPORTING_DUTY;FAW Licensed HCM Consumers Role;FAW Licensed HCM Authors Role;AuthenticatedUser;Modeler;ModelerAdmin;SecurityAdmin;Consumer;OA4F_HCM_LEGAL_EMPLOYER_DATA;Author;FAW Service Administrator Role;OA4F_CONTENT_REPORTING_CONFIG;FunctionalAdmin;FAW Functional Administrator Role');

I can see

SEC_OBJ_CODE='OA4F_SEC_HCM_LEGAL_EMPLOYER_LIST') T3979 for PRod, but how to fix this please ?

Regards,

Lauriane

Lauriane Massin Whitaker · Apr 4, 2025 7:42AM

And when I check on the SME level, same object permissions both n dev and prod…

Lauriane Massin Whitaker · Apr 4, 2025 8:37AM

I managed to solve the issue , I had to remove the FAW HCM Consumer role from my roles in OCI… now I can see the legal employers !

How come this specific role prevents users from seeing this data level whilst they also are service admin ? Shouldn't the fact of being service admin not taking priority over the consumer role ?

Regards,

LAuriane

MFA for Oracle Community

Oracle Fusion Data Intelligence

Categories

SEC_SECURE_BY_OBJ_MEMBER_USER limitations

Best Answer

Answers