Using Section Access With LDAP

    Introduction

    In post about Section Access is possible see how to create a new hidden script and define a section table to specify who will access the dashboard and what data will be available to him or her. But, every single time someone new need access to QVW document, is necessary open the script to change section table. For some companies this approach can be not the best choice. If you had read previous post about section access, you know that table should need specify every single user that need access to data. Of course, if company has a LDAP authentication process, for example, maybe the best way to specify privilege to open the document (QVW) is adding users to LDAP groups. Remember! Section Access has two objectives. First one is guarantee of only specified users will be able to open the document. Second one is specify what data an authorized user is able to interact. This is a reduction term. Look at the following basic table where some users was specified to access some document.

    Section Access;

    LOAD * INLINE [

      ACCESS, USERID, PASSWORD

      ADMIN, Franco, senha

      USER, Geilson, senha1

      USER, Nilton, senha2

      USER, Bruno, senha3

    ];

    Section Application;

    On this basic configuration, only users from this table will be able to open the dashboard from QlikView Desktop or accessing it through the access point. After the click over the link to access the document, both user and password will be asked by QlikView to let user open the QVW file. Another way to specify access to users is filling the table with users from Windows list. The following sample is allowing a number of users from operating system to access the document.

    Section Access;

    LOAD * INLINE [

      ACCESS, NTNAME, NTDOMAINSID

      ADMIN, Franco.Galati, S-1-5-21-3468352827-1570571401-3998840258

      USER, Nilton.Barcelos, S-1-5-21-3468352827-1570571401-3998840258

    ];

    For both cases, any user that needs to be added or dropped from the list, it's necessary edit the QVW file, to open the script, got to in hidden script and change the table. So, add users from Windows system don't mean let the document unchanged. This post has another approach, where groups from LDAP or Active Directory can be created at organization level and users that need access data with reduction (section access) just will be added to service directory groups. No change is needed at script level. To let a user access the dashboard just security network team add user to specific group. Of course, to deny access just remove the user from the group. Different groups can have many purposes, as a manager group to let users added to it access all data from a specific dashboard. In the other hand, regional coordinators can be added to groups where some data will be available.

    Connecting To Active Directory

    AD can be read as any data source to QlikView, just specifying the correct way. First of all, it's necessary an user account that can read data from Active Directory. Basically, any user that is able to login in AD is also able to read data from it. That means, for test purposes, you can use your network account. To test this behavior, create a new qlikview Document and save it. After that, open the script editor (CTRL + E). For this test no hidden script will be used, but for final version is highly recommended use hidden scripts to avoid access to security area in document. Ok, from script editor follow the procedures below:

    1. From connection options select OLEDB. Then, click on Connect button.

    DataSource1.PNG

    2. When the connection window is open, select OLE DB Provider for Microsoft Directory Services.

    3. In data source field, fill data about active directory using the follow format: LDAP://domain.com.

    DataSource2.PNG

    4. Fill both username and password for user that will search data from Active Directory.

    5. Click on Test Connection button and wait the connection process.

    6. If any warning message is shown just ignore it. If error messages are shown, try correct the previous fields.

    Results should be something like the following connection string. Using this instruction (command) QlikView will connect to Active Directory database and will let you get data as groups or users from that. Save the job and run the script just to test the connection procedures. Probably no error message will be shown if all fields are filled correctly.

    OLEDB CONNECT TO [Provider=ADsDSOObject;

      User ID=qknow\franco.galati;

      Encrypt Password=False;

      Data Source=LDAP://qknow.com.br;

      Mode=Read;

      Bind Flags=0;

      ADSI Flag=-2147483648] (XPassword is IQONdCNOULZEHacGGLIaI);

    For test purposes, this post creates three groups, as defined below. These groups were defined at Active Directory level. Each one is designed to users be added with specific rights.

    • QlikView Dashboard Admin: Accesses the dashboards with administrative rights.
    • America Users: Access the dashboard with reduction in place, where only American countries are available.
    • Europe Users: Access all data from Europe countries with no administrative rights.

    With these groups, to let users to access the document (QVW), security network administrators need add user to the specific group as defined by the company. After, in this post, both users and groups will be read from AD and a section access table will be created using these information. Select some groups from your domain to test this behavior. Now, it's time to read all data from AD using the following code. Therefore, open the script editor and paste the following fragment of script after CONNECT command.

    FOR Each arg in 'QlikView Dashboard Admin','America Users','Europe Users'

    Groups:

    LOAD distinguishedName As GroupKey, cn As Grupo;

    SQL select distinguishedName, cn FROM 'LDAP://DC=qknow,DC=com, DC=br' WHERE objectCategory='group' AND cn ='$(arg)';

    LET vRows = NoOfRows('Groups');

    LET vGK = FieldValue('LastGroup', vRows);

    Users:

    LOAD '$(vGK)' As GroupKey, Upper(sAMAccountName) As LanID, cn As Name;

    SQL SELECT sAMAccountName, cn FROM 'LDAP://DC=qknow,DC=com, DC=br' WHERE memberOf='$(vGK)';

    Next arg;

    This code uses a FOR...EACH as a circle starting with arg variable. Pay attention that arg has three values, one for each group before defined. So, for each value all script instructions will be executed. The first group is loaded to Groups table in memory. After, users from this specific group are loaded to Users table. At the end QlikView will load in memory two tables, one for groups and another one for users. The link between these tables are defined by a common field.

    Remember! Use hidden script when implementing the real reduction using this procedure. 

    With the previous code inserted at script level, reload the document to execute all instructions. After that, go to main page and add some lists to verify if your groups and users were filled. In the case of this post, three users were loaded and also three groups. Each user, by design of this sample, was added to a specific group.

    SectionAccessWithAd1.PNG

    To finish this section, as a best practice is a good idea remove any unnecessary resource, as variables. To do that, just assign the null value to previous variables, avoiding let them in memory. Of course this is not a procedure to use section access, but is a good idea remove any unnecessary resources.

    LET arg  = Null();

    LET vRows = Null();

    LET vGK  = Null();

     

     

     

     

    Section Access Setup

    If you're not familiarized with section access and reduce procedures, take a look at this post before continue. This because some procedures that need to be setup here will not be detailed. Reduction means that user will read only a partial data where someone give to her or his rights to read data as were defined. To reduction run proper is necessary link two sections. First is section access. Second is section application. Therefore, a common field need to be defined in both sections. So, this portion of code is not necessary to setup reduction, but will be used on this post to show you how to link both tables. So, let define who will be able to control the document as ADMIN.

    Permissions:

    Mapping LOAD * INLINE [

    Group, Permission

    QlikView Dashboard Admin, ADMIN

    Europe Users, USER

    America Users, USER];

    This map table is used just to define in the following LOAD who will be able to use the document as USER or ADMIN. Let's go to section access:

    Section Access;

    LOAD ApplyMap('Permissions', Grupo) As ACCESS, 'S-1-5-21-2130454873-934382291-718547170' As NTDOMAINSID, GroupKey, Grupo As GROUPS Resident Groups;

    Left Join
    LOAD
    LanID As NTNAME, GroupKey Resident Users;

    Ok, now you have a section access defined and we need link this table with application data. For test, this post is using the field GROUP as a link between these two sections. In application level, countries were defined to be linked to groups. So, only specific groups are able to read data from its relation countries. When an user access the document, she or he will fill login information as user and password. Or, this user can be from Windows login where single sign on is in place. In fact, both kind of users can be defined at section access table. This means that some users can use single sign on while other users will need fill login information.

    Section Application;

    LOAD * INLINE [

    GROUPS, Countries

    Europe Users, Spain

    America Users, Mexico

    Europe Users, France

    America Users, Brazil

    Europe Users, Austria ];

    Remember!

    • Section access will avoid anyone who isn't defined at domain group, access the document.
    • Reduction is in place when both section access and section application share a common field.
    • When working in a real world, put this code at hidden script with a password to access it.
    • Before test your job, create a backup file. Some mistakes can avoid you access this document definitely.
    • Reduction means users accessing a partial data only. Not all data will be available.
    • For reduction work, some properties at document level need be setup.

    If any of these topics are new for you, please, read the original section access post.

     

    Testing Section Access

    For test purposes, load some data as your datasource. In the following INLINE command you will load user data as sample. When a specific user from a domain access the document only a partial data are available, based on its credentials. This next load has a common field with application session: Countries.

    LOAD * INLINE [

    Countries, 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 ];

    To set the document to work with reduction is necessary change some properties at document level. To do that, outside of script editor (back to main window), goto to document properties pressing CTRL + ALT + D, or from the Settings menu click Document Properties. Click Security tab to select what users will be able to do at document level. For example, to deny an user save the document uncheck Save Documentoption. 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. Also, 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.

    After run the script at last time and save the document, it's time to test the procedure. For that, exit QlikView application to avoid use any credentials in cache. Start QlikView Desktop and open your project. In the case of this post, user franco.galati is logged in Windows from domain. So, no credentials is requested by document. Result can be seen below.

    This user is member of QlikView Dashboard Admin group. Therefore, all data are available for him.

    SectionAccessWithAd2.PNG

    Now, for testing this behavior, user franco.galati is dropped from previous group and add to Europe Users group. This change is done in Active Directory, not in QMC or QVW. When reload process is done a new table for section access will update all memberships of groups. Now, franco.galati is not an administrator anymore. Take a look on window configuration from domain.

    ActiveDirectory1.PNG

    ActiveDirectory2.PNG

    Exiting Qlikview application and starting it again will be possible to see all data associated with franco.galati, but only for europe countries. This is the natural behavior because from section access franco.galati user got a new group. And, this new one is designed from application section to read only data from europe, associating Country field from both section and application tables.

    SectionAccessWithAd3.PNG

    To finish a real job, just schedule QVW file to reload how many times your business need to update users in groups. That's because any modification at AD need to reload dashboard to update section access table.

    Remember:

    • Always user are added or dropped from groups, QVW needs execute the reload.
    • Scheduled reload procedures at Server level can be enough to update these information.
    • No modification is needed to allow user to access data from QVW.
    • Only in Active Directory changes need to be done.
    • To allow user to access data or avoid user to read some rows, just add user to specific group.

     

     

    Need More?

    www.qknow.com.br