Section Access (Data Reduction) using Active Directory

    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;