We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Access query at Endpoint level

nitishdas
New Contributor
New Contributor

Hi Team,

I am trying to achieve a requirement that a user would be able to see the endpoint A in the ARS if he has access or account created in endpoint B. In the access query we also need to include the sav role of that user should be either Role End User or Role admin and the requestee's company should not be a certain specific value.

I have written one query for the same requirement. Could you please let me know if this is correct or it any changes are required:

WHERE(SELECT u.USERNAME FROM users u INNER JOIN user_accounts ua ON u.USERKEY=ua.USERKEY INNER JOIN accounts a ON ua.ACCOUNTKEY = a.ACCOUNTKEY where u.userkey IN (select DISTINCT us.userkey from user_savroles us inner join SavRoles s on us.rolekey=s.rolekey WHERE s.rolename IN ('ROLE_END_USER','ROLE_ADMIN')) AND a.ENDPOINTKEY='17' AND a.STATUS IN('Manually Provisioned','Active','1') AND u.USERKEY='${requestee.id}') AND '${requestee.companyname}' != 'AAA B2B Users'

21 REPLIES 21

Rajesh-R
Saviynt Employee
Saviynt Employee

@nitishdas  - The following sample will work:

 

WHERE USERS.USERKEY IN (SELECT REQUESTEE.USERKEY FROM USERS REQUESTEE, USERS REQUESTORS, USER_ACCOUNTS UA, ACCOUNTS A WHERE REQUESTEE.USERKEY = UA.USERKEY AND A.ACCOUNTKEY = UA.ACCOUNTKEY AND A.STATUS IN ('ACTIVE', 1, 'MANUALLY PROVISIONED') AND A.ENDPOINTKEY = <> AND REQUESTEE.EMPLOYEECLASS IN ('EMPLOYEE','INTERNAL PARTNER','COMPUTING ENTITY') AND REQUESTEE.USERKEY = ${requestee.id} AND REQUESTORS.USERKEY IN (SELECT DISTINCT US.USERKEY FROM USER_SAVROLES US, SAVROLES SR WHERE US.ROLEKEY = SR.ROLEKEY AND SR.ROLENAME IN ('ROLE_ADMIN','ROLE_FUNCTION_IDADMIN','ROLE_DEVOPS','ROLE_FUNCTION_SOC','ROLE_FUNCTION_ISDL1')) AND REQUESTORS.USERKEY = ${requestor.id})

 


Thanks
Rajesh Ramalingam
Saviynt India

Hi @Rajesh-R ,

I have one query here that is this query checking that a requestor can raise the request of endpoint b if he has access to endpoint 17?

Does it return result in data analyzer


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak ,

I am unable to do so bcz I guess this savroles table is restricted in UI through data analyzer.

Check from analytics


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak ,

When I am doing a Preview of the below query then I am getting results:

SELECT u.USERNAME FROM users u INNER JOIN user_accounts ua ON u.USERKEY=ua.USERKEY INNER JOIN accounts a ON ua.ACCOUNTKEY = a.ACCOUNTKEY where u.userkey IN (select DISTINCT us.userkey from user_savroles us inner join SavRoles s on us.rolekey=s.rolekey WHERE s.rolename IN ('ROLE_END_USER','ROLE_ADMIN')) AND a.ENDPOINTKEY='17' AND a.STATUS IN('Manually Provisioned','Active','1')

But when I am putting the below query in access query I am getting an error "

Error Detail
Unexpected Error occured Please try later.."
 
where ${requestee.id} IN (SELECT u.userkey FROM users u INNER JOIN user_accounts ua ON u.USERKEY=ua.USERKEY INNER JOIN accounts a ON ua.ACCOUNTKEY = a.ACCOUNTKEY where u.userkey IN (select DISTINCT us.userkey from user_savroles us inner join SavRoles s on us.rolekey=s.rolekey WHERE s.rolename IN ('ROLE_END_USER','ROLE_ADMIN')) AND a.ENDPOINTKEY='17' AND a.STATUS IN('Manually Provisioned','Active','1')) AND '${requestee.companyname}' != 'AAA B2B Users'

Hi @Rajesh-R ,

This did not work.

Rajesh-R
Saviynt Employee
Saviynt Employee

@nitishdas 

This will give results like below:

The requestor must have the savroles mentioned

The requestee must have an account in the endpointkey=17

requestee companyname must not be in - COMPANYNAME NOT IN ('AAA B2B Users')

WHERE USERS.USERKEY IN (SELECT REQUESTEE.USERKEY FROM USERS REQUESTEE, USERS REQUESTORS, USER_ACCOUNTS UA, ACCOUNTS A WHERE REQUESTEE.USERKEY = UA.USERKEY AND A.ACCOUNTKEY = UA.ACCOUNTKEY AND A.STATUS IN ('ACTIVE', 1, 'MANUALLY PROVISIONED') AND A.ENDPOINTKEY = 17 AND REQUESTEE.COMPANYNAME NOT IN ('AAA B2B Users') AND REQUESTEE.USERKEY = ${requestee.id} AND REQUESTORS.USERKEY IN (SELECT DISTINCT US.USERKEY FROM USER_SAVROLES US, SAVROLES SR WHERE US.ROLEKEY = SR.ROLEKEY AND SR.ROLENAME IN ('ROLE_ADMIN','ROLE_FUNCTION_IDADMIN','ROLE_DEVOPS','ROLE_FUNCTION_SOC','ROLE_FUNCTION_ISDL1')) AND REQUESTORS.USERKEY = ${requestor.id})

 


