topic Re: Section Access Help - Admin not seeing all data in QlikView

Section Access Help - Admin not seeing all data

Thu, 25 Oct 2012 11:51:02 GMT

Good Afternoon All,

Please could someone help me with the following. I am trying to implement Section Access, here is my code:

Section Access;

LOAD

UPPER(ACCESS) AS ACCESS,

UPPER(HOME) AS NTNAME,

UPPER(DEPARTMENT) AS REGION;

SQL

SELECT

UP.USERTEXT4 AS ACCESS,

UP.USERTEXT3 AS HOME,

U.DEPARTMENT AS DEPARTMENT

FROM sysdba.USERINFO AS U

INNER JOIN sysdba.USERSECURITY AS US

ON U.USERID = US.USERID

INNER JOIN sysdba.USERPROFILE AS UP

ON U.USERID = UP.USERID;

And my Application code is:

Section

Application;

LOAD * INLINE [

REGION, Countryxx

UK, UK

DENMARK, Denmark

SCANDINAVIA, Denmark

SCANDINAVIA, Norway

SCANDINAVIA, Sweden

NORWAY, Norway

SWEDEN, Sweden

GERMANY, Germany

SPAIN, Spain

PORTUGAL, Portugal

IBERIA, Spain

IBERIA, Portugal

FRANCE, France

BELGIUM, Belgium

HOLLAND, Holland

LUXEMBOURG, Luxembourg

BENELUX, Belgium

BENELUX, Holland

BENELUX, Luxembourg

USA, USA

CANADA, Canada

AUSTRALIA, Australia

AUSTRALIA, New Zealand

AUSTRALIA, Singapore

NEW ZEALAND, New Zealand

SINGAPORE, Singapore

];

For any user it's working fine, but I want the admin to see everything despite the region they are assigned to. I am sure I have done this before and got it working, please can someone tell me how to fix this and tell me why it isn't working.

Kind Regards,

Miles

Re: Section Access Help - Admin not seeing all data

hic — Thu, 25 Oct 2012 12:17:35 GMT

The ADMIN must be mapped against all regions, and it is not possible for me to see in your post if he is.

A generic way to map an admin to all data can be found on http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

HIC

Re: Section Access Help - Admin not seeing all data

Thu, 25 Oct 2012 13:41:18 GMT

Hi Henric,

I will try to be clearer, my section access table has ACCESS, NTNAME, and REGION fields. the data for example looks like this:

Admin, Dualwise\tracey.roberts, UK

User, Dualwise\miles.newey, Norway

When I log into the dashboard I only see Norway, which is correct, but when Tracey is logging in she is only seeing UK, but as an admin, she should see all regions, but is not.

Are you saying with your post, that Tracey has to have all regions against her, effectively doubling up her rows so

Admin, Dualwise\tracey.roberts, UK

Admin, Dualwise\tracey.roberts, Norway

Admin, Dualwise\tracey.roberts, Sweden

I am really stuck here as this doesn't make sense,

Kind Regards,

Miles

Re: Section Access Help - Admin not seeing all data

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

Yes, you need to double up her rows. But it can be done in a smart way:

Keep the left table in Section Access, and keep the right one in data. When you create the right one, you do it through two consecutive Load statements:

Load '<ALL>' as %REGION, REGION From Regions ;

Load REGION as %REGION, REGION From Regions ;

HIC

Re: Section Access Help - Admin not seeing all data

charlotte_qvw — Wed, 04 Dec 2013 17:00:57 GMT

Thanks Henric, this helped me today with an issue

Re: Section Access Help - Admin not seeing all data

Wed, 04 Dec 2013 17:42:00 GMT

Hi Henric, Can we use the * for Admin user id's like below ?

ACCESS NTNAME REGION

ADMIN GF\QVSERVICE *

USER GF\ABCDE USA

The above one is correct or ?

Re: Section Access Help - Admin not seeing all data

hic — Thu, 05 Dec 2013 07:15:03 GMT

The logic is tricky here...

First, if you use the star * , then this will be matched against REGIONs in that table, i.e. USA. But it will not match other REGIONs found in data. So, an ADMIN will see USA only.

However if you leave REGION blank for the ADMIN, the REGION will not match anything. This (together with strict exclusion) will normally lead to that the user cannot open the file. But for an ADMIN, it will lead to full access, i.e. no reduction at all. So, an ADMIN will see all REGIONs.

HIC

Re: Section Access Help - Admin not seeing all data

Wed, 18 Dec 2013 14:29:57 GMT

Hi Henric

I am stuck in a similar situation but seeing different results.

Section Access;

LOAD * INLINE [
    ACCESS, USERID, PASSWORD, DEPARTMENT
    ADMIN, ADMIN, ADMIN,
    ADMIN, M09000, M09000,
    USER, M09849, M09849, BI
    USER, M01122, M01122, IT
];

Section Application;
LOAD * INLINE [
USERID, DEPARTMENT
    M09849, BI
    M01122, IT
];

I have user M0900 as an ADMIN with no specified department (we would like him to see all departments, listed and unlisted). When I run it through the council the two USERIDs with 'User' level access are seeing the respective departments but the M0900 user is blocked from gaining access to the dashboard.

Do you have any ideas as to what I could have done wrong?

Thanks!

Re: Section Access Help - Admin not seeing all data

hic — Wed, 18 Dec 2013 15:36:49 GMT

As I said, the logic is tricky here...

The algorithm is (if I remember correctly...):

Try to reduce according to the value in Section Access

If Success Then

Return this application

Else

If USER='ADMIN' or NOT STRICT_EXCLUSION Then

Return application unreduced

Else

Return 'Access Denied'

You have <blank> in DEPARTMENT, so the reduction attempt will not succeed. The second IF-clause gives access to your ADMIN but not to your USERs. So, both ADMIN and M09000 should see all data.

But there is an additional twist: You can only log on as ADMIN if you open a physical file with QlikView Desktop. If you access the file via QlikView Server, you are always considered USER. So, if M09000 opens it on a server, he will be denied access.

HIC

Re: Section Access Help - Admin not seeing all data

Wed, 18 Dec 2013 15:41:53 GMT

Thanks Henric,

So what I gather from your explanation, if we have two 'super users' who are supposed to have access to ALL departments we would have to list each of them with all 20+ departments at a USER level?

There is no way to leave it generic so when we add a new department to the organization it will automatically added to those two users access levels?

Re: Section Access Help - Admin not seeing all data

hic — Wed, 18 Dec 2013 16:00:39 GMT

There is always a way ...

If you start by defining a symbol '<ALL>', for example through

MapDepartment:

Mapping

Load distinct '<ALL>' as Symbol, Concat(distinct Department,',') as Departments From Departments ;

...then you can use this in your authorization table

LOAD

ACCESS,

USERID,

PASSWORD,

Trim(Subfield(Applymap('MapDepartment',DEPARTMENT),',')) as DEPARTMENT

INLINE [

ACCESS, USERID, PASSWORD, DEPARTMENT

ADMIN, ADMIN, ADMIN,<ALL>

ADMIN, M09000, M09000,<ALL>

USER, M09849, M09849, BI

USER, M01122, M01122, IT];

The Applymap() function will replace '<ALL>' with 'BI,IT,X,Y,Z' or whichever departments you have.

The Subfield() function will create an implicit loop, so that if you have 3 values, you will get three records.

HIC

Re: Section Access Help - Admin not seeing all data

Thu, 19 Dec 2013 18:44:11 GMT

Thanks, we were able to work your logic into our existing work and get it up and running!