Global config Role Request Query

[PHN]

Hello All,

Our requirement is to only expose project-specific enterprise roles to users. The project name is being stored at custom properties of user CP40 and role CP40. So we can compare role and user custom properties to make specific role request able. One role can only belong to one project but one user can belong to multiple projects. 

Using Request Roles Query in -> Global config

Case 1) when user is part of one project, this is working fine

Query: and rl.customproperty40 like concat('%',(select u.customproperty40 from Users u where u.id={currentUser}),'%')

Values: Role.customproperty40 = NOL-NORD

            User.customproperty40 = NOL-NORD

Case 2) User with multiple projects - Tried multiple ways to form query but no luck

Values: Role.customproperty40 = NOL-NORD

            User.customproperty40 = NOL-NORD,NOL-SUED

Query: and rl.customproperty40 like concat('%',(select replace(u.customproperty40, ',', '%\' or rl.customproperty40 like \'%')  from Users u where u.id={currentUser}),'%')

-> This is throwing error “illegalArgumentException, replace method is not supported in Hibernate query“

Query: and rl.customproperty40 in (select u.customproperty40 from Users u where u.id={currentUser})

-> This is failing because subquery only returns one row like this NOL-NORD,NOL-SUED

Query:   rl.customproperty40 in (select sv.customproperty17 from Users u, Savroles sv, User_savroles us where u.id=us.userkey and sv.id=us.rolekey and u.id={currentUser})

-> Failed because Savroles table is not exposed

Has anyone implemented a similar kind of query in Global config to compare multiple values in role requestable configuration?

Thanks,

[rushikeshvartak]

and (
rl.customproperty40 like concat('%', (select trim(substring_index(substring_index(u.customproperty40, ',', n.n), ',', -1))
from Users u, (select 1 as n union all select 2 union all select 3 union all select 4) n
where u.id = {currentUser} and char_length(u.customproperty40) - char_length(replace(u.customproperty40, ',', '')) >= n.n - 1), '%')
or rl.customproperty40 like concat('%', (select trim(substring_index(substring_index(u.customproperty40, ',', n.n + 1), ',', -1))
from Users u, (select 1 as n union all select 2 union all select 3 union all select 4) n
where u.id = {currentUser} and char_length(u.customproperty40) - char_length(replace(u.customproperty40, ',', '')) >= n.n), '%')
)

[PHN]

Thank you, Rushikesh,

Tried using the given query but it is throwing error. Seems it is not supporting the subqeury (select 1 as n union all select 2 union all select 3 union all select 4). it is working for you?

and rl.customproperty40 like concat('%', (select trim(substring_index(substring_index(u.customproperty40, ',', n.n), ',', -1)) from Users u, (select 1 as n union all select 2 union all select 3 union all select 4) n where u.id = {currentUser} and char_length(u.customproperty40) - char_length(replace(u.customproperty40, ',', '')) >= n.n - 1), '%')

----------------

2024-06-18T11:36:04+02:00-idwms-c.s.s.i.e.ControllerExceptionHandler-http-nio-9091-exec-8-g5spn-ERROR-Error : {}|java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 225 [Select rl.id from com.saviynt.ssm.entity.Roles rl where 1=1 and rl.customproperty40 like concat('%', (select trim(substring_index(substring_index(u.customproperty40, ',', n.n), ',', -1)) from com.saviynt.ssm.entity.Users u, (select 1 as n union all select 2 union all select 3 union all select 4) n where u.id = 3666 and char_length(u.customproperty40) - char_length(replace(u.customproperty40, ',', '')) >= n.n - 1), '%')]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) 

Thanks,

[rushikeshvartak]

Not working in v24.6

[PHN]

Thank you for checking.