QlikView Security

Posted by matthew on 15/06/2010 under How To | 5 Comments to Read

Last week I encountered a few problems with a QlikView app where I had to implement a section access component. The section access needed to fetch the users and roles from an Oracle Database.

Reading through the QlikView Docs I found the chapter about security, explaining an example how to implement it but unfortunately the QlikView Docs aren’t so clear and I had to search through the QlikView forum to find people with similar issues.

Issues I encountered :

1. Fetching users from the database within the section access does not work. After a succesfull reload I closed down the QlikView App and every user I tried to login with was denied access. So the QlikView apps did become useless, lucky me I made a backup.

2. Adding an inline table with admin users and concatenating a load statement with the users from the database did not work either (at least not the way I always write a concatenate statement). As mentioned in the the QlikView Docs all column names and values need to be in upper case if not they will be converted. But as experienced this is not the case, you need to explicitly make every column upper case with the function Upper().

Why I first insert an inline table is mainly because of fixed users that never change and secondly because of this post of William (link to post ).

Finally this is the code that works:

DbUsers_login:
LOAD
ID
,	LOGIN
,	TYPE
,	NAME;
SQL SELECT *
FROM "SEC"."USERS_LOGIN";

Section Access;
Star is *;

Security:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, ROLE
ADMIN, ADMIN, 123, *
];

CONCATENATE (Security)
LOAD
'ADMIN' as ACCESS,
upper(LOGIN) as USERID,
'123' as PASSWORD,
'*' as ROLE
Resident DbUsers_login;

Section Application;

drop table DbUsers_login;