Saviynt Query Portal

All Modules

ARS Requests ARS Tasks Accounts Accounts, Endpoint Accounts, Endpoints Analytics Audit Logs Audit Trail Campaign Certification Connections Connections, Job Control Panel Endpoints Job Control Panel Roles Rules Security Systems User Group Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLES

Roles Details

Module: Roles

Description

Get the necessary details of all Roles

Dynamic Input

nan

Execute in

Data Analyzer, Analytics

SQL Query

SELECT
r.rolekey,
r.role_name,
r.description,
r.displayname,
ep.endpointname,
re.entitlement_valuekey,
ev.entitlement_value,
et.entitlementname AS entitlement_type_name,
pr.role_name AS parent_role_name,
r.requestable,
CASE
WHEN r.roletype = 0 THEN 'None'
WHEN r.roletype = 1 THEN 'Enabler Role'
WHEN r.roletype = 2 THEN 'Transactional Role'
WHEN r.roletype = 3 THEN 'Emergency Access Role'
WHEN r.roletype = 4 THEN 'Enterprise Role'
WHEN r.roletype = 5 THEN 'Application Role'
WHEN r.roletype = 6 THEN 'Entitlement Role'
ELSE 'Unknown'
END AS Role_Type,
CASE
WHEN r.status = 1 THEN 'Active'
WHEN r.status = 0 THEN 'Inactive'
ELSE 'Composing'
END AS Role_Status,
u.username AS update_user_name,
r.updatedate
FROM roles r
LEFT JOIN endpoints ep
ON r.endpointkey = ep.endpointkey
LEFT JOIN role_entitlements re
ON r.rolekey = re.rolekey
LEFT JOIN entitlement_values ev
ON re.entitlement_valuekey = ev.entitlement_valuekey
LEFT JOIN entitlement_types et
ON ev.entitlementtypekey = et.entitlementtypekey
LEFT JOIN roles pr
ON r.parentroles = pr.rolekey
LEFT JOIN users u
ON r.updateuser = u.userkey
ORDER BY r.rolekey;

Comments

nan

Query copied!