Sheet Level / Data Level Security

[Kelliesy]

I have an app that holds data for our entire company.  I want to limit the data that is returned when a user views the sheets to only the employees that report to that manager.  Is the only way to do this, without creating multiple versions of the app, to use section access?  We have user groups but no set up currently that outlines our company hierarchy so would I have to also set up groups that divide the employees into each manager?  My goal is to do this with the last amount of manual up keep being required.  We are using Qlik Sense SaaS.

[BrunPierre]

You’re on the right track. The best way to make sure managers only see their own employees’ data is by using section access combined with data reduction.

How is the data set up to connect managers to their employees?

[howdash]

Context

I've worked with HR data in the past but we had a Human Resource Management (HRM) system that was maintained by HR and used as the source of truth for manager-employee mapping. Even in that case, there were actual HR team members who manually maintained that manager-employee mapping. There's likely no way around having to maintain that mapping manually.

Now, whether that mapping is maintained in Excel or in an HRM system, whether it's maintained by you or by HR team, those are separate questions. But someone has to maintain that manager-employee mapping.

Managing Manager-Employee Mapping

Ideally the manager-employee mapping would be managed by HR team since they likely have most exposure to who manages who and when employees move teams, new employees get hired, and existing employees leave.

Recommendation

I recommend reaching out to whoever is most suited in your company and have them create a spreadsheet with manager-employee mapping as the central, source of truth that you and others on your team can use for section access and whatever else is needed.

Creating a centrally managed manager-employee mapping and designating a team or an individual to maintain that mapping as employees move around, join and leave, and then using that centrally managed mapping for section access is likely the best way to manage access with least amount of effort.

[Kelliesy]

At the moment the data is not set up to connect managers to their employees.  We do have some HR tables that have employee and manager but they are not connected to the data we want to limit.  I probably could find a way to map the HR data to our data that we want to limit.  

What is data reduction?  Is that a different feature/function?

[Kelliesy]

Having never done section access, if I did have some sort of sheet (we love a good GoogleSheet) how would I use that in my section access? I know i need an inline table but do I upload the file and use that in my table? 

[howdash · Accepted solution]

No, the section access table doesn't have to be an inline load script, it can be a regular load statement.

PRO TIP

If you have never worked with section access the biggest pro tip I can give you is make a copy of your app before you start building out your section access table. If you don't build the table right, you can lock yourself out of the application and will need to open the app without data, edit the section access script and reload the app. Not a huge issue but it can be nerve wrecking.

DATA REDUCTION

To answer your question about data reduction, it's just a fancy term that describes what Qlik Sense is doing behind the scenes when you add a section access to an app. It essentially checks who is opening the app, looks in the section access table to see what data that user is allowed to see, and then get's rid of (a.k.a. reduces) any data that user is not allowed to see before the app is loaded for a user. The end result is once the app loads for the user, the user will only be able to see the data that the user was allowed to see according to the section access table - i.e. an app with "reduced" dataset.

QUICK SECTION ACCESS HOW-TO

At high level, the requirements for a section access to work are:

1. Section access script must start with Section Access; statement.
2. Section access table must have ACCESS, USERID or USER.EMAIL, and a field containing uppercase values used for data reduction.
   1. In your case it would be manager IDs or names.
3. Column names of section access table must be uppercased.
4. Values in all columns of section access table must be uppercased.
5. Section access script must end with Section Application; statement.
6. Whatever column that you will use to hold manager IDs/names in the section access table must also exist in your data model.
   1. So if you have MANAGER_ID column in your section access table containing uppercased manager IDs, you will also need to have a corresponding MANAGER_ID column in your data model that will hold matching, uppercased manager IDs.

In your case, the section access script would look something like this:

Section Access;

// create a section access table containing service account
sectionAccess:
LOAD *
INLINE [
ACCESS, USERID
ADMIN, INTERNAL\SA_SCHEDULER
];

