Section Access using Windows Authentication

[montubhardwaj]

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

[kaushiknsolanki]

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

[Miguel_Angel_Baeyens]

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

[montubhardwaj]

@ 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

[kaushiknsolanki]

HI,

   Yes your are right.

Regards,

Kaushik Solanki 

[Miguel_Angel_Baeyens · Accepted solution]

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

[montubhardwaj]

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

[christian77]

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?

[Miguel_Angel_Baeyens]

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

[christian77]

Tanks a lot Miguel.

It's gonna help me for sure.