Добрый день, Qlik-мир! Сегодня расскажу, как разграничить доступ к данным на основе Section Access.

Разграничения данных по SACCESS – довольно простая и при этом полезная вещь, не требующая большого количества кода.  Фактически идёт простое добавление ещё одной таблицы, в которой прописываются ограничения.

Section Access: разграничиваем права доступа к данным

Разграничение доступа к данным происходит через добавление к стандартным полям в таблице Section Access тех полей, по которым происходит разграничение.

Безусловно, о Section Access можно прочитать подробно в справочном руководстве по QlikView в разделе 29. Но ведь всем известно, что большинство людей обращается к инструкции, когда уже ничего не помогает, поэтому в этом посте – кратко о том, как ограничить доступ пользователей к определенным данным.

Есть стандартные поля:

• ACCESS
• USERID
• PASSWORD
• SERIAL
• NTNAME
• NTDOMAINSID
• NTSID
• OMIT

Но для разграничения доступа мы возьмём только 2 из этих полей, а третье поле создадим собственное и по нему назначим разграничение прав.

Section Access: приступаем к работе

Давайте предположим, что у нас есть 6 типов регионов (GEO_TYPE), и к каждому из этих типов мы хотим предоставить доступ только одному человеку.

Это можно сделать двумя способами.

• Привязать ограничения к условию расчёта или отображения данных.
• Срезать данные на этапе загрузки приложения.

В первом случае, условие нужно будет вбивать всюду, где есть расчёт по нужным нам полям (в данном случае по типам регионов). Так как данные всё ещё будут присутствовать в приложении то, создавая свой объект, человек получит доступ к данным, которые не должны быть ему доступны.

Во втором случае, данные, которые не должны быть ему видны, в любом случае не будут для него доступны, поэтому рассмотрим подробнее именно такой вариант разграничения прав пользователей.

Итак, для начала сделаем нашу табличку. Её можно как загружать INLINE, прописывая всё в скрипте, так и загружая сам файл.

ACCESS	NTNAME	GEO_TYPE
ADMIN	DOMAINNAME1ADMIN1	*
ADMIN	DOMAINNAME2ADMIN2	100;200;300;400;500;600
USER	DOMAINNAME1USER1	100
USER	DOMAINNAME2USER2	100;200

Я занесу эту табличку в EXCEL-файл и загружу ее из EXCEL.

Section Access; SACCESS: LOAD Distinct        ACCESS,        NTNAME,        SubField(GEO_TYPE,’;’) AS GEO_TYPE FROM QVSA.xls (biff, embedded labels, table is User$); Section Application;

При помощи SubField(GEO_TYPE,’;’) мы разделяем одну ячейку, чтобы связать данные из столбца GEO_TYPE  с таким же столбцом в данных и потом сделать срез.

Если мы поставим «*» , это равносильно тому, что мы прописали все возможные значения.

НА ЗАМЕТКУ! Для пользователя от имени, которого будет происходить перезагрузка на сервере, желательно ставить «*» или все возможные значения. В ACCESS надо прописывать ADMIN, иначе он может либо сохранить уже обрезанный файл, либо и вовсе его не сохранить из-за недостаточности прав.

ВНИМАНИЕ: В таблице SACCESS названия столбцов обязательно должны быть прописаны заглавными буквами или цифрами. Значения в них тоже должны содержать в себе только заглавные буквы или цифры. Скрипт для SACCESS пишется исключительно в Hidden Script.

Section Access: настройка прав доступа

После добавления SACCESS и перезагрузки, мы увидим, что ничего не изменилось. Данные отображаются, как и прежде, даже если у нас указано, что нам можно видеть только 100 и 200 тип.

Чтобы всё работало надо включить в настройках обрезание данных по SACCESS. Заходим в Settings → Document Properties → Opening  и ставим галочки:

• Initial Data Reduction Based on Section Access
  • Strict Exclusion

Вторая галочка убирает все данные, которые не подходят вам по SACCESS в момент запуска приложения.

Так как мы сами указываем в файле кто админ, а кто пользователь, то нам надо заглянуть ещё на закладку Security и убрать все галочки, кроме последней, «Admin Override Security»:

