QlikView Section Access from Database with SQL request

[JenniferE]

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.

[PadmaPriya]

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

[JenniferE]

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

[Andre_Sostizzo · Accepted solution]

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/man...