endpoint access query not working as expected

[sampath18]

Hi All,

I am facing a strange issue while fulfilling following requirement.

I wanted to dispany endpoints to only specific sav role members, so accordingly i have created following queries and placed under endpoint access query parameter in endpoint level

I have tried using below two queries but endpoint is not getting displayed even after refreshing the sessions

we are using v23.9 and recently upgraded to it. We got this requirement in 23.9 only not sure if it issue with our version only. can anyone guide me with this issue.

Where users.userkey in (select distinct us.userkey from user_savroles us, savroles sr where us.rolekey=sr.rolekey and sr.rolename in ('ROLE_ADMIN'))

where users.userkey in (select userkey from user_savroles where rolekey in ('1','5'))

This working when we tried below query. looks like users.userkey is not working

where ${requestor.id} in (select userkey from user_savroles where rolekey in ('1','5'))

[This message has been edited by moderator to merge reply message]

[sk]

@sampath18 : If you want use role key then use below

where users.username in (SELECT DISTINCT u.username from users u inner join user_savroles usr on u.userkey=usr.userkey and usr.rolekey in (1,5)

or if you want to use role name then use below format

where users.username in (SELECT DISTINCT u.username from users u inner join user_savroles usr on u.userkey=usr.userkey inner join savroles sr on sr.rolekey=usr.rolekey and sr.rolename in ('ROLE_ADMIN')

[sampath18]

Hi @sk Thanks for responding.

Above query also not working in v23.9. may be it is due to any issue with mysql 8.0?

Currently ${requestor.id} is working well with query.

[sk]

@sampath18 : No it shouldn't it is working for us as expected.

Can you navigate to Global Config-> Requests->Enable enhanced application catalog based searches? and confirm whether it is enabled or not?

[sampath18]

Hi @sk 

I have enabled above mentioned global config related to application catalog. But it is still not working for me.I have run the microservices job after changing the configurations. Still the custom sav role user is unable to view the application.

At the same time with below query working fine but it is being thrown as error from microservice job.

where ${requestor.id} in (select userkey from user_savroles where rolekey in ('1','5'))

Please note that this v23.9, if you tested in the same version i am quite confused on why it is not working for us.

Error from micro service job status

[Exchange:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{requestor.id} in (select userkey from user_savroles where rolekey in ('1','5'))' at line 1]

[sampath18]

Hi @sk  In short, When i enable the config Global Config-> Requests->Enable enhanced application catalog based searches , 

None of the conditions are working, when above config is disabled then my above query is working. However microservice job throwing above error.

[rushikeshvartak]

Do you see any error when application list page is loaded / Microservice service job details ?

[sampath18]

Hi @rushikeshvartak 

I am not seeing any error while loading applicatio request page. However, although below query is working fine, but microservice job is returing below error

Error returned when below query is used.

where ${requestor.id} in (select userkey from user_savroles where rolekey in ('1','5'))

[Exchange:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{requestor.id} in (select userkey from user_savroles where rolekey in ('1','5'))' at line 1]