15 Replies Latest reply: Sep 27, 2013 11:45 AM by Carlos Reyes RSS

    Sheet Security with Section Access

    Carlos Reyes

      Hi,

       

      I'm developing an app that needs to constraint the access to certain sheets/tabs to some users, and, since this application is being published through a publisher distribution task, I decided to use Section Access in order to fulfill that requirement. However I've encountered a problem I've been unnable to solve.

       

      So, I created a small inline table with the user profiles, which is loaded within the Section Access part of the script, and after that I load the profile information, outside of the Section Access part, so that it'll be filtered per user during the distribution task. Theorically this should work, but only the first user (which is the same that runs the qlikview services) can enter the app through the Access Point or the Open in Server option in the Desktop. The remaining users are not allowed to go in the application and get the classical Section Access error that indicates that they don't have the right to see that app.

       

      At first I thought it was a problem of incorrect user names (NTAME) but I've tested the same users, and inline load method, with other apps, that do filter the data model and not only sheets, and it works perfectly fine.

       

      I've attached a small example of my requirement with the Section Access script commented and the Document Properties "Initial Data Reduction Based on Section Access" and "Strict Exclusion" disabled so you can open the file.

       

       

      Section Access;
      
      SECURITY:
      LOAD * INLINE [
          ACCESS, NTNAME, USER
          ADMIN, EVOLCON-CR\CARLOSREYES, CARLOSREYES
          ADMIN, EVOLCON-CR\PRUEBA1, PRUEBA1
      ];
      
      Section Application;
      
      SHEET_ACCESS:
      LOAD * INLINE [
          USER, SHEET_A, SHEET_B
          CARLOSREYES, 0, 1
          PRUEBA1,  1, 0
      ];
      
      

       

      I hope you can help me to solve or get around this issue. I already know about the MATCH(OsUser(), 'User1', 'User2', 'UserN') option and although it works I would like to know why this doesn't. if this approach is incorrect and will never work I need to get an efficient alternative, since this app will be distribuited to more than 50 users so it'll be cumbersome to use the MATCH() method.

       

      Thanks in advance.

        • Re: Sheet Security with Section Access
          Iyyappan v

          Hi,

           

          please check the following link to refer section access.

           

          Sheet Level security

          Sheet level access

           

          Regards,

            • Re: Sheet Security with Section Access
              Carlos Reyes

              I've watched the video and reviewed your example but both use USERID and not NTNAME, which should work similar to NTNAME, so it helped me to reassure that what I'm trying is correct, but my main problem is that users are not able to login to the app because this is distribuited with publisher based on the NTNAME... so... still no solution for this.

               

              ¿Has anybody faced this scenario? Almost a year ago I did this on another app but using version 10 and I didn't have this problem. ¿Does anybody knows if there is somekind of limitation or rule regarding Section Access and Distribution taks?

               

              Thanks in advance.

                • Re: Sheet Security with Section Access
                  Bill Britt

                  Hi,

                   

                  If it will work with USERID it should work with NTNAME. Just make sure the service account has rights to all sheets.

                   

                   

                  Bill

                    • Re: Sheet Security with Section Access
                      Carlos Reyes

                      Hi Bill,

                       

                      The main problem is not the services account, because it works for that profile, but for the rest of the section access records/profiles, which are the ones that cannot go into the application. It is really weird because the distribution task runs perfectly well and the services account can go into the app. But when a different user, with a valid section access profile, wants to open the app  it gets the classic section access error that says it doesn't have the permission to see tha app.

                       

                      I know it's a lot to ask but could you try to replicate the scenario I describe in my post. You can download the sample file to get the exact idea of what I'm trying to do.

                       

                      Thanks.

                        • Re: Re: Sheet Security with Section Access
                          Bill Britt

                          HI,

                           

                          I will try to test it today. However, the attach document and put it on the server for the users that is having issues. Have them open it and take a screen shot of what they see. Make sure it is showing the same as the NTNAME you have in section access. Also, try unchecking the strict Exclusion and see what you get.sa.png

                          • Re: Re: Sheet Security with Section Access
                            Bill Britt


                            Hi,

                             

                            Checkout the attached document. I have made a couple of changes to the script and the condition property on the sheets. This should give you an idea on how it should work.

                             

                            Bill

                              • Re: Sheet Security with Section Access
                                Carlos Reyes

                                Hi Bill,

                                 

                                I did uncomment the section access code in the document you provided, "Sheets_Security.qvw", and kept the "Strict Exclusion" option disabled. Then I distribuited the app through a publisher task. Now both users can go into the app, but the USER and SHEET fields are not being reduced/disabled accordingly. Both users see the same sheet although the OSUSER() function shows they're different. It seems that the distribution task only takes the first USER, SHEET record for both users.

                                 

                                 

                                 

                                 

                                If I enable the "Strict Exclusion" option, the user CARLOSREYES can go into the app, but the user PRUEBA1 gets the following error message:

                                 

                                 

                                Do you have any idea of what may be happening?

                                 

                                Thanks for your help !

                                  • Re: Sheet Security with Section Access
                                    Peter Cammaert

                                    How do you distribute your document? To all Authenticated Users, or just to a list of Named users (assuming you have a publisher)? This seems more like a distribution problem.

                                     

                                    Peter

                                    • Re: Sheet Security with Section Access
                                      Bill Britt

                                      Hi,

                                       

                                      What account are you reloading it under? If that account only have access to sheet A then that is all that will be show. Also, if you have rights to sheeet B and it isn't available you will not be able to get in.

                                       

                                      To add your service account

                                       

                                      Section Access;

                                      SECURITY:
                                      LOAD * INLINE [
                                         ACCESS, NTNAME, USER
                                      ADMIN, SERVICEACCOUNT, SERVICEACCOUNT
                                      ADMIN, EVOLCON-CR\CARLOSREYES, CARLOSREYES 
                                      ADMIN, EVOLCON-CR\PRUEBA1, PRUEBA1
                                      ];


                                      Section Application;

                                      SHEET_ACCESS:
                                      LOAD * INLINE [
                                          USER, SHEET
                                      SERVICEACCOUNT,*
                                          CARLOSREYES, 0
                                          PRUEBA1,  1
                                      ];

                                        • Re: Re: Sheet Security with Section Access
                                          Carlos Reyes

                                          Bill,

                                           

                                          All QlikView services run under EVOLCON-CR\CARLOSREYES.

                                           

                                          I changed the Section Access code in order to enable CARLOSREYES to see all sheets, and also I added a new user in order to see if the sheets are correctly enabled or disabled:

                                           

                                          Section Access;

                                           

                                          SECURITY:

                                          LOAD * INLINE [

                                             ACCESS, NTNAME, USER 

                                              ADMIN, EVOLCON-CR\CARLOSREYES, CARLOSREYES 

                                              ADMIN, EVOLCON-CR\PRUEBA1, PRUEBA1

                                              ADMIN, EVOLCON-CR\PRUEBA2, PRUEBA2   

                                           

                                          ];

                                           

                                          Section Application;

                                           

                                           

                                          SHEET_ACCESS:

                                          LOAD * INLINE [

                                              USER, SHEET1, SHEET2

                                              CARLOSREYES, 1, 1

                                              PRUEBA1,  1, 0

                                              PRUEBA2,  0, 1   

                                          ];

                                           

                                          So, I ran the distribution task again, which by the way distributes to NAMED USERS list (PETER), and I still have the same problem.

                                           

                                          If I keep "Strict Exclusion" disabled all users can go into the app but the three use the CARLOSREYES profile, so the three users are able to see SHEET1 and SHEET2:

                                           

                                          CARLOSREYES

                                           

                                          PRUEBA1

                                           

                                          PRUEBA2

                                           

                                           

                                           

                                          IF I replace the CARLOSREYES profile to use " * " instead of " 1 " , all users, including CARLOSREYES can only see SHEET1:

                                           

                                          Section Access;

                                           

                                          SECURITY:

                                          LOAD * INLINE [

                                             ACCESS, NTNAME, USER 

                                              ADMIN, EVOLCON-CR\CARLOSREYES, CARLOSREYES 

                                              ADMIN, EVOLCON-CR\PRUEBA1, PRUEBA1

                                              ADMIN, EVOLCON-CR\PRUEBA2, PRUEBA2   

                                          ];

                                           

                                          Section Application;

                                           

                                          SHEET_ACCESS:

                                          LOAD * INLINE [

                                              USER, SHEET1, SHEET2

                                              CARLOSREYES, *, *

                                              PRUEBA1,  1, 0

                                              PRUEBA2,  0, 1   

                                          ];

                                           

                                          CARLOSREYES

                                           

                                          PRUEBA1

                                           

                                          PRUEBA2

                                           

                                           

                                          And If I enable "Strict Exclusion" , in both scenarios with " * " or " 1 ", only CARLOSREYES can go into the app. The users PRUEBA1 and PRUEBA2 get the "YOU DON'T HAVE ACCESS TO THIS DOCUMENT" window.

                                           

                                          By the way... the condition for sheets to be enabled are the next:

                                           

                                          SHEET1 : =Sum(SHEET1)=1

                                          SHEET2:  =Sum(SHEET2)=1

                                           

                                           

                                          So I have no idea of what's wrong...

                                           

                                          Thanks a lot for your help !

                                            • Re: Sheet Security with Section Access
                                              Bill Britt

                                              Carlos,

                                               

                                              Sent you a private email

                                               

                                              Bill

                                                • Re: Sheet Security with Section Access
                                                  Carlos Reyes

                                                  Bill,

                                                   

                                                  Your document works as expected, so I don't know why mine isn't working. The only doubt/difference I have regarding your scenario is that it is based on USERID and not in NTNAME. Also, I don't know if you distribuited your app with publisher... I suspect that my problem comes from the distribution but I don't why...

                                                   

                                                  Thanks.

                                                    • Re: Sheet Security with Section Access
                                                      Peter Cammaert

                                                      Well the funny thing is that everything is working as expected (even "strict exclusion") if we  assume that the application is correctly configured but QVS has problems identifying the user for Section Access.

                                                       

                                                      If I look at all those screenshots, it seems that section access always comes up with the same profile CARLOSREYES, whether OSUser() reports some other user or not. And that means that the application, upon being opened, is always presented by QVS with the same account, probably the service account.

                                                       

                                                      Could this be an entirely different problem? With AD Queries or with the configured DSC?

                                                       

                                                      If you post the last version of the application here, I'll test it with our own publisher. Just to make sure that it isn't application-design-related.

                                                       

                                                      Peter

                                                      • Re: Re: Sheet Security with Section Access
                                                        Bill Britt

                                                        Hi Carlos,

                                                         

                                                        If you tested on my server it is using both NTname and UserID. Attached is the document and you will have to user admin to get into it. I did change the script from what you had. When you look at the script my service account for publisher is qvpub.

                                                         

                                                        Bill

                                                          • Re: Re: Re: Sheet Security with Section Access
                                                            Carlos Reyes

                                                            Bill and Peter,

                                                             

                                                            After reviewing my document based on Bill's document I realized my horrible mistake. It was right in front of me but it seems my mind was blind to the obvious. So, I realized that, as both said, everything was working as expected and I had to put a blank value into CARLOSREYES profile for Section Access and delete CARLOSREYES record from the data table. So, this is the code that worked:

                                                             

                                                            Section Access;

                                                             

                                                            SECURITY:

                                                            LOAD * INLINE [

                                                              ACCESS,    NTNAME,                    USER

                                                                ADMIN,    EVOLCON-CR\CARLOSREYES,  

                                                                USER,    EVOLCON-CR\PRUEBA1,        PRUEBA1

                                                                USER,    EVOLCON-CR\PRUEBA2,        PRUEBA2

                                                             

                                                            ];

                                                             

                                                            Section Application;

                                                             

                                                            SHEET_ACCESS:

                                                            LOAD * INLINE [

                                                                USER,            SHEET1,    SHEET2

                                                                PRUEBA1,          1,            0

                                                                PRUEBA2,          0,            1

                                                            ];

                                                             

                                                            I want to thank you both for helping to me to realize this dumb error.

                                                             

                                                            Although, this message is the real asnwer to what I needed, Bill's document taught to me another and more complete method to do this kind of requiriment. So, the answer goes to him.

                                                             

                                                            Thanks a lot !