Restricting Roles based on Dataset and entitlement values

[sandeepsingh]

Hello Everyone,

We are trying to restrict application roles by writing below-mentioned query in the Role Type of the endpoint. This query concat certain details to create a role name by which we need to filter.

Query: 

r.role_name in (SELECT DISTINCT CONCAT('APP1',':',dv.attribute2,':',dv.attribute5,':',ev.entitlement_value) FROM Roles r,Entitlement_values ev,dataset_values dv where ev.entitlementtypekey=100 and FIND_IN_SET(${dynattribute1},r.customproperty2) and dv.datasetname='Dataset_Sample1' and dv.attribute1=${dynattribute1})

Sample Data:

Role name will be like "App1:Category1:SubCategory1:Entitlement"

Let us know if the query is good or something we might be missing.

Note: The inner query of above mention below works. We cheched it by putting in as an another  dynamic attribute.

SELECT DISTINCT CONCAT('APP1',':',dv.attribute2,':',dv.attribute5,':',ev.entitlement_value) FROM Roles r,Entitlement_values ev,dataset_values dv where ev.entitlementtypekey=100 and FIND_IN_SET(${dynattribute1},r.customproperty2) and dv.datasetname='Dataset_Sample1' and dv.attribute1=${dynattribute1}}

[nimitdave]

Please provide error snippet while page load for the specific endpoint.

I assume that no application roles are showing.

[sandeepsingh]

There is no error. System accepts the query in the dynamic attribute as well as role type. But on the request page application roles are not displayed.

[nimitdave]

@sandeepsingh , so Datset_Values is not exposed in requested query under role type. You should be getting an error as below:

X-B3-SpanId=9bba52eb42da079c, TENANT_ID=DEFAULT, X-B3-TraceId=9bba52eb42da079c] 6 --- [http-nio-8787-exec-4] c.s.s.a.e.ControllerExceptionHandler : ILLEGAL-ARGUMENT-ERROR|java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: dataset_values is not mapped [Select count(r.id) from com.saviynt.ssm.entity.Roles r where r.roletype in (5) and status= 1 and r.endpointkey= 26 and r.requestable=true and (r.role_name in (select DISTINCT CONCAT('QB',':',dv.attribute2,':',dv.attribute3,':',ev.entitlement_value) FROM com.saviynt.ssm.entity.Roles r,com.saviynt.ecm.identitywarehouse.domain.Entitlement_values ev,dataset_values dv where ev.entitlementtypekey=88 and FIND_IN_SET('FIN',r.customproperty2) and dv.datasetname='RoleViewTest1' and dv.attribute1='FIN')) ]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at

As of now you can leverage the dynamic attributes for sending the dataset values to requested roles query.

Further you can provide your use case so we can think of any other approach for it.

[sandeepsingh]

We have put simple text "r.role_name='TECHTOOL:RTC:Trucks - Renault:RTC_45'" still it does not take. Also, are we sure that dataset is not exposed to roletype? because we had a query with datasets and it worked.