Introduction

This section explain how to configure Section Access to reduce data based on authenticated user. You’ll able to hide some rows from user or group when some access to a document is done. Section Access is useful to protect document data from unauthorized access, as well to limit what data authorized users are able to see. If you wanna to test this resource, load the following sample data.

LOAD * INLINE [
Country, City, Company
Argentina, Buenos Aires, Los Espandrilos Fantasticos
Austria, Graz, Ski Store
Belgium, Bruxelles, De la Vita
Brazil, Campinas, Paintho da Gama
Brazil, Rio de Janeiro, Da Bikini Expertu
Brazil, São Paulo, Roba di Piel
Brazil, São Paulo, Tendha do Flamengo
Canada, Tsawassen, Big Foot Shoes
France, Nantes, Le Sais de Rión
France, Paris, Chateau de Ville
Germany, Stuttgart, Autokleider
Ireland, Cork, Boleros
Mexico, México D.F., Don Balón
Mexico, México D.F., Los Sombreros Gigantes
Portugal, Lisboa, El Traige do Benfica
Portugal, Lisboa, La Roba do Santho
Spain, Barcelona, El Chandal del Barca
Spain, Madrid, Las Corbatas
Spain, Sevilla, Los Trajes de Matador
Sweden, Stockholm, Stephanies ];

Hidden Script

When using Section Access is advisable use a Hidden Script. Follow this procedure to create a new one.

1. Create a new QlikView document. Open Script Editor using CTRL + E shortcut.

2. Paste previous data to be used as sample, below variable definitions.

3. From File menu click Create Hidden Script.

  Note:  To use a hidden script is necessary to create a password. 

4. Enter a password and confirm your choice.

Hidden scripts are executed before other fragments of code. Multiples tabs are allowed to be created for hidden scripts, but all of then are executed before regular tabs. Since the user credentials will be displayed in clear text, Section Access is a solution in order to ensures data security. All access control can be managed via text files, databases or INLINE commands. This article are describing how to use INLINE command just to explain Section Access procedures. Don’t forget Hidden Script password. Any modification in the future depends of this password.

Section Access

With hidden script tab active, from Insert menu click Section Accesss and then Inline command. First step is define what kind of authentication process will be done when a user try access the document (.QVW). For test purposes, select the button Basic User Access Table. Two check-boxes will be automatic selected. These represents a table of users to be defined in hidden script through USERID and PASSWORD fields.

When Inline Data Wizard is showed, a table of users need to be filled. Values allowed to ACCESS field are either ADMIN or USER, with all letters in upercase. ADMIN are users with all rights over all data and itens on QlikView document. In another hand, USERS are people with restricted data to analyse. The following table was defined with three users, with one of them as document administrator. When users table is finished click Ok button. At this moment two sections are included in Hidden Script, both Section Access and Section Application.

Up to this moment just permissions to open a file are defined. If a user is authenticated using one of these credentials all data are showed for him/her. To finish this step is necessary to execute a reload procedure and save the QlikView document. But, remember who are ADMIN and USER, and also passwords defined to them. These informations are necessary to test your access and data reduction.

Script in Hidden tab should be something like following code.

Section Access;
LOAD * INLINE [
   ACCESS, USERID, PASSWORD
   ADMIN, Frank, password
   USER, Jonh, johnpwd
   USER, Mary, marypwd
];
Section Application;

QlikView will prompt for a User ID and compare to the value in USERIDfield. This user ID is not the same as the Windows user ID. After that, password is required from user attempting access a document. PASSWORD field must contain an accepted password. QlikView will prompt for a Password and compare to the value in this field. This password is not the same as the Windows password. To avoid or restrict access to all rows loaded in-memory, it’s necessary to link these two sections (Access and Application) through a common field. Therefore, add a new one as shown in the following code fragment.

Section Access;
LOAD * INLINE [
   ACCESS, USERID, PASSWORD, LINK
   ADMIN, Frank, password, *
   USER, John, jonhpwd, 1
   USER, Mary, marypwd, 2
];

Section Application;
LOAD * INLINE [
   LINK, Country
      1, Spain
      1, Mexico
      2, France
      3, Brazil
      3, Austria ];

LINK field (fields name is your choice) connects both sections through values in columns with same name. Then, when John access the document only rows connecting these two sections are displayed for him. In Section Application a new INLINE table is defining what rows are available. John will see all data about Spain or Mexico, but not about other Countries.

  Note:  Every time a change is made a reload process should be executed.

Configure Document Properties

In order to work with reduction, I mean, each user access only specific rows based on Section Access, some properties need modification at document level. After reload your script at last time, return to QlikView Desktop main interface to access document properties from Settings menu (CTRL + ALT + Dshortcut).

