User Management

This chapter details information about User Management within the AGR system.

System User

The System user is a super admin user that should only be used by AGR Consultants. The user can’t be deleted and can only be edited from My Profile. The System user has the System role and that role can’t be added to other users.

System user roles and access groups can only be edited from DB.

Name System
Username system
Default password ********
Roles System
User Access -

System Features

Only users with the System role can see the following features in Roles page:

  • Development Mode
  • User Management Modify

This means that the System user can activate “Development Mode” or “User Management Modify” on other users but other users won’t even see the feature in the feature list, even if the feature is activated. This way the System user can activate “User Management Modify” on f. ex. the Admin role, but user with Admin role can’t give other roles that feature.

How to Manage App Features

Usually two features are created per workspace, one for users that should be able to modify and another one for read only usage.

features

  • id (int) - required: identity column - autogenerated
  • caption (nvarchar) - required: The name of the feature that is shown in the user management page in the system. You can either hard code the caption or a translation string
  • feature_ref (nvarchar - required): the name of the feature, must be unique. should end with .mod if the feature should allow the user to modify.
  • description (nvarchar) - required: The description of what priviledges users have when having access to this feature. You can either hard code the caption or a translation string
  • enabled (bit) - required: 1 the feature should be available in the system; 0 otherwise
  • system (bit) - required: 1 if this a feature only meant for AGR developers/system user; 0 otherwise
  • workspace_feature (bit) - required: 1 if this a feature created with a workspace; 0 otherwise

Disable Enable Features

To disable or enable features the enabled column in the features table is updated. Disabling features will hide features from all users.

Change Feature to a System Feature

Update the system column in the features table. This will hide features in Roles page from all users except System user. The System user can then enable the feature for other roles but other users will never see the feature in Roles page. More on this in System User documentation.


Data Restrictions

To restrict witch data the user can see in the system you can use the user access functionality. Note that this will add dynamic filters on top of all queries in the database so it might slow the system down in some cases.

Step 1

Make access group called Everything for users allowed to see everything

insert into [agr_prod].[dbo].[user_access_groups] 

values ('Everything',4,'A',1)
  • ‘Everything’ meaning users in this group will see everything
  • Location table happens to have id 4 in data_element_ref_tables
  • ‘A’ for groups that are not restricted
  • 1 for is_inclusive

Step 2

Make access group for users with restricted access.

Here we create an access group called Verslun Akureyri that will have restricted access in AGR. It has to have the same name as the user “Verslun Akureyri” , at least in this version of the code.

insert into [dbo].[user_access_groups] 

values ('Verslun Akureyri',4,'R',1)
  • ‘Verslun Akureyri’ found from name in User table
  • Location table has id 4 in data_element_ref_tables
  • ‘R’ for restricted
  • 1 for is_inclusive

Step 3

Define for restricted access group which location they can see. Here below, user group 3 can see location 1, and also we set restricted user to user group.

declare @user varchar(30) = 'akureyri' 
declare @location varchar(30) 
declare @location_no varchar(30) 
declare @userID int  
declare @loc_id varchar(30) 

select @loc_id = loc.id, @userid = u.id, @location = loc.name, @location_no = loc.location_no, @user = u.name from locations loc  
join users u 
on loc.id = u.location_id 
where loc.location_type = 'store' 
and u.username = @user 



select @user,@location,@location_no,@userID,@loc_id 
insert into [user_access_group_restrictions]  
select uag.id as user_access_group, loc.id as location_id_that_user_group_sees from locations loc   
cross join user_access_groups uag   
where loc.location_type = 'store' and loc.location_no = @location_no  
and uag.access_group_name = @user   

insert into [user_access_group_users]  
select u.id as userid, uag.id as user_access_group_id from users u   
cross join user_access_groups uag   
where uag.access_group_name = @user  
and u.name = @user
  • Here User Group ‘Akureyri’ has user_access_group id = 3 and location_id = 1 (red box on photo below)
  • Add the users that belong to each group. User ‘Akureyri’ has user_id 7 and has access to usergroup 3 which is store ‘Akureyri’ (yellow box on photo below)

create

Step 4

Now Akureyri is the only user that is able to see anything we need to add other users to the Everything user group.

Following code gives you all users that don’t have any access group and should probably go into the Everything user group .

insert into [dbo].[user_access_group_users] 
select  
id,  
1 --This should be the ID of the  "everything" user group.  
from [dbo].[users] where id not in (select user_id from [agr_prod].[dbo].[user_access_group_users])

Step 5

We need to activate these user access changes by updating the core.setting table

update core.setting  
set setting_value = 'true'  
where setting_key = 'user_access_active'

Extra: Step 6

  • Create a list of stores (or any other group) that are used for the access restriction.
select * from locations where location_type = 'store'
  • You can use this piece of code to create SQL commands to add them into the user_access_groups
select 'insert into [agr_prod].[dbo].[user_access_groups] values ('''+name+''',4,''R'',1)' from locations where location_type = 'store'
  • Use this code to populate user_access_group_restrictions. Maps the access group to the correct location_id:
insert into [user_access_group_restrictions] 
       select uag.id as access_group_name, l.id as location_id from locations l  
       join user_access_groups uag on l.name = uag.access_group_name
  • Finally, if all users already exist you could create some code to map users to each Access Group.