Roles query to filter application roles at ARS based on requestee attribute.

[Sucheta]

Hello,

We want to filter application roles of respective endpoints based on DA combination user selects at ARS. Below is the working query if we hardcode userkey value.

Working query:

r.customproperty1 IN (SELECT DISTINCT CONCAT('TECHTOOL;',dv.attribute2,';',dv.attribute7) FROM Dataset_Values dv,Users u WHERE dv.datasetname='BALDO_BUSINESSAREA_MAPPING' AND dv.attribute7='${businessarea_brand}' AND dv.attribute3 != 'NOT SUPPORTED' AND userkey=3116 AND FIND_IN_SET(dv.attribute2,u.customproperty2) > 0)

As soon as I try userkey= ${requestee} or userkey=’ ${requestee}’ or users.id=(currentUser), role combination does not populates at ARS

Basically we need userkey variable here to make roles populate.

Thanks!

[naveenss]

Hi @Sucheta  are you trying to use ${requestee} variable in the role type query? If yes, that's not supported. Please create a dynamic attribute that holds the requestee's value and use the dynamic attribute in the role type query like below

userkey='${userRequestee}'

where ${userRequestee} is a dynamic attribute.

[Sucheta]

Hi @naveenss ,

Created DA with 'Account' as requesttype. Role query updated to, 

r.customproperty1 IN (SELECT DISTINCT CONCAT('TECHTOOL;',dv.attribute2,';',dv.attribute7) FROM Dataset_Values dv,Users u WHERE dv.datasetname='BALDO_BUSINESSAREA_MAPPING' AND dv.attribute7='${businessarea_brand}' AND dv.attribute3 != 'NOT SUPPORTED' AND userkey='${userRequestee}' AND FIND_IN_SET(dv.attribute2,u.customproperty2) > 0)

Roles are still not populating. Please let me know if I am missing something. 

Thanks!

[naveenss]

@Sucheta  The query should be 

select userkey as ID from users where userkey=${requestee}

[Sucheta]

Thanks for that @naveenss . Somehow roles are still not populating. 

[pmahalle]

Hi @Sucheta ,

Are you able to see requestee is populating in dynamic attribute while requesting?

[Sucheta]

@pmahalle I have made it hidden as it is unnecessary information for requestor. Is it like hidden attribute would not compute at ARS? However when I make it non-hidden, error page appears while requesting.

Thanks!

[pmahalle]

@Sucheta 

Which means value is not properly coming in dynamic attribute. Try with username instead of userkey.

Use below queries:

Dynamic attribute : select username as ID from users where userkey=${requestee}

Role Query: r.customproperty1 IN (SELECT DISTINCT CONCAT('TECHTOOL;',dv.attribute2,';',dv.attribute7) FROM Dataset_Values dv,Users u WHERE dv.datasetname='BALDO_BUSINESSAREA_MAPPING' AND dv.attribute7='${businessarea_brand}' AND dv.attribute3 != 'NOT SUPPORTED' AND u.username='{userRequestee}' AND FIND_IN_SET(dv.attribute2,u.customproperty2) > 0)

[Sucheta]

@pmahalle It did not work

[pmahalle]

@Sucheta 

Put $ with userRequestee in Role query as below:

r.customproperty1 IN (SELECT DISTINCT CONCAT('TECHTOOL;',dv.attribute2,';',dv.attribute7) FROM Dataset_Values dv,Users u WHERE dv.datasetname='BALDO_BUSINESSAREA_MAPPING' AND dv.attribute7='${businessarea_brand}' AND dv.attribute3 != 'NOT SUPPORTED' AND u.username='${userRequestee}' AND FIND_IN_SET(dv.attribute2,u.customproperty2) > 0)

Also, when you made dyanamic attribute un-hidden, you can see username populating?

[Sucheta]

@pmahalle no luck on this

Thanks!

[naveenss]

@Suchetakeep the userRequestee field non editable but dont hide it. Also please tweak the role type query like below

r.customproperty1 IN (SELECT DISTINCT CONCAT('TECHTOOL;',dv.attribute2,';',dv.attribute7) FROM Dataset_Values dv,Users u WHERE dv.datasetname='BALDO_BUSINESSAREA_MAPPING' AND dv.attribute7='${businessarea_brand}' AND dv.attribute3 != 'NOT SUPPORTED' AND u.userkey=${userRequestee} AND FIND_IN_SET(dv.attribute2,u.customproperty2) > 0)

Let me know if this works. 

[Sucheta]

@naveenss no luck on this