LDAP Query Syntax Tips

By Chris Heller • September 22, 2008

I’ve had a few conversations recently about the strangeness of LDAP query syntax so I thought a post some useful information and links here. You might not have had the need to know anything about LDAP query syntax as part of working with PeopleSoft though. PeopleSoft’s delivered LDAP integration does a good job of providing some rich functionality (authenticating users, caching profiles, role memberships, etc.) without forcing you to deal with LDAP query syntax.

LDAP Queries generated by PeopleTools

For example, in the PeopleSoft Authentication Map page ( PeopleTools -> Security -> Directory -> Authentication Map ) you can select which attribute in the directory (such as sAMAccountName) should be used for looking up the user trying to log in. Under the covers, the following LDAP query string is generated (if chrisheller is trying to login):

(sAMAccountName=chrisheller)

That’s a pretty simple example though. Looking up the group membership in order to do PeopleSoft role assignment for a user shows slightly more complex LDAP queries.

  • Novell’s eDirectory wants (&(objectclass=groupOfNames)(uniquemember=chrisheller))
  • Active Directory wants (&(objectclass=group)(member=chrisheller))
  • Oracle and Netscape want (&(objectclass=groupOfUniqueNames)(uniquemember=chrisheller))

These get generated for you automatically in the delivered PeopleCode. There are some other more complicated examples, but those get the basics across.

LDAP Query Syntax

Instead of having the queries written in a form similar to how you might speak, (attribute1=value1)&(attribute2=value2), the operator (‘&’ for AND, ‘!’ for NOT, ‘|’ for OR) gets pulled to the front and the whole thing wrapped in parentheses. A good reference is Microsoft’s page on MSDN for search filter syntax, which even includes how do things like bitwise comparisons in LDAP queries. Another good article is Unlocking the LDAP Search Filter which has some good explanations to go along with the syntax.

Another good way to get familiar with some of the possibilities for LDAP queries is to look at other examples that have been posted on the internet. JiJiTechnologies has a nice list of some example LDAP search queries. For example, here is a query that returns users that do not have a manager in the directory.

(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)(!manager=*))

and here is a query that returns accounts that will expire in a certain amount of time (see below for more on generating datetime values for LDAP queries)

(&(objectCategory=person)(objectClass=user)(!sAMAccountType=805306370)(!accountExpires=0) (!accountExpires=9223372036854775807)(!accountExpires<=currentTime)(accountExpires<=givenTime))

What can I do with a custom LDAP query?

Well, you might want to do some custom LDAP processing yourself from PeopleCode. Maybe you want to audit the manager entry in the LDAP directory with what is stored within your PeopleSoft HCM database. You might generate LDAP queries like the following to check on a one-by-one basis

(&(sAMAccountName=chrisheller)(manager=CN=larrygrey,OU=Employees,DC=greysparling,DC=com))

(or you might dump the employee/manager attributes from the directory in bulk instead).

Maybe you want to the delivered LDAP authentication to only login users that won’t have their account expire in the next day. You could change the delivered PeopleCode in FUNCLIB_LDAP.LDAPAUTH.FieldDefault to include that check as part of the LDAP query used (note that this is a customization; there is not a place for you to add this without customizing).

As part of our Desktop Single Signon for PeopleSoft product, we also provide the ability to use attributes in an LDAP directory as part of the process of mapping a network login to a PeopleSoft account. In the LDAP configuration there are “prepend” and “append” hooks in place to be able to modify the LDAP query that our code generates.

The feature was originally added because of a PeopleSoft customer that only wanted Single Signon to apply to users that were required to login with a Smartcard. If the user’s account wasn’t setup to require a Smartcard to login, then they wanted Single Signon to not establish their PeopleSoft session, and instead leave them at the PeopleSoft login page.

The attribute in Active Directory that contains the needed information is called userAccountControl. Unfortunately, this attribute is actually a bitfield, so we have to apply the bitwise operators that I mentioned above. In the Single Signon configuration they added a prepend value of (&(userAccountControl:1.2.840.113556.1.4.803=262144) and the append value of ) (that’s a single parentheses to close off the query).

At runtime, the generated LDAP query would have been (sAMAccountName=chrisheller), but with the prepend and append values added in, the query becomes (&(userAccountControl:1.2.840.113556.1.4.803=262144)(sAMAccountName=chrisheller)).

