Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Oct 7, 2022 9:22:46 AM
Jun 20, 2016 2:18:05 PM
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)
This is very useful!
Thanks
Helpful. Thanks!
Awesome! Thanks
very helpful ! Thanks
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?
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.
Thank you, Anlee!
Very useful!!
Thanks Anlee!!
Hi I am finding this interesting.
Will it work with Qlik Sense ?
Thanks
Shweta
Shweta, I've never tested this in Qlik Sense, but it should work the same way.