Row level access - showing all

Support
#1

I have a list view where i am expecting only data related a Company the user has access to through row level roles assigned but it is loading data for all companies.

Here is details:

Loader of the List View:

<loader id="attendMissedAppsDl" readOnly="true">
                <query>
                    <![CDATA[select e from hr_AttendMissedApp e
                    join e.employeeProfile.company c
                    join e.employeeProfile.operatingLocation o
                    where e.employeeProfile.company.id IN :companyIdList
                    and e.employeeProfile.operatingLocation.id IN :operatingLocationIdList
                    and e.employeeProfile.functionalArea.id IN :funcAreaIdList]]>
                </query>
            </loader>

Controller:

The log shows the user has access to only one company, one operating location and all functional areas.

        List<UUID> companyIdList = inteaccMDGGeneralBean.getCompanyIdListAuthorized();
            List<UUID> operatingLocationIdList = inteaccMDGGeneralBean.getOperatingLocIdListAuthorized();
            List<UUID> funcAreaIdList = inteaccMDGGeneralBean.getFunctionalAreaIdListAuthorized();

            log.info("Authorized company IDs: {}", companyIdList);
            log.info("Authorized location IDs: {}", operatingLocationIdList);
            log.info("Authorized functional area IDs: {}", funcAreaIdList);

            attendMissedAppsDl.setParameters(ParamsMap.of("companyIdList", companyIdList, "operatingLocationIdList", operatingLocationIdList, "funcAreaIdList", funcAreaIdList));
            attendMissedAppsDl.load();

Here is the row level role:
image

This may be noted that this row level role is working for other screen but not here.

#2

Hi!

There is a suggestion that the concatenation of the where condition from the row-level role doesn’t applies correctly.

Try to enable logs for SQL using logging.level.eclipselink.logging.sql and analyze them. Most likely, the query is constructed incorrectly and you need to add brackets.

Regards,
Dmitriy

#3

Hi Dmitriy
The row level role is working properly as you see below, the companyIdList is only 1

