8 Replies Latest reply: Nov 1, 2017 9:30 AM by Aehman K RSS

    Section access query

    Andres Garcia

      Hi

       

       

      I am trying to apply section access and Section application to an app, but when following the script below it creates me a synthetic Key.

       

      I have a main data table from where I am I have 4 fields that would work as fields for data reduction, Cluster, Country, Location and BU.

       

      If I do the section access with only one field it works well, but if I try to use one additional reduction field it creates the synthetic key.

       

       

      Any suggestion?

        • Re: Section access query
          Aehman K

          Can you post your Section Access script here?

            • Re: Section access query
              Andres Garcia

              Section Access;

              LOAD * INLINE [

                  ACCESS, USERID, PASSWORD, %CLUSTER, %COUNTRY, %LOCATION, %BU

                  ADMIN, ADMIN, 123, *, *, *, *

                  USER, USER1, 123, C1, USA, *, *

                  USER, USER2, 123, C2, CANADA, LOCATION1, *

              ];

              Section Application;

               

              LOAD * INLINE [

                  Cluster, %CLUSTER, Country, %COUNTRY, Location, %LOCATION, Bu, %BU

                  C1, C1, USA, USA, Location1, LOCATION1, bu1, BU1

                  C2, C2, Canada, CANADA, Location2, LOCATION2, bu2, BU2

              ];

                • Re: Section access query
                  Aehman K

                  Section Access;

                  LOAD * INLINE [

                      ACCESS, USERID, PASSWORD, %CLUSTER, %COUNTRY, %LOCATION, %BU

                      ADMIN, ADMIN, 123, *, *, *, *

                      USER, USER1, 123, C1, USA, *, *

                      USER, USER2, 123, C2, CANADA, LOCATION1, *

                  ];

                  Section Application;

                   

                  LOAD * INLINE [

                      Cluster, %CLUSTER, Country, %COUNTRY, Location, %LOCATION, Bu, %BU

                      C1, C1, USA, USA, Location1, LOCATION1, bu1, BU1

                      C2, C2, Canada, CANADA, Location2, LOCATION2, bu2, BU2

                  ];


                  why are you using 2nd Inline table?


                  Adding service account to Section Access may work plus using Star is *; before section Access but I don't get why 2nd table i being used?

                  • Re: Section access query
                    Aehman K

                    Star is *;

                    LOAD * INLINE [

                    ACCESS, USERID, PASSWORD, %CLUSTER, %COUNTRY, %LOCATION, %BU

                        ADMIN, ADMIN, 123, *, *, *, *

                        USER, USER1, 123, C1, USA, *, *

                        USER, USER2, 123, C2, CANADA, LOCATION1, *

                        ADMIN, INTERNAL/sa_scheduler, *, *, *, *.*

                       ];

                      • Re: Section access query
                        Andres Garcia

                        I change a bit my section access Script when adding the Location  as reduction field It allows me to access only as Admin and User3, but It denies access to the rest of users and when i return as admin the data remains as User 3 reducted data, If I user two reductions fields It works really well

                         

                        Section Access;

                        LOAD * INLINE [

                            ACCESS, USERID, PASSWORD, CLUSTER, COUNTRY, LOCATION,

                            ADMIN, ADMIN1, 123, *, *, *,

                            USER, USER1, 123, Argentina, Uruguay, *,

                            USER, USER2, 123, Brazil, *, *,

                            USER, USER3, 123, Caricam, Costa Rica,Terra Campus,

                            USER, USER4, 123, Mexico,  *, *,

                            USER, USER5, 123, Pacific, Ecuador, *,

                        ];

                        Section Application;

                         

                        Section Application;

                        SecApp1:

                        LOAD

                            Cluster,       

                            Upper(Cluster) as CLUSTER   

                        FROM

                        [Z:\01 Metrics\03 QlikView\QV_DB\QVD Sources\Main_Data.qvd]

                        (qvd);

                         

                        SceApp2:

                        LOAD

                            Country,

                            Upper(Country) as COUNTRY

                        FROM

                        [Z:\01 Metrics\03 QlikView\QV_DB\QVD Sources\Main_Data.qvd]

                        (qvd);

                         

                        SceApp3:

                        LOAD

                            Location,

                            Upper(Location) as LOCATION

                        FROM

                        [Z:\01 Metrics\03 QlikView\QV_DB\QVD Sources\Main_Data.qvd]

                        (qvd);

                         

                        SceApp4:

                        LOAD

                            Business_Unit,

                            Upper(Business_Unit) as BUSINESS_UNIT

                        FROM

                        [Z:\01 Metrics\03 QlikView\QV_DB\QVD Sources\Main_Data.qvd]

                        (qvd);

                          • Re: Section access query
                            Mark Ritter

                            I believe that the userid needs to contain the Domain (The User Directory that it was loaded from).  Displayed in the QMC

                             

                            So:

                             

                            Domain\User1

                            • Re: Section access query
                              Aehman K

                              Star is *;

                               

                              Section Access;

                               

                              SA:

                              LOAD

                                         

                                  Upper(Cluster) as CLUSTER,

                                  Upper(Country) as COUNTRY,

                                  Upper(Location) as LOCATION,

                                  Upper(Business_Unit) as BUSINESS_UNIT  

                              FROM

                              [Z:\01 Metrics\03 QlikView\QV_DB\QVD Sources\Main_Data.qvd]

                              (qvd);

                               

                               

                              LOAD * INLINE [

                                  ACCESS, USERID,  CLUSTER, COUNTRY, LOCATION,

                                  ADMIN, INTERNAL/sa_scheduler,*,*,*

                                  USER, DOMAIN\USER1, ARGENTINA, URUGUAY, *

                                  USER, DOMAIN\USER2, BRAZIL, *, *

                                  USER, DOMAIN\USER3, CARICAM, COSTA RICA,TERRA CAMPUS

                                  USER, DOMAIN\USER4, MEXICO,  *, *,

                                  USER, DOMAIN\USER5, PACIFIC, ECUADOR, *

                              ];

                              Section Application;

                               

                               

                              Try the above script.

                              I do not see the need to load your table every single time when you using Upper case when you loading it from same table.

                              Plus you don't need to add Password for users as it will be NTName based access.

                              If you are not sure about NTName for users, create a text box in test app and type in =OSUsers() in text box. This would give you NTName of different users logging in or search thru your AD or your User search in QMC.

                              If you get locked out then right click on the app and 'Open without Data' to modify the script.

                               

                              Thanks.

                      • Re: Section access query
                        Ricardo Gerhard

                        Hi Andres,

                         

                        Try to reproduce and improve your skill base on this link:

                        http://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/Security/manage-security-with-section-access.htm

                         

                        PS: the section access will be applied when reloaded and published on stream. Reload, Publish and close the browser.