Section Access Dynamically from excel

[Saif1]

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.

[hic · Accepted solution]

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);

[Saif1]

@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?

[hic]

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.

[Saif1]

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?

[Saif1]

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 ???