topic Help with Acces Section / Data Reduction in QlikView

Help with Acces Section / Data Reduction

Tue, 31 May 2011 15:46:51 GMT

I'm trying to control acces and set data reduction as per windows user logged on using the following script:

ODBC

CONNECT TO AS400

Section Access;

SQL
SELECT
access,
userid,
password,
ntname,
acesstype
FROM absqvw.qvaccess;

Section Application;

SQL
SELECT
accesstype,
zona
FROM absqvw.zonasacc

The tables look like this:

ACCESS	USERID	PASSWORD	NTNAME	ACCESSTYPE
ADMIN	qvadmin	qvadmin
ADMIN			delta\carlos	ALL
ADMIN			delta\jmanuel	ALL
ADMIN			delta\marc	ALL
USER			delta\amile	ALL
USER			delta\jordi	ALL
USER			delta\jose	ALL
USER			delta\jsantafe	ALL
USER			delta\qvbio	BIO
USER			delta\qvcat	CAT
USER			delta\qvesp	ESP
USER			delta\qvexp	EXP
USER			delta\qvmad	MAD
USER			delta\qvmur	MUR
USER			delta\qvsvq	SVQ
USER			delta\qvval	VAL
USER			delta\raul	ALL

ACCESSTYPE	ZONA
ALL
BIO	401
BIO	402
BIO	403
BIO	404
CAT	101
CAT	102
CAT	103
CAT	106
CAT	108
CAT	109
CAT	111
ESP	101
ESP	102
ESP	103
ESP	106
ESP	108
ESP	109

I would expect that any user loged in as per column NTNAME would be able to open the document (without entering any credentials) and be restricted to records with a given "ACCESSTYPE"

In case the loggin of the user is not found in NTNAME then QV should ask for a username and a password and I could use qvadmin qvadmin to access the document with no restrictions.

As said, this is what I would expect but unfortunately, the actual result is that I'm not even able to open the document.

What's wrong with me?

I think a lot of beginners (like me) would appreciate if some one could explain how to preceed in a case like that since the explanations in the reference manual are very poor.

Re: Help with Acces Section / Data Reduction

Tue, 31 May 2011 17:51:36 GMT

Hi Carlos,

I think u have to specify the restrictions in section application like if i want to restrict finace dept users except with id 23 and i want to allow all the marketing people then i'll use my script like this ...

section application;

select * inline from

userid,dept,pass

23,finance,allowme

*,marketing,allowus

from qvaccess.qvw

may be the script have some typo's but try in this i mean using specifications of restrictions to restrict using section appliaction like userid/ntname etc. may be it will work

thanks a lot

bradd

Help with Acces Section / Data Reduction

prieper — Tue, 31 May 2011 19:29:10 GMT

Have you tried the fieldnames in capitals?

Peter

Re: Help with Acces Section / Data Reduction

Wed, 01 Jun 2011 07:43:46 GMT

Oh yes. Capitals was one of the issues. I needed to adjust 3 things:

- Field names in capitals
- Also capitals for the user names in the external access table
- This external access table has fixed length fields so there are spaces after the user names which need to be removed (Rtrim).

Now the script is this:

Section Access;

SQL

SELECT
Rtrim(access) as ACCESS,
Rtrim(userid) as USERID,
Rtrim(password) as PASSWORD,
Rtrim(ntname) as NTNAME,
ACCESSTYPE
FROM absqvw.qvaccess;

Section Application;

SQL
SELECT
accesstype,
zona
FROM absqvw.zonasacc

and the access table looks like this:

ACCESS	USERID	PASSWORD	NTNAME	ACCESTYPE
ADMIN			DELTA\CARLOS	*
ADMIN			DELTA\JMANUEL	*
ADMIN			DELTA\MARC	*
ADMIN	QVADMIN	QVADMIN		*
USER			DELTA\AMILE	*
USER			DELTA\JORDI	*
USER			DELTA\JOSE	*
USER			DELTA\JSANTAFE	*
USER			DELTA\QVBIO	BIO
USER			DELTA\QVCAT	CAT
USER			DELTA\QVESP	ESP
USER			DELTA\QVEXP	EXP
USER			DELTA\QVMAD	MAD
USER			DELTA\QVMUR	MUR
USER			DELTA\QVSVQ	SVQ
USER			DELTA\QVVAL	VAL
USER			DELTA\RAUL	*

The only thing still not working is the access of the QVADMIN user. I would expect that in case the current windows user is not in the table (NTNAME), it would ask for a user and password but simply says "Access denied to this Qlik View document".

Anyone can figure out why the access of QVADMIN is not working?

Re: Help with Acces Section / Data Reduction

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 07:51:18 GMT

Hello,

The ADMIN access is always overridden when you access the document via client (either Desktop - Open in Server, Plugin or AJAX).

Do you have your IE browser configured to pass your current credentials to the server?

In any case, you need to create a NTNAME user for QVADMIN instead of USERID and PASSWORD, since if a NTNAME record exists in Section Access, they will be matched automatically, ignoring any other USERID and PASSWORD combinations.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with Acces Section / Data Reduction

Wed, 01 Jun 2011 09:58:14 GMT

Thank you for your reply Miguel Angel.

What I'm pretending is to be able to locally open this document, in any desktop (even out of our NT domain) with full authority using QVADMIN user and QVADMIN password (credentials asked for by Qlikview upon trying to open the document).

Regards,

Carlos

Re: Help with Acces Section / Data Reduction

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 10:09:57 GMT

Hello Carlos,

Again, anytime you open your session in your laptop, there are some NT credentials. Those may be either DOMAIN\DomainUserName or COMPUTER\LocalUserName. If you section access has the field NTNAME your credentials will automatically checked with the ones in Section Access. Should they don't match, you will be denied access.

In other words, the following line should grant you access locally

ADMIN QVADMIN QVADMIN * *

Using the "*" under the NTNAME field.

Anyway, the most secure way to grant you access is get rid of USERID and PASSWORD fields and use all your NTNAMEs (both local and domain).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Help with Acces Section / Data Reduction

Wed, 01 Jun 2011 10:26:37 GMT

ok. This is absolutely clear to me now.

Thanks again for your help.