Implement role-based filtering on Business Process data
The role-based access mechanism (RBAC) limits access to data through Analytics dashboards based on the following filter categories:
Group filter restricting access to data for all users in a particular group, accounting for defined filtering criteria (for instance, name contains value x).
User filter restricting access to data for a particular user, accounting for defined filtering criteria (for instance, name contains value x).
Any user can have both filters assigned. In this case, the user sees all Business Process-related data that both filters permit to see (combined, not intersecting).
You can create and assign filters in Control Tower. The Analytics component supports filtering Business Process data by the following criteria:
- Author
- Tag
- Title
Database
To implement RBAC, add the User Name column to a view or source code that supplies data for a dashboard and is used as a data source.
To do this, join data from the wh_user_process_execution_link and wh_application_user tables using the LEFT JOIN operation. In the example below, the operation is used to define the wb_process_t_v view.
Expand to view example
create view [dm].[wb_process_t_v]
as
with wb_process_t_v_cte
as (
select
pe.uuid as 'Process Execution'
, t.id as 'Task Id'
, p.name as 'Process Name'
, pe.start_date as 'Process Execution Start Date'
, pe.end_date as 'Process Execution End Date'
, cast(pe.start_date as NVARCHAR(19)) +' - ' + pe.uuid as 'Process Execution by Start Date'
, pe.sla_type as 'SLA type'
, upper(left(pe.status,1)) + lower(substring(pe.status,2,100)) as 'Process Status'
, iif(pe.tracking_activated = 1, 'Activated', 'Not Activated') as 'Tracking'
, t.name as 'Task Name'
, t.has_processing_issues as 'Task Issues'
from (wh_process_execution pe
join wh_process p
on pe.process_id = p.id
join wh_task t
on t.process_execution_id = pe.id)
where
(pe.start_date >= dateadd(day,-30, getutcdate())
or pe.status = 'Processing')
and t.task_type != 'Single Task'
)
select
cte.[Process Execution]
, cte.[Task Id]
, cte.[Process Name]
, cte.[Process Execution Start Date]
, cte.[Process Execution End Date]
, cte.[Process Execution by Start Date]
, cte.[SLA type]
, cte.[Process Status]
, cte.[Tracking]
, cte.[Task Name]
, cte.[Task Issues]
, u.user_name as 'User Name'
from wb_process_t_v_cte cte
join wh_user_process_execution_link l
on cte.[Process Execution] = l.process_execution_uuid
join wh_application_user u
on l.user_id = u.id and upper(u.status)='ACTIVE'
When you need a default user with all data visible and without any assigned filters, define one. The main idea of adding the default user is to retrieve a single dataset with the hardcoded default user as User Name instead of duplicating the same data for all users without restrictions at the platform level.
Expand to view example
create view [dm].[wb_process_t_v]
as
with wb_process_t_v_cte
as (
select
pe.uuid as 'Process Execution'
, t.id as 'Task Id'
, p.name as 'Process Name'
, pe.start_date as 'Process Execution Start Date'
, pe.end_date as 'Process Execution End Date'
, cast(pe.start_date as NVARCHAR(19)) +' - ' + pe.uuid as 'Process Execution by Start Date'
, pe.sla_type as 'SLA type'
, upper(left(pe.status,1)) + lower(substring(pe.status,2,100)) as 'Process Status'
, iif(pe.tracking_activated = 1, 'Activated', 'Not Activated') as 'Tracking'
, t.name as 'Task Name'
, t.has_processing_issues as 'Task Issues'
from (wh_process_execution pe
join wh_process p
on pe.process_id = p.id
join wh_task t
on t.process_execution_id = pe.id)
where
(pe.start_date >= dateadd(day,-30, getutcdate())
or pe.status = 'Processing')
and t.task_type != 'Single Task'
)
select
cte.[Process Execution]
, cte.[Task Id]
, cte.[Process Name]
, cte.[Process Execution Start Date]
, cte.[Process Execution End Date]
, cte.[Process Execution by Start Date]
, cte.[SLA type]
, cte.[Process Status]
, cte.[Tracking]
, cte.[Task Name]
, cte.[Task Issues]
, u.user_name as 'User Name'
from wb_process_t_v_cte cte
join wh_user_process_execution_link l
on cte.[Process Execution] = l.process_execution_uuid
join wh_application_user u
on l.user_id = u.id and upper(u.status)='ACTIVE'
union
select
cte.[Process Execution]
, cte.[Task Id]
, cte.[Process Name]
, cte.[Process Execution Start Date]
, cte.[Process Execution End Date]
, cte.[Process Execution by Start Date]
, cte.[SLA type]
, cte.[Process Status]
, cte.[Tracking]
, cte.[Task Name]
, cte.[Task Issues]
, cast('default' as nvarchar(138)) as 'User Name'
from wb_process_t_v_cte cte
Superset SQL
The final step to utilize the filters at the dashboard level requires additional SQL code modification on the Control Tower side. To do this, join data from the dm.wh_application_user table in your SQL code (Data > SQL queiries) using the LEFT JOIN operation similar to the one below:
select *
from dm.wb_overview_v2 wov
left join dm.wh_application_user wau on ( case
when wau.no_filter=1 then 'default'
else '{{ current_username() }}'
end
)=wov.[User Name]
where wau.user_name ='{{ current_username() }}'
After you add this to your SQL code, the platform picks up current_username as a user logs in and provides access to data in accordance with the filters preset for the user.
Validating dashboard and permissions
To validate the outcome and dashboard behavior, log in as:
- A user with no filters, and verify what data is available or not. Depending on the default user approach, it is either all data or nothing.
- A user with a target filter that enables data visibility on the dashboard and includes related processes.
- A user with a different filter that does not include required processes, and the user should see no data.
Be aware that in cases when a user is a member of a group with filters and has individual filters assigned, they can see all Business Processes that satisfy the criteria defined by all filters (combined, not intersecting).