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 ];
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.
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.
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
When Inline Data Wizard is showed, a table of users need to be filled. Values allowed to
ACCESS field are either
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
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).
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
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
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
Access Restriction Table Wizard window, click on the button
Basic NT Security. Notice two checkboxes automatically selected, both
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.