7 Replies Latest reply: Jun 1, 2011 6:26 AM by Carlos Ramos RSS

    Help with Acces Section / Data Reduction

    Carlos Ramos
      I'm trying to control acces and set data reduction as per windows user logged on using the following script:
      ODBC
      CONNECT TO AS400

      Section Access;

      SQL
      SELECT
      access,
      userid,
      password,
      ntname,
      acesstype
      FROM absqvw.qvaccess;

      Section Application;

      SQL
      SELECT
      accesstype,
      zona
      FROM absqvw.zonasacc
      The tables look like this:
      ACCESSUSERIDPASSWORDNTNAMEACCESSTYPE
      ADMINqvadminqvadmin
      ADMINdelta\carlosALL
      ADMINdelta\jmanuelALL
      ADMINdelta\marcALL
      USERdelta\amileALL
      USERdelta\jordiALL
      USERdelta\joseALL
      USERdelta\jsantafeALL
      USERdelta\qvbioBIO
      USERdelta\qvcatCAT
      USERdelta\qvespESP
      USERdelta\qvexpEXP
      USERdelta\qvmadMAD
      USERdelta\qvmurMUR
      USERdelta\qvsvqSVQ
      USERdelta\qvvalVAL
      USERdelta\raulALL
      ACCESSTYPEZONA
      ALL
      BIO401
      BIO402
      BIO403
      BIO404
      CAT101
      CAT102
      CAT103
      CAT106
      CAT108
      CAT109
      CAT111
      ESP101
      ESP102
      ESP103
      ESP106
      ESP108
      ESP109

      I would expect that any user loged in as per column NTNAME would be able to open the document (without entering any credentials) and be restricted to records with a given "ACCESSTYPE"

      In case the loggin of the user is not found in NTNAME then QV should ask for a username and a password and I could use qvadmin qvadmin to access the document with no restrictions.
      As said, this is what I would expect but unfortunately, the actual result is that I'm not even able to open the document.
      What's wrong with me?
      I think a lot of beginners (like me) would appreciate if some one could explain how to preceed in a case like that since the explanations in the reference manual are very poor.
        • Re: Help with Acces Section / Data Reduction

          Hi Carlos,

           

          I think u have to specify the restrictions in section application like if i want to restrict finace dept users except with id 23 and i want to allow all the marketing people then i'll use my script like this ...

           

          section application;

          select * inline from

          userid,dept,pass

          23,finance,allowme

          *,marketing,allowus

          from qvaccess.qvw

           

          may be the script have some typo's but try in this i mean using specifications of restrictions to restrict using section appliaction like userid/ntname etc. may be it will work

           

          thanks a lot

           

          bradd

          • Re: Help with Acces Section / Data Reduction
            Carlos Ramos

             

            Oh yes. Capitals was one of the issues. I needed to adjust 3 things:

             

             

             

            - Field names in capitals
            - Also capitals for the user names in the external access table
            - This external access table has fixed length fields so there are spaces after the user names which need to be removed (Rtrim).

             

             

             

            Now the script is this:

             

            Section Access;

             

            SQL

            SELECT
            Rtrim(access) as ACCESS,
            Rtrim(userid) as USERID,
            Rtrim(password) as PASSWORD,
            Rtrim(ntname) as NTNAME,
            ACCESSTYPE
            FROM absqvw.qvaccess;

            Section Application;

            SQL
            SELECT
            accesstype,
            zona
            FROM absqvw.zonasacc

             

             

             

            and the access table looks like this:

             

             

             

            ACCESS

            USERID

            PASSWORD

            NTNAME

            ACCESTYPE

            ADMIN

            DELTA\CARLOS

            *

            ADMIN

            DELTA\JMANUEL

            *

            ADMIN

            DELTA\MARC

            *

            ADMIN

            QVADMIN

            QVADMIN

            *

            USER

            DELTA\AMILE

            *

            USER

            DELTA\JORDI

            *

            USER

            DELTA\JOSE

            *

            USER

            DELTA\JSANTAFE

            *

            USER

            DELTA\QVBIO

            BIO

            USER

            DELTA\QVCAT

            CAT

            USER

            DELTA\QVESP

            ESP

            USER

            DELTA\QVEXP

            EXP

            USER

            DELTA\QVMAD

            MAD

            USER

            DELTA\QVMUR

            MUR

            USER

            DELTA\QVSVQ

            SVQ

            USER

            DELTA\QVVAL

            VAL

            USER

            DELTA\RAUL

            *

             

             

             

             

             

            The only thing still not working is the access of the QVADMIN user. I would expect that in case the current windows user is not in the table (NTNAME), it would ask for a user and password but simply says "Access denied to this Qlik View document".

             


            Anyone can figure out why the access of QVADMIN is not working?