Using Section Access With LDAP
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
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.
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.
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 ];
- 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 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. 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.
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.
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.
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.
- 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 informations.
- 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.
- How to setup section access to avoid users to read all data. Reduction basic procedure.