10 Replies Latest reply: Oct 25, 2012 5:24 PM by Henric Cronström RSS

    section access and application access

      Hi everybody...

       

      Im not sure i quite get this topic fully..

       

      Lets take an example:

      1. I want my ADMIN account to see all - Thats easy enough.

      2. I want one of my shops only to see data about it self - How do i do that?

      3. What defines where my "security is" Is it my tablename, or listbox name, or my "selcted field to make the listbox" (I have used the last one as you can see)

       

      Example:

       

      STAR is ALL;

      Section Access;

      LOAD * INLINE [

          ACCESS, USERID, PASSWORD, USERKEY, SHOPNAME

          ADMIN,  ADMIN,  ADMIN,    ALL,     ALL

          USER,   Customer1, Customer1,   ALL,     ALDI

          USER,   Customer2, Customer2,   ALL,     COLES

      ];

       

      USERKEY is my UsersName (I know that key is dumb, but i dont have any names in data yet, so i use my key)

      SHOPNAME contains all my shops

       

      So if i want Customer1 to only see ALDI what should i do?

       

      Should i make a secction application looking like this;

       

      Load * Inline [

      SHOPNAME,

      ALDI,

      COLES

       

      ????

       

      Thank you in advanced

        • Re: section access and application access
          Henric Cronström

          First, I strongly suggest you use NTNAME instead of USERID and PASSWORD. NTNAME is mapped against Windows users and groups, or against the authenticated user if you use ticketing on a web server. So Section Access then becomes a tool for authorization only.

           

          Secondly, you should use one single reducing field, in your case SHOPNAME. This field should exists in both Section Access and in the data. But you need to map the 'ALL' symbol to all shops. This you can do using generic keys. See more on

           

          http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

           

          HIC

            • Re: section access and application access

              Surely it can be done a lot easier than that - That looks complicated and to much backend development in frontend tool.

               

               

              I have Keys from all dimensions in my fact.

               

              But basically i could just use an conditional on every sheet (i have to make it twice though then) but that is a easy solution..

               

              But i simply cannot believe that u just cannot say ShopName = Aldi - and then it only shows aldi???

               

              Theres gotta be a way

               

              btw.

               

              My SHOPNAME in Section Application is linked to my DimShop - where i have a key called ShopKey which is linked to my fact. So it should work.......

                • Re: section access and application access
                  Henric Cronström

                  But this is easy!

                   

                  You write "But i simply cannot believe that u just cannot say ShopName = Aldi - and then it only shows aldi???". That is exactly what you should do. The twist is to make the admin see everything. For that, you need the generic keys and a bridge table that looks like:

                  ShopNames.png

                  Now you link %SHOPNAME to the authorization table in Section Access and SHOPNAME to the data. And you're done.

                   

                  The blog post describes a situation with two reducing fields. And this thread describes a case with only one reducing field, so this is less complex.

                   

                  HIC

                    • Re: section access and application access

                      But the admin is always able to see ALL ??

                       

                      I did like this:

                       

                      Star is ALL;

                      Section Access;

                      LOAD * INLINE [

                          ACCESS, USERID, PASSWORD

                          ADMIN, ADMIN, ADMIN

                          USER, ALDI, ALDI

                          USER, COLES, COLES

                      ];

                      Section Application;

                       

                       

                      LOAD * INLINE [

                      USERID, ShopName

                      ALDI, Aldi

                      COLES, ALL

                       

                       

                      ];

                       

                      This allows only Aldi to see Aldi related data, and shows only Coles related data..So i dont see the point in using your idea ?? Unless yes u have more date you need to reduct, then i can see the point in having it more easy to edit and overview over which tables(data) you have given access to see

                      • Re: section access and application access

                        Furthermore, i am not sure i totally understand you.

                         

                        If I have these access account for an example:

                         

                        Star is ALL;

                        Section Access;

                        LOAD * INLINE [

                            ACCESS, USERID, PASSWORD

                            ADMIN, ADMIN, ADMIN

                            USER, ALDI, ALDI

                            USER, COLES, COLES

                        ];

                        Section Application;

                         

                        Then i have some questions for you:

                         

                        1. Should i then make the Section Access as a table ? For instance call it AccessDimension

                         

                        If i want a User, for instance ALDI, to only see ALDI  in my DimSHOP and  a certain AreaName in DimProduct

                         

                        Attributes: ShopName (DimShop) and AreaName (DimProduct) (It could be householding goods)

                         

                        What do i do then??

                         

                        Should i make table in excel for instance which contrains

                         

                        %ShopName, ShopName , %AreaName, AreaName

                         

                        Or should i make a table for both?

                         

                        And where should i link %ShopName and %AreaName too? Cause you want ShopName and AreaName to link to my dimensions DimShop and DimProduct right??

                         

                        Or could i just do like this:

                         

                        Star is ALL;

                        Section Access;

                         

                         

                         

                         

                        LOAD * INLINE [

                            ACCESS, USERID, PASSWORD

                            ADMIN, ADMIN, ADMIN

                            USER, ALDI, ALDI

                            USER, COLES, COLES

                        ];

                        Section Application;

                         

                         

                        DimAccess:

                        LOAD * INLINE [

                        USERID, ShopName, AreaName

                        ADMIN, ALL, ALL

                        ALDI, Aldi, Kioskvarer

                        COLES, ALL, Husholdning

                         

                         

                        ];

                         

                        But i think it will be to complicated or cause to many problems???

                         

                         

                        Hope you understand, and im sorry that i dont totally understand, im pretty new at qlikview

                          • Re: section access and application access
                            Henric Cronström

                            That was many questions...

                             

                            First, don't use the "Star is" statement. It doesn't do what you want. It will expand the star symbol to all values in that table only. But you may have shops that are not listed in the Section Access table. So, my suggested solution contains a symbol 'ALL' that I expand myself.

                             

                            But to expand that, you need the bridge table that I mentioned earlier.

                             

                            "Should i then make the Section Access as a table"

                            It is a table, but hidden. If you mean if you should label it "AccessDimension", then the answer is that it doesn't matter. But I think it is good practice to label all tables. It is easier to read...

                             

                            "If i want a User, for instance ALDI, to only see ALDI  in my DimSHOP and  a certain AreaName in DimProduct"

                            Ahh, this is when it becomes interesting. If you have several reducing fields (fields that link Section Access with the data) then I strongly suggest you do what I suggest in my blog http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization.

                            If you don't, you will encounter cases where you think QlikView doesn't work the way it should. Example:Several.png

                            This table you would interpret as "A can see the data if (Shop=X and Product=ANY) or (Shop=ANY and Product=Y)". But QlikView interprets it the other way around: "A can see data if (Shop=X or Shop=ANY) and (Product=ANY or Product=Y)". So it won't work. A will be able to see everything.

                             

                            Instead you should have a concatenated field %AUTHORIZATIONKEY that is a combination of the other two.

                             

                            Bottom line: If you want to avoid problems, you should only have one reducing field.

                             

                            Read more on http://community.qlik.com/docs/DOC-3451

                             

                            HIC

                              • Re: section access and application access

                                Thank you, i think i understand, but could you maybe give an example with my data??

                                 

                                Is it like this?

                                 

                                If i have ShopName and AreaName from DimShop and DimProduct and my FactTable called FactAll has ShopKey and ProductKey

                                 

                                First i create a new AuthTable with the values i want to reduce on, so it would kinda like this

                                 

                                SourceAuthTable:

                                LOAD

                                USERID,

                                ShopName,

                                AreaName

                                 

                                FROM ????? Cause ShopName is in my DimShop and AreaNAme in DimProduct and USERID is a hidden table?? or should i resident or concatenate? Or is it the applymap as you describe in your link??

                                 

                                And then i do like this

                                 

                                AuthTable:

                                LOAD

                                USERID,

                                ShopName &'|'& AreaName as %AuthID

                                 

                                And then i make a bridge table which is kinda like this:

                                 

                                AuthBridgeTable:

                                Load ShopName&'|'& AreaNAme as AuthID, ShopNAme&'|'& AreaNameas %AuthID  From ????;

                                     Load ShopName&'|'& AreaNAme as AuthID, ShopName&'|'&'<ANY>' as %AuthID  From ????;

                                     Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'& AreaName as %AuthID  From ????? ;

                                     Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID  From ?????;

                                 

                                or am i totally wrong?

                                  • Re: section access and application access
                                    Henric Cronström

                                    You need to create/modify three of the tables you Load in QlikView

                                    1. The authorization table (in Section Access): Use USER, ACCESS, %AUTHID (only UPPER case here)
                                    2. The transaction table in the data: Each record must belong to only one shop and only one area. If you don't have these fields there, then you need to use applymap to get them there. Use whatever fields you have in that table PLUS AuthID
                                    3. The Authorization bridge: Use %AUTHID and AuthID

                                    See also attachments.

                                     

                                    HIC

                                      • Re: section access and application access

                                        Wow amazing with so much help, that is just awesome - Thank you very much!!!

                                         

                                        Can you confirm this then?

                                         

                                        Section Access;

                                         

                                        Load

                                        ACCESSUSERIDUpper(ShopName &'|'& AreaName)as%AUTHID
                                        USERADAMA|ANY
                                        USERBETTYANY|Y
                                        ADMINXERXESANY|ANY

                                         

                                        ];

                                        Section Application;

                                         

                                        Data:

                                        Load

                                        ShopName,

                                        AreaNAme,

                                        ShopName &'|'& AreaName as AuthID

                                         

                                        APPLYMAP blabla (havent used applymap before, but i can investigate in it)

                                         

                                        AuthBridgeTable:

                                        Load ShopName&'|'& AreaName as AuthID, ShopName&'|'& AreaName as %AUTHID Resident Data;

                                        Load ShopName&'|'& AreaName as AuthID, ShopName&'|'&'<ANY>' as %AUTHID Resident Data;

                                        Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'& AreaName as %AUTHID Resident Data;

                                        Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'&'<ANY>' as %AUTHID Resident Data;

                                         

                                        Or do i also need to use map in Section Access, or even create a brand new table with userid, access and stuff in, like you have done in excel in the authoraztion sheet? Cause i dont have want data form excel, and it is from 2 different tables

                                         

                                        Iguess ANY is = ALL?

                                          • Re: section access and application access
                                            Henric Cronström

                                            Looks OK.

                                             

                                            No, you do not need to map anything in the Section Access. And you don't need to create an extra table. I created a table in Excel with USERID, ACCESS, ShopName and AreaName because I wanted you to see what the source table could look like and which fields you should load from it.

                                             

                                            All you need to do is decide where you want to keep and maintain this table: In a database? In Excel? Or as an inline in the script? Once you've done that, you need to have a Load statement inside Section Access that loads this data. And thats it.

                                             

                                            HIC