Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/05/2024 03:57 AM
Hi Team,
Can you please help me out with the SQL query that results user's having all the roles and accounts that are provisioned.
Thanks,
Sharanya V
07/05/2024 07:15 AM
SELECT r.role_name,u,username,a.name,a.status
FROM roles r,role_user_account rua,users u,accounts a
WHERE rua.rolekey= r.rolekey and u.userkey=rua.userkey and a.accountkey=rua.accountkey
07/05/2024 07:21 AM
Hi @rushikeshvartak ,
Thanks for the reply, In the same query can we also check for endpoints provisioned for the user along with roles and account information.
If yes , can you please update the same
Thanks,
Sharanya
07/05/2024 07:24 AM
join with arstasks table and endpoints table
07/05/2024 07:24 AM
SELECT r.role_name,u,username,a.name,a.status,a.arstaskkey
FROM roles r,role_user_account rua,users u,accounts a
WHERE rua.rolekey= r.rolekey and u.userkey=rua.userkey and a.accountkey=rua.accountkey
07/05/2024 07:40 AM
07/05/2024 01:02 PM
@sharu please check below query:
SELECT
R.ROLE_NAME,
U.USERNAME,
A.NAME,
A.STATUS,
A.ARSTASKKEY,
E.ENDPOINTNAME
FROM
ROLES R
JOIN
ROLE_USER_ACCOUNT RUA ON RUA.ROLEKEY = R.ROLEKEY
JOIN
USERS U ON U.USERKEY = RUA.USERKEY
JOIN
ACCOUNTS A ON A.ACCOUNTKEY = RUA.ACCOUNTKEY
JOIN
ARSTASKS AR ON AR.ACCOUNTKEY = A.ACCOUNTKEY
JOIN
ENDPOINTS E ON AR.ENDPOINT = E.ENDPOINTKEY