Can I get a ruling on multiple alternations between SECTION ACCESS / SECTION APPLICATION?

[evan_kurowski]

Hello Qlikers,

In trying to configure SECTION ACCESS which had two different sets of users merging, a routine was attempted which used our "standard" SECTION ACCESS, which upon completion will declare SECTION APPLICATION.

We then got a request for additional permissions which I'd like to include as an addendum to the SECTION ACCESS, without discarding our original "default" entries, nor modify our established includes.

So the scripting sequence goes something like:

SECTION ACCESS; //here's the DEFAULT security entries
...
SECTION APPLICATION; //some permission & log tables
...
SECTION ACCESS;  //invoked a second time, adding some REGIONAL users
...
SECTION APPLICATION;  //here's the actual data load
...
//the entire above sequence works fine in desktop, will reload via QMC, but QMC is failing to complete the creation of the user document

The issue is, this sequence is working fine in the desktop, and we can trace both sets of DEFAULT + REGIONAL permissions applied on SECTION ACCESS on a desktop reload.

When it is moved up to the server however, the QMC can't complete the process via task.  We can see the application logs showing the QMC service account reached the end of the load script successfully, but it fails on the last step to produce the user document instance.

It would be understandable if it weren't allowed to double back and append new security entries to a section access that has already closed on a later passes, but on the other hand, if you have permission to the script & perform reload, then the document is kind of in your hands.

Can anyone confirm the "official" way this is supposed to perform?  Are multiple non-continuous invocations of SECTION ACCESS allowed, and compatible with the server?  (QV 11.2)  ~Appreciate your help Qlik Community!

[Peter_Cammaert]

It works for me. Something comparable to

Section Access;

SA:

LOAD * INLINE [

ACCESS, NTNAME

ADMIN, DOMAIN\SERVICEACCOUNT

ADMIN, OTHERUSER\MYUSERACCOUNT

];

Section Application;

Interim:

NOCONCATENATE

LOAD * INLINE [

ACCESS, NTNAME

USER, OTHERDOMAIN\HISUSERACCOUNT

];

Section Access;

CONCATENATE (SA)

LOAD * RESIDENT Interim;

Section Application;

Dummy:

LOAD * INLINE [

Product, Amount

ABC, 1000

DEF, 2000

GHI, 2500

];