После этого только у админа будет полный доступ к приложению, а у пользователей только будет возможность просмотра данных (нельзя будет посмотреть скрипт или сохранить приложение).

ВНИМАНИЕ: Если вы хотите протестировать на себе права USER, создайте лучше копию приложения, потому что вы не сможете изменить приложение, будучи с такими правами, и вернуть права ADMIN себе.

На этом сегодня все. До новых встреч здесь, на qlikdaily.ru

П.С. Не забывайте про комментарии. Спасибо, что прочитали  🙂

= #$(LastExecTime)#

AND ModificationTime = #$(LastExecTime)#

AND ModificationTime

I often get asked during training classes how we can hide a tab (sheet), object or data from an un-authorized person. Many of you know that Section Access provides row level filtering of data, but it also allows you to accomplish the other two tasks at the same time and in a cohesive way. This article explains how to expand the traditional use of Section Access to secure other objects in a QlikView dashboard based on security.

Note that following functionality does not pertain exclusively to Active Directory, so this can be modified to use the authentication type of your choice. This article is based on use cases tested in QlikView 11, although it should work in previous versions as well.

Consider the following scenario: You have a two sheets in your QlikView document that you want to secure based on a user’s membership in an Active Directory group or their login name. This example uses a spreadsheet or a database that stores authorization data, although an inline table could be used. The source data is be comprised of the following columns:  ACCESS, NTNAME, SAUSER, TABACCESS.SHEET1, TABACCESS.SHEET2 and FIELDACCESS.DIVISIONFILTER.

• ACCESS – Indicates the level of access you would like the user to have within the application when opened from QlikView designer. Valid values are ADMIN or USER. Both column name and value must be capitalized.
• NTNAME – The Active Directory group name or user name logging in. Column name must be capitalized and value must be in the form of DomainUsername. You can see an example by writing the expression =OSUSER() inside of a text box in the UI of a QlikView document. The value must be capitalized.
• SAUSER – Is the key we will filter our data with. The value must be capitalized.
• TABACCESS.SHEET1 – Integer value where 0 = False and 1 = True, this represents the value used to determine if a user is allowed access to a sheet or an object.
• TABACCESS.SHEET2 – Integer value where 0 = False and 1 = True, this represents the value used to determine if a user is allowed access to a sheet or an object.
• FIELDACCESS.DIVISIONFILTER – String value used for row level filtering of data.

The list below explains the naming conventions used for the TABACESS columns used for Section Access.

• The column names can be whatever makes sense in your organization and will ideally reflect other best practices or coding standards already in practice within your environment. If your organization currently does not have coding standards, use this as an opportunity to create them.
• The naming convention used here generally reflects a Namespace paradigm and is the closest approximation that is currently available in QlikView. Tt represents what is conveyed (TABACESS) and to what (SHEETX). This can be extended to OBJECTACCESS, VARIABLES etc.

The approach taken in this article employs Section Access based on an authenticated and authorized domain user and performs row level filtering of the data It also covers authorization to grant the user the correct level of accessibility within the document e.g.(ADMIN or USER).

The first step loads the Section Access table. The SAUSER field is used for row level filtering, and for all intents and purposes is simply the NTNAME column duplicated.

Section Access;
SATABLE:

LOAD ACCESS,

NTNAME,
SAUSER
FROM
AppConfig.xlsx
(ooxml, embedded labels, table is App1);

Section Application;

The second step is to create an entitlement table within the data model that contains the TABACCESS fields as well as other fields needed to perform authorization. Note the aliasing of the field Division, which controls which Divisions user has access to.

DocSecurity:

LOAD SAUSER,

TABACCESS.SHEET1,
TABACCESS.SHEET2,
FIELDACCESS.DIVISIONFILTER as Division
FROM
AppConfig.xlsx
(ooxml, embedded labels, table is SheetAuthorization);

Here is the entire script:

SET ThousandSep=’,’;
SET DecimalSep=’.’;
SET MoneyThousandSep=’,’;
SET MoneyDecimalSep=’.’;
SET MoneyFormat=’$#,##0.00;($#,##0.00)’;
SET TimeFormat=’h:mm:ss TT’;
SET DateFormat=’M/D/YYYY’;
SET TimestampFormat=’M/D/YYYY h:mm:ss TT’;
SET MonthNames=’Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec’;
SET DayNames=’Mon;Tue;Wed;Thu;Fri;Sat;Sun’;