Thanks
Rajesh Ramalingam
Saviynt India

Hi @Rajesh-R ,

We need to check that the requestor must have access to endpoint 17 as a requirement before raising access for endpoint B and the requestee company should not be AAA B2B users

Rajesh-R
Saviynt Employee
Saviynt Employee

@nitishdas  - Please try below:

WHERE USERS.USERKEY IN (SELECT REQUESTEE.USERKEY FROM USERS REQUESTEE, USERS REQUESTORS, USER_ACCOUNTS UA, ACCOUNTS A WHERE REQUESTORS.USERKEY = UA.USERKEY AND A.ACCOUNTKEY = UA.ACCOUNTKEY AND A.STATUS IN ('ACTIVE', 1, 'MANUALLY PROVISIONED') AND A.ENDPOINTKEY = 17 AND REQUESTEE.COMPANYNAME NOT IN ('AAA B2B Users') AND REQUESTEE.USERKEY = ${requestee.id} AND REQUESTORS.USERKEY IN (SELECT DISTINCT US.USERKEY FROM USER_SAVROLES US, SAVROLES SR WHERE US.ROLEKEY = SR.ROLEKEY AND SR.ROLENAME IN ('ROLE_ADMIN','ROLE_END_USER')) AND REQUESTORS.USERKEY = ${requestor.id})

Thanks
Rajesh Ramalingam
Saviynt India

Hi @Rajesh-R ,

Getting error, it is also not working.

Manu269
All-Star
All-Star

@nitishdas can you try this :

WHERE USERS.USERKEY IN (SELECT REQUESTEE.USERKEY FROM USERS REQUESTEE, USERS REQUESTORS, USER_ACCOUNTS UA, ACCOUNTS A WHERE REQUESTORS.USERKEY = UA.USERKEY AND A.ACCOUNTKEY = UA.ACCOUNTKEY AND A.STATUS IN ('ACTIVE', 1, 'MANUALLY PROVISIONED') AND A.ENDPOINTKEY = <EPKEY> AND REQUESTEE.COMPANYNAME NOT IN ('<CompanyName>') AND REQUESTEE.USERKEY = ${requestee.id} AND REQUESTORS.USERKEY IN (SELECT DISTINCT US.USERKEY FROM USER_SAVROLES US, SAVROLES SR WHERE US.ROLEKEY = SR.ROLEKEY AND SR.ROLENAME IN (<SAV ROLEs>)) AND REQUESTORS.USERKEY = ${requestor.id})

Note : Replace the required values at the location

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.

Hi @Manu269 ,

Yeah already tried this but this is bit wrong bcz it is giving the user key of the requestee's key but the requirement is for requestor's key and I changed it but it throwing error.

WHERE USERS.USERKEY IN (select requestedBy.userkey from users requestedBy,user_accounts ua,accounts a where requestedBy.userkey=ua.userkey and a.accountkey=ua.accounkey and a.endpointkey=17 and requestedBy.userkey=${requestor.id}) AND USERS.USERKEY NOT IN (select enduser.userkey from users u2 where and enduser.userkey=${requestor.id} and enduser.companyname NOT IN ('AAA B2B Users'))


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak ,

It seems the sav roles check is not done in your query. We need to check that as well.

Update sav role keys in query

WHERE USERS.USERKEY IN (select requestedBy.userkey from users requestedBy,user_accounts ua,accounts a where requestedBy.userkey=ua.userkey and a.accountkey=ua.accounkey and a.endpointkey=17 and requestedBy.userkey=${requestor.id}) AND USERS.USERKEY NOT IN (select enduser.userkey from users u2 where and enduser.userkey=${requestor.id} and enduser.companyname NOT IN ('AAA B2B Users')) AND USERS.USERKEY IN (select savuser.userkey from USER_SAVROLES savuser where savuser.userkey=${requestor.id} and SR.rolekey in (1,2,3))

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak ,

Getting error "Unexpected error occured"

Where ? while updating endpoint or requesting access

Share logs ?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak ,

I am getting the error while updating the access query in endpoint.

v5.5 accessquery column length is 255 and query is 531 characters 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

WHERE USERS.USERKEY IN (select requestedBy.userkey from users requestedBy,user_accounts ua,accounts a where requestedBy.userkey=ua.userkey and a.accountkey=ua.accountkey and a.endpointkey=17 and requestedBy.userkey=${requestor.id}) AND USERS.USERKEY NOT IN (select enduser.userkey from users enduser where enduser.userkey=${requestee.id} and enduser.companyname NOT IN ('AAA B2B Users')) AND USERS.USERKEY IN (select savuser.userkey from USER_SAVROLES savuser where savuser.userkey=${requestee.id} and savuser.rolekey in (1,2,3))

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.