topic Re: Section Access AD user and Custom user in QlikView

Section Access AD user and Custom user

suzel404 — Sun, 24 Jul 2016 22:42:41 GMT

Hi all,

I want to set up section access in qv with custom user and domain users.

custom user are create in local qv machine and domain users are in the domain AD.

I want to set up the security where domain user access to the report and see only the country and custom user see the report with the country.

Is it possible to mix user create in local machine of qvserver and users created in Domain AD in one table ?

Is this code below is correct and optimized ?

Section Access;

LOAD

UPPER(ACCESS) AS ACCESS,

UPPER(USERID) AS USERID,

UPPER(PASSWORD) AS PASSWORD,

UPPER(COUNTRY) AS COUNTRY,

UPPER(REDUCTION) AS REDUCTION INLINE [

ACCESS, USERID, PASSWORD, NTNAME, REDUCTION

ADMIN, admin, admin, , *

ADMIN, , ,DOMAIN\adminDB, *

USER, user1, user1, ,1

USER, , ,DOMAIN\test1, 2

USER, , ,DOMAIN\test1, 1

USER, , ,DOMAIN\test2, 3

USER, , ,DOMAIN\test3, 2

USER, , ,DOMAIN\test3, 3

];

Section Application;

Star is *;

RD_COUNTRY:

LOAD * INLINE [

REDUCTION, COUNTRY_ID

1,1

2,2

3,3

4,4

];

SALES_COUNTRIES

LOAD * INLINE [

COUNTRY_ID, SALES

1, 100

2, 200

3, 4646

4, 755

5, 422];

Thanks,

Regards.

Re: Section Access AD user and Custom user

michael_anthony — Sun, 24 Jul 2016 23:33:18 GMT

It looks okay other than:

UPPER(COUNTRY) AS COUNTRY,

should probably be referencing NTNAME as the COUNTRY reference is applicable in the application section.

Have you tried testing the code, does it work?

Re: Section Access AD user and Custom user

suzel404 — Mon, 25 Jul 2016 06:04:08 GMT

Hi Michael,

Not yet, I'll tried at noon.

Thanks you for your response.

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 08:08:23 GMT

Hi Michael,

My script is the following :

Section Access;

LOAD

UPPER(ACCESS) AS ACCESS,

UPPER(USERID) AS USERID,

UPPER(PASSWORD) AS PASSWORD,

UPPER(NTNAME) AS NTNAME,

UPPER(REDUCTION) AS REDUCTION

FROM $(vFile)Gestion_Droit_Utilisateurs_QlikView.xlsx (ooxml, embedded labels, table is SECURITY_USERS);

Section Application;

Star is *;

REDUCTION_OPERATOR:

LOAD * INLINE [

REDUCTION, COUNTRY_ID

1, 1

2, 2

3, 3

7, 4

5, 5

4, 4

14, 14

22, 22

16, 16

25, 25

26, 26

27, 27

23, 23

18, 18

17, 17

19, 19

20, 20

21, 21

8, 8

9, 9

10, 10

11, 11

12, 12

13, 13

15, 15

28, 28

24, 24

];

COUNTRY:

LOAD

ID,

COUNTRY_ID

FROM $(vBase)COUNTRY.qvd (qvd);

I reload , copy and paste the qvw in user document.

I received this message in boucle when I tested NTNAME Security.

Is the some modifications on the QV Server ?

Regards,

Re: Section Access AD user and Custom user

Peter_Cammaert — Tue, 26 Jul 2016 09:35:44 GMT

Yes, it is possible to mix Custom users and AD users in the same Section Access table. However, Custom users are something special in QlikView and I don't think you're really talking about QlikView Custom users. It seems that you want to define local Windows users on the server machine? In that case, define them in Manage->Local Groups and Users and add their ID's to the NTNAME column with the machine name prefixed, like:

MachineName\LocalUser

DomainName\DomainUser

If you do want to add QlikView Custom users to your set-up, first in QMC define a DSC and add the users to the list in that same panel. Note that a default domain of "CUSTOM" will be suggested to collectively identify these users as Custom users.

In Section Access, now add them to the NTNAME column like this:

MachineName\LocalUser

DomainName\DomainUser

CUSTOM\CustomUser