star is *;

Section Access;
SATABLE:

LOAD ACCESS,

NTNAME,
SAUSER
FROM
AppConfig.xlsx
(ooxml, embedded labels, table is App1);

Section Application;

DocSecurity:

LOAD SAUSER,

TABACCESS.SHEET1,
TABACCESS.SHEET2,
FIELDACCESS.DIVISIONFILTER as Division
FROM
AppConfig.xlsx
(ooxml, embedded labels, table is App1);

FACTTABLE:
LOAD * INLINE ;

Here is a screenshot of the source Excel file.

Note the following upon reloading and reopening the QlikView document:

1. The data in document has been correctly reduced
2. Both sheets and all divisions are visible based on the Excel document.

The second scenario provides access to Sheet 1 and the North division.

Again a reload is performed and the document reopened. Below is the expression used for conditional sheet access:

TABACCESS.SHEET1 =1

Access to other sheets or objects follows this same methodology but is adjusted to use the appropriate field from the spreadsheet.

In summary, this article provides an example of how to extend the functionality of Section Access and data reduction to perform document object authorization. It also provides an example of how naming conventions can be used to architect a solution that is more robust and lowers maintenance costs for your QlikView documents.

In the next article I will expand on this topic to demonstrate how values can be aggregated while still providing an ‘Others’ total for dimension values to which you do not have detail access.

QlikView, QlikView Development

One of the things that is perhaps most key to anything involving data is security. It is fundamental in QlikView, and Section Access is one of the core components of that security model.

For the uninitiated; Section Access allows you to apply an ‘initial selection’ based on a user account that the user can then not clear or see beyond. This effectively locks your app so users can only see the data they are supposed to. Much has been written on Section Access and you can find lots of information on setting it up – but what I want to look at here is one of the pitfalls with Section Access.

The worst thing that can happen with Section Access is that you can lock yourself, and everybody else, out. Right out.

Section Access applies security at a row and then a document level. This, in a worst case scenario, could mean you don’t have the ability to get back in to your document to access your scripts and visualizations.

Lock outs can occur from a coding error when applying Section Access – or can catch you off guard by occurring due to a data load error. To explain further; if a user can not access any data they can not access the document. If the document contains no data then no one can access it. Most reload failures will cause the load to stop and the document will be left in its prior state (with the old data intact) – but if a table in the load is present but empty this may not happen.

Here are some tips to avoid lock out happening.

The first is simple: back up.  Always do this before any Section Access change – but it is good to back up regularly also. Saving a copy of your document without Section Access enabled is the sure-fire way of ensuring you are not locked out. Just make very sure this is secured by your network security and is not put anywhere near your Access Point.

Have a back door login. Typically Section Access will be on domain accounts, ideally at a specified domain SID. A change to user accounts or domain could lock all users out. By adding a user name and password based account also you can gain access should this happen. Obviously this username and password should be secured as per your info-sec policy on other admin passwords (often these are locked in safes until required).

Have some Section Access accounts hard coded in your QlikView load script. Generally accounts are loaded from a database table or flat file. If data from this source is cleared down then no accounts will be loaded – in-line loaded accounts will cause this to not spell disaster.

Test well before closing your document. This simple tip could save you a lot of messing around. When amending Section Access; make your changes, reload and save. The temptation now is to close QlikView and re-open your document to test – don’t do this! After saving your document open a new instance of QlikView and test the document by opening it in the new instance. If you are unable to open the document you still have it open in another window to change the security and try again.

Hopefully these simple tips will save you from pain, expense and tricky explanations to bosses.

As I said at the start there is much written on setting up Section Access – so I don’t want to repeat that here. Just to mention though that any changes to Section Access script or data will require a reload, and setting the options on the Opening tab of the document properties tab are critical.

Also, even with Section Access set up correctly on your document you don’t want someone taking it off site. Make sure you secure all files with Active Directory permissions on the server. This is particularly important with QVD files – that have no other security around them.

Finally, at risk of stating the obvious, security and access restrictions are perhaps the most important things to consider with your QlikView implementation. Make sure you give them the consideration they deserve and always test thoroughly.