Qlik - Limit Acces To Data

15 Sep 2021

Dificulty ★★★☆☆

Why do it?

In some cases you need to limit what users can see in certan qlik files.
This solution means that users do not need to reload data.
And will only see the data that he is allowed to see.
But in case you have huge quantity of rows it will affect the performance of opening qlik files.

Lets create some demo data

In data load create some data

[Random_Data]:
SELECT '001'  as Connecting_Column
      ,'piko' as Shown_Data
      union
SELECT '002'  as Connecting_Column
      ,'miko' as Shown_Data     
;

And show it in Qlik
In the first grid lets add ‘Connecting_Column’ and ‘Shown_Data’
In a seperate grid add function OSuser()

=OSuser()

In the second grind you will find your user and group.

First limitation

  
Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, REDUCTION
ADMIN, QLIK-TEST\qliksvc ,  1
];
Section Application; 

This part creates hidden table that is used in Qlik and recognizes logged in user with user in this table.
In ACCESS input USER or ADMIN or *
In USERID input user as ‘UserDirectory’ + ‘/’ + ‘UserId’ or use *
REDUCTION for this one you can use any name but you will have to connect to it later. It specifies in which group of filters user belongs.

If you want to add multiple user just add lines betwen [] like

Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, REDUCTION
ADMIN, QLIK-TEST\qliksvc ,  1
ADMIN, QLIK-TEST\foo ,  1
USER, QLIK-TEST\bar ,  2
USER, QLIK-TEST\baz ,  3
*, QLIK-TEST\quux ,  3
];
Section Application; 

For REDUCTION - you can use difrent name

  
REDUCTION:
LOAD * inline [
REDUCTION , Connecting_Column
1 , 001
1 , 002
];

It represents groups of filters like in current example user in group 1 can see 001 and 002 in Connecting Column.
You will see examples as we proceed.

Current whole code

LIB CONNECT TO 'Microsoft_SQL_Server_192.xxx.xxx.xxx';


Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, REDUCTION
ADMIN, QLIK-TEST\qliksvc ,  1
];
Section Application;


REDUCTION:
LOAD * inline [
REDUCTION , Connecting_Column
1 , 001
1 , 002
];


[Random_Data]:
SELECT '001'  as Connecting_Column
      ,'piko' as Shown_Data
      union
SELECT '002' as Connecting_Column
      ,'miko' as Shown_Data     
;

And we will get all current data

Now if we change REDUCTION table

LIB CONNECT TO 'Microsoft_SQL_Server_192.xxx.xxx.xxx';


Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, REDUCTION
ADMIN, QLIK-TEST\qliksvc ,  1
];
Section Application;


REDUCTION:
LOAD * inline [
REDUCTION , Connecting_Column
1 , 001
];


[Random_Data]:
SELECT '001'  as Connecting_Column
      ,'piko' as Shown_Data
      union
SELECT '002' as Connecting_Column
      ,'miko' as Shown_Data     
;

Lets add more data

LIB CONNECT TO 'Microsoft_SQL_Server_192.xxx.xxx.xxx';


Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, REDUCTION
ADMIN, QLIK-TEST\qliksvc ,  1
];
Section Application;


REDUCTION:
LOAD * inline [
REDUCTION , Connecting_Column
1 , 001
1 , 003
];


[Random_Data]:
SELECT '001'  as Connecting_Column
      ,'piko' as Shown_Data
union
SELECT '002' as Connecting_Column
      ,'miko' as Shown_Data     
union
SELECT '003' as Connecting_Column
      ,'zingo' as Shown_Data    
;

Change ACCES to *

LIB CONNECT TO 'Microsoft_SQL_Server_192.xxx.xxx.xxx';


Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, REDUCTION
*, QLIK-TEST\qliksvc ,  1
];
Section Application;


REDUCTION:
LOAD * inline [
REDUCTION , Connecting_Column
1 , 001
1 , 003
];


[Random_Data]:
SELECT '001'  as Connecting_Column
      ,'piko' as Shown_Data
union
SELECT '002' as Connecting_Column
      ,'miko' as Shown_Data     
union
SELECT '003' as Connecting_Column
      ,'zingo' as Shown_Data    
;

Move limitation to Database

LIB CONNECT TO 'Microsoft_SQL_Server_192.xxx.xxx.xxx';

And change select to database
Section Access;
[Security]:
LOAD ACCESS, USERID, REDUCTION;
SELECT [ACCESS]
      ,upper([USERID]) as USERID
      ,[REDUCTION]
FROM [dbo].[A_SECURITY];
Section Application;


REDUCTION:
LOAD REDUCTION, Connecting_Column;
SELECT [REDUCTION]
      ,Connecting_Column
      from dbo.A_REDUCTION;


[Random_Data]:
SELECT '001'  as Connecting_Column
      ,'piko' as Shown_Data
union
SELECT '002'  as Connecting_Column
      ,'miko' as Shown_Data     
union
SELECT '003'   as Connecting_Column
      ,'zingo' as Shown_Data    
;

And if we change data to

we will get