Using USERID and PASSWORD is something else entirely (those aren't called Custom users). Those users are defined just for this document (or for every document that loads them from an external source). They do not exist outside of QlikView documents, and as such cannot be assigned file permissions or QlikView server properties. You can mix these with the preceding by adding columns USERID and PASSWORD to your Section Access table, just like you did in your OP.

As suggested by Michael Anthony, include the NTNAME column in your LOAD statement. You are currently ignoring the data from the INLINE table.

Best,

Peter

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 13:30:08 GMT

Thank you Peter.

I use a xls file like this :

ACCESS	NTNAME	COUNTRY_ID
ADMIN	DOMAIN\AdminDB	*
USER	DOMAIN\login1	*

---

SECTION ACCESS;

LOAD

ACCESS,

NTNAME,

OPERATOR_ID

FROM $(vFile)Gestion_Droit_Utilisateurs_QlikView.xlsx (ooxml, embedded labels, table is LOGIN);

STAR IS *;

SECTION APPLICATION;

LOAD

COUNTRY_ID

FROM $(vBase)Country.qvd (qvd);

With the login1 I can't see all countries. only 5! With load inline I have no problem.

Why can I see only 5 countries ?

Regards,

Re: Section Access AD user and Custom user

Peter_Cammaert — Tue, 26 Jul 2016 13:34:46 GMT

No idea, really.

Because there is no connection between Section Access (OPERATOR_ID) and the COUNTRY_ID field, there won't be any reduction for anyone, so everyone should be able to see everything.

Are you sure this is the correct script?

Peter

Re: Section Access AD user and Custom user

Peter_Cammaert — Tue, 26 Jul 2016 13:42:50 GMT

Also keep in mind that your STAR statement only affects subsequent LOAD/SELECT statements. Because it seems that '*' only appear in a table created before the STAR statement, those stars will not behave like wildcards.

Moreover, if you are testing this set-up in QV Desktop, you should be aware that User IDs with role ADMIN will always break data reduction.

Peter

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 14:11:46 GMT

Thank you for your response.

It is weel COUNTRY_ID

What is the caracter if I want the user see ALL countries ?

Joshua

Re: Section Access AD user and Custom user

Anil_Babu_Samineni — Tue, 26 Jul 2016 14:16:02 GMT

Yes, It is possible if you want to do that way

But, here i didn't understand this about Sales. This Sales is field right?

SALES_COUNTRIES

LOAD * INLINE [

COUNTRY_ID, SALES

1, 100

2, 200

3, 4646

4, 755

5, 422];

Re: Section Access AD user and Custom user

Peter_Cammaert — Tue, 26 Jul 2016 14:31:34 GMT

If so, then add COUNTRY_ID to your Section Access table instead of OPERATOR_ID.

Next, put the STAR IS statement before the Section Access table LOAD that contains asterisk characters. Otherwise they will not be flagged as wildcards, and they will remain simple asterisks.

Finally, and this is the most difficult step: a wildcard in Section Access will only work for the values listed in the current table load. If there are only wildcard characters in your table and no real country IDs, the wildcards will map to no other value. This is because a Section Access field will NOT be merged with a Section Application field with the same name. So the wildcards in your Section Access table will only translate to all other values in the same Section Access table, not to the remaining values in the nameless table in Section Application.

You can solve this and make the wildcards work again by adding all the remaining COUNTRY_ID values from Country.qvd to your Section Access table. Add dummy (non-existent) values for the other columns ACCESS and NTNAME and your document should be good to go with functioning wildcard specifications.

Best,

Peter

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 15:15:03 GMT

Sorry my original script is :

SECTION ACCESS;

LOAD

ACCESS,

NTNAME,

OPERATOR_ID

FROM $(vFile)Gestion_Droit_Utilisateurs_QlikView.xlsx (ooxml, embedded labels, table is LOGIN);

STAR IS *;

SECTION APPLICATION;

LOAD

OPERATOR_ID,

FROM $(vBase)XXX.qvd (qvd);

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 15:21:37 GMT

Attachment.

Re: Section Access AD user and Custom user

Peter_Cammaert — Tue, 26 Jul 2016 17:01:21 GMT

Your situation is becoming more and more confusing (cf. your last reply below...)

Anyway, the following link table LOAD may fix your problem mapping some users (with a particular OPERATOR_ID) to specific values, and other - more administrator-type - users mapping to all link values. Use this as a template to create your own solution.

Section Access;

LOAD ACCESS,

NTNAME,

OPERATOR_ID AS OPERATOR_LINK

FROM $(vFile)Gestion_Droit_Utilisateurs_QlikView.xlsx

(ooxml, embedded labels, table is LOGIN);

// STAR IS *; // Forget about this ! see my earlier comment

Section Application;

LinkTable:

LOAD OPERATOR_ID AS OPERATOR_LINK,

OPERATOR_ID

FROM $(vBase)XXX.qvd (qvd);

CONCATENATE (LinkTable)

LOAD DISTINCT

'*' AS OPERATOR_LINK,

OPERATOR_ID

RESIDENT LinkTable;

Best,

Peter

[Edit] Modified the second LOAD to read from the Resident table.

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 19:39:53 GMT

Thank you I'll try it tomorrow noon.

Regards,

Re: Section Access AD user and Custom user

suzel404 — Tue, 26 Jul 2016 20:43:12 GMT

I imagine an issue...

DOMAIN\login1 has only access to OPERATOR_ID 1 and 2 in the qvw application 1 and DOMAIN\login1 has only access to OPERATOR_ID 3 , 6, 7 in the qvw application 2. With the unique Excel File. I have a conflit , right ?

What is the best practices to manage section access with users and different security from application data ?

regards,

Re: Section Access AD user and Custom user

Peter_Cammaert — Tue, 26 Jul 2016 20:54:27 GMT

That seems like a weird premise, but it can be done. Maybe you should post this question in a new discussion while we try to solve your original issue in this one.

Best,

Peter

Re: Section Access AD user and Custom user

suzel404 — Thu, 28 Jul 2016 01:50:40 GMT

Thank you Peter for your time and response.