PL/SQL LDAP over SSL – Please Test

I’ve been meaning to write this for quite a while now (measured in years), but never got around to it.  None of the DBMS_LDAP code samples support SSL, including the APEX LDAP Authentication Scheme.  Most LDAP directories require SSL for authentication, as you would otherwise send usernames and passwords in clear text.  Depending on your network topography, the risk of someone capturing that data might be very low, but just the same, it should really be SSL. 

One perceived barrier to making this work was my belief that this required an Oracle Wallet which are a bit of a hassle to setup.  There was also some confusion on my part that Oracle Wallets required a license for the Advanced Security Option (ASO), so this would limit the use of this code to only the subset of our customers that have purchased this option.  I’m still looking for the email on this but I believe we confirmed that the Oracle Wallet Manger by itself (such as for DBMS_LDAP or UTL_HTTP) does not require ASO.  Only when you are using the explicitly defined features of ASO such as encrypted column / tablespaces / backups, network encryption, and a few others I can’t remember.  If someone has more info on this, please feel free to comment as I’m too [ busy | lazy | (select adjective from adjectives where id = round(dbms_random.value(1, 100)))] to find it [today | tomorrow | (select sysdate + round(dbms_random.value(1,365)) from dual) ].  When I finally sat down to write this for my current project, I quickly discovered that for SSL Mode 1 (which is what 99.999% of people want), no wallet is required!  Can’t believe I put this off for so long based on a false assumption.

So what does this code do? It can:

• Bind to an LDAP directory using SSL or non-SSL
• Authenticate a user using their exact Distinguished Name (DN) such as: cn=tyler.muth, l=amer, dc=mycompany, dc=com
• Given a starting point (search base) and a username (tyler.muth), search for that user, retrieve their DN, then authenticate.  This handles users in multiple sub-trees.

This is NOT production code! This is NOT production code! This is NOT production code!  Why am I posting it here then?  Because I want people to test it.  I’ve tested it on the project I’m working on, but that doesn’t mean it’s bug free.  I want people who are familiar with PL/SQL and LDAP to review and test my code.  As I receive suggested changes to the code, I’ll update it.  When I feel comfortable that enough people have tested, I’ll blog again and “release it” to the community to use.  My goal is make this as flexible and easy as possible so it just becomes a utility that people can use when they need LDAP.  I can’t make any promises, but if it makes it back into APEX, then you will have helped to improve a product.  If you are not all that familiar with PL/SQL and LDAP, or are looking for a production ready solution, this is probably not for you.  When it’s ready, this blog post will be mostly empty and point to the new location of the code.

I’ve only tested this against OID using an 11.something database, so I’d love to have people test this against Active Directory and any other LDAP directories they work with and at least 10gR2 and XE.  Mark Wilcox is testing it with Oracle Virtual Directory in the next few days as well.  I also ran Wireshark on the database server to capture the communitcation between the database and the LDAP directory.  As expected, in non-ssl mode, my username and password were in the clear.  With SSL enabled, everything was garbage.   Please use Wireshark with caution as it may violate network policies of your organization, get you fired, and accelerate global warming.  It’s a great tool when used ethicly.

I really appreciate the feedback I’ve recieved in the past from this community, especially in pointing out errors with my code.  Thanks in advance for your help.

Customize the ldap_globals package with data from your environment. For most environments, you’ll only need to change g_host and g_search_base.

create or replace package ldap_globals
authid definer
is

	g_host         	constant varchar2(255)   := 'ldap.mycompany.com';
    g_port          constant number          := 636;
    g_search_base   constant varchar2(255)   := 'dc=mycompany,dc=com';
    g_search_filter constant varchar2(255)   := 'cn=';
	g_exact_dn      constant boolean   		 := false;
	g_use_ssl       constant boolean   		 := true;

	-- if g_exact_dn is false, then we will start at the search base and find the user to get their DN.
	--   if true then we will just bind with the DN and you should pass the fully qualified user DN into p_username

	-- SSL is hard-coded to use mode 1, as modes 2 and 3 are highly unlikely.  See the DBMS_LDAP doc for more info.
	-- No wallet is needed for mode 1

