topic Re: Section Access Dynamically from excel in Management & Governance

Section Access Dynamically from excel

Saif1 — Wed, 29 Jan 2025 16:31:11 GMT

Hello Qlikers,

Can Row level security be achived Dynamically ?

as for the user to change it from excel , not hard coded in qlik sense using the

ACCESS and USERID table.

at least to make it in excel so the user can change it in excel not in Qlik sense backend.

thank you in advance.

Re: Section Access Dynamically from excel

hic — Tue, 06 Dec 2022 08:42:06 GMT

Of course you can. And you should. The script is the wrong place for an authorization table. Excel or a real database is better.

But you still need ACCESS and USERID, but now in the Excel table. E.g.

LOAD
ACCESS,
USERID,
<otherfields>
FROM [....xlsx] (ooxml, embedded labels, table is Sheet1);

Re: Section Access Dynamically from excel

Saif1 — Wed, 07 Dec 2022 10:28:33 GMT

@hic Hello Hic ,

can you help me with something,

i tried to put the section access table in excel and read from the excel

but when i try with a user A it gives me ACCESS DENIED when trying in excel, but when doing it in the backend (hard code it in Qlik script) it works, so what am i doing wrong?

ACCESS	USERID	DEPT_ID
ADMIN	domain\SCHEDULAR_SA	*
ADMIN	domain\QLIK ADMIN	*
USER	domain\A	*
USER	domain\B	123

this is a demo table in excel ( i doubled checked everything is in UPPER case )

the script :

ACCESS:

load

UPPER("ACCESS") as ACCESS,

UPPER("USERID") as USERID,

UPPER(DEPT_ID) as DEPT_ID

from excel etc etc .

so what is wrong ? , why it gives access denied in when importing from excel, and when from backend (script) it works?

Re: Section Access Dynamically from excel

hic — Wed, 07 Dec 2022 13:09:37 GMT

Yes, you cannot use wildcards like that. The * will only refer to other DEPT_ID in the same table. And not to departments not listed in Section Access.

You could try something along the following:

AuthorizationTable:
Load distinct 'ADMIN' as ACCESS, 'domain\DUMMY' as USERID, UPPER(DEPT_ID) as DEPT_ID Resident Departments;
Concatenate
Load UPPER("ACCESS") as ACCESS, UPPER("USERID") as USERID, UPPER(DEPT_ID) as DEPT_ID From Excel ... ;

or use '<ALL>' instead of '*' in the Excel table, and then the following script:

AllDepartments:
Load Concat(DEPT_ID,'|') as AllDepartments Resident Departments;
Let vAllDepartments = Peek(AllDepartments,-1,AllDepartments);

AuthorizationTable:
Load UPPER("ACCESS") as ACCESS, UPPER("USERID") as USERID, UPPER(SubField(Replace(DEPT_ID,'<ALL>','$(vAllDepartments)'),'|')) as DEPT_ID From Excel ... ;

Then you will get all DEPT_IDs into the Authorization table.

Re: Section Access Dynamically from excel

Saif1 — Thu, 08 Dec 2022 05:13:08 GMT

thank you so much, but my problem is not that all departments in the table,

my problem is that, am using the same table, in QLIK load script it works ( Inline writing) ,

but when i put it in excel , it gives the users (ACCESS DENIED)

I know how to put the rest of the departments in the table, why it is not giving access through excel tho?

Re: Section Access Dynamically from excel

Saif1 — Thu, 08 Dec 2022 11:49:59 GMT

i am trying several things,

what worked that when the ACCESS field on user A is USER , it gets ACCESS DENIED , but when changing to ADMIN it works, why tho ?

the logic is correct, the link table is correct, the data is correct , but am still getting ACCESS DENIED on the users

THIS DID NOT WORK :

ACCESS , USERID, DEPARTMENT, CENTERID

ADMIN, DOMAIN\QLIKADMIN,<ALL>,<ALL>

USER, DOMAIN\A,866,<ALL>

we see in the table above that the ACCESS for user A was "USER"

when changing to ADMIN , it worked, but i dont think this is the right method.

THIS WORKED but not a fix :

ACCESS , USERID, DEPARTMENT, CENTERID

ADMIN, DOMAIN\QLIKADMIN,<ALL>,<ALL>

ADMIN, DOMAIN\A,866,<ALL>

Any Help ???