2025-05-14T15:14:51.032-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'hr_AttendMissedApp' delete] for user [13220723] by io.jmix.securityflowui.constraint.UiEntityConstraint
2025-05-14T15:14:51.033-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'hr_AttendMissedApp' delete] for user [13220723] by io.jmix.securityflowui.constraint.UiEntityConstraint
2025-05-14T15:14:51.185-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'mdg_Company' create, update, delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-14T15:14:51.186-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Applied row-level constraint to [mdg_Company where={{E}.compId = 'RTM'}] for user [13220723] by io.jmix.securitydata.constraint.ReadEntityQueryConstraint
2025-05-14T15:14:51.296-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'mdg_OperatingLocation' create, update, delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-14T15:14:51.297-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Applied row-level constraint to [mdg_OperatingLocation where={{E}.operLocCode = '200' }] for user [13220723] by io.jmix.securitydata.constraint.ReadEntityQueryConstraint
2025-05-14T15:14:51.419-04:00  INFO 84953 --- [nio-8080-exec-5] c.i.h.v.l.a.AttendMissedAppListView      : Authorized company IDs: [2d54b78b-a90a-a3d9-1268-f676390c21f7]
2025-05-14T15:14:51.419-04:00  INFO 84953 --- [nio-8080-exec-5] c.i.h.v.l.a.AttendMissedAppListView      : Authorized location IDs: [1ce30500-3d45-68da-39a5-2348f2f89a7a]
2025-05-14T15:14:51.420-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'hr_AttendMissedApp' delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-14T15:14:51.640-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'mdg_Company' create, update, delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-14T15:14:51.641-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Applied row-level constraint to [mdg_Company where={{E}.compId = 'RTM'}] for user [13220723] by io.jmix.securitydata.constraint.ReadEntityQueryConstraint
2025-05-14T15:14:51.759-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'mdg_OperatingLocation' create, update, delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-14T15:14:51.760-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Applied row-level constraint to [mdg_OperatingLocation where={{E}.operLocCode = '200' }] for user [13220723] by io.jmix.securitydata.constraint.ReadEntityQueryConstraint
2025-05-14T15:14:51.870-04:00  INFO 84953 --- [nio-8080-exec-5] c.i.h.v.l.a.AttendMissedAppListView      : Authorized company IDs: [2d54b78b-a90a-a3d9-1268-f676390c21f7]
2025-05-14T15:14:51.870-04:00  INFO 84953 --- [nio-8080-exec-5] c.i.h.v.l.a.AttendMissedAppListView      : Authorized location IDs: [1ce30500-3d45-68da-39a5-2348f2f89a7a]
2025-05-14T15:14:51.870-04:00 DEBUG 84953 --- [nio-8080-exec-5] io.jmix.core.AccessLogger                : Denied access to [entity 'hr_AttendMissedApp' delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint

It’s not working here:

<loader id="attendMissedAppsDl" readOnly="true">
                <query>
                    <![CDATA[select e from hr_AttendMissedApp e
                    join e.employeeProfile.company c
                    join e.employeeProfile.operatingLocation o
                    where e.employeeProfile.company.id IN :companyIdList
                    and e.employeeProfile.operatingLocation.id IN :operatingLocationIdList]]>
                </query>
            </loader>

and where is it loaded as shared above. I have remove functional Area criteria, still not working

#4

To expand the context, I recommend that looking at the logs of generated SQL queries, as I advised earlier. Unfortunately, I was unable to reproduce the problem on my project. Could you attach a test project that reproduces the problem?

Dmitriy

#5

Hi Can I get the logs of generated SQL queries?

#6

In application.properties you can set

logging.level.eclipselink.logging.sql = debug

and then follow in the console the executed SQL commands

Regards
Felix

1 Like
#7

Thanks @f.zehnder

Hi @d.kremnev
Here is the SQL log

2025-05-18T17:48:18.997-04:00 DEBUG 57536 --- [nio-8080-exec-2] eclipselink.logging.sql                  : <t 1876172886, conn 363646483> [120 ms] spent
2025-05-18T17:48:18.999-04:00 DEBUG 57536 --- [nio-8080-exec-2] io.jmix.core.AccessLogger                : Denied access to [entity 'mdg_OperatingLocation' create, update, delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-18T17:48:19.000-04:00 DEBUG 57536 --- [nio-8080-exec-2] io.jmix.core.AccessLogger                : Applied row-level constraint to [mdg_OperatingLocation where={{E}.operLocCode = '200' }] for user [13220723] by io.jmix.securitydata.constraint.ReadEntityQueryConstraint
2025-05-18T17:48:19.001-04:00 DEBUG 57536 --- [nio-8080-exec-2] eclipselink.logging.sql                  : <t 1876172886, conn 1456112828> SELECT ID, ADDRESS, ADDRESS_LOCAL, NAME, NAME_LOCAL, OPER_LOC_CODE, ROW_LEVEL_ROLE, USE_HOLI_CAL_DEFAULT_TO_EMPL, HOLIDAY_CALENDAR_ID, UPAZILA_ID, WORK_SHIFT_DEFAULT_ID FROM MDG_OPERATING_LOCATION WHERE (OPER_LOC_CODE = ?)
	bind => [200]
2025-05-18T17:48:19.129-04:00 DEBUG 57536 --- [nio-8080-exec-2] eclipselink.logging.sql                  : <t 1876172886, conn 1456112828> [128 ms] spent
2025-05-18T17:48:19.130-04:00  INFO 57536 --- [nio-8080-exec-2] c.i.h.v.l.a.AttendMissedAppListView      : Authorized company IDs: [2d54b78b-a90a-a3d9-1268-f676390c21f7]
2025-05-18T17:48:19.130-04:00  INFO 57536 --- [nio-8080-exec-2] c.i.h.v.l.a.AttendMissedAppListView      : Authorized location IDs: [1ce30500-3d45-68da-39a5-2348f2f89a7a]
2025-05-18T17:48:19.131-04:00 DEBUG 57536 --- [nio-8080-exec-2] io.jmix.core.AccessLogger                : Denied access to [entity 'hr_AttendMissedApp' delete] for user [13220723] by io.jmix.security.constraint.CrudEntityConstraint
2025-05-18T17:48:19.134-04:00 DEBUG 57536 --- [nio-8080-exec-2] eclipselink.logging.sql                  : <t 1876172886, conn 1681296009> SELECT t1.ID, t1.APPL_DATE, t1.ATTEN_DATE, t1.ATTEN_MISS_ENTRY_NO, t1.EMPL_ID_MACHINE, t1.IN_TIME, t1.OUT_TIME, t1.PROCESS_STATE, t1.VERSION, t1.EMPLOYEE_PROFILE_ID, t1.WORK_SHIFT_ID, t0.ID, t0.DTYPE, t0.EMPLOYEE_CODE, t0.FIRST_NAME, t0.LAST_NAME, t0.MIDDLE_NAME, t0.VERSION, t0.COMPANY_ID, t0.DEPARTMENT_ID, t0.DPS_MEMBER_ID, t0.FUNCTIONAL_AREA_ID, t0.GRATUITY_MEMBER_ID, t0.HR_BUSINESS_PART_PROF_ID, t0.JOB_FAMILY_ID, t0.MANAGER_ID, t0.MANAGER_MATRIX_ID, t0.MARKET_HIERARCHY_ID, t0.OPERATING_LOCATION_ID, t0.PF_MEMBER_ID, t0.WORKFLOW_GROUP_ID, t0.DELETED_BY, t0.DELETED_DATE, t0.ALLOWANCE_SECOND_PMT_ID, t0.ATTEND_LOCATION_ID, t0.BANK_BRANCH_ID, t0.CURRENT_COUNTRY_ID, t0.CURRENT_DISTRICT_ID, t0.DESIGNATION_ID, t0.EMPL_CATEGORY_ID, t0.EMPL_GRADE_ID, t0.EMPL_GRADE_SUB_ID, t0.EMPL_SEPARATION_ID, t0.EMPLOYEE_GROUP_ID, t0.HOLIDAY_CALENDAR_ID, t0.HR_BUSINESS_PARTNER_ID, t0.JOB_POSITION_ID, t0.JOB_PROFILE_ID, t0.LEAVE_PROFILE_ID, t0.NATIONALITY_ID, t0.PAYSCALE_ID, t0.PAYSCALE_CMP_ID, t0.PAYSCALE_STRUCTURE_ID, t0.PERMANENT_COUNTRY_ID, t0.PERMANENT_DISTRICT_ID, t0.PR_PAYMENT_METHOD_ID, t0.PR_PAYMENT_METHOD_SEC_ID, t0.RELIGION_ID, t0.SECTION_ID, t0.WORK_SHIFT_ID, t0.WORK_SHIFT_ROTATE_RULE_ID, t2.ID, t2.CODE, t2.CREATED_BY, t2.CREATED_DATE, t2.LAST_MODIFIED_BY, t2.LAST_MODIFIED_DATE, t2.NAME, t2.VERSION, t3.ID, t3.NAME, t3.OPER_LOC_CODE, t3.HOLIDAY_CALENDAR_ID, t3.UPAZILA_ID, t3.WORK_SHIFT_DEFAULT_ID, t4.ID, t4.NAME, t4.SHIFT_CODE, t4.VERSION, t4.OT_ROUNDING_RULE_ID, t4.PUNCTUAL_ATTEND_CRITERIA_ID FROM HR_ATTEND_MISSED_APP t1 LEFT OUTER JOIN MDG_EMPL_PROFILE t0 ON (t0.ID = t1.EMPLOYEE_PROFILE_ID) LEFT OUTER JOIN HMD_DESIGNATION t2 ON (t2.ID = t0.DESIGNATION_ID) LEFT OUTER JOIN MDG_OPERATING_LOCATION t3 ON (t3.ID = t0.OPERATING_LOCATION_ID) LEFT OUTER JOIN MDG_WORK_SHIFT t4 ON (t4.ID = t1.WORK_SHIFT_ID) ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
	bind => [0, 50]
#8

Hi
According to the logs you provided, no where conditions are applied to the HR_ATTEND_MISSED_APP table query. It is difficult to say why this happens without a test project.

Try comparing the logs for loading data from the HR_ATTEND_MISSED_APP table on a working view and on a view where your data is loading incorrectly.

Best regards,
Dmitriy

#9

Hi Dmitriy
You’re right, there is no “where” conditions applied in the query in list screen but in fact I have used row-level role as below:
image

As a result i am expecting the user only have access to those records which are related to subordinate employees only. However, it shows everything.

#10

Today, I found another strange behavior when I was trying to use REST API. This REST API user has full access defined in application properties:

jmix.authserver.client.myclient.resource-roles = system-full-access

@Questions

  1. Am I not supposed to have full access avoiding the row level security?
  2. When I assign the row level role to any user, will this only then control the row level access or It will applied to all users as soon as it is created?

While I have row-level row not restricting access, API user who has full access is restricted to row level restricted table.

image

#11

I have investigated further for the row level role and see something is not working.

  1. Here is how I have defined the row-level role
    image

  2. Here you see the row level role I have created and assigned to the user:

image

  1. When this user logs in, see all the entities (operating locations)
    image

The row-level role that I have assigned (saved of course) to the user, i have used teh following code from the documentation to check:

private void printAuthenticationInfo() {
        UserDetails user = currentAuthentication.getUser();
        Authentication authentication = currentAuthentication.getAuthentication();
        Locale locale = currentAuthentication.getLocale();
        TimeZone timeZone = currentAuthentication.getTimeZone();

        System.out.println(
                "User: " + user.getUsername() + "\n" +
                        "Authentication: " + authentication + "\n" +
                        "Roles: " + getRoleNames(authentication) + "\n" +
                        "Locale: " + locale.getDisplayName() + "\n" +
                        "TimeZone: " + timeZone.getDisplayName()
        );
    }


    private String getRoleNames(Authentication authentication) {
        return authentication.getAuthorities().stream()
                .map(GrantedAuthority::getAuthority)
                .collect(Collectors.joining(","));
    }

And got the resource roles assigned but teh row level role that i have assigned but not found as below:

User: 00133907
Authentication: OAuth2AuthenticationToken [Principal=com.inteacc.mdg.entity.User-4d670aa2-4930-7987-e601-b0b84486f1d2 [detached], Credentials=[PROTECTED], Authenticated=true, Details=WebAuthenticationDetails [RemoteIpAddress=127.0.0.1, SessionId=1044A20328FCA698514FF98514EE0FAE], Granted Authorities=[ROLE_EMPLOYEE_USER_DEFAULT_ROLE_CODE, ROLE_EMPLOYEE_USER_DEFAULT_REPORT_ROLE_CODE]]
Roles: ROLE_EMPLOYEE_USER_DEFAULT_ROLE_CODE,ROLE_EMPLOYEE_USER_DEFAULT_REPORT_ROLE_CODE
Locale: English
TimeZone: Eastern Standard Time

Awaiting any supports.

#12

Hi!

About your questions:

Question 1

Resource roles provide access to resources.
If you assign a system-full-access role to a user, he will have access to all resources in the application (views, entities, entity attributes, etc.)

Row-level roles are used to deny access to specific rows in the database. Even if you have access to all resources (assigned system-full-access), you may not have access to specific rows in the database.

Question 2

Row-level roles will only be applied if you assign them to a user.

Your investigation

Your example looks correct. Please check if the syntax of your where clause is correct.

Could you try to reproduce this behavior on a test project? Then we can say for sure whether it is a bug or not.

Dmitriy