Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
Henric_Cronström
Not applicable

Re: section access and application access

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

10 Replies
Henric_Cronström
Not applicable

Re: section access and application access

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

Not applicable

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.......

Henric_Cronström
Not applicable

Re: section access and application access

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

Not applicable

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

Not applicable

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

Henric_Cronström
Not applicable

Re: section access and application access

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

Not applicable

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?

Henric_Cronström
Not applicable

Re: section access and application access

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

Not applicable

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?