Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE

Section Access (Data Reduction) using Active Directory

cancel
Showing results for 
Search instead for 
Did you mean: 
AC3
Contributor III
Contributor III

Section Access (Data Reduction) using Active Directory

Last Update:

Oct 7, 2022 6:22:46 AM

Updated By:

Sonja_Bauernfeind

Created date:

Jun 20, 2016 11:18:05 AM

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)

Labels (1)
Comments
flanfranco
Partner - Contributor III
Partner - Contributor III

This is very useful!

Thanks

Not applicable

Helpful. Thanks!

vinafidalgo
Partner - Creator
Partner - Creator

Awesome! Thanks

Not applicable

very helpful ! Thanks

dafnis14
Specialist
Specialist

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?

AC3
Contributor III
Contributor III

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.

dafnis14
Specialist
Specialist

Thank you, Anlee!

joseph_morales
Creator II
Creator II

Very useful!!

Thanks Anlee!!

shwetagupta
Partner - Creator II
Partner - Creator II

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

Thanks
Shweta

AC3
Contributor III
Contributor III

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

Version history
Last update:
‎2022-10-07 09:22 AM
Updated by: