11 Replies Latest reply: Dec 19, 2013 1:44 PM by KAITLYN WEBB RSS

    Section Access Help - Admin not seeing all data

      Good Afternoon All,

       

      Please could someone help me with the following. I am trying to implement Section Access, here is my code:

       

      Section Access;

      LOAD

      UPPER(ACCESS) AS ACCESS,

      UPPER(HOME) AS NTNAME,

      UPPER(DEPARTMENT) AS REGION;

      SQL

      SELECT

      UP.USERTEXT4 AS ACCESS,

      UP.USERTEXT3 AS HOME,

      U.DEPARTMENT AS DEPARTMENT

      FROM sysdba.USERINFO AS U

      INNER JOIN sysdba.USERSECURITY AS US

      ON U.USERID = US.USERID

      INNER JOIN sysdba.USERPROFILE AS UP

      ON U.USERID = UP.USERID;

       

       

       

       

       

       

      And my Application code is:

       

       

      Section

      Application;

      LOAD * INLINE [

      REGION, Countryxx

      UK, UK

      DENMARK, Denmark

      SCANDINAVIA, Denmark

      SCANDINAVIA, Norway

      SCANDINAVIA, Sweden

      NORWAY, Norway

      SWEDEN, Sweden

      GERMANY, Germany

      SPAIN, Spain

      PORTUGAL, Portugal

      IBERIA, Spain

      IBERIA, Portugal

      FRANCE, France

      BELGIUM, Belgium

      HOLLAND, Holland

      LUXEMBOURG, Luxembourg

      BENELUX, Belgium

      BENELUX, Holland

      BENELUX, Luxembourg

      USA, USA

      CANADA, Canada

      AUSTRALIA, Australia

      AUSTRALIA, New Zealand

      AUSTRALIA, Singapore

      NEW ZEALAND, New Zealand

      SINGAPORE, Singapore

      ]
      ;

       

       

       

       

       

      For any user it's working fine, but I want the admin to see everything despite the region they are assigned to. I am sure I have done this before and got it working, please can someone tell me how to fix this and tell me why it isn't working.

       

      Kind Regards,

       

      Miles

        • Re: Section Access Help - Admin not seeing all data
          Henric Cronström

          The ADMIN must be mapped against all regions, and it is not possible for me to see in your post if he is.

           

          A generic way to map an admin to all data can be found on http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

           

          HIC

            • Re: Section Access Help - Admin not seeing all data

              Hi Henric,

               

              I will try to be clearer, my section access table has ACCESS, NTNAME, and REGION fields. the data for example looks like this:

               

              Admin, Dualwise\tracey.roberts, UK

              User, Dualwise\miles.newey, Norway

               

              When I log into the dashboard I only see Norway, which is correct, but when Tracey is logging in she is only seeing UK, but as an admin, she should see all regions, but is not.

               

              Are you saying with your post, that Tracey has to have all regions against her, effectively doubling up her rows so

               

              Admin, Dualwise\tracey.roberts, UK

              Admin, Dualwise\tracey.roberts, Norway

              Admin, Dualwise\tracey.roberts, Sweden

               

              I am really stuck here as this doesn't make sense,

               

              Kind Regards,

               

              Miles

                • Re: Section Access Help - Admin not seeing all data
                  Henric Cronström

                  Yes, you need to double up her rows. But it can be done in a smart way:

                  Regions.png

                  Keep the left table in Section Access, and keep the right one in data. When you create the right one, you do it through two consecutive Load statements:

                       Load '<ALL>' as %REGION, REGION From Regions ;

                       Load REGION as %REGION, REGION From Regions ;

                   

                  HIC

                    • Re: Section Access Help - Admin not seeing all data
                      Charlotte Beattie

                      Thanks Henric, this helped me today with an issue

                      • Re: Section Access Help - Admin not seeing all data
                        Srikanth P

                        Hi Henric, Can we use the * for Admin user id's like below ?

                         

                        ACCESS NTNAME            REGION

                        ADMIN    GF\QVSERVICE  *

                        USER     GF\ABCDE          USA

                         

                        The above one is correct or ?

                          • Re: Section Access Help - Admin not seeing all data
                            Henric Cronström

                            The logic is tricky here...

                             

                            First, if you use the star * , then this will be matched against REGIONs in that table, i.e. USA. But it will not match other REGIONs found in data. So, an ADMIN will see USA only.

                             

                            However if you leave REGION blank for the ADMIN, the REGION will not match anything. This (together with strict exclusion) will normally lead to that the user cannot open the file. But for an ADMIN, it will lead to full access, i.e. no reduction at all. So, an ADMIN will see all REGIONs.

                             

                            HIC 

                              • Re: Section Access Help - Admin not seeing all data

                                Hi Henric

                                 

                                I am stuck in a similar situation but seeing different results.

                                 

                                Section Access;

                                LOAD * INLINE [
                                    ACCESS, USERID, PASSWORD, DEPARTMENT
                                    ADMIN, ADMIN, ADMIN,
                                    ADMIN,  M09000, M09000,
                                    USER, M09849, M09849, BI
                                    USER, M01122, M01122, IT
                                ]
                                ;


                                Section Application;
                                LOAD * INLINE [
                                USERID, DEPARTMENT
                                    M09849, BI
                                    M01122, IT
                                ]
                                ;

                                 

                                 

                                 

                                I have user M0900 as an ADMIN with no specified department (we would like him to see all departments, listed and unlisted).  When I run it through the council the two USERIDs with 'User' level access are seeing the respective departments but the M0900 user is blocked from gaining access to the dashboard.

                                 

                                Do you have any ideas as to what I could have done wrong?

                                 

                                Thanks!

                                  • Re: Section Access Help - Admin not seeing all data
                                    Henric Cronström

                                    As I said, the logic is tricky here...

                                     

                                    The algorithm is (if I remember correctly...):

                                    Try to reduce according to the value in Section Access

                                         If Success Then

                                              Return this application

                                         Else

                                              If USER='ADMIN' or NOT STRICT_EXCLUSION Then

                                                   Return application unreduced

                                              Else

                                                   Return 'Access Denied'

                                     

                                    You have <blank> in DEPARTMENT, so the reduction attempt will not succeed. The second IF-clause gives access to your ADMIN but not to your USERs. So, both ADMIN and M09000 should see all data.

                                     

                                    But there is an additional twist: You can only log on as ADMIN if you open a physical file with QlikView Desktop. If you access the file via QlikView Server, you are always considered USER. So, if M09000 opens it on a server, he will be denied access.

                                     

                                    HIC

                                      • Re: Section Access Help - Admin not seeing all data

                                        Thanks Henric,

                                         

                                        So what I gather from your explanation, if we have two 'super users' who are supposed to have access to ALL departments we would have to list each of them with all 20+ departments at a USER level?

                                         

                                        There is no way to leave it generic so when we add a new department to the organization it will automatically added to those two users access levels?

                                          • Re: Section Access Help - Admin not seeing all data
                                            Henric Cronström

                                            There is always a way ...

                                             

                                            If you start by defining a symbol '<ALL>', for example through

                                             

                                                 MapDepartment:

                                                 Mapping

                                                 Load distinct '<ALL>' as Symbol, Concat(distinct Department,',') as Departments From Departments ;

                                             

                                            ...then you can use this in your authorization table

                                             

                                                 LOAD

                                                   ACCESS,

                                                   USERID,

                                                   PASSWORD,

                                                   Trim(Subfield(Applymap('MapDepartment',DEPARTMENT),',')) as DEPARTMENT

                                                 INLINE [

                                                     ACCESS, USERID, PASSWORD, DEPARTMENT

                                                     ADMIN, ADMIN, ADMIN,<ALL>

                                                     ADMIN,  M09000, M09000,<ALL>

                                                     USER, M09849, M09849, BI

                                                     USER, M01122, M01122, IT];

                                             

                                            The Applymap() function will replace '<ALL>' with 'BI,IT,X,Y,Z' or whichever departments you have.

                                            The Subfield() function will create an implicit loop, so that if you have 3 values, you will get three records.

                                             

                                            HIC