1 Reply Latest reply: Apr 19, 2010 7:27 AM by Martijn ter Schegget RSS

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

      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 .... !

        • OSuser() and different output in section access, access based by AD group
          Martijn ter Schegget

          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 Big Smile. 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.