article Section Access (Data Reduction) using Active Directory in Member Articles

Section Access (Data Reduction) using Active Directory

AC3 — Fri, 07 Oct 2022 13:22:46 GMT

Purpose: Use Active Directory Groups to determine what data to display for each user.

After piecing together many articles and community advice, I created this script and hope it's useful to others. It reads Active Directory for members in a particular group and displays only the data according to the User's group(s).

Please refer to Introduction to Section Access for an introduction to Section Access.

In Document Settings on the Opening tab, Click the Initial Data Reduction Based on Section Access option.

You will need the full canonical name of your Active Directory. Replace text in orange with the parts of the canonical name as needed.

The script runs in the Hidden Scripts section.

OLEDB CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Data Source=LDAP:// DC=DCHOST,DC=DCCOM;Mode=Read];

DataSecurity:

//Users in the East Group

Load Upper(sAMAccountname) as NTNAME, 'East' as Zone, 'USER' as ACCESS;

SELECT sAMAccountname

FROM 'LDAP:// DC=DCHOST,DC=DCCOM'

WHERE objectCategory='person'

AND objectClass='user'

and memberOf='CN=EASTGROUP,DC=DCHOST,DC=DCCOM';

//Users in the West Group

Concatenate

Load Upper(sAMAccountname) as NTNAME, 'West' as Zone, 'USER' as ACCESS;

SELECT sAMAccountname

FROM 'LDAP:// DC=DCHOST,DC=DCCOM'

WHERE objectCategory='person'

AND objectClass='user'

and memberOf='CN=WESTGROUP,DC=DCHOST,DC=DCCOM';

//Users who can see everything

Concatenate

Load Upper(sAMAccountname) as NTNAME, '*' as Zone, 'ADMIN' as ACCESS;

SELECT sAMAccountname

FROM 'LDAP:// DC=DCHOST,DC=DCCOM'

WHERE objectCategory='person'

AND objectClass='user'

and memberOf='CN=ALLGROUP,DC=DCHOST,DC=DCCOM';

STAR is *;

SECTION Access;

LOAD

ACCESS,

NTNAME

Resident DataSecurity;

SECTION Application;

LOAD

NTNAME,

Zone

Resident DataSecurity;

Drop Table DataSecurity;

Note: Changes with Qlikview 12: A Listbox to display Zone will now display the *. To hide the * in the Listbox, use this expression for the Field: =aggr(only({<Zone={'?*?'}>}Zone),Zone)

Re: Section Access (Data Reduction) using Active Directory

flanfranco — Tue, 21 Jun 2016 12:26:31 GMT

This is very useful!

Thanks

Re: Section Access (Data Reduction) using Active Directory

Tue, 21 Jun 2016 14:02:23 GMT

Helpful. Thanks!

Re: Section Access (Data Reduction) using Active Directory

vinafidalgo — Tue, 21 Jun 2016 19:12:46 GMT

Awesome! Thanks

Re: Section Access (Data Reduction) using Active Directory

Tue, 21 Jun 2016 20:09:24 GMT

very helpful ! Thanks

Re: Section Access (Data Reduction) using Active Directory

dafnis14 — Mon, 27 Jun 2016 18:24:25 GMT

Hi,

Thanks for sharing!

I have a question: in case a user belongs to a few groups, and there is no All group,

what is the recommended script?

Re: Section Access (Data Reduction) using Active Directory

AC3 — Mon, 27 Jun 2016 21:49:00 GMT

Use the same script. DataSecurity will have a record for every group the user belongs to. This will give access to the groups for this user.

Re: Section Access (Data Reduction) using Active Directory

dafnis14 — Tue, 28 Jun 2016 04:12:06 GMT

Thank you, Anlee!

Re: Section Access (Data Reduction) using Active Directory

joseph_morales — Wed, 06 Jul 2016 10:35:53 GMT

Very useful!!

Thanks Anlee!!

Re: Section Access (Data Reduction) using Active Directory

shwetagupta — Fri, 08 Jul 2016 07:22:59 GMT

Hi I am finding this interesting.
Will it work with Qlik Sense ?

Thanks
Shweta

Re: Section Access (Data Reduction) using Active Directory

AC3 — Tue, 12 Jul 2016 16:17:07 GMT

Shweta, I've never tested this in Qlik Sense, but it should work the same way.

Re: Section Access (Data Reduction) using Active Directory

santiago_respane — Mon, 29 Aug 2016 11:51:42 GMT

Excellent contribution!!

Thanks for sharing!

Re: Section Access (Data Reduction) using Active Directory

Tue, 30 Aug 2016 14:59:16 GMT

Right time right place!!

i had made connection to LDAP before, but never thought of doing section access this way..

Thanks Anlee!

Re: Section Access (Data Reduction) using Active Directory

satishkurra — Sat, 11 Mar 2017 05:19:36 GMT

This looks great.

Can we achieve hierarchy based section access as well with this?

Thanks

Satish

Re: Section Access (Data Reduction) using Active Directory

AC3 — Mon, 13 Mar 2017 20:12:21 GMT

Satish,

You should be able to depending on how your AD groups are setup and you'll have to supplement the script above to accommodate the different levels. See here for more info: Authorization using a Hierarchy

Re: Section Access (Data Reduction) using Active Directory

Bill_Britt — Wed, 22 Nov 2017 13:25:20 GMT

You can't use NTNAME in Qlik Sense.

Bill