end ldap_globals;
/
show errors

There’s no need (that I know of) to customize any code in this function.

create or replace function  ldap_authenticate(
	p_username        in    varchar2,
	p_password        in    varchar2)
return boolean
authid definer
is
	l_retval      pls_integer;
	l_session     dbms_ldap.session;
	l_attrs       dbms_ldap.string_collection;
	l_message     dbms_ldap.message;
	l_entry       dbms_ldap.message;
	l_dn          varchar2(1000);
	l_user_handle dbms_ldap_utl.handle;
	l_return      pls_integer;
	l_user_type   pls_integer;

	l_filter      varchar2(256);

	function do_connect
		return pls_integer
	as
		l_ldap_user   varchar2(256) := NULL;
		l_ldap_pwd    varchar2(256) := NULL;
	begin
		dbms_ldap.use_exception := true;
		l_session := dbms_ldap.init( ldap_globals.g_host , ldap_globals.g_port);

    if ldap_globals.g_use_ssl = true then
      l_retval := DBMS_LDAP.open_ssl(
					ld 				=> l_session,
                    sslwrl			=> NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle
                    sslwalletpasswd	=> NULL, -- wallet password
                    sslauth			=> 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3
    end if; 

		return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );
	end;

	procedure do_close
	as
	begin
		l_retval := dbms_ldap.unbind_s( l_session );
	end do_close;

	procedure handle_error(p_message in varchar2)
	as
	begin
		do_close;

		$IF $$logger $THEN
			logger.log_error(p_message);
		$END

		RAISE_APPLICATION_ERROR (-20001,p_message);
	end;

begin
	l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;

	l_retval := do_connect;

	if ldap_globals.g_exact_dn = false then

		l_attrs(1)  := 'dn';
    l_filter := '('|| ldap_globals.g_search_filter|| p_username||')';
		l_retval := dbms_ldap.search_s( l_session, ldap_globals.g_search_base, dbms_ldap.scope_subtree, l_filter, l_attrs, 0, l_message );

		l_retval := DBMS_LDAP.count_entries(l_session, l_message);

		if l_retval > 1 then
			handle_error('More than one entry found when searching for users DN.');
		end if;

		if l_retval = 0 then
			handle_error('No entries were found when searching for user DN');
		end if;

		l_entry := dbms_ldap.first_entry( l_session, l_message );
		l_dn := dbms_ldap.get_dn( l_session, l_entry );
	else
		l_dn := p_username;
	end if; -- ldap_globals.g_exact_dn = false

	l_retval := null;
	l_retval := DBMS_LDAP_UTL.create_user_handle(
				user_hd     => l_user_handle,
				user_type   => DBMS_LDAP_UTL.TYPE_DN,
				user_id     => l_dn);

		begin
      dbms_ldap.use_exception := false;
      l_return := null;
			l_return := dbms_ldap.simple_bind_s(
				ld      => l_session,
				dn      => l_dn,
				passwd  => p_password
				);
      if l_return != 0 then
        do_close;
        return false;
      end if;

		exception when others then
			do_close;
			raise;
		end;

    dbms_ldap.use_exception := true;
	do_close;
	if l_return is null then
		handle_error('simple_bind_s did not return a value.');
		return false;
	end if;

	if l_return = 0 then
		return true;
	end if;

	exception when others then
		do_close;
		raise;
		return false;
end ldap_authenticate;
/
show errors

A simple test script. If using SQL Developer, make sure you enable output on the DBMS_OUTPUT tab.

set serveroutput on
declare
	l_return boolean;
begin
	l_return := ldap_authenticate(
					p_username	=> 'first.last',
					p_password	=> '');

	if l_return = true then
		dbms_output.put_line('Authenticated');
	else
		dbms_output.put_line('Fail!');
	end;
end;
/