and with the accounts swapped (AP accounts in second block, service service account stays in first but it's probably the same in your situation). It reloads just fine in both QV Desktop and the QMC. I can open all documents in the AP as well.

Using various 11.20 versions at home.

[evan_kurowski]

Hello Peter,

Thank you for testing & verifying. I think I can add some more cause & effect to my observations, would you mind perhaps verifying one additional thing?
 

The issue does not appear to be the repeated alternating sequence between SECTION ACCESS/SECTION APPLICATION.

  My issue occurs when attempting to load the same field names “reserved for use” in the SECTION ACCESS area, and then call them openly in the SECTION APPLICATION.

For example, in the SECTION APPLICATION I wanted to re-list the entries for the user permissions out in the open (so they could be verified).   However, though every one of these syntaxes will work on the desktop, if I reuse any of the field names from SECTION ACCESS in the clear area, the QMC will balk at completing the task.

SECTION ACCESS; //load set of DEFAULT entries
//using ACCESS, NTNAME, etc..

SECTION APPLICATION; //all field names NOT PRIOR USED in section access

SECTION ACCESS; // load set of REGIONAL entries

SECTION APPLICATION; //load application & audit the section access entries

//This load is preventing the QMC task from completing, when it is commented out, everything proceeds
[SHOW_SECTION_ACCESS_ENTRIES_OUT_IN_OPEN__THIS_IS_PROBLEM]:
NOCONCATENATE
LOAD  ACCESS,
NTNAME,
NTDOMAINSID
FROM [MyUserSpreadsheet.xlsx]
(ooxml, embedded labels, table is SECTION);



//Qualifying the above, or loading with field names that dont collide with Section Access produces no error

QUALIFY *;

[SHOW_SECTION_ACCESS_ENTRIES_OUT_IN_OPEN__THIS_IS_FINE]:
NOCONCATENATE
LOAD  ACCESS,
NTNAME,
NTDOMAINSID
FROM [MyUserSpreadsheet.xlsx]
(ooxml, embedded labels, table is SECTION);

[Peter_Cammaert · Accepted solution]

Well, it's a known fact that you can't create synthetic keys between single tables in both Section Access and Section Application (like you seem to do in your example prefixed with //This load is preventing...) because they will break your section access.

You can however create circular references between Section Access and Section Application by using multiple key values from a single table in Section Access to those same fields in different tables (no two fields in the same table) in Section Application. Those circular references will be cut when Section Access separates from the remainder of your data model.

Or you can create a unique composite key as an additional field in your SA table and link it to the same field in a Section Application table that expands back into the composing fields (your the comfort you are seeking) without any problem. Like

Section Access;

SA:

LOAD ACCESS, NTNAME, NTSID,

     ACCESS&'|'&NTNAME&'|'&NTSID AS LINKFIELD

FROM Somewhere (options);

Section Application;

MyCopy:

LOAD Upper(ACCESS&'|'&NTNAME&'|'&NTSID) AS LINKFIELD, // This one in capitals

     ACCESS AS Access,

     NTNAME AS NTName,

     NTSID AS NTSid

FROM Somewhere (options);

or something. Indeed, Section Access has its own peculiar character.

Peter

[Peter_Cammaert]

Not only, point assignments, but editing posts seems to be broken as well in the community.

Nevermind, replace NTSID by NTDOMAINSID and NTSid by NTDomainSid in my previous post. It shouldn't be too diffuclt though to find out what I'm getting at.

[evan_kurowski]

Peter Cammaert wrote:

Well, it's a known fact that you can't create synthetic keys between single tables in both Section Access and Section Application (like you seem to do in your example prefixed with //This load is preventing...) because they will break your section access.

You can however create circular references between Section Access and Section Application by using multiple key values from a single table in Section Access to those same fields in different tables (no two fields in the same table) in Section Application. Those circular references will be cut when Section Access separates from the remainder of your data model.

Or you can create a unique composite key as an additional field in your SA table and link it to the same field in a Section Application table that expands back into the composing fields (your the comfort you are seeking) without any problem. Like

Section Access;

SA:

LOAD ACCESS, NTNAME, NTSID,

     ACCESS&'|'&NTNAME&'|'&NTSID AS LINKFIELD

FROM Somewhere (options);

Section Application;

MyCopy:

LOAD Upper(ACCESS&'|'&NTNAME&'|'&NTSID) AS LINKFIELD, // This one in capitals

     ACCESS AS Access,

     NTNAME AS NTName,

     NTSID AS NTSid

FROM Somewhere (options);

or something. Indeed, Section Access has its own peculiar character.

Peter

  Haha, definitely NOT a known fact here, thus the thread regarding the same.  Did not find any threads on the topic after several searches, nor descriptions in the server manuals, but thank you for confirming.

What would really throw someone for a loop are these "no-no but server only" rules.  The desktop allows field names from the section access area to be re-used when performing reloads, and in that context all the scripts above are fine (even successfully applying the section access). 

One of the elements constantly draining off productivity is the divergence between what is functional on desktop vs. server, with breakage sitting latent and moving through many development phases, only to appear on the final publication step. 

If these differences persist, this really implies that you have to develop from the server backwards, finding a technique that works on desktop, but verifying it is server compatible, before committing further to usage.  Then the next technique, etc..  Almost to the point that having the developer certification is not going to be practical, without being paired with the administrator certification, as techniques developed in the one sphere get invalidated when migrated to the other.

Appreciate your help on this!

[Peter_Cammaert]

Hmm, what you are describing shouldn't be the case.

What really is different is that QV Desktop provides a developer with an ADMIN role, and that one doesn't exist on the server. ADMIN in QV Desktop means that you get access to a document in ways that do not obey the normal Section Access rules (like QV Desktop disabling strict exclusion even though it's enabled) because QV Desktop assumes that a Developer is attempting to get in with an ADMIN role (and definitely won't like being thrown out unexpectedly of his/her own document). The reload engine is the same piece of software as QV Desktop. Only the QlikView Server service enforces strict adherence to all Section Access rules (and throws all this ADMIN stuff out of the window).

Since many years, I hold on to the insight that QV Desktop is actually a very powerful design tool for which a number of luminaries and community greats have created a series of spectacular effects, but that I shouldn't assume that these can be automagically migrated to the end-users in their browsers. Indeed, some restrictions apply.

Many things that work in QV Desktop but don't seem to work in the Server are due to the fact that the Server environment is completely different, although the server may look like a normal Windows PC with C:, 😧 and Z: drives and MS Office and VBScript engines. However client-server architectures mixed with web techniques (mostly inherited from unix) make for a confusing mix of seemingly random technologies and features. Unfortunately, that's not necessarily the fault of the creator of the software.

The approach you mentioned at the end of your post is certainly useful. If you plan to use fancy tricks in your documents, test them in WebView before rolling them out, and give your end-users a chance to evaluate them before putting them into production.

Anyway, enjoy QlikView and the Qlik Community. There's always help around the corner.

Good luck,

Peter

[evan_kurowski]

Peter Cammaert <span class="icon-status-icon icon-mvp" title="Mvp"></span> <span class="icon-status-icon icon-partner" title="Partner"></span> wrote:

Hmm, what you are describing shouldn't be the case.

What really is different is that QV Desktop provides a developer with an ADMIN role, and that one doesn't exist on the server. ADMIN in QV Desktop means that you get access to a document in ways that do not obey the normal Section Access rules (like QV Desktop disabling strict exclusion even though it's enabled) because QV Desktop assumes that a Developer is attempting to get in with an ADMIN role (and definitely won't like being thrown out unexpectedly of his/her own document). The reload engine is the same piece of software as QV Desktop. Only the QlikView Server service enforces strict adherence to all Section Access rules (and throws all this ADMIN stuff out of the window).

Since many years, I hold on to the insight that QV Desktop is actually a very powerful design tool for which a number of luminaries and community greats have created a series of spectacular effects, but that I shouldn't assume that these can be automagically migrated to the end-users in their browsers. Indeed, some restrictions apply.

Many things that work in QV Desktop but don't seem to work in the Server are due to the fact that the Server environment is completely different, although the server may look like a normal Windows PC with C:, 😧 and Z: drives and MS Office and VBScript engines. However client-server architectures mixed with web techniques (mostly inherited from unix) make for a confusing mix of seemingly random technologies and features. Unfortunately, that's not necessarily the fault of the creator of the software.

The approach you mentioned at the end of your post is certainly useful. If you plan to use fancy tricks in your documents, test them in WebView before rolling them out, and give your end-users a chance to evaluate them before putting them into production.

Anyway, enjoy QlikView and the Qlik Community. There's always help around the corner.

Good luck,

Peter

Hello Peter,

Nothing more fancy than Section Access using ‘Basic NT Security’

For example, a script that exits immediately after an INLINE Section Access, with ‘Strict Exclusion’ applied in Document Settings:

  Section Access:
LOAD * INLINE [
ACCESS, NTNAME, NTDOMAINSID, REDUCTIONFIELD
USER, NORTH\EVANSACCOUNT, S-1-5-21-1957
USER, NORTH\QLIKVIEWADMIN, S-1-5-21-1957

];
Section Application;

exit script;

Values assigned to [REDUCTIONFIELD] are left null in Section Access, and the field is not defined in Section Application.
When I re-open this document on desktop, it allows me back in as a USER role (proving role reduction was applied).  This app will reload on the QMC, without the presence of [REDUCTIONFIELD] in the Section Application (meaning user account QLIKVIEWADMIN can complete the job on the server). When I open the reload distribution .qvw instance using EVANSACCOUNT, I also get the USER role.

When the following is appended to the above script, I get locked out of the document.

Section Application;

[REDUCTIONFIELD]:
LOAD * INLINE [
REDUCTIONFIELD
NOPE
];

According to rules of ‘Strict Exclusion’ [REDUCTIONFIELD] values in Section Access have to match something, anything in Section Application, and this all fits what is expected regarding Section Access. 

“Since many years, I hold on to the insight that QV Desktop is actually a very powerful design tool for which a number of luminaries and community greats have created a series of spectacular effects”

This concept I agree with, except perhaps the contributing body could extend beyond “luminaries and community greats”.  Having been through assignments with clients who actively customize their installations (thus when done openly, getting assigned version numbers that do not even exist in the public releases table), it is know that “off the grid” is possible.  So I guess my question is: If a function is tried that is known to work, and no one is watching, does it have to?