Behaviours server-side validation LDAP or Oracle sql

[Dmitri Barski]

Hi all,

I am trying to implement a verification for a certain custom field against LDAP. The idea is: whatever is entered there, must be a valid user name in our LDAP directory (...however, not necessarily a valid/active JIRA user!).

The initial approach (use Scriptrunner's builtin scripts to suck the account list into the field options) failed due to the number of accounts (SR was only able to import about 10 per second...after 10 hours I had only 40k users imported, which is not even 30% of our accounts...) and already this incomplete field took ages to load (select list with 40k entries...) on the webpage.

I tried nFeed - was not able to get it set up, actually, it gives me a NullPointer exception.

Tried to give UserGroup Picker PRO a chance, too - it can apparently only pick active users from JIRA and restrict the search further.

So I ended up with making it a single-line text field and have a serverside validation script tokenize it and check against LDAP or database. The results are not exactly encouraging:

- connecting to Oracle works fine in the Script Console, but fails with "ClassNotFound" within a Behaviours script on the attempt to load Oracle jdbc...

- connecting to LDAP does not work even in Scriptconsole (brings a "java.lang.NoClassDefFoundError: groovy/lang/Closure" on LDAP.newInstance call).

Any suggestions?

Thanks in advance

Best regards

Dmitri

[Dmitri Barski]

Solved/circumvented the problem with a workaround (ymmv though :)): created a META-INF/lib directory inside the jar file and put ojdbc6.jar (Oracle JDBC), respectively groovy-ldap.jar into it. This did not solve LDAP problem completely (I still cannot really access the results, but I am able to check whether a particular entry exists), Oracle access worked perfectly.

[JamieA]

Yeah, other people have done this actually. Sorry, should have mentioned this possibility.

[Dmitri Barski]

An LDAP picker would be of course much more elegant - Behaviours have some caveats like validating only when the focus leaves the fields, so I need to combine it with "Field required" validator...

[JamieA]

I have written an ldap validated user picker before, I'll hunt around for the code and see if I can post it. It's not very exciting though.

> after 10 hours I had only 40k users imported, which is not even 30% of our accounts...) and already this incomplete field took ages to load (select list with 40k entries...) on the webpage

Select lists really don't scale like this, even if they did users cannot pick from lists of 130k entries.

> connecting to Oracle works fine in the Script Console, but fails with "ClassNotFound" within a Behaviours script on the attempt to load Oracle jdbc

You could try the suggestions here: https://jamieechlin.atlassian.net/wiki/display/GRV/Connecting+to+Databases- although not tested in behaviours plugin, which at the moment works differently to SR.

[Dmitri Barski]

I have written an ldap validated user picker before, I'll hunt around for the code and see if I can post it. It's not very exciting though.

This would be great - as already said,I was not able to make it work, neither in Behaviours nor in Script Console.

> after 10 hours I had only 40k users imported, which is not even 30% of our accounts...) and already this incomplete field took ages to load (select list with 40k entries...) on the webpage

Select lists really don't scale like this, even if they did users cannot pick from lists of 130k entries.

Basically, with the approach you described in https://answers.atlassian.com/questions/43534/can-i-have-multi-select-custom-field-behave-like-components-versions-fields, the lists become handleble for the user again (however, I have modified it in my code, so that it does not take the field ID btu the field name...This makes it survive migrations between environments). Without that - yeah, 130k entries in a select list would be insane. The load time is insane though, either.

> connecting to Oracle works fine in the Script Console, but fails with "ClassNotFound" within a Behaviours script on the attempt to load Oracle jdbc

You could try the suggestions here: https://jamieechlin.atlassian.net/wiki/display/GRV/Connecting+to+Databases- although not tested in behaviours plugin, which at the moment works differently to SR.

This is an interesting approach, and I will for sure give it a try tomorrow.

For now, I have put my validation into the normal validator, however this has the downside that I cannot really tell the user which ID was wrong in what he entered, only that some are wrong...

[Dmitri Barski]

Tried it just now, with the following code:

import groovy.sql.Sql
import java.sql.Driver
 
try { 
 def driver = Class.forName('oracle.jdbc.driver.OracleDriver').newInstance() as Driver
 getFieldById(getFieldChanged()).clearError()
}
catch(Exception e) {
 getFieldById(getFieldChanged()).setError("Error loading SQL driver")
 throw e;
}

No success - "java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver"

[Dmitri Barski]

By the way, are you referring to this picker?

http://blogs.onresolve.com/2007/12/ldap-picker-custom-field/

[JamieA]

that was an early version of it...

[Dmitri Barski]

Any luck in finding that version of an LDAP picker?...