// load all manager IDs used to control which user gets to see which manager's records
managerIDs:
LOAD Distinct Upper([Manager ID]) as MANAGER_ID
FROM [lib://GoogleDrive/somelongstringoflettersandnumbers/manager-employee-mapping.csv]
;

// load users that should have admin access to all manager records
Concatenate(sectionAccess)
LOAD 'ADMIN' as ACCESS,
      Upper(UserId) as USERID
FROM [lib://GoogleDrive/somelongstringoflettersandnumbers/some-data-source-containing-list-of-admin-users.csv]
;

// join list of manager IDs for all admin users so that they would have full access to all records for all managers
Join(sectionAccess)
LOAD MANAGER_ID
Resident managerIDs
;

// load list of users and which manager's data each user should be able to see
Concatenate(sectionAccess)
LOAD 'USER' as ACCESS,
      Upper(UserId) as USERID,
      Upper([Manager ID]) as MANAGER_ID
FROM [lib://GoogleDrive/somelongstringoflettersandnumbers/some-data-source-containing-mapping-of-users-to-manager-ids.csv]
;

Section Application;

// the rest of the script that loads data for the app

The idea is to give Qlik Sense a table (table named sectionAccess in this example) that contains access type of each user, list of users that will be accessing the app, and list of managers that each user should be able to see in the app. Imagine a spreadsheet with three columns - ACCESS, USERID, and MANAGER_ID - that simply holds list of users and which manager's data each user can see.

Conceptually, it's nothing too complicated but it does usually take lots of time and lots of thinking to nail down and get it to work just right. So don't beat yourself up if you'll be struggling with figuring out section access. It's one of the advanced features of Qlik Sense and it does take time to learn.

[Kelliesy]

This is great!  Thank you so much for this very detailed reply.  I understand this concept so much better now.  

[Kelliesy]

Follow up question -- what if I need to limit the data by Cost Center?  Here is what I have but it's not working;  

[SECTION_ACCESS]:
LOAD *
INLINE [
ACCESS, USERID
ADMIN, INTERNAL\SA_SCHEDULER]

Managers:
LOAD Distinct Upper(Cost_Center_Manager) as MANAGER
FROM [lib://Data_HR:DataFiles/WDWorker.qvd](qvd);

// join list of manager IDs for all admin users so that they would have full access to all records for all managers
Join(SECTION_ACCESS)
LOAD MANAGER
Resident Managers;

// //load list of users and which manager's data each user should be able to see
Concatenate(SECTION_ACCESS)
LOAD DISTINCT 'USER' as ACCESS,
Upper(Cost_Center) as USERID,
Upper(Cost_Center_Manager) as MANAGER
FROM [lib://DataFiles/Worker.qvd](qvd)
WHERE NOT ISNULL(Cost_Center_Manager) ;

DROP TABLE Managers;
Section Application;

In my data I have a field called CC_Manager that I renamed MANAGER.  I also have no idea how to add myself into this because I'm the admin not a Manager or CC.  You are right, this takes a lot of thinking!

[howdash]

You can do something like this:

[SECTION_ACCESS]:
LOAD *
INLINE [
ACCESS, USERID
ADMIN, INTERNAL\SA_SCHEDULER
ADMIN, YOURDOMAIN\KELLIESY
]
;


Managers:
LOAD Distinct Upper(Cost_Center_Manager) as MANAGER
FROM [lib://Data_HR:DataFiles/WDWorker.qvd](qvd);

// join list of manager IDs for all admin users so that they would have full access to all records for all managers
Join(SECTION_ACCESS)
LOAD MANAGER
Resident Managers;

// load list of users and which manager's data each user should be able to see
Concatenate(SECTION_ACCESS)
LOAD DISTINCT 'USER' as ACCESS,
Upper(User_Id) as USERID,
Upper(Cost_Center_Manager) as MANAGER
FROM [lib://DataFiles/Worker.qvd](qvd)
WHERE NOT ISNULL(Cost_Center_Manager);

DROP TABLE Managers;
Section Application;

 The two changes you would need to make are:

1. Add the your domain name and user ID to the SECTION_ACCESS table.
   1. For example, at howdash my domain name is HD and my username is mikhail. So I would add HD\MIKHAIL to the SECTION_ACCESS table. You would do the same but for your domain and username. That will give you full access to all managers and cost centers in the app.
2. In the Concatenate load script, the USERID field must contain the domain name and usernames of users that will be accessing the app.
   1. You were using Upper(Cost_Center) as USERID and instead it should be whatever field you have in the Worker.qvd file that contains list of users that will be accessing the app.

This will enable you to control which cost center and manager data each user will be able to see.

That's only half of the problem solved though. To make this work, you will need to make sure that the data model has the matching MANAGER field that contains a combination of manager IDs and cost centers.

Here's an example of what your SECTION_ACCESS and a table in the data model would look like:

SECTION_ACCESS:

ACCESS	USERID	MANAGER
ADMIN	INTERNAL\SA_SCHEDULER	CC01-MGR01
ADMIN	INTERNAL\SA_SCHEDULER	CC02-MGR01
ADMIN	INTERNAL\SA_SCHEDULER	CC03-MGR01
ADMIN	INTERNAL\SA_SCHEDULER	CC04-MGR01
ADMIN	INTERNAL\SA_SCHEDULER	CC04-MGR02
ADMIN	INTERNAL\SA_SCHEDULER	CC05-MGR02
ADMIN	INTERNAL\SA_SCHEDULER	CC06-MGR03
ADMIN	DOMAINNAME\KELLIESY	CC01-MGR01
ADMIN	DOMAINNAME\KELLIESY	CC02-MGR01
ADMIN	DOMAINNAME\KELLIESY	CC03-MGR01
ADMIN	DOMAINNAME\KELLIESY	CC04-MGR01
ADMIN	DOMAINNAME\KELLIESY	CC04-MGR02
ADMIN	DOMAINNAME\KELLIESY	CC05-MGR02
ADMIN	DOMAINNAME\KELLIESY	CC06-MGR03
USER	DOMAINNAME\MIKHAIL	CC04-MGR02
USER	DOMAINNAME\MIKHAIL	CC05-MGR02
USER	DOMAINNAME\JACOB	CC01-MGR01
USER	DOMAINNAME\JESSICA	CC02-MGR01
USER	DOMAINNAME\JESSICA	CC06-MGR03
USER	DOMAINNAME\PHIL	CC06-MGR03

In this example:

• You will see all cost centers and all managers.
• User with username MIKHAIL will see data for cost centers CC04 and CC05 but only for manager MGR02.
• User with username JACOB will see data for cost center CC01 and manager MGR01.
• User with username JESSICA will see data for cost centers CC02 and CC06 for manager MGR01 and MGR06.
• User with username PHIL will see data for cost center CC06 and manager MGR03.

Once you have this section access table build and combinations of cost centers and managers that each user should see created, you will need to create the same MANAGER field that will hold combination of cost centers and managers in the data model.

Here's an example of the corresponding table that you'll need to have in the data model for the section access to work:

DATA:

MANAGER	Cost Center ID	Manager ID	Some Data Field	Another Data Field
CC01-MGR01	CC01	MGR01	abc	def
CC01-MGR01	CC01	MGR01	ghi	jkl
CC02-MGR01	CC02	MGR01	mno	pqr
CC02-MGR01	CC02	MGR01	stu	vwx
CC03-MGR01	CC03	MGR01	yz	xwv
CC04-MGR01	CC04	MGR01	uts	rqp
CC04-MGR02	CC04	MGR02	onm	lkj
CC04-MGR02	CC04	MGR02	ihg	fed
CC05-MGR02	CC05	MGR02	cba	abc
CC05-MGR02	CC05	MGR02	def	ghi
CC06-MGR03	CC06	MGR03	jkl	mno
CC06-MGR03	CC06	MGR03	pqr	stu
CC06-MGR03	CC06	MGR03	vwx	yz

With both the section access and the data tables available in the data model and associated on the MANAGER field, Qlik Sense will be able to reduce data in the DATA table based on whichever records each user was specified to see in the SECTION_ACCESS table.

So you, for example, will be able to see everything because the SECTION_ACCESS table has you listed as ADMIN and you have all possible combinations of managers and cost centers listed in the MANAGER field.

But user with username MIKHAIL, for example, will only see this portion of the DATA table:

MANAGER	Cost Center ID	Manager ID	Some Data Field	Another Data Field
CC04-MGR02	CC04	MGR02	onm	lkj
CC04-MGR02	CC04	MGR02	ihg	fed
CC05-MGR02	CC05	MGR02	cba	abc
CC05-MGR02	CC05	MGR02	def	ghi

No other records will be shown to MIKHAIL user.

The data reduction will happen similarly for all other users listed in the SECTION_ACCESS table. So, JACOB, as another example, will only be able to see these records in the DATA table:

MANAGER	Cost Center ID	Manager ID	Some Data Field	Another Data Field
CC01-MGR01	CC01	MGR01	abc	def
CC01-MGR01	CC01	MGR01	ghi	jkl

Does that make sense? I know it's a whole lot but hopefully the examples and the tables with example records helped to visualize what's happening behind the scenes.

[Kelliesy]

Most of that makes sense.  I understand that my User ID should be domain/Manager (because in my case that is who will be using the app).  

Where I get lost is that my last field which I currently have named Manager (this was me attempting a million different ways to do this) is really CostCenter because in our data we have a table that has Manager and the Cost Center(s) they are related to.  I was thinking I could something like this;  

//Give myself and my schedule acct access to all costcenters;
[SECTION_ACCESS]:
LOAD *
INLINE [
ACCESS, USERID, COSTCENTER
ADMIN, INTERNAL\SA_SCHEDULER, *,
ADMIN, domain/KELLIE SY, *];

//Bring in the Managers and CostCenters they are associated to
Managers:
LOAD Distinct Upper(Cost_Center) as COSTCENTER
Upper(Cost_Center_Manager) as USERID   ///some how add the domain to the beginning of this
FROM [lib://DataFiles/WDWorker.qvd](qvd);

//Joining in the Managers to the Section Access and assigning them Admin
JOIN (SECTION_ACCESS)
Load 'ADMIN' as ACCESS,
COSTCENTER,
USERID
RESIDENT Managers
WHERE Not IsNull(USERID); // we have some blanks I want to remove

Section Application;

However, if I add the domain to the UserID I now don't have a field in my data that would match it.  I do have a Manager and Cost Center field in my data.  This is where I am getting stuck.  Do I have to create a key field like you listed above that does Manager/CostCenter in both my Section Access and my data?  

Hopefully that makes sense.  I really appreciate your help on this!