Friday, 23 January 2015

SQLCl - LDAP anyone?

since  we released our first preview of SDSQL, we've made  a lot of changes to it and enhanced a lot of things too in there so it would be more useable.  One specific one was the use of LDAP which some customers on SQLDeveloper are using in their organisations as a standard and our first release precluded them from working with this.

Well, to add this, we wanted a way that we could specify the LDAP strings and then use them in a connect statement.  We introduced a command called SET LDAPCON for setting the LDAP connection.  You can set it like this at the SQL> prompt
 set LDAPCON jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom  

or set it as an environment variable
 (~/sql) $export LDAPCON=jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom  

Then you can come along and as long as you know your service name, we're going to swap out the ENTRY delimiter in the LDAP connection with your service.  We're working on a more permanent way to allow these to be registered and used so they are more seamless.

In the meantime, you can then connect to your LDAP service like this
 BARRY@ORCL>set LDAPCON jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom  
 BARRY@ORCL>connect barry/oracle@orclservice_test(Emily's Desktop)  
 Connected  
 BARRY@PDBOH12>tables  
 Command=tables  
 TABLES   
 TEST    

Here's a qk little video of it in action!  You can then use  the 'SHOW JDBC' command to show what you are connected to.


This is the latest release which should be online soon, and you  can download it from here.

3 comments:

Jack said...

Hi Barry,

I'm a long time Oracle user and am just now converting from SQL*Plus to SQLcl. Looks like a great tool to replace sqlplus (saying that with a tear in my eye).

I know you describe a way to use LDAP in this article, but we've standardized on Oracle's LDAP (OID) server in our shop. It is a standard setup as described in Oracle documentation, where we have setup the SQLNET.ORA and LDAP.ORA files with the LDAP_ADMIN and TNS_ADMIN environment variable pointing to their location.

Is there a chance you could provide support for this setup?

Proper support for it would follow these steps:
1) Look for the LDAP_ADMIN env variable first, and if that didn't exist, look for the TNS_ADMIN variable.
2) Look for the SQLNET.ORA file in the path specified in env variable
3) Interrogate the NAMES.DIRECTORY_PATH line in the SQLNET.ORA file to verify whether preference for name resolution is LDAP or TNSNAMES.
4) If LDAP, look in LDAP.ORA file for LDAP server details. Then try to lookup the database connection details. If not found, try the next connection method specified in NAMES.DIRECTORY_PATH directive above.


Thanks!
Jack

Barry McGillin said...

We can and will certainly look at that. We can also do a show ldap which will walk the order tree and tell you what we can see. We also do this with show tns to show the tns locations.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.