Integrating Single Sign On in Qlikview – Part I

Scenario

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:

SSO - Entity table

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.
SELECT
1 as entityid,
‘External’ as Entity_name,
‘External users’ as Descr,
as Email
FROM DUAL
UNION
SELECT
2 as entityid,
‘Internal’ as Entity_name,
‘Internal users’ as Descr,
as Email
FROM DUAL
UNION
— Here, I bring my users from a table in my DB.

SELECT
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:

SSO - Group table

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:

SELECT
rownum AS id,
memberid,
groupid
FROM (
SELECT
entity_id AS memberid,
1 AS groupid
FROM db.SSO_ENTITY
WHERE entity_NAME = ‘External’
UNION
SELECT
entity_id AS memberid,
2 AS groupid
FROM db.SSO_ENTITY
WHERE entity_NAME = ‘Internal’
) subquery
;

Once we have these two tables/views, we are ready to make the necessary changes in Qlikeview Server.

Keep reading:

Integrating Single Sign On in Qlikview – Part II

Integrating Single Sign On in Qlikview – Part III

Advertisements

Tags: , , , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: