Problem again with Section Access through DB

[MK9885]

Hello all,

I'm facing problems again with my section access table. I'm trying to achieve it through Data base.

Following is my Qlik view script.

SECTION Access;

USERADMIN:

LOAD EMPLOYEEID,

     NTNAME,

     Upper(BUCODE) as BUCODE,

     USERNAME,

     ROLENAME,

     ACCESS 

FROM

[..\QVD\02_TransformQVD\Transform_USERADMIN_ROLE.QVD](qvd);

Section Application;

Below is my extract table from db

TRACE ('UTC - UESP USERADMIN ROLE VW Table load');

ESP_USERADMIN_ROLE_VW:

LOAD

    USERNAME,

    "NTNAME",

    EMPLOYEEID,

    "ROLENAME",   

    "BUCODE",  

    "ACCESS";

SQL SELECT

    USERNAME,

    "NTNAME",

    EMPLOYEEID,

    "ROLENAME",   

    "BUCODE",  

    "ACCESS"

FROM

  Schema."Tablename";

The problem I'm facing here is when I run the qvw in QMC I can only see as USER, which I'm not. I have 2 accounts given in db. One is as Admin and other is as User.

Even other Admins are only seeing one BUCODE and it is not showing them all BU's.

I'm trying to restrict the visibility throught BUCODE which is for ex: CA,DE,AB,SW etc....

I as an Admin should see all BU's and from my other account I should see only DE.

The Query seems right and also Qlik Script but I do not what's happening... Can anyone please help me with this?

Thanks.

stalwar1‌loveisfail‌ @peter cammaert

[stabben23]

Hi,

Are you distributing the Application throw Publisher? If so, Try to have only one admin, the service account.

[tresesco]

Try unoptimized load, like:

SECTION Access;

USERADMIN:

LOAD EMPLOYEEID,

     NTNAME,

     Upper(BUCODE) as BUCODE,

     USERNAME,

     ROLENAME,

     ACCESS

FROM

[..\QVD\02_TransformQVD\Transform_USERADMIN_ROLE.QVD](qvd);

Section Application where 2=2;

[MK9885]

One Admin?

For publishing there are only 2 Admins. I added Service account to the Inline in section access as you can see the code below.....

But to access the document there are more than 4.

I also tried directly using SQL select statement in section application

SECTION Access;

ESP_USERADMIN_ROLE_VW:

LOAD

    USERNAME,

    "NTNAME",

    EMPLOYEEID,

    "ROLENAME",   

    UPPER("BUCODE") as BUCODE,  

    "ACCESS";

SQL SELECT

    USERNAME,

   NTNAME,

   EMPLOYEEID,

   ROLENAME,

  UPPER(BUCODE) as BUCODE,

   "ACCESS"

FROM

  Schema."Tablename";

Concatenate	(ESP_USERADMIN_ROLE_VW)
	LOAD * INLINE [

    EMPLOYEEID, NTNAME, BUCODE, USERNAME, ROLENAME, ACCESS

    *, CORP\S-Admin-a, *, *, *, ADMIN

];

SECTION Application;

and still I do not have access as Admin, I only see one BU. This is very strange behavior.

[MK9885]

I also have to add a ServiceAccount to Inline as the service account is not added in db table. Cus in future the distribution will be done through service account.

How do I also add Inline to the above script you gave?

LOAD * INLINE [

    EMPLOYEEID, NTNAME, BUCODE, USERNAME, ROLENAME, ACCESS

    *, CORP\S-Admin-a, *, *, *, ADMIN

];

This is my Inline table.

[stabben23]

Yes its a strange behavior when distribute Applications With Section Acces throw Publisher.

I had the same problem ones, If there is no need for distribution (reduce the app) it could be a solution to put the Application in a folder who is visible in AccessPoint and just do a reload of the Application.

You can also try to change the * against ' ' (blank , no quotes). Star maybe not be all in this case.

[MK9885]

No " in SQL query?

And no * in Inline?

Just blank?

[tresesco]

Just put that inline table below the unoptimized qvd load it would get auto concatenated as long as both the tables are having all fields in common;

[MK9885]

Yes, I did that and after running in QMC it couldn't access it as an Admin. It said Failed to open the document.

But as a user I was able to

[Peter_Cammaert]

Note that Section Access grants special privileges to ADMIN-type users in QV Desktop only. In the AccessPoint everybody will be a USER. Even those entries that specify ACCESS=ADMIN will be switched to USER access.

That also means that if you apply Data reduction in your document, there will be no escaping like there is in QV Desktop (ADMIN gets to see all data, even when Strict Exclusion is enabled).

If you want to add a fixed entry to Section Access through the use of an INLINE LOAD, just concatenate the two parts. For example:

Section Access;

Users: // First entry = service account. For reloads only !

LOAD * INLINE [

ACCESS, NTNAME, LINKFIELD

ADMIN, domain\serviceaccount,

];

CONNECT TO ...;

CONCATENATE (Users) // Next add DB users

SQL SELECT ACCESS, NTNAME, LINKFIELD FROM DatabaseTable;

DISCONNECT;

CONCATENATE (Users) // finally, add users from local table

LOAD ACCESS, NTNAME, LINKFIELD

FROM SectionAccess.QVD (QVD)

WHERE 1 = 1;

Section Application;

:

Best,

Peter