In Opening tab is necessary to select Initial Data Reduction Based on Section Access option. This check in QlikView will allow only data assigned to user to be accessed after document is opened. As a Section Application can include multiples table associated with Section Access, maybe a same user can be associated a excluded range of rows. Therefore, access to the document will be denied whenever the field values in the section access reduction fields lack matches in their corresponding section application field. Having Strict Exclusion option unselected will mean that if QlikView can’t find a match to reduce data, all data in the document will be visible on USER level. However, ADMIN will always be able to see all data, regardless of the reduction. Best practice is to use strict exclusion to avoid unwanted access to the QlikView document.

It’s strongly recommended check Prohibit Binary Load to avoid possible to load data from the document’s qvw file via a binary statement in another QlikView document. It’s highly recommended to use this option to increase security, but, in fact, isn’t necessary to work with reduction at document level.

Testing Section Access

To test Section Access functionality, add some lists to main interface like picture at side. At the first moment all data are available because no user was used to open a document. Lists will be filled with correct data based on user login when you close QlikView and open the document again.

Close QlikView application and start it again. Select your document to open and wait for login window. Fill it with a correct USERID, like John. If a USERID is valid, QlikView ask for a password. Fill password box and click Ok.

Using John as login only data between Section Access and Section Application that match are available. John is able to access all data that correspond to his permissions. Moreover, John is able to access a lot of options at document level, including open Script Editor window. To deny access to properties at document level it’s necessary change some permissions using a administrator login. So, close QlikView application again and follow the procedures in the next section.

Properties Permissions at Document Level

To avoid normal user (not administrator) access some properties, is necessary start QlikView Desktop again using USERID as ADMIN. In this article Frank is the Administrator. Using Frank as user is possible restrict permissions at document level for regular users (not ADM). From Settings menu click Document Properties (CTRL + ALT + D shortcut). Then, click Security tab to select what users will be able to do at document level. For example, to deny a user save the document uncheck Save Document option. To avoid users to access this window, uncheck Access Document Properties. It’s also possible to avoid user to edit scripts unchecking Edit Script option. For a complete list of options use help instructions in QlikView Desktop.

Always you change some properties or when updating script is necessary to close QlikView Desktop application to alternate between users with different privileges. It’s because QlikView will maintain your credentials up to close Desktop application. If you would like stop QlikView to maintain your current section, change the option Remember Login Credentials Until QlikView Exits at User Preferences.

This section and all sections before is assuming USERID and PASSWORD defined at script level. However, it’s possible to delegate authentication procedures to operation system. In this case is not necessary save password informations at script level. To delegate authentication to Windows, see next section.

User Section Using Operation System

In this section you will able to configure QlikView Desktop to trust in Windows for authentication procedures. Only users authenticated at operating system level will be able to open the document and see data according to her/his permissions. Using Frank as user with administrator privileges, open the previous document. Press CTRL + E to open Script Editor.

Using password defined to the Hidden Script, from File menu click Edit Hidden Script. Enter the password you defined when Hidden Script was created. After that, delete or comment previous section access and first row for section application, as you can see below.

/* -- Comment Start
Section Access;
LOAD * INLINE
[
    ACCESS, USERID, PASSWORD, LINK
    ADMIN, Frank, password, *
    USER, John, johnpwd, 1
    USER, Mary, marypwd, 2
];
Section Application;
*/ -- Comment Finish

Put cursor below this block of committed rows, but before LOAD INLINE of previous section application. Again, from Insert menu, click Section Access and then Inline. From Access Restriction Table Wizard window, click on the button Basic NT Security. Notice two checkboxes automatically selected, both NTNAME and NTDOMAINSID. Follow the same procedure to define users able to open this document. But, this time, use logins from Windows operation system (or DOMAIN).

When finished, a code similar at below will be included in hidden script. When a user to try open the document, QlikView Section Access will get a user from Windows operation system and compare with INLINE table. To ensure that only users from your domain are able to open the document, it’s necessary to fill NTDOMAINSID column in INLINE table. NTDOMAINSID is a unique identification for your domain or workstation. To get this value, from Insert menu click on Domain SID. Ensure that cursor is located after comma for first user. Copy and paste Domain SID for each user.

Section Access;
LOAD * INLINE [
    ACCESS, NTNAME, NTDOMAINSID, LINK
    ADMIN, franco.galati, Domain SID Here, *
    USER, geilson.junior, Domain SID Here, 1
    USER, nilton.barcelos, Domain SID Here, 2
];

Section Application;
LOAD * INLINE [
   LINK, Country
      1, Spain
      1, Mexico
      2, France
      3, Brazil
      3, Austria ];

  Note:  Click on realod button to changes take effect.

Close QlikView Desktop application after save the document. Using a login from Windows operation system, open the document. In this case, franco.galati was a user authenticated from Windows. For that, only rows filled in INLINE sections are available, what includes values 1 and 2 for LINK column. It’s because franco.galati is a administrator and a asterisk was used as LINK. Therefore only rows in INLINE section access are available. Try access the document using a different login at Windows Operating System to verify reduction working.

Download

• Qknow Section Access.QVW