In case you haven’t memorized the MSDN documentation link from above yet (it’ll be on the midterm), the “:1.2.840.113556.1.4.803” part is the bitwise AND operator, which we are applying to the userAccountControl attribute. The 262144 is the decimal value for the “Smart card required for login” setting (also known as ADS_UF_SMARTCARD_REQUIRED). Here is a good list of the various different bitvalues that can be in the userAccountControl field.

So now when the LDAP query runs as part of the Single Signon user mapping, if the user’s account does not mandate Smartcard login, then the LDAP query will not return a match, which means that the user will not be automatically logged in to PeopleSoft.

Converting date/time values between Active Directory and PeopleCode

I have some PeopleCode written for this, but it’s getting late so I’ll save that for another post. If you’re interested in it, leave a comment. For now, I’ll just leave it as saying that this writeup on Active Directory’s Integer8 attributes by Richard Mueller was extremely helpful in coming with it.

(update : here is a post that provides the PeopleCode for handling all of the datetime conversions between ActiveDirectory format and PeopleCode datetime variables).

Labels: 2008, LDAP, Security

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

International Rollouts of PeopleSoft – Do's and Don'ts

By Chris Heller • April 15, 2008

Session 3291 in the OAUG section.

I went to Sylvain Nguyen’s presentation on PeopleSoft global rollouts. Sylvain used to be a manager for PeopleSoft Global Financials development, and is now the CEO of Ataway. Ataway is a consulting company that specializes in PeopleSoft (note that we’ve worked with them before)

I came in about 15 minutes late, because the OAUG tracks are not sync-ed up with the Quest tracks timewise. Which is probably driven more by scheduling lunch for everyone here than anything else. Sylvain was in the middle of discussing the question “How can a global rollout be cost efficient, fast paced, and with quality when so many odds are stacked against it?”. This then led into a series of Dos and Don’ts.

Do

  • Define template based global methodology
  • Identify business leaders and analysts in the US and local countries
  • Use local resources in the project team

Don’t

  • Start user requirement gathering before corporate business processes are mapped
  • Underestimate the impacts of working with remote teams

Do you have a US based team travel to each country to gather requirements? Sylvain recommends having local people onsite for the project implementation. They know the business practices, they know the culture, so they can be of great assistance.

Gathering local requirements. When planning deployment, the first thing to do is identify/document your proper business processes. If the core business processes are documented then the requirements gathering is much easier.

One other thing that Sylvain recommended is to avoid consulting companies that don’t have global experience. He gave an example of an implementation where consulting company didn’t know what VAT was, so they left it to be calculated manually. The local users thought this was a joke since the most basic local packages would do this automatically, but they were told that PeopleSoft was state of the art, etc.

The presentation then got into data strategies. It’s common to have a single set of vendors, a small number of setids for US based implementations. That probably won’t work for a global deployment, so if you haven’t looked at how PeopleSoft supports this, then it’s time to learn. (note: a great resource for this is our weblog post on SetIDs and Business Units).

One thing that comes up in some implementations is that the local users already know English, so people wonder why it’s necessary to have the global support. Sylvain gave an example of Japanese users that know English. But they need to interact with other people that don’t. If you send an invoice in English in Japan, you probably won’t get paid because the mailman won’t know how to deliver it. So you do need to be sure that your Japanese users can enter things like addresses in Japanese.

Do

  • Trust psft features around global
  • Prototype early as possible
  • Involve local business leaders in review of designs. Implementation time is too late.

Don’t

  • Underestimate the impact on existing customizations
  • Forget that production support will have to change to handle global users and requirements

There was then a short performance discussion. Most people understand about wide area networking and that there may be performance issues when you have users half way around the world. But you also have to consider things like running batch jobs in the middle of the night in the US. There’s never really a good time to do that in a global implementation because it’s always someone’s work day. So you have to look at better tuning of batch or even locking out users from targeted areas while batch is running.

Global deployments also impact your support organization. If a critical business issue happens at 3am headquarters time, who takes the call? (Hillary!) Would you allow the local teams to make code changes to do a critical fix if needed? Would you make your project team at headquarters wear pagers? Sylvain recommends setting service level agreements up front for these sorts of things so that it can be decided upon rationally up front, instead of waiting for a crisis to happen.

Do

  • Identify and train local SME as early as possible
  • Assign dedicated local support analysts
  • Train the support team on the new processes and features

Don’t

  • Underestimate time and cultural differences in resolving problems.
  • Think the project is over when the country is live.

