OSuser() and different output in section access, access based by AD group

[#1]

Hi All,

I'm having problems to apply file security based on domain names and groups. I have couple of groups in AD and the access is based on them. For example, members of Group Location1 Viewer can only see Location1 data. Now I have a group called SalesReps and I would like to have restricted access based on the name of Sales Person - if the person is in this group. I mean for example if Peter is in Sales Rep Viewer group, system sees it's Peter and based on OSUSER() gives him only data for himself. I don't want to add users manualy in section access as the idea is just add users into AD group Sales Rep Viewer (because we have table matching ids and names)

The idea was to use this in the Access table (see full code):

LOAD ACCESS, NTNAME, DEPT, if(SALESPERSONACCESS = 'ALIASREPNAME', upper(subfield(osuser(),'',2)), 'ALLSALESREP') as SALESPERSONACCESS

But it doesn't work.

The problem is this:

If I use OSUSER() within the screen (or as a variable) - my user name is correct ("ONDREJ" , "PETER",..)

If I use OSUSER() in section access (or in the script) - my username is "QLIKVIEWSERVICE"

I use : upper(subfield(osuser(),'',2))

Any idea how to achieve this ?

BTW: Access for users is via acces point - IE plugin

Full script code is something like this:

Section Access;

AccessTable:

LOAD *

INLINE [

ACCESS, NTNAME, DEPT, SALESPERSONACCESS

ADMIN, Ondrej, *, ALLSALESREP

ADMIN, QlikViewService, *, ALLSALESREP

ADMIN, Global QlikView Admins, *, ALLSALESREP

ADMIN, Qlikview Developer, *, ALLSALESREP

USER, Qlikview Location1 Viewer, LOCATION1, ALLSALESREP

USER, Qlikview Location2 Viewer, LOCATION2, ALLSALESREP

USER, Qlikview Location3 Viewer, LOCATION3, ALLSALESREP

USER, Qlikview Manager Viewer, *, ALLSALESREP

USER, Qlikview SalesRep Viewer, *, SALESREPNAME

];

Section Application;

AccessBySite:

LOAD *

INLINE [

DEPT,Site

LOCATION1, Prague

LOCATION2, London

LOCATION3, NewYork

];

AccessByPerson:

LOAD *

INLINE [

SALESPERSONACCESS, SalesPersonName

MICHAEL, Michael V

JOHN, John C

TOM, Tom D

.

.

SALESREPNAME, Test Name

];

Thank you .... !

[#2]

Hi Ondrej,

Instead of reading the Section Access table from a manually created INLINE table, you can read it from any valid datasource for QV, such as an Excel file, a database, or... Active Directory itself . See this article on the Guerrilla BI blog on how to do this.

I'll leave the details of how to create the correct Section Access table to you.

One final IMPORTANT tip (see also page 247 - Chapter 27 'Section Access' - of the QVS reference manual): add one row to your Section Access table for the QlikView service account, with 'ACCESS'-level 'ADMIN', and with NO value in the reductionfield (in your case: 'SALESPERSONACCESS'). If you don't , then after the first reload the QlikView service account which is supposed to do the reloads won't be able to access the file anymore! The bit about leaving this field empty fooled me as well, but putting '*' in this field won't work.
Adding this row can be as easy as putting the following statement after the LOAD-statement which loads accounts from Active Directory:

CONCATENATE LOAD * INLINE [
 ACCESS, NTNAME, DEPT, SALESPERSONACCESS
ADMIN, QLIKVIEW, ,
];

Regards,

Martijn ter Schegget
CND Development

PS: on the server the OsUser() call in the script will always return the QlikView service account, because it is evaluated during script reload, and the script reload is run with the QlikView service account credentials. When a user logs in, the reload has already finished, and the value for OsUser() in the script won't change.