Qlik Community

QlikView Security & Governance

Discussion Board for collaboration on QlikView Security and Governance.

stabben23
Honored Contributor

Section Access on Sheet Level

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.

SA.png

This is my settings in document setting.

Tags (1)
9 Replies
Employee
Employee

Re: Section Access on Sheet Level

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

jebamalai
Valued Contributor

Re: Section Access on Sheet Level

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
Honored Contributor

Re: Section Access on Sheet Level

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
Honored Contributor

Re: Section Access on Sheet Level

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.

Not applicable

Re: Section Access on Sheet Level

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

Not applicable

Re: Section Access on Sheet Level

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

Re: Section Access on Sheet Level

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
Honored Contributor

Re: Section Access on Sheet Level

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.

Not applicable

Re: Section Access on Sheet Level

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

Community Browser