topic Re: section access and application access in QlikView

section access and application access

Thu, 25 Oct 2012 11:39:17 GMT

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

hic — Thu, 25 Oct 2012 12:08:08 GMT

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

Thu, 25 Oct 2012 12:24:36 GMT

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

hic — Thu, 25 Oct 2012 16:39:53 GMT

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:

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

Thu, 25 Oct 2012 17:21:21 GMT

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

Thu, 25 Oct 2012 17:48:48 GMT

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

hic — Thu, 25 Oct 2012 18:31:26 GMT

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:

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.

HIC

Re: section access and application access

Thu, 25 Oct 2012 18:53:42 GMT

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

hic — Thu, 25 Oct 2012 19:42:16 GMT

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

The authorization table (in Section Access): Use USER, ACCESS, %AUTHID (only UPPER case here)
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
The Authorization bridge: Use %AUTHID and AuthID

Re: section access and application access

Thu, 25 Oct 2012 20:04:24 GMT

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

Can you confirm this then?

Section Access;

Load

ACCESS	USERID	Upper(ShopName &'\|'& AreaName)as%AUTHID
USER	ADAM	A\|ANY
USER	BETTY	ANY\|Y
ADMIN	XERXES	ANY\|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

hic — Thu, 25 Oct 2012 21:24:21 GMT

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