9 Replies Latest reply: Nov 11, 2010 4:18 AM by Simon Koenen RSS

    Section Access with MySQL Database

    Simon Koenen

      Hi,

      I would like to create a section access for my QlikView document.

      But it does not work...

       

      Here is my source code:

      ----------------

      Section Access;
      SQL SELECT ACCESS,
      NTNAME,
      USER_NAME
      FROM `qvr_vgv`.`tbl_section_access`;


      Section Application;

      user_allocation:
      SQL SELECT USER_NAME,
      SAPUSER
      FROM `qvr_vgv`.`tbl_user_allocation`;

      ----------------

      I think the problem are the [ that have to be around the fields access and ntname.

      I have tried to rename the db fields with the brackets as well as renaming the fileds within the sql statement (ACCESS AS [ACCESS]) but this does not work either.

       

      If you have any idea please answer.

      Thank you

       

      Kind regards

      Simon Koenen

        • Section Access with MySQL Database
          Karl Pover

          What part doesn't work exactly? The script doesn't run, the user can't login or the data is not filtered properly?

          With NTNAME make sure you include the domain or include the NTDOMAINSID field. Also, a step that many developers miss when filtering data with section access is ticking the checkbox to enable Initial Data Reduction based on Section Access in the Opening tab in Document Settings.

          Regards.

          • Section Access with MySQL Database
            Miguel Angel Baeyens de Arce

            Hello Simon,

            First of all, I'd give a try to

             

            Section Access;LOAD UPPER(ACCESS) AS ACCESS, UPPER(NTNAME) AS NTNAME, UPPER(USER_NAME) AS USER; // USER is required to ask the user for additional login, otherwise, leave it as USER_NAME //UPPER(PASSWORD) AS PASSWORD // Uncomment it if you are going to use the USER field, otherwise, delete it SQL SELECT ACCESS, NTNAME, USER_NAMEFROM `qvr_vgv`.`tbl_section_access`; Section Application; user_allocation:NOCONCATENATE LOAD *;SQL SELECT USER_NAME, SAPUSERFROM `qvr_vgv`.`tbl_user_allocation`;


            Hope that helps!

              • AW:Re: Section Access with MySQL Database
                Simon Koenen

                First of all thank you for your tips I have tried to change my statements in the way Miguel A. Baeyens has mentioned but it still does not work.

                Here are a few more details. I hope this will help you understanding my problem:

                The MySQL Database looks like this:

                tbl_section_access:

                ACCESS NTNAME USER_NAME
                ADMIN VAILLANT\skoenen SKOENEN
                ADMIN VAILLANT\bjbraune BJBRAUNE

                 

                tbl_user_allocation:

                USER_NAME SAPUSER
                SKOENEN KOENENS
                BJBRAUNE BRAUNEB

                My main objective is, that the user opens the document and if he has a valid authorization he will only see the data he is authorized for. In my case I need the connection between the Windows User and the SAP User. If I login the system has to limit the user allocation table to my sapuser and if my collegue logs in he will only see his sapuser.

                If I use an Excelfile containing the necessary data (both tables) it works but if I use the same table structure within a mysql database the fields are empty and after closing the qv file I am not able to open it anymore because the message appears that I am not authorized. I think this problem has something to do with the brackets around the field names but if I change my sql statement within the section access and put a load statement in the head of it this does also effects the authorization error.

                I hope this makes everything a bit easier to understand.

                  • AW:Re: Section Access with MySQL Database

                     

                    Hi,

                     

                    You need to reorganize the Section Access table like to contain only fields NTNAME and the desired restriciton field, ex SAPUSER .

                    Later on, one table contains a field called SAPUSER with uppercase field name, and uppercase content

                     

                     


                    Section Access;

                    LOAD
                    UPPER(t1.NTNAME) as NTNAME,
                    UPPER(t2.SAPUSER) as SAPUSER;
                    SQL select .. from `qvr_vgv`.`tbl_section_access` t1 inner join `qvr_vgv`.`tbl_user_allocation` t2 on t1.USER_NAME = t2.USER_NAME


                    Section Application;
                    user_allocation:
                    ..
                    Supper(SAPUSER) as SAPUSER
                    ..


                     

                    -Alex

                     

                     



                    • AW:Re: Section Access with MySQL Database
                      Miguel Angel Baeyens de Arce

                      Hello Simon,

                      I see some things here. What Alexandru says in the post above seems to be related. If you are not able to log into the document, I assume you are using "Reduce Data..." and "Strict Exclusion", then it's not a user/password issue, since it's not asking for the password three times, but avoiding you to open the document. If that's the case, be sure that a SAPUSER exists in your datamodel further than just the Auth table.

                      My guess is that SAPUSER field is not present in any other table in the datamodel, so when it asks for login and password, then QlikView reduces according to that user, the document becomes empty, so you cannot access it.

                      If you want your users only see some information (records) but not all of them, then you should add in some tables SAPUSER as field. Take the following example. Say your users are sales agents, that are only allowed to see information about their own clients, but not other sales agents' clients. SAPUSER field would correspond to field "Sales Agent" in "Clients" table, so any client will have a field with that information, so any user will be able to login.

                      Is that right?

                        • AW:Re: AW:Re: Section Access with MySQL Database
                          Simon Koenen

                          Hi,

                          I have removed the section access and the tables are connected by the field user_name in the data model. So a connection between the tables is possible..

                          Furthermore I have added some more tables with dummy data and deactivated the strict exclusion but I still get the no access message on restarting the document. I have replaced the user_name with the sapuser to remove the connection table (user allocation). What confuses me is the fact that it works if I use a csv-flatfile or an internal table with the same data structure.

                          My source code looks like this:

                          ODBC CONNECT TO QV_VGV_MYSQL;


                          Section Access;
                          SQL SELECT *
                          FROM `qvr_vgv`.`tbl_section_access`;

                          Section Application;
                          SQL SELECT region,
                          SAPUSER,
                          value
                          FROM `qvr_vgv`.`tbl_auth_dummy_data_1`;

                          SQL SELECT costs,
                          SAPUSER
                          FROM `qvr_vgv`.`tbl_auth_dummy_data_2`;

                           

                          and my table content:

                          [access table]

                          ACCESS NTNAME SAPUSER
                          ADMIN VAILLANT\bjbraune brauneb
                          ADMIN VAILLANT\skoenen koenens

                           

                          [dummy table]

                          SAPUSER costs
                          brauneb 7343464
                          koenens 34349976

                           

                          [dummy table]

                          SAPUSER region value
                          koenens de 432433
                          koenens en 7575
                          brauneb de 76731
                          brauneb nl 654687