3 Replies Latest reply: May 21, 2015 3:02 AM by Shivendoo Kumar RSS

    Implementing Read and Write Access to Application?

    Shivendoo Kumar

      Hi Guys,

      I am trying to Implement Sheet Hide and show.

       

      I have users from Active Directory. They have been assigned under QlikView_Read and QlikView_Write

      Users under QlikView_Read can view only SHEET1 where He/She can't allow to add any objects or sheet whereas Users under QlikView_Write will have access to

      SHEET1 and SHEET2. On SHEET2, user can add objects and can delete it.

       

      Now I have few users who are not in AD and I want to add then from Excel.

       

      Below is my Query:

      AD_USERS:

      SQL

      SELECT

        ACCESS,

        NTNAME,

        GROUPNAME,

        EMPID

      FROM FROM DBO.ADUSERS;

      CONCATENATE(AD_USERS)

      LOAD

      'USER'                AS ACCESS,

      'ALL'                 AS NTNAME,

      'ALL'                 AS GROUPNAME,

      [Employee ID]         AS EMPID

      FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd)

      WHERE NOT EXISTS (EMPID,[Employee ID] );

       

      Section Access;

      SECTION_ACCESS:

      LOAD ACCESS,

           NTNAME,

           GROUPNAME AS %GROUPNAME,

           EMPID AS %EMPID

      RESIDENT AD_USERS;

      CONCATENATE (SECTION_ACCESS)

      LOAD ACCESS,

           NTNAME,

           GROUPNAME AS %GROUPNAME,

           EMPID AS %EMPID

      FROM

      $(vQlikViewADMINUsersPath)

      (ooxml, embedded labels, table is QlikViewADMIN_USERS);

      Section Application;

      DROP TABLE AD_USERS;

      SHEET_ACCESS:

      LOAD GROUPNAME AS %GROUPNAME,

           SHEET1 AS %SHEET1,

           SHEET2 AS %SHEET2,

           SHEET3 AS %SHEET3

      FROM

      $(vQlikViewADMINUsersPath)

      (ooxml, embedded labels, table is SHEET_ACCESS);

       

      Dim_Employee:

      LOAD

           [Employee ID] AS %EMPID,

           NAME,

           DOB,

           PROJECT

      FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd);

      Fact_Employee_Salary:

      LOAD

           [Employee ID] AS %EMPID,

           Salary,

           Incentive

      FROM $(vDimQVDStoragePath)\FACT_EMP_DATA.qvd(qvd);

      Now Suppose User Qlik/Shiven.Kumar is not available in AD Table and so AD_USERS will not have data related to Qlik/Shiven.Kumar

      so what I am doing in section access I am adding this user from Excel sheet QlikViewADMIN_USERS

      ACCESS NTNAME                GROUPNAME EMPID

      ADMIN    Qlik/Shiven.Kumar    ALL                *

       

      and excel sheet SHEET_ACCESS holds this data:

      GROUPNAME    SHEET1 SHEET2 SHEET3

      ALL                              1     1     1

      QlikView_Read              1     0     0

      QlikView_Write              1     1     0

       

      After deploying this app to server, If I try to login with Qlik/Shiven.Kumar, I am allowed but I am not allowed to access this application.

      Any users who is available in AD is abale to access this appplication.

      What wrong I am doing?

        • Re: Implementing Read and Write Access to Application?
          Toni Kautto

          Please notice that all the data in the Section Access table must be in upper case. This then has the implication that the data in the reduction field in the data model also must be in upper case.

            • Re: Implementing Read and Write Access to Application?
              Shivendoo Kumar

              Toni Kautto,

               

              Thank you very much. Once I changed the below code and converted GROUPNAME and NTNAME to Upper Case and 'ALL'  AS GROUPNAME to '*'  AS GROUPNAME

              It is working now.

               

              AD_USERS:

              SQL

              SELECT

                ACCESS,

                NTNAME,

                GROUPNAME,

                EMPID

              FROM FROM DBO.ADUSERS;

              CONCATENATE(AD_USERS)

              LOAD

              'USER'                AS ACCESS,

              'ALL'                 AS NTNAME,

              '*'                 AS GROUPNAME,

              [Employee ID]         AS EMPID

              FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd)

              WHERE NOT EXISTS (EMPID,[Employee ID] );

               

              Section Access;

              SECTION_ACCESS:

              LOAD ACCESS,

                   UPPER(NTNAME)         AS NTNAME,

                   UPPER(GROUPNAME) AS %GROUPNAME,

                   EMPID AS %EMPID

              RESIDENT AD_USERS;

              CONCATENATE (SECTION_ACCESS)

              LOAD ACCESS,

                   UPPER(NTNAME)         AS NTNAME,

                   UPPER(GROUPNAME) AS %GROUPNAME,

                   EMPID AS %EMPID

              FROM

              $(vQlikViewADMINUsersPath)

              (ooxml, embedded labels, table is QlikViewADMIN_USERS);

              Section Application;

              DROP TABLE AD_USERS;

              SHEET_ACCESS:

              LOAD UPPER(GROUPNAME) AS %GROUPNAME,

                   SHEET1 AS %SHEET1,

                   SHEET2 AS %SHEET2,

                   SHEET3 AS %SHEET3

              FROM

              $(vQlikViewADMINUsersPath)

              (ooxml, embedded labels, table is SHEET_ACCESS);

               

              Dim_Employee:

              LOAD

                   [Employee ID] AS %EMPID,

                   NAME,

                   DOB,

                   PROJECT

              FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd);

              Fact_Employee_Salary:

              LOAD

                   [Employee ID] AS %EMPID,

                   Salary,

                   Incentive

              FROM $(vDimQVDStoragePath)\FACT_EMP_DATA.qvd(qvd);

               

              and Changed ALL to * in Below Excel Data

              ACCESS NTNAME                GROUPNAME EMPID

              ADMIN    Qlik/Shiven.Kumar   *                *

               

              and excel sheet SHEET_ACCESS holds this data:

              GROUPNAME    SHEET1 SHEET2 SHEET3

                  *                              1     1     1

              QlikView_Read              1     0     0

              QlikView_Write              1     1     0