Section Access on Sheet Level

[stabben23]

Hi,

Problem: Lets say I have 10 users from a AD-group, 5 of them should have access to a sheet.

If I use a list with there NTNAME and all other things i Section Access. The list is only include the 5 that should have access.

The problem is that the 5 that not should have access to the sheet even dont get access to the document.

This is because they are not in the list with there NTNAME, is there a way to use "all other users"?

I only know about the user that should have access, the other users is "hide" in AD-groups.

This is my settings in document setting.

[Bill_Britt]

Hi,

This will not work if you are using groups. When using groups it would mean all members of the group would have access.

Bill

[Anonymous]

You can also use the sheet properties and select Condition show.

here you can create the condition based on the users.

Rest all will not be able to view.

Try and let me know

[stabben23]

Hi Jebamalai,

thats exact the solution we are running today. This is a very consultant friendly solution but the customer will have possibility to manage this from a list themself. So I moved all user from the Conditional show into the list. But the problem is when user who's not in that list dont get access to the application.

[stabben23]

Thanks Bill,

Do u have a smart solution on this problem? We have tried with 2 different AD-groups but that doesent work either.

I have now try to use a connection to AD but dont find OSUser field in AD tables, only the full name.

[#6]

Hi!

Sounds interesting, the "Condition show"-solution. Do you have an example for how to write the condition there?

I'm quite new in QV and still have some problem with their syntax.

As a admin, which solution is easiest to admin? Sheet-condition-based or an table in the db (if i got that to work).

[#7]

Hi Staffan,

have a look at this thread, should help with sheet level section access

http://community.qlik.com/docs/DOC-4435

hope that helps

Joe

[avinashelite]

Hi Staffan,

Follow the below approach.

First load 10 user data from the DB or the actual source with NTNAME

then for the sheet access load the list of user who want access from an excel with the value 1 and in section application use this to get the sheet access. Please try the below code.

ODBC CONNECT TO database

SECTION Access;

LOAD Upper(if(ADMINISTRATOR='true','ADMIN','USER')) as ACCESS,

    Upper("EMPLOYEE_USERID") as NTNAME

    ;

SQL SELECT *

FROM  table ';

SECTION Application;

LOAD Upper(USERID)as NTNAME,

     SHEET

FROM

excel;

now use this sheet condition in the sheet properties to hide and show on the basis of the user login

[stabben23]

Hi All,

I actually solved this, I will try to explain how I do this.

First of all, I have 14 different AD groups that I reduce and distribute this application to. Some of the user should have access to a specific sheet. It could be from 0-all in these 14 AD groups.

The first thing I did was to read all AD members from all 14 AD groups into a QVD file. I read this direct from AD database. I flag these users with a '0' by default as sheetaccess. We also create a AD group that should have access to the sheet. I read these from AD database and flag them as '1' as sheetaccess. In the script where I did this I now do some join,concatenate and group by so I got a distinct member with either a 0 or 1, because all sheetmebers will have both 0 and 1 first. In the script I also tell witch of the users that should be Admin or Users. I use that in the Section Access later. Now I have a qvd file that contain all my User, I now read this as a Section Access:

Section Access;
LOAD
  ACCESS, //User or Admin
     upper('Domain\'& NTNAME) as NTNAME

    
FROM
$(QVDPath)SheetAccess.qvd
(qvd)
where 1=1;

SECTION Application;

SheetSequrity:
LOAD

upper('Domain\'&NTNAME) as NTNAME,
    SHEET,  //1 or 0
FROM
$(QVDPath)SheetAccess.qvd
(qvd)
where 1=1 and NTNAME <> 'QlikService';

QlikviewService is the account that runs the qlikview services. When the distribution from QMC is done Qlikview "belives" that this is the only account that should access the application. You can find this in an other tread here at the community.

In the Document Properties --> Opening i tick Initial Data Reduction Based on Section Access and on the sheet conditional on the specific sheet  =sum(if(upper(OSUser())=NTNAME,SHEET))

Now when a user is access the application (the distributed one he has access to) it compare the NTNAME and OSUser and see if he is a 0 or 1 and gives access to the sheet.

[#10]

Thanks for information!

This is how i solved it. It seems to work, so far at least. I have few users to handle.

TABLES IN XLS
QV_Ek_Per

ACCESS	NTNAME	ANS_ID	BH_GRUPP
ADMIN	\mario00	1100	BH_ALL

ANS_ID - different key-values for what they can see in QV. Multiple AND_ID for 1 user needs multiple rows.
BH_GRUPP - rules of what sheets they have access to (according to table QV_Ek_Per_Sheet).
All rows for 1 user must have the same BH_GRUPP-value.

QV_Ek_Per_Sheet

BH_GRUPP	SH02	SH09	SH07	SH12	SH19	SH20
BH_ALL	1	1	1	1	1	1
BH_EJ_MV	1	1	1	1	1	0

SH02 etc is COLS named after Sheet-ID, just so it will be easier to know what it is.
1= Access to sheet, 0= hidden sheet
All USERS just have 1 BH_GRUPP in QV_Ek_Per even if thay multiple rows with different ANS_ID

SCRIPT

SECTION ACCESS;

LOAD
UPPER(ACCESS) AS ACCESS,
UPPER(NTNAME) AS NTNAME,
UPPER(ANS_ID) AS ANS_ID,
UPPER(BH_GRUPP) AS BH_GRUPP
FROM
[...\Excelfiler\Behorighet2.xls]
(biff, embedded labels, table is QV_Ek_Per$);

SECTION APPLICATION;

BH_GRUPP:
Load BH_GRUPP,
SH02,
SH09,
SH07,
SH12,
SH19,
SH20
FROM
[...\\Excelfiler\Behorighet2.xls]
(biff, embedded labels, table is QV_Ek_Per_Sheet$);

SHEET PROPERTIES (this example from Sheet SH20)
Show sheet
Condition marked with condition   “=SH20=1”

So, next week I will try it live, would be intresting to see if it works ous as I wish. 😃