topic Re: VBScript to check for LDAP group membership in QlikView

VBScript to check for LDAP group membership

Mon, 16 Jul 2012 14:01:17 GMT

I have been working on this problem for days, and I am struggling to find a solution that works.

Basically, I want a function in the following form:

in_group(username, groupname)

That returns 1 if the the username is in the group, and 0 otherwise. Does anyone have any sources or solutions for this problem? I am trying to work with the following code:

Function GetMembers(gDN)
    Set objGroup = GetObject("LDAP://" & gDN)
    objGroup.GetInfo
    arrMemberOf = objGroup.GetEx("member")
    
    For Each strMember in arrMemberOf
        Set objMember = GetObject("LDAP://" & strMember)
        ObjDisp = objMember.Name
        oDL = Len(ObjDisp) - 3
        ObjDisp = Right(ObjDisp,oDL)
        ObjCatArray = Split(objMember.objectCategory,",")
        oType = ObjCatArray(0)
        oTL = Len(oType) - 3
        oType = Right(oType,oTL)
        msgbox "Member:" & ObjDisp & Space(20-Len(ObjDIsp)) &" Type:" & oType
        If oType = "Group" Then
            GetMembers(strMember)
        End If
        Set objMember = Nothing
    Next
End Function
Public Function SearchGroup(ByVal vSAN)
    Dim oRootDSE, oConnection, oCommand, oRecordSet
    Set oRootDSE = GetObject("LDAP://rootDSE")
    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Open "Provider=ADsDSOObject;"
    Set oCommand = CreateObject("ADODB.Command")
    oCommand.ActiveConnection = oConnection
    oCommand.CommandText = "<LDAP://" & oRootDSE.get("defaultNamingContext") & _
        ">;(&(objectCategory=Group)(samAccountName=" & vSAN & "));distinguishedName;subtree"
    Set oRecordSet = oCommand.Execute
    On Error Resume Next
    SearchGroup = oRecordSet.Fields("distinguishedName")
    On Error GoTo 0
    oConnection.Close
    Set oRecordSet = Nothing
    Set oCommand = Nothing
    Set oConnection = Nothing
    Set oRootDSE = Nothing
End Function

and, alternatively:

Function IsMember(ByVal objADObject, ByVal strGroupNTName)

' Function to test for group membership.

' objADObject is a user or computer object.

' strGroupNTName is the NT name (sAMAccountName) of the group to test.

' objGroupList is a dictionary object, with global scope.

' Returns True if the user or computer is a member of the group.

' Subroutine LoadGroups is called once for each different objADObject.

Dim objRootDSE, strDNSDomain

' The first time IsMember is called, setup the dictionary object

' and objects required for ADO.

If (IsEmpty(objGroupList) = True) Then

Set objGroupList = CreateObject("Scripting.Dictionary")

objGroupList.CompareMode = vbTextCompare

Set adoCommand = CreateObject("ADODB.Command")

Set adoConnection = CreateObject("ADODB.Connection")

adoConnection.Provider = "ADsDSOObject"

adoConnection.Open "Active Directory Provider"

adoCommand.ActiveConnection = adoConnection

Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")

adoCommand.Properties("Page Size") = 100

adoCommand.Properties("Timeout") = 30

adoCommand.Properties("Cache Results") = False

' Search entire domain.

strBase = "<LDAP://" & strDNSDomain & ">"

' Retrieve NT name of each group.

strAttributes = "sAMAccountName"

' Load group memberships for this user or computer into dictionary

' object.

Call LoadGroups(objADObject)

Set objRootDSE = Nothing

End If

If (objGroupList.Exists(objADObject.sAMAccountName & "\") = False) Then

' Dictionary object established, but group memberships for this

' user or computer must be added.

Call LoadGroups(objADObject)

End If

' Return True if this user or computer is a member of the group.

IsMember = objGroupList.Exists(objADObject.sAMAccountName & "\" & strGroupNTName)

End Function

Thoughts? Bonus points if the solution uses "Safe Mode" instead of "System Access." Thanks for your help.

Re: VBScript to check for LDAP group membership

nstefaniuk — Mon, 16 Jul 2012 14:22:27 GMT

Why do you need to create a function ? Why don't you retrieve content of all groups then manage it in your script?

Alternatively, can you create OLEDB connections with OLEDB Provider for Microsoft Directory Services ?

Re: VBScript to check for LDAP group membership

Mon, 16 Jul 2012 14:26:43 GMT

I use this function to control the display of tabs by setting variables based on AD group membership.

I am not sure if I can created OLEDB connections.

Re: VBScript to check for LDAP group membership

nstefaniuk — Mon, 16 Jul 2012 14:31:34 GMT

Hu?

You mean that, in the dashboard, you want to test if user belongs to AD group, and if yes, you fill a variable and do an action?

If yes, I suggest you to do this test in script, then use Section Access.

Re: VBScript to check for LDAP group membership

Mon, 16 Jul 2012 14:38:40 GMT

Thank you. How would you suggest that I do this in the script? I use section access to assign the variable?

Re: VBScript to check for LDAP group membership

nstefaniuk — Mon, 16 Jul 2012 14:48:57 GMT

In script you load all values Group - AD Account. Then you calculate in script if an AD account matches with your condition and store content in table USER_SECURITY.

Then you create a section access and link the security table to USER_SECURITY on upper(AD account) ( upper(AD account) is the value used to join). From now it means that, when user opens the application, he will have only 1 line in table USER_SECURITY, the line of his own account.

And you can add security in the tab to control that only(SECURITY_FLAG) = 1

Re: VBScript to check for LDAP group membership

nstefaniuk — Mon, 16 Jul 2012 14:59:21 GMT

Example.

Test user : ADMIN / ADMIN

USER1 / USER1

USER2 / USER2

Re: VBScript to check for LDAP group membership

Mon, 16 Jul 2012 15:39:25 GMT

Does this mean I will need to maintain a list of users manually? I have a working solution in VBScript right now that has explicit lists of users, but I was hoping to use AD groups as a way of avoiding that. Although, if I could have this list maintained in an external text file and loaded somehow, that would work. I recently learned about including external text files as scripts. We will likely need Section Access in the future anyway. Thank you for helping me realize this.

Re: VBScript to check for LDAP group membership

nstefaniuk — Mon, 16 Jul 2012 15:46:38 GMT

No no, you can do Section Access with AD groups, that was just a portable example.

Re: VBScript to check for LDAP group membership

swuehl — Mon, 16 Jul 2012 15:47:44 GMT

There should be some samples on how to access LDAP and retrieve group information here in the forum, have you checked e.g. this?

http://community.qlik.com/thread/8342

Re: VBScript to check for LDAP group membership

qlikviewforum — Mon, 22 Jul 2013 16:56:42 GMT

Hi,

The QVW which tries to load the data from LDAP loads only the partial data frequently. We are unable to find the root cause of the issue as the QVW doesn't fails but it loads only the partial data.Can someone please help us to identify the root cause of the issue. It has been happening from quite a long time but we didn't succeed in finding the root cause of the issue. Hope someone can help us for the same.

Regards,

qvforum