section access using database data

[#1]

Hello,

?Has anyone successfully used section access with a sql select instead of the typical inline loads?

section access;

load * inline

[USERID, PASSWORD, ACCESS, PARTICIPANT_ID

admin0, pw0, ADMIN,

user1, pw1, USER, 1

user2, pw2, USER, 2

];

/*

SQL

SELECT

u."login" AS "USERID"

, u.passwd AS "PASSWORD"

, CASE WHEN pu.participant_id = 0 THEN 'ADMIN' ELSE 'USER' END AS "ACCESS"

, CASE WHEN pu.participant_id = 0 THEN NULL ELSE pu.participant_id END AS "PARTICIPANT_ID"

FROM "security".users u

JOIN "security".participant_users pu ON pu.user_id = u.id

;

*/

section application;

security_pool: [repeat the sql sqlect]

...

As it stands, when I stay with the usual inline load, all works well. As soon as I switch to the sql select, you can't even login and must revert back to a previous version of the QV to fix, which is highly frustrating.

[Anonymous · Accepted solution]

I'd say that using database data for Section Access is more typical than load inline. the only hint is that you need to use function upper() for all fields:
LOAD
upper(login) as USERID,
upper(passwd) as PASSWORD,
...;
SQL SELECT
u."login",
u.passwd,
...
FROM...

[#3]

Yep, I did that and now I'm in and can see my data, and more to the point, ONLY the data that should be seen by that login.

Thank you Thank you Thank you

Altho... I noted that I must refresh to see any data as "ADMIN1", but didn't need to for the other users.

?Why would that be?

[Anonymous]

I'm sure that it happens because the user ADMIN1 doesn't have associated data. And, you're probably using "strict exclusion option. Try to remove "strict exclusion", or make association to all data for ADMIN1.
As for the full access to all data after reload - this is the way it has been always working for any user. If this is a problem - do not allow users to reload.