Managing TNS lookups in OpenLDAP via PL/SQL

I have an Oracle Express Edition (XE) database running on almost every pc in my home. It’s not so numerous that I can’t manage them by manipulation of TNSNAMES.ORA; but I always thought it would be nice to manage them centrally via LDAP service name lookup.

Unfortunately Oracle Internet Directory (OID) licensing is included with a valid Oracle Database license unless that database is XE. I don’t really need or want a full OID installation anyway. So, I looked into other LDAP alternatives and decided on OpenLDAP. It’s open source, free and I can install it on Linux or Windows. I chose Linux and followed the instructions here…
http://diznix.com/articles/oracle-names-resolution-with-openldap without issue.

configuring one of the Windows XE installs to use my new linux server was simple enough
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP, EZCONNECT)

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\ldap.ora
DIRECTORY_SERVERS= (mylinuxserver:389:636)
DEFAULT_ADMIN_CONTEXT = "dc=home"
DIRECTORY_SERVER_TYPE = OID

The next step was getting my databases defined in the ldap server. I could do it through ldif files as shown in the link above; but I thought it would be nicer to have a sql and pl/sql interface to the server. Oracle facilitates that with the DBMS_LDAP package. To make it even easier, I built a wrapper package for it.

First, set up a user to own the package and, since I’m using 11g XE I need to create an Access Control List to allow my owner to talk to my ldap/oid server. I’m using the default non-SSL port 389.

create user oidowner identified by pa55w0rd;
grant create session to oidowner;
grant create procedure to oidowner;
grant create type to oidowner;

DBMS_NETWORK_ACL_ADMIN.assign_acl('oid.xml', 'mylinuxserver',389,389);
COMMIT;
END;

Next I create my package and a supporting type to make listing the contents easier

create or replace type vcarray as table of varchar2(4000);

Package code is available from my dropbox https://dl.dropboxusercontent.com/u/75336811/sdsoid_pkg_1.3.txt

And finally, I add an entry for an XE installation on one of my PCs.

BEGIN
sdsoid.insert_oid(
'dc=home',
'mylinuxserver',
'seanxe',
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seanpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))',
'cn=myadminuser,dc=home',
'myadminpassword'
);
END;

Then I check my work…

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>tnsping seanxe

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seanpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
OK (0 msec)

But, I want a SQL interface to the LDAP entries. This is where the collection type defined before the package comes in.
Using the list_oid table function it’s easy to retrieve some of the entries for other databases I’ve created.

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>sqlplus oidowner/pa55w0rd@seanxe

COLUMN_VALUE
-------------------------------------------------------------------------------------------------------------------------
kidsxe: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=familypc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
testxe: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
seanxe: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=seanpc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))

Currently all entries are maintained and retrieved with the full connect descriptor. In my next release I’ll add support for parameterized entries. I have tested this code on 12c databases and it works without modification.

Questions and comments are welcome.