Sylvain gave an example of a project review where there were no complaints about the new country rollout in Japan. As it turns out, the users were unhappy, but did not want to say anything. This is just a cultural difference, but the project team was not aware that no complaints was not the same thing as no issues.

One question came up at the end was about whether or not to use a single PeopleSoft instance or multiple PeopleSoft instances for development when you have different development teams around the world. Sylvain recommends a single instance so that you don’t have to worry about missing changes from one environement. There were a few nodding heads in the audience that Sylvain pointed out.

Labels: 2008, Collaborate, Global

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Firewall Product as Savior

By Chris Heller • April 11, 2008

We had an interesting situation with one of our customers recently where creative use of one of our products, the ERP Firewall for PeopleSoft, saved the customer from having to do an emergency PeopleTools upgrade. Needless to say, the drinks are on them at Collaborate.

For those that aren’t familiar with our ERP Firewall for PeopleSoft product, it is a Web Application Firewall that has deep knowledge of PeopleSoft applications. It doesn’t just requests coming in as URL strings that someone can write regular expressions to process, it sees the request in the context of PeopleSoft. It knows what a PeopleSoft component is; it knows what a Web Profile is, it understands PeopleSoft security, etc.

The problem that our customer hit was that when someone enters an invalid password logging in to PeopleSoft, PeopleTools would drop the portal and node name from the URL. Normally this wouldn’t be a problem because most people are accessing the default portal in PeopleSoft (generally the EMPLOYEE portal). When you login to PeopleSoft and don’t specify a portal, you get the default portal. Makes perfect sense.

However, when you also have a large number of customers accessing the CUSTOMER portal, then it gets more interesting. The customer end user attempts to login at https://some.host.com/psp/ps/CUSTOMER/CUST/h/?cmd=login . They enter a bad password by accident, and then they get redirected to https://some.host.com/psp/ps/?cmd=login along with the standard message saying that the username or password is incorrect.

So they type in the correct password and get logged in. Except now they are pointed to the EMPLOYEE portal (because the CUSTOMER portal reference got removed). And not being an EMPLOYEE, they don’t have access to anything. Oops. Their session is valid, but the URL is pointing to somewhere where they get nothing.

Turns out that this is fixed in a PeopleTools patch (8.48.13 for the 8.48 codeline, I’m not sure about other PeopleTools versions), but the customer was live with an earlier patch release in the 8.48 codeline and was concerned about dropping a new version of PeopleTools in.

Since they have the ERP Firewall product already (they use it for restricting employees from using the customer facing / internet accessible web server and force them to go through web servers that are just for employee use) we decided to treat accessing the EMPLOYEE portal as a security condition that we want to detect. However, instead of doing something like blocking access, we calculate the proper CUSTOMER portal URL and silently redirect the user there. So we’re actually using a security tool to solve a usability problem.

You might think that just replacing EMPLOYEE with CUSTOMER in the URL would be enough to solve the problem, but there were a few wrinkles which ended up making the ERP Firewall piece a really good fit.

Part of the challenge was making sure that we kept all of the users correct context when redirecting. Most users would be coming through the portal home page, but some might be coming in from deep links into order history or from bookmarks, etc. So we couldn’t just have a single URL to redirect people to.

The stickier problem was that the ERP Firewall needed to redirect differently based on whether the person was logged on or not. If the user was not logged in, and we redirected them to the CUSTOMER portal home page, PeopleTools viewed that as a login attempt, and gave the user the signon page. Normally PeopleTools handles this quite well; an attempt to hit a deep link in PeopleSoft when you’re not logged in gets you the signon page, and once you login, you go to that deep link that you originally requested.

However, due to this bug, the CUSTOMER portal was getting dropped again, so it was necessary to append the cmd=login parameter that PeopleTools recognizes as a request for the login page. Of course, if the user is logged in already and you redirect them with a cmd=login link, then you just killed their session.

The nice thing is that the ERP Firewall for PeopleSoft has the deep knowledge of PeopleSoft to make this trivial. It knows what a PeopleSoft portal is, it knows what PeopleSoft roles a user has, it knows whether they are logged in or not, and it knows how to properly generate and/or modify PeopleSoft URLs in a safe fashion.

If you are interested in learning more about this product, here’s a link to the product page. In addition to providing an overview, you can watch an in-depth demo that shows exactly how it works and how you would configure it.

Labels: , ,

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives