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; /