Integrating Single Sign On in Qlikview – Part I
This series of posts are intended to help you integrate Single Sign On using an ODBC connection to a database. This database will contain the users used for authorization in Qlikview.
I will not be covering the authentication side of Single Sign On as it’s not related to Qlikview. However, information on how to create webtickets (necessary to implement single sign on) can be found in Qlikview Community.
What is Single Sign-On (SSO)?
Single Sign On is a user authentication process that allows said user to access different systems. By implementing SSO the user only enters one user and password. This will then authenticate the user against different system within an organization.
Implementing Single Sign On in Qlikview
Single Sign On in Qlikview will only make sense and be possible when using Qlikview Server. Implementing Single Sign On will allow users within an organization to use their current organization user and password. What they use, is not of our concern. This can be facebook users, Window users, custom users, etc.
One thing that is important to understand is that Qlikview does not do authentication. An external SSO system must be in charge of authentication. After a user had been authenticated (who is this user?), Qlikview can handle authorization to the different files (what can this user see/have access to?)
1. Creating DB tables with all my users
The first step is to create tables with the users used by the authentication process. We must create 2 tables or views in any database (Oracle, mySQL, SQL Server, etc). These two tables must have a specific structure. Views are usually better for SSO as there’s no need of an external process to fill in the tables.
The first table will contain Entity data. An entity can be a user (person) or a group. Each user can belong to a group. You can give this table any name you want. In my example I will call it SSO_ENTITY. This table must have four fields (please do not change the field names).
ENTITYID: incremental number. Primary key.
ENTITY_NAME: user login name OR group name.
DESCR: user full name or group description.
EMAIL: user email. You can leave this empty for group entries.
The data in this view will look like the image below:
Here’s an example on how to create a view with these fields in a SQL:
–NOTE: The first 2 select create 2 groups: External and Internal. Internal users will be assigned to the Internal group a
— external users to the External group.
1 as entityid,
‘External’ as Entity_name,
‘External users’ as Descr,
” as Email
2 as entityid,
‘Internal’ as Entity_name,
‘Internal users’ as Descr,
” as Email
— Here, I bring my users from a table in my DB.
u.user_id as Entityid,
u.login_name as Entity_name,
c.FIRSTNAME || ‘ ‘ || c.LASTNAME as Descr,
c.EMAIL as Email
FROM db.myUsers uar
INNER JOIN db.users u ON uar.user_id = u.user_id
INNER JOIN db.UserContact c ON u.CONTACT_ID = c.CONTACT_ID
we are now ready to create the second view. I will call my group table SSO_GROUPS. This table must contain the following fields:
ID: incremental unique number (primary key)
MEMBERID: this will have the ENTRYID created in the first table SSO_ENTITY (foreign key)
GROUPID: group ID belonging to each member. In my example I created two groups. One for Internal and another one for External users (ENTRYID 1 and 2).
This view will look like the image below:
As you can see, I’m assigning groups to each memeber. Members 100 and 101 are external users (Group ID 2) and 102 and 103 are internal users (Group ID 1).
Here’s an example of the SQL used in the SSO_GROUPS view:
rownum AS id,
entity_id AS memberid,
1 AS groupid
WHERE entity_NAME = ‘External’
entity_id AS memberid,
2 AS groupid
WHERE entity_NAME = ‘Internal’
Once we have these two tables/views, we are ready to make the necessary changes in Qlikeview Server.