topic Re: Section Access using Windows Authentication in QlikView

Section Access using Windows Authentication

montubhardwaj — Wed, 29 Jun 2011 09:20:26 GMT

Hello all,

This question/ doubt is related to Section Access. I have an application which is placed in shared path. I am not publishing this application to users so user s has to access this application from the shared path only. All users have QV software installed in their machines. Now I want to restrict the access from all the users. Users should be able to open this application only if they provide their Windows username and password i.e. the Credentials they use to Logon their machines over the company network.

Once they login, the data should be reduced based on the users i.e. employees should not be able to see the data which manager is seeing. Please give me heads up on this to proceed.

Regards,

Sharma

Section Access using Windows Authentication

kaushiknsolanki — Wed, 29 Jun 2011 09:26:48 GMT

Hi,

For this purpose you need to do 2 things.

1. for allowing authorised person to open the file.

For this you can click on the file and go to properties of file. Here you can set the security and using this you can set the user who can access file.

2. After opening the file he should see only his data, for which he is authorised.

To achieve this you need to use the section access in your qlikview application.

For more on section access have a look at the below link.

http://community.qlik.com/docs/DOC-1853

Regards,

Kaushik Solanki

Re: Section Access using Windows Authentication

Miguel_Angel_Baeyens — Wed, 29 Jun 2011 09:43:06 GMT

Hello Sharma,

Use the NTNAME field in section access, and in the Document Properties in the Settings menu, Opening tab, check "Initial Data Reduction based on Section Access". Back your file up just in case.

STAR IS *;

SECTION ACCESS;

LOAD * INLINE [

ACCESS, NTNAME, EMPCODE

ADMIN, YOURDOMAIN\ADMIN, *

USER, YOURDOMAIN\USER1, USER1

USER, YOURDOMAIN\USER2, USER2

USER, YOURDOMAIN\MANAGER1, USER1

USER, YOURDOMAIN\MANAGER1, USER2

USER, YOURDOMAIN\MANAGER1, USER3

];

SECTION APPLICATION;

Employees:

LOAD EMPCODE, // must be uppercase and so their values

....

In the example above there are four users to access the document, Admin, User1, User2 and Manager1. Admin will allowed to see all EMPCODE values listed in section access (in this case, USER1, USER2 and USER3), User1 will only see User1 related information, likewise User2, and Manager1 will see all records containing "USER1", "USER2" and "USER3". User3, though, is not allowed to access the document because he has no matching record.

Notes:

All fields and values in section access tabel must be uppercase
The NTNAME field must be in the form DOMAIN\USERNAME as returned by the function =OSUser() in QlikView
As in the example above, the field used to reduce data (in this case EMPCODE) must exist in the data model in the same case (name uppercase, values uppercase).
Only one value is allowed per line, so as a general rule, if Manager1 is allowed to see 100 employees you have to code 100 lines for Manager1 with each possible value of EMPCODE.
The way QlikView will reduce data is exactly the same as if you select one value in EMPCODE, go to File, Reduce Data, Keep Possible Values. That's the information each user will see.
The "*" in the ADMIN line doesn't mean "all values in field", but "all values listed in the section access".

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Section Access using Windows Authentication

montubhardwaj — Wed, 29 Jun 2011 11:14:07 GMT

@ Kaushik: Thnsk for sharing the document. It was helpful.

@Miguel: First of all, thanks for sharing and writing so much.... Helped a way lot. I have a question on this:

For Data Reduction on some field, I need to have that field in Section Access and pull the same from DB or other sources in Section Application. Right ?

Thanks,

Sharma

Section Access using Windows Authentication

kaushiknsolanki — Wed, 29 Jun 2011 11:24:01 GMT

HI,

Yes your are right.

Regards,

Kaushik Solanki

Re: Section Access using Windows Authentication

Miguel_Angel_Baeyens — Wed, 29 Jun 2011 11:28:34 GMT

Hi,

That's correct. Consider this very dummy example

SECTION ACCESS;
LOAD * INLINE [
ACCESS, NTNAME, COUNTRY
ADMIN, DOMAIN\ADMIN, US
ADMIN, DOMAIN\ADMIN, ES
USER, DOMAIN\PETER, ES
];
SECTION APPLICATION;
Customers:
LOAD CustID,
     Name,
     Address,
     Upper(Country) AS COUNTRY; // possible values are ES, US (note forced uppercase)
SQL SELECT * 
FROM Database.CustomersTable;

When you reload the document, all records will be retrieved from the database and stored into the QVW file (provided the NT User has permissions enough to get all data, otherwise you will likely get an error in your reload log).

But when user DOMAIN\PETER logs in, QlikView reduces (removes) all records in table Customers where value for COUNTRY is not "ES". So if Peter is allowed to save the document, the next time the ADMIN opens it, the QVW file will only have COUNTRY='ES' records, and nothing else.

In a server environment the QVW cannot be saved by users, so no data is reduced from the actual file. Instead, QlikView loads into memory only the records resulting of that reduction, so the user is actually seeing only the reduced set of records. Thus, when the Admin logs in after Peter, he will see all records, because there's no reduction for him.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Section Access using Windows Authentication

montubhardwaj — Wed, 29 Jun 2011 12:30:26 GMT

Thank You guys... that was indeed helpful and informative.

Section Access using Windows Authentication

christian77 — Wed, 29 Jun 2011 13:55:39 GMT

Hi:

If I enter the QV file from a .NET application that has already ask me for USERID and PASSWORD:

¿is there a way to avoid entering again USERID and PASSWORD when the file opens, and at the same time keep the section access?

Re: Section Access using Windows Authentication

Miguel_Angel_Baeyens — Wed, 29 Jun 2011 14:47:19 GMT

Hi,

It is possible. You can use the NTNAME field instead of USERID and PASSWORD in section access as shown above, and on the other hand, make your browser (it only works with IE as far as I know) pass the same credentials the user has logged on Windows (Control Panel, Internet Options, Security, Custom Level, User Authentication, Automatic logon with current username and password).

Although the user will not be prompted, the result is the same.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Section Access using Windows Authentication

christian77 — Wed, 29 Jun 2011 14:53:45 GMT

Tanks a lot Miguel.

It's gonna help me for sure.

Re: Section Access using Windows Authentication

krishnaroopa — Mon, 12 Dec 2011 06:16:21 GMT

I have similar need. Defined the section access as follows:

Section ACCESS;

LOAD * INLINE [

ACCESS, NTUSER, CLIENTID

ADMIN, CLOUD02\ADMINISTRATOR, *

USER, CLOUD02\QLIKVIEW, 20

USER, CLOUD02\TR_MGM, 14

USER, CLOUD02\KM_MGM, 20

];

First administrator logs in and we have both data 14 and 20.

Next when another user qlikview logins in, we get both.

Data is not reduced. For any user login, all data of 14 and 20 are shown.

If first tr_mgm logs in, the data is reduced, but the same data is available for next user km_mgm?

In the web browser too, the data is not reduced as per the client id in section access. Last reloaded data is shown

In the document properties, "initial data reduction" is checked.

thanks

KRS