topic Re: Problem again with Section Access through DB in QlikView

Problem again with Section Access through DB

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problem again with Section Access through DB

stabben23 — Wed, 26 Oct 2016 11:42:49 GMT

Hi,

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

Re: Problem again with Section Access through DB

tresB — Wed, 26 Oct 2016 11:49:00 GMT

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;

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 11:52:24 GMT

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.

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 11:54:20 GMT

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.

Re: Problem again with Section Access through DB

stabben23 — Wed, 26 Oct 2016 12:05:27 GMT

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.

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 12:11:31 GMT

No " in SQL query?

And no * in Inline?

Just blank?

Re: Problem again with Section Access through DB

tresB — Wed, 26 Oct 2016 12:11:51 GMT

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;

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 12:13:36 GMT

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

Re: Problem again with Section Access through DB

Peter_Cammaert — Wed, 26 Oct 2016 12:18:22 GMT

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

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 12:31:08 GMT

I used below script as mentioned...

Section Access;

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

LOAD * INLINE [

EMPLOYEEID, NTNAME, BUCODE, USERNAME, ROLENAME, ACCESS

SERVICEACNT, CORP\ADMINA, *, SERVICE, ESP ADMIN, ADMIN

];

CONCATENATE (Users) // Next add DB users

SQL SELECT

USERNAME,

NTNAME,

EMPLOYEEID,

ROLENAME,

UPPER(BUCODE) as BUCODE,

"ACCESS"

FROM

Schema."Tablename";

DISCONNECT;

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

LOAD EMPLOYEEID,

NTNAME,

UPPER(BUCODE) as BUCODE,

USERNAME,

ROLENAME,

ACCESS

FROM

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

WHERE 1 = 1;

Section Application;

But through desktop as well I'm only able to see as a user but not admin.

And you said even Admins will be only have access as a User in access point? They cannot see all BU'S?

Re: Problem again with Section Access through DB

Peter_Cammaert — Wed, 26 Oct 2016 12:58:05 GMT

That's all highly data-dependent, and since you only post your script code in this thread, I can only suggest you try first with the following technique. Comment out the Section Access; statement (put two slashes in front of it), save and reload. Save again, close and reopen. Put a listbox for field NTNAME on a sheet, and one-by-one select those users that should get a special data treatment. Trace the linkage and you will be able to figure out why some data appears that shouldn't, and why some data refuses to appear while it should.

After you found the cause, do not forget to undo these steps before finally publishing your document.

[Edit] Yes, all Account=ADMIN users will be demoted to USER status in the Access Point. Most of the Admin features (actually Developer features) are useless in the AP anyway...

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 15:29:14 GMT

In DB I've given Blank to all link field values.

And I've commented out Section Access to check with filters in UI and when I select a NTNAME for Admin account I see no data at all, while for a user I see only user related data.

Can you please suggest? and the Link field is linked to a dimension not to fact. Can this be an issue?

the link field should be available in fact table?

the link field I've is linked to a dim table and that dim table is linked to fact.

It gives me Failed to load the document error for only ADMIN account in accesspoint. For user I can see restricted fields.

The current script I'm using

SECTION Access;

ESP_USERADMIN_ROLE_VW:

LOAD

    EMPLOYEEID,

    NTNAME,

    upper(BUCODE) as BUCODE,

    USERNAME,

    upper(ROLENAME) as ROLENAME,

    Upper("ACCESS") as ACCESS;

SQL SELECT

    USERNAME,

    "NTNAME",

    EMPLOYEEID,

    "ROLENAME",

    Upper("BUCODE") as BUCODE,

   "ACCESS"



FROM

Schema."TableName";

LOAD * INLINE [

    EMPLOYEEID, NTNAME, BUCODE, USERNAME, ROLENAME, ACCESS

      , CORP\S-HRITQLIKDEV-a, , S-HRITQLIKDEV-a, ESP ADMIN, ADMIN



];

SECTION Application;

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 15:50:59 GMT

Are there any settings to be checked or implemented in QMC or Qlikview Services?

Re: Problem again with Section Access through DB

MK9885 — Wed, 26 Oct 2016 17:12:10 GMT

If I uncheck the Strict exclusion box I've full access as admin and users can only see user data.

I still have Initial Data reduction based on Section Access as checked.

Usually we check both boxes but unchecking the Strict is working fine for me.

One more question....

The ADMIN accounts are defined in Date Base not in Inline and those users are getting restricted.

so to fully access the data a user should be added to inline as an Admin?

Re: Problem again with Section Access through DB

MK9885 — Thu, 27 Oct 2016 15:28:34 GMT

pcammaert‌

Any suggestions on my above comments?

Thanks.

Re: Problem again with Section Access through DB

Peter_Cammaert — Thu, 27 Oct 2016 15:57:23 GMT

I think I have said everything I can.

If your linkage doesn't work with Section Access disabled, then trace and fix any faulty link field values.