Implementing Read and Write Access to Application?

[#1]

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?

[qlikviewwizard]

Hi Shivendookumar,

Check these two.

Section Access: Strict Exclusion

Column level access using Section Access

Hope it will helpful.

[ToniKautto · Accepted solution]

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.

[#4]

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