topic Re: QlikView Section Access from Database with SQL request in QlikView

QlikView Section Access from Database with SQL request

JenniferE — Fri, 18 Jun 2021 08:57:51 GMT

I am new to QlikView and I have a problem to make the section access for my board. I saw everywhere that section access uses an LOAD [col_name1, col_name2] INLINE syntax, with generally the columns ACCESS, USERID and PASSWORD stocked in an external file, like an excel file.

The thing is, in my project the access to certain datas of the board has to be authorized based on the data in the table profile of my database. That profile table contains an ID, a name, a firstname and a personnal_number. And so, the people who have a personnal_number registered can have access to the whole board, while the people who don't have a personnal_number should only see some data (which is treatment of the column event_nature of the table event).

So, when opening the board, the user should be asked his name and firstname, and then QlikView will look into the database to see if he has a personnal_number registered or not.

My question is: I have absolutely zero idea how to do this.

I started this little code but it's obviously uncomplete, and probably false too...

Section Access; Load Upper(name) as NAME, Upper(firstname) as FIRSTNAME, Upper(personnal_number) as PERSONNAL_NUMBER, Upper(event_nature) as NATURE; SQL Select name as NAME, firstname as FIRSTNAME, personnal_number as PERSONNAL_NUMBER FROM `my_db`.`profile`; SQL Select event_nature as NATURE FROM `my_db`.`event`; Section Application;

Can anyone help me please? First to correct this code and second to tell me how to tell QlikView that the access is based on the personnal_number and the event_nature?

Thank you so much for your time and help.

Re: QlikView Section Access from Database with SQL request

PadmaPriya — Fri, 18 Jun 2021 20:28:23 GMT

Hello @JenniferE

Please refer to below thread:

https://community.qlik.com/t5/QlikView-App-Dev/Section-Access-Loading-from-SQL/td-p/258137

Thanks,

Padma Priya

Qlik Support

Re: QlikView Section Access from Database with SQL request

JenniferE — Mon, 21 Jun 2021 13:25:34 GMT

Hello, and thank you @PadmaPriya for your answer.
I managed to get access from y database. Now when my user tries to connect to the board:

if he is an ADMIN, he enters his USERID which is his personnal_number and I added a column password, so he also has to enter his password.
if he is an USER, he should leave the USERID prompt window empty and only enter his password.

But I still have a problem:
When I try to connect with my ADMIN account, who has a * for the data (and should see all the data) I can only see some of the data (the one already present in my table for other users).
So my question is: how can I say that I want my admin to see absolutely every value available for this column?

Here is a screenshot of my table:

And my code for the section access is:

Section Access; Load Upper(ACCESS) as ACCESS, Upper(USERID) as USERID, Upper(PASSWORD) as PASSWORD, Upper(NATURE) as NATURE; SQL Select access_level as ACCESS, matricule as USERID, user_pwd as PASSWORD, nature as NATURE FROM `bdd`.`profil`; Section Application; SQL Select access_level, matricule, user_pwd, nature FROM `bdd`.`profil`;

Re: QlikView Section Access from Database with SQL request

Andre_Sostizzo — Wed, 25 Aug 2021 13:52:19 GMT

Hello @JenniferE,

The reduction fields in the Section Access table must have an empty string to match everything else not included in the table.

This is also mentioned in the article below in section 1.:

https://community.qlik.com/t5/Knowledge/Section-Access-Best-Practice-Guidance/ta-p/1712333

Also, in the first example under link below this is somewhat hinted:

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Security/manage-security-with-section-access.htm#anchor-1