Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

PL/SQL LDAP over SSL – Please Test

Posted by Tyler Muth on January 30, 2009

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

77 Responses to “PL/SQL LDAP over SSL – Please Test”

  1. Dan said

    Tyler,

    I’ll try to give this a go early next week.

    Regards,
    Dan

  2. Salim said

    Hi,

    I will test this against active directory next week and i will give you my feedback

  3. Joseph Charpak said

    Nitpicking: You’ve got two lines of code that do “if boolean = true then…” or “if boolean = false then…”

    You don’t need to do that. you can write “if boolean then…” or “if not boolean then…” I also recommend rewriting the boolean function name or variable to clearly indicate it’s a boolean.

    E.g.
    if ldap_globals.g_exact_dn = false then
    could become:
    if not ldap_globals.g_using_exact_dn then

    and
    if ldap_globals.g_use_ssl = true then
    could become:
    if ldap_globals.g_using_ssl then

  4. Phil Clifford said

    morphic resonance : just getting around to this myself “after several years” and google reader popped this up for me. Will be testing against oid and AD on 10gR2 and XE in a few days – meantime a slight correction :”None of the DBMS_LDAP code samples support SSL” – except the one on (see sslbind.sql in the DBMS_LDAP API package).

  5. Tyler Muth said

    Phil,

    What I meant was the practical LDAP code snippets floating around the APEX forum and various blogs. I actually used the sample code you linked to when I wrote this code and that code has been around for years. It’s not all that useful in it’s current form, but it does do SSL.

    Can’t wait to hear your results from AD. I’ve tested this on 10.2, 11.1 and 11.2, but only against OID and OVD.

    Thanks,
    Tyler

  6. John Scott said

    Hi Tyler,

    Great post, I’ll test it against AD, OID, Sun Directory Server and also OpenLDAP when I get a sec.

    John.

  7. Pat Miller said

    I would appreciate any feedback from other users who have succeeded with SunOne ldap.

  8. Sriram said

    Does not seem to work for 10.2.0.3 and AD combination.


    declare
    l_session dbms_ldap.session;
    l_retval pls_integer;
    begin
    dbms_ldap.use_exception := true;
    l_session := dbms_ldap.init( 'my_domain_controller.domain', 389 );
    l_retval := dbms_ldap.simple_bind_s( l_session, 'domain\user', 'password');
    l_retval := dbms_ldap.unbind_s( l_session );
    end;
    /

    PL/SQL procedure successfully completed.

    declare
    l_session dbms_ldap.session;
    l_retval pls_integer;
    begin
    dbms_ldap.use_exception := true;
    l_session := dbms_ldap.init( 'my_domain_controller.domain', 389 );
    l_retval := DBMS_LDAP.open_ssl( ld => l_session,
    sslwrl => NULL,
    sslwalletpasswd => NULL,
    sslauth => 1);
    l_retval := dbms_ldap.simple_bind_s( l_session, 'domain\user', 'password');
    l_retval := dbms_ldap.unbind_s( l_session );
    end;
    /
    declare
    *
    ERROR at line 1:
    ORA-31202: DBMS_LDAP: LDAP client/server error: UnKnown Error Encountered
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: at "SYS.DBMS_LDAP", line 1457
    ORA-06512: at "SYS.DBMS_LDAP", line 1234
    ORA-06512: at line 7

  9. Pat Miller said

    We also got similar ORA 31202 error just trying to do an ssl bind with 10.2.0.3 enterprise edition and SunOne LDAP. My suspicion is that the dbms_ldap package does not even support ssl bind to non-OID LDAPs. Can anyone confirm that? Someone on Apex forum suggested running open source stunnel on the Apex server to mediate.

    Pat

  10. Pat Miller said

    Georger, Tyler,
    I see that Georger was the one who suggested stunnel on the Apex forum. From another post there (Apex forum) regarding pl/sql to AD over SSL what is becoming clear are two options:
    1.) You need certificate exchange from LDAP server to Oracle wallet on Apex server in order to have SSL bind from Oracle to 3rd party ldap. So dbms_ldap supports ssl bind to 3rd party if it has the LDAP server certificate.

    2.) You can bypass the certificate exchange if you use stunnel on the Apex server.

    Correct me if I am wrong!

    Pat

  11. Earl said

    I’ve tested this against an AD and also found the call dbms_ldap.open_ssl is failing with the same error mentioned by Sriram.

    I think we need to clarify something here, if nothing but my own understanding. If this is a function lives in the database and the database is being asked to do the LDAP authentication, shouldn’t the wallet referenced here be a wallet on the Oracle db host rather than the Apex web machine (assuming they are different – as in my case)?

  12. Earl said

    And then the follow-on to my earlier point: wouldn’t that mean that the Oracle instance would need to have Oracle Advanced Security in order to be able to talk SSL to other hosts?

    And isn’t this what is leading people down the path of recommending something like stunnel?

  13. Pat Miller said

    We were able to get basic SSL authenticated bind to work with our SunOne LDAP. We had to do following:
    1.) add our LDAP cert to the Oracle wallet on server and
    2.) put that file location into open_ssl function 2nd parameter and the password into 3rd parameter, and sslauth type 1 in 4th parameter.

    A bind with our ‘service user’ dn and password succeeded. We are not using advanced security.

    I think we will have to adapt your authentication function since we cannnot do a search until we have done a ‘service user’ dn bind as a valid service followed by the search for the dn of the user based on the userid. In our environment, the SunOne LDAP must have registered our Apex service as a valid service dn (e.g., Cold Fusion server has a service dn, Websphere app server has a service dn, etc.).

    Stay tuned.

    Pat

  14. Pat Miller said

    Correction to my last post.
    #2) should be type 2 in 4th parameter of the open_ssl call.

    Pat

  15. Pat Miller said

    Tyler,
    I don’t understand use of: l_ldap_user and l_ldap_pwd in this section of your authentication function:

    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( l_session, NULL, NULL, 1);
    end if;
    return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );

    Pat

  16. Pat Miller said

    Hi,
    Just succeeded in getting your function to work for us with our SunOne ldap server! Now just have to add it to Apex as our custom Auth function.

    Keys to getting ours to work:
    A.) had to get our SunOne LDAP server cert into the oracle wallet and identify that location in the open_ssl call; so we had a type 2 call — one way auth.
    B.) Our LDAP requires an application dn so we had to first bind with that dn and password so: l_ldap_user varchar2(256)had to be set to our service dn and l_ldap_pwd varchar2(256) had to be set to our dn password. Then the search for user could succeed.
    C.) The last problem I had was I was using the wrong search base in the ldap_globals package since our schema is so different from the exammples I had seen, but our LDAP administrator was very helpgul in this regard and gave me the correct search base for our LDAP. Once that was correct we were able to authenticate!

    Thanks for providing this posting!!

    Pat

  17. Pat Miller said

    Hi,
    We were so happy to get Tyler’s code adapted and working against our SunOne LDAP. Then we upgraded to Apex 3.2 and now we cannot authenticate. The function still works when we run it thru sql*plus or sql developer with some test users. We made no change to the application but now those same test users who we were authenticating are not able to authenticate — we get invalid credentials. Has anyone else upgraded to 3.2 and are using custom LDAP authentication?

    Pat

  18. SE said

    Sriram,

    It didn’t work for AD because domain\user isn’t valid for ldap only kerberose. You still need to find the full DN just like other ldap systems. Also disable SSL initially and make sure it works then. AD can be difficult when it comes to SSL and on port 389 they use TLS also. 636 is LDAPS. By the way this works for Novell E-directory.

  19. SE said

    PAT,

    I read on another forum that there is a bug. The solution was to change the authentication scheme and then re-apply the one you want.

  20. Jeff said

    Hello,
    Does anyone know how the ldap certificate is managed if you do not use the Oracle Wallet? I have a 3rd party secure ldap which we are trying to authenticate against using Tyler’s code but we don’t want to install OID, Oracle Wallet, or Oracle Certificate Authority. I am getting an error on the dbms_ldap.open_ssl call which has session, null for the wallet location and null for the password, the type is 1.
    thanks,
    Jeff

  21. TomS said

    thanks for the code, i needed full dn search by the uid :)by the way, SSL doesn’t work on my 10.2.0.3.0.

  22. Hi All – there is an Oracle Note via Oracles Metalink Site

    Note.215532.1 Support of PL/SQL API ( DBMS_LDAP ) and C API with Third Party LDAP v3 Directory Servers

    having spoken to Oracle in the last few days the 3 different SSL modes
    1,2 and 3. Mode 1 will only wotk with OID, you will need to use mode 2 and 3 for SSL to work with 3rd party direcotories and Oracle will not support issues with 3rd party directories. It does appear AD will work mode 2, and as Pat above mode 2 appears to work with Sun One.

    I will be testing Oracle DB 10.2.0.4 and sun One directory in mode 2 over a load balancer in the coming days and will let you the results.

    Darragh

  23. Hi Pat – I have a question for you please

    what steps did you follow to get the SunOne Ldap Certificate into an oracle wallet ?
    I know the steps for AD but am not familar with the SunOne directory.

    Darragh.

    • Pat Miller said

      Hi, Darragh,
      Regarding wallet and cert for SunOne:
      l_retval := DBMS_LDAP.open_ssl(l_session,’file:/var/opt/ORACLE/WALLETS/oracle’,’xxxxxxxxx’,2);

      We had to load the cert from our SunOne server into the Oracle wallets directory on the server as referenced above in dbms_ldap call. We got the cert from our SunOne ldap administrator. The xxxxxxxx is the wallet password as described by Tyler.

      Pat

      • darragh duffy said

        Hi Pat – thanks for your rely – one or two questions if you dont mind:

        1. when you loaded the cert from sun, did you use Oracle Wallet Manager or simple place into oracle_home/wallets/oracle ?

        2. Did you have to get an additional Cert fom Verisifn / Twarte etc. or simply did you just us the one issued by the Sun One Administrartor ?

        I have DBMS_LDAP working without the ldapS part, I have been trying to get the ldapbind.exe command to work first i.e.

        ldapbind -h smusdir.bms.com -p 636 -D “uid=core_sso,ou=nonpeople,o=bms.com” -w -U 2 -W “file:c:\coressl” -P
        but I get the Error Unknown Error –

        and if I get that to work I was going to progress to amending my PLSQL code, did you try the above LDABIND command that comes with Oracle DB / Oracle AS etc?

        thanks in advance for your time, Darragh

      • Pat Miller said

        Answers below each question

        >1. when you loaded the cert from sun, did you use Oracle Wallet >Manager or simple place into oracle_home/wallets/oracle ?

        I believe it was just a matter of copying into the wallets directory, but I will double check.
        The following helpful hint was posted at oracle apex (applies to us even though this one relates to AD):
        “The most important trick is that you have an oracle wallet with the root CA certificates in it so that the SSL bind can validate the certificates issued by your AD servers…. To set up ldap via pl/sql in a new server, we simply copy over our ldap wallet and use the exact same code in each instance. The ldap wallet holds nothing of value, just the root certificate authority certs, which aren’t a secret because they automatically get propagated to every windows machine in our domain.”

        >2. Did you have to get an additional Cert fom Verisifn / Twarte etc. >or simply did you just us the one issued by the Sun One >Administrartor ?
        NO, we just used the cert that was provided us by our SunOne ldap administrator– it was Equifax.

        >I have DBMS_LDAP working without the ldapS part, I have been trying >to get the ldapbind.exe command to work first i.e.

        >ldapbind -h smusdir.bms.com -p 636 -D >“uid=core_sso,ou=nonpeople,o=bms.com” -w -U 2 -W “file:c:\coressl” -P
        >but I get the Error Unknown Error –

        >and if I get that to work I was going to progress to amending my >PLSQL code, did you try the above LDABIND command that comes with >Oracle DB / Oracle AS etc?

        Once we had the certs in place we were able to do secure bind. I don’t think we tried a simple bind since we could not get any response other than verify that we could ping the ldap server. Once the certs were in place we tried command line ldaps call and the LDAP administrator checked logs and could see the bind. Then we proceeded with continued work on Tyler’s code to work with our LDAP. The one key item we did not have working properly initially was the search base we initially used was wrong — our schema (higher ed) is quite different from many of the examples used so we had to make sure our search base and other parameters in ldap_globals package were appropriate for our ldap.

  24. Pat Miller said

    Also, everything is working now in Apex 3.2. Belated thanks to SE concerning the bug. Re-applying the authentication scheme did the trick.

    Pat

  25. Eduardo said

    Hi,

    I’m testing with this function to get authenticated in a Active Directory Scheme but I’m not sure why I need to specified my complete name as username parameter (Benjamin Eduardo Garcia) and not my alias (beg) to get authenticated. Someone of you know why is happening that. I’m a newbie in Active Directory so I don’t know if I’m missing something in the search_base or any other place, the values of global variables are:

    g_host constant varchar2(255) := ‘192.168.78.3’;
    g_port constant number := 389;
    g_search_base constant varchar2(255) := ‘DC=patopur,DC=net’;

    BTW the same happens when I use APEX LDAP authentication scheme, any idea?

    Thanks

    Eduardo

  26. Hi All – I finally Managed to get this to work. The main Issue I had was the certificate seemed to be invalid.

    I also managed to verify that this work by using wireshark, intalled it on the Database Server and filter by ip.dst == 192.168.1.xxx

    works quiet nicely.

    also have this working in Novell, still having issues with Sun One but I think this is the certificate or a networking issue.

    Darragh.

  27. Chris Gilbane said

    We also were of the opinion that you needed ASO to use Oracle Wallet. However, our account manager also mentioned something similar to your original blog post. Likewise I am also trying to find something concrete on licensing – did you every find anything yourself?

    Cheers

    Chris

  28. Rohit said

    Hi,

    Thanks for posting the code. I tested the above function with OpenLDAP. The “user authentication” works fine when the password is in unencrypted form. but when the password is stored in encrypted for it does not work. I would greatly appreciate if anybody can provide some inputs/insights.

    Thank you.

    RJ

  29. brian said

    Is the posted code in the main section good for SSL?

  30. Dan said

    I am trying to retrieve the OID to check for password warn (2.16.840.1.113730.3.4.4) and passowrd expiration time (2.16.840.1.113730.3.4.5) in Sun One, can someone suggest. Thanks a lot in advance.

    Thank you

  31. Hi,
    To Brian — the posted code definitely works with SSL. You have to keep in mind the that the Oracle server where Apex is run from needs to have the Cert from your LDAP server in the Oracle wallet. This may not be required for OID, but it is required for other LDAP servers. I have been using Tyler’s code for our enterprise SunOne LDAP authentication which requires SSL and a service DN authorized to authenticate successfully for couple years now.

    Now I am wanting to retrieve attributes. Has anyone integrated DBMS_LDAP..search_s with Tyler’s code to retrieve user attributes and would be willing to share. Our LDAP engineers gave me some code to retrieve attributes for Perl and said it can be done but they have no clue how to do this in pl/sql. This I think would also answer Dan’s question if expiration time is an attribute in LDAP..

    Pat

    • Piyush Balan said

      Pat,
      you use a built-in calld DBMS_LDAP.first_attribute to get the attribute for the outcome of DBMS_LDAP.first_entry

      This is to be followed by DBMS_LDAP.get_values that in turn uses the output of DBMS_LDAP.first_attribute.

      You need to loop around the outputs of each and then use DBMS_LDAP.next_attribute and DBMS_LDAP.next_entry to keep progressing

      here’s a code snipped of something that I’ve written to run against Oracle 8.1.5:

      IF DBMS_LDAP.COUNT_ENTRIES (ld => vsession
      ,msg => vmessage
      ) > 0
      THEN
      — Get all the entries returned by our search.
      ventry := DBMS_LDAP.first_entry (ld => vsession
      ,msg => vmessage
      );
      — dbms_output.put_line (‘Retval: ‘||TRIM(TO_CHAR(iretval)));
      — dbms_output.put_line (‘Message: ‘||vmessage);
      END IF;

      <>
      WHILE ventry IS NOT NULL
      LOOP
      itotalCount := itotalCount + 1;
      — Get all the attributes for this entry.
      DBMS_OUTPUT.PUT_LINE(‘—————————————‘);
      vattr_name := DBMS_LDAP.first_attribute (ld => vsession
      ,ldapentry => ventry
      ,ber_elem => beber_element
      );
      <>
      WHILE vattr_name IS NOT NULL LOOP
      — Get all the values for this attribute.
      scvals := DBMS_LDAP.get_values (ld => vsession
      ,ldapentry => ventry
      ,attr => vattr_name
      );
      <>
      FOR ival IN scvals.FIRST .. scvals.LAST LOOP

      IF vattr_name = ‘uid’ AND vcreateString = 0
      THEN
      voutputString := voutputString||SUBSTR(scvals(ival),1,200);
      END IF;

      IF vattr_name = ‘EmailAddress’ AND vcreateString = 0
      THEN
      voutputString := voutputString||’,’||SUBSTR(scvals(ival),1,200);
      END IF;

      END LOOP values_loop;

      vattr_name := DBMS_LDAP.next_attribute (ld => vsession
      ,ldapentry => ventry
      ,ber_elem => beber_element
      );
      END LOOP attibutes_loop;

      ventry := DBMS_LDAP.next_entry (ld => vsession
      ,msg => vmessage
      );
      END LOOP entry_loop;

      Hope this helps.

  32. Piyush,
    Thank you very much for your reply. Yes, I was able to use the method you are describing to do exactly what I needed. It has been working well for us! I had posted on Apex OTN site and got some help from there.

    Pat

  33. Sanjay said

    Hi,
    I to have struggled to make it work over Secure Port 636 from Oracle to AD.
    My issue was related to Certificate which was given to me by Active Directory Admins , that CERT was importing fine but did not work and I always get “SSL Handshake error” in 11.2 DB environment and 10g environment, it was Unknown Error Occured.
    Certificate which we need from AD Admins is called “CA Certificate Chain” and there was even URL where even we can download from http://hostname/certsrv/
    You need to replace hostname with correct name for “Microsoft Certificate Services ” server. Your AD admins should help to tell you about this. When you go to above URL, I saw option to Download CA Certificate Chain which you need to download and save it and then import into Oracle Wallet Manager as a Trusted Certificate.

    Oracle Support Note : How to get SSL Certificates from a Microsoft Certification Services CA [ID 178806.1]

    I am not sure whether you need to self sign cert from the server where you are running this but this document was helpful.
    I have followed this document.

    How To Generate A Wallet Containing A Self Signed Certificate Using ORAPKI [ID 560982.1]

    — Change your password and wallet location and cn=hostname to hostname where you are running.

    orapki wallet create -wallet /home/oracle/wallettest/ -pwd password -auto_login

    orapki wallet add -wallet /home/oracle/wallettest/ -dn ‘cn=hostname’ -keysize 1024 -self_signed -validity 3065 -pwd password

    —–
    To Debug Issues, replace ADServerName with AD Info and Wallet Location and Password and your search base.

    ldapsearch -h ADServerName -p 636 -U 2 -W “file:/home/oracle/wallettest” -P “walletpassword” -d -1 -b “your search base” -s sub “(cn=xyz)” dn >/tmp/outfile1.txt 2>&1

    It would generate /tmp/outfile1.txt , you can check where it is failing.

    Good Luck.

  34. Susan Johnson said

    Hi,

    I’ve successfully created an AD user using DBMS_LDAP package using combination of Oracle Wallet and SSL, however, the user created is disabled. Can anyone tell me what am I doing wrong or how I do enable user’s account?

    FUNCTION Create_User(p_user VARCHAR2, p_ddso VARCHAR2, p_fullname VARCHAR2) RETURN VARCHAR2 IS
    my_session dbms_ldap.session;
    my_message dbms_ldap.message;
    user_attrs dbms_ldap.string_collection;
    user_array dbms_ldap.mod_array;
    user_vals dbms_ldap.string_collection;
    user_dn VARCHAR2(256);
    first_name VARCHAR2(50);
    last_name VARCHAR2(50);
    first_last_name VARCHAR2(100);
    middle_initial VARCHAR2(1);
    uname VARCHAR2(30);
    ddsogrp VARCHAR2(100);
    ddsoou VARCHAR2(256);
    ddsonm VARCHAR2(256);
    ddsogp VARCHAR2(256);
    ddsoau VARCHAR2(256);
    retval PLS_INTEGER;
    dpword VARCHAR2(30) := ‘xpasswd123’;
    ldap_valsb DBMS_LDAP.BERVAL_COLLECTION ;
    UTC_passwd RAW(400);
    l_user_type pls_integer;
    BEGIN
    retval := -1;
    l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;
    my_session := create_session;

    dbms_output.put_line(RPAD(‘simple_bind_s Returns ‘,25,’ ‘) || ‘: ‘|| TO_CHAR(retval));

    if ldap_globals_pkg.gc$use_ssl = true then
    dbms_output.put_line(‘use_ssl is true’);
    retval := DBMS_LDAP.open_ssl(my_session,
    ldap_globals_pkg.wallet_loc,
    ldap_globals_pkg.wallet_passwd,
    2); — NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3
    end if;

    retval := dbms_ldap.simple_bind_s(my_session, ldap_globals_pkg.gc$ldap_user, ldap_globals_pkg.gc$ldap_passwd);
    — issue the search
    user_attrs(1) := ‘*’;

    — retrieve all attributes
    retval := dbms_ldap.search_s(my_session, ldap_globals_pkg.gc$ldap_base, dbms_ldap.scope_subtree, ‘cn=’||p_user, user_attrs, 0, my_message);
    dbms_output.put_line(RPAD(‘search_s Returns ‘,25,’ ‘) || ‘: ‘|| TO_CHAR(retval));
    dbms_output.put_line(RPAD(‘LDAP message ‘,25,’ ‘) || ‘: ‘ ||RAWTOHEX(SUBSTR(my_message,1,8)) || ‘(returned from search_s)’);

    — count the number of entries returned
    retval := dbms_ldap.count_entries(my_session, my_message);
    dbms_output.put_line(RPAD(‘Number of Entries ‘,25,’ ‘) || ‘: ‘|| TO_CHAR(retval));
    dbms_output.put_line(‘————————————————‘);

    if retval = 1 then
    dbms_output.put_line(‘User have an AD account’);
    return ‘User already exist’;
    else
    dbms_output.put_line(‘Create user in Active Directory’);
    — create and setup attribute array for the New entry
    uname := lower(p_user);
    dbms_output.put_line(uname);
    ddsogrp := p_ddso;

    if substr(p_ddso, 1,3) = ‘020’ then
    ddsogrp := ‘0200’;
    end if;

    first_name := mr_api.delim_piece(p_fullname, ‘ ‘, 1);
    dbms_output.put_line(first_name);

    middle_initial := mr_api.delim_piece(p_fullname, ‘ ‘,2);
    dbms_output.put_line(middle_initial);

    last_name := mr_api.delim_piece(p_fullname, ‘ ‘, 3);
    dbms_output.put_line(last_name);

    first_last_name := first_name || ‘ ‘ || last_name;
    dbms_output.put_line(first_last_name);

    — add user to groups(highly managed group and ddso group)
    select ddso_ou, office_name
    into ddsoou, ddsonm
    from mr.ad_lookup
    where ddso_code = p_ddso;
    dbms_output.put_line(ddsoou);
    dbms_output.put_line(ddsonm);
    user_array := DBMS_LDAP.CREATE_MOD_ARRAY(12);

    user_vals(1) := uname;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘sAMAccountName’,user_vals);

    user_vals(1) := uname;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘CN’,user_vals);

    user_vals(1) := last_name;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘SN’,user_vals);

    user_vals(1) := first_name;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘givenName’,user_vals);

    user_vals(1) := middle_initial;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘initials’,user_vals);

    user_vals(1) := p_ddso;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘description’,user_vals);

    user_vals(1) := last_name||’, ‘||first_name||’ ‘||middle_initial||’.’;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘displayName’,user_vals);

    dbms_output.put_line(ddsonm);
    user_vals(1) := ddsonm;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘physicalDeliveryOfficeName’,user_vals);

    utc_passwd := UTL_RAW.cast_to_raw(convert(‘”‘ || dpword ||'”‘,’AL16UTF16LE’));
    ldap_valsb(1) := utc_passwd;

    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD,’unicodePwd’,ldap_valsb);

    –user_vals(1) := ‘FALSE’;
    — DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD,’enabled’,user_vals);
    user_vals(1) := ‘FALSE’;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD,’AccountDisabled’,user_vals);

    user_vals(1) := ‘User’;
    DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘objectClass’,user_vals);

    — DN for ENtry to be added under ldap_base
    user_dn := ‘CN=’||uname||’,OU=Users,OU=’||ddsoou ||’,OU=DDSO,’||ldap_globals_pkg.GC$ldap_base;
    dbms_output.put_line(rpad(‘Adding Entry for DN ‘,25,’ ‘) || ‘:[‘ || user_dn ||’]’);

    — Add new Entry to ldap directory
    retval := dbms_ldap.add_s(my_session,user_dn,user_array);
    dbms_output.put_line(rpad(‘add_s retruns ‘,25,’ ‘) || ‘: ‘ || to_char(retval));

    — Add the user to the portal group —
    ddsogp := ‘CN=GP-‘||ddsoou||’-HM-User,OU=Groups,OU=’||ddsoou ||’,OU=DDSO,’||ldap_globals_pkg.GC$ldap_base;
    ddsoau := ‘CN=’||ddsogrp ||’-allusers,OU=Groups,OU=’||ddsoou ||’,OU=DDSO,’||ldap_globals_pkg.GC$ldap_base;
    dbms_output.put_line(ddsoau);
    dbms_ldap.free_mod_array(user_array);

    — Add the user to the portal group —
    retval := add_in_group (my_session, ddsogp, user_dn) ;
    retval := add_in_group (my_session, ddsoau, user_dn) ;
    — free LDAP Message
    retval := dbms_ldap.msgfree(my_message);

    retval := DBMS_LDAP.unbind_s(my_session);

    DBMS_OUTPUT.PUT_LINE(RPAD(‘unbind_res Returns ‘,25,’ ‘) || ‘: ‘ || TO_CHAR(retval));

    DBMS_OUTPUT.PUT_LINE(‘Resultt -> OK’);

    RETURN ‘OK’ ;
    end if;
    — Handle Exceptions
    EXCEPTION
    WHEN OTHERS THEN
    GN$ErrCode := SQLCODE ;
    GC$ErrLib := SQLERRM ;
    DBMS_OUTPUT.PUT_LINE(‘ Error code : ‘ || TO_CHAR(GN$ErrCode));
    DBMS_OUTPUT.PUT_LINE(‘ Error Message : ‘ || GC$ErrLib);
    DBMS_OUTPUT.PUT_LINE(‘ Exception encountered .. exiting’);
    RETURN ( GC$ErrLib ) ;
    END;

    Susan

    • Delano said

      How did you get the certificate imported correctly into your Oracle Wallet. I have exported the certificates from our AD server and have imported into Oracle Wallet. but every time I try to run a connection i get the SSL handshake failed error.

  35. Hi! This is my 1st comment here so I just wanted to give
    a quick shout out and say I truly enjoy reading your
    blog posts. Can you recommend any other blogs/websites/forums that go over the same topics?
    Thanks for your time!

  36. There are also several important baseball tips on hitting involving the front foot.
    Edging the Phillies and was the man that fired Tony LaRussa 1.
    John Sterling: Let me get one thing out of 10 for
    their team, as well.

  37. Write more, thats all I have to say. Literally, it
    seems as though you relied on the video to make your point.
    You definitely know what youre talking about, why throw away your intelligence on just posting videos to your weblog when you could be giving
    us something informative to read?

  38. Excellent, what a webpage it is! This website provides valuable data to us,
    keep it up.

  39. Lori said

    Howdy! Would you mind if I share your blog with my twitter group?
    There’s a lot of folks that I think would really enjoy your content. Please let me know. Cheers

  40. I am in fact grateful to the holder of this website who has shared this fantastic paragraph at at this place.

  41. Good day! I know this is kinda off topic but I’d figured I’d ask.
    Would you be interested in trading links or maybe guest authoring a blog post
    or vice-versa? My website addresses a lot of the same subjects as yours and I think we
    could greatly benefit from each other. If you are interested feel free to send
    me an email. I look forward to hearing from you!
    Terrific blog by the way!

  42. Greetings from California! I’m bored to tears at work so I decided to check out your site on my iphone during lunch break. I love the knowledge you present here and can’t wait to take a look when I get home.
    I’m shocked at how quick your blog loaded on my cell phone .. I’m not even using WIFI, just 3G .
    . Anyways, very good site!

  43. I was wondering if you ever considered changing the structure
    of your website? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content
    so people could connect with it better. Youve got an awful lot of text for only having one or two images.
    Maybe you could space it out better?

  44. Greetings! I’ve been reading your blog for a while now and finally got the bravery to go ahead and give you a shout out from Atascocita Texas! Just wanted to mention keep up the good job!

  45. My coder is trying to convince me to move to
    .net from PHP. I have always disliked the idea because of the expenses.
    But he’s tryiong none the less. I’ve been using WordPress on
    a number of websites for about a year and am anxious about switching
    to another platform. I have heard very good things
    about blogengine.net. Is there a way I can import all my wordpress content into it?
    Any help would be really appreciated!

  46. Hi to all, for the reason that I am actually keen of reading
    this website’s post to be updated regularly. It includes pleasant data.

  47. My brother suggested I would possibly like this blog. He was entirely right.
    This post truly made my day. You cann’t consider just how a lot time I had spent for this info! Thanks!

  48. Hi Dear, are you actually visiting this website on a regular basis, if so after that you will without doubt
    get pleasant know-how.

  49. I am extremely impressed with your writing skills and also with the layout on your weblog.

    Is this a paid theme or did you customize it yourself?
    Either way keep up the nice quality writing, it’s rare to see a nice blog like this one today.

  50. I loved as much as you will receive carried out right here.
    The sketch is attractive, your authored material stylish.
    nonetheless, you command get bought an edginess over that you wish be delivering the following.
    unwell unquestionably come further formerly again since exactly the same nearly
    a lot often inside case you shield this increase.

  51. Thanks a bunch for sharing this with all folks you really realize what you are talking approximately!

    Bookmarked. Please also consult with my web site =). We may have a link change arrangement among us

  52. A fascinating discussion is worth comment. I think that
    you should write more on this subject matter, it may not be a taboo subject but generally people don’t discuss these subjects. To the next! All the best!!

  53. A highly-critical report said one in six of those requiring intensive care
    after surgery did not get it because of any sort of
    health problem or any side effect! This is
    not really good though as your gallbladder is removed laparoscopically
    or through open wounds. This paruresis female create-up wants to make you conscious with the effects of those people,” we can reach the goal of WHO. We control a master ova bank. Dandelion is useful to use on mucous membranes and reduce inflammation paruresis female in the body. 4 million 2010 revenue.

  54. I wander disconsolately into the bedroom, it called the guillotine becomes
    lighter in color, eventually turning gray or translucent white.
    Mr Ostler, please go on.

  55. Pretty nice post. I just stumbled upon your weblog and wanted to say that I’ve really enjoyed surfing around your blog posts. In any case I’ll be subscribing
    to your rss feed and I hope you write again soon!

  56. Web Site said

    Thank you for any other excellent article. The place else may just anyone
    get that kind of information in such a perfect way of writing?

    I’ve a presentation next week, and I am at the search for such info.

  57. I usually do not drop a comment, but I looked through some remarks on this page PL/SQL LDAP over SSL
    – Please Test Tyler Muths Blog. I do have a few questions for you if it’s allright. Is it only me or does it appear like some of the remarks look as if they are coming from brain dead individuals?😛 And, if you are posting on additional sites, I would like to follow you. Would you make a list of all of all your public pages like your twitter feed, Facebook page or linkedin profile?

  58. Tina said

    Way cool! Some very valid points! I appreciate you writing this post and also the rest
    of the site is also very good.

  59. Wilmer said

    great issues altogether, you just gained a brand new reader.
    What would you suggest in regards to your post that you made a few days ago?

    Any positive?

  60. Good replies in return of this difficulty with firm arguments and explaining all
    on the topic of that.

  61. Do you mind if I quote a couple of your posts as long as I provide credit and sources
    back to your webpage? My blog site is in the exact same area of interest as yours and my visitors
    would truly benefit from a lot oof the information yoou present
    here. Please let me know if his okay with you.
    Appreciate it!

  62. Hi to all, because I am in fact keen of reading this
    web site’s post to be updated on a regular basis. It
    includes fastidious material.

  63. The producer asserts you have the ability to merely sprinkle it on & see
    the pounds vanish!. This is since possibly it could change
    the efficacy of the medicines. The large majority of it is increased in India.

  64. Hі there just happened upon your website via Bing after I typed in, “PL/SQL LDAP over SSL – Please Test Tyler Muths Blog” or something similar (can’t quite remembeг eхactly).
    Anyways, I’m deliɡhted I found it simply because your
    content is exactly what I’m looking for (wгiting а university paper)
    and I hope you don’t mind if І collect some information from here anԁ I will of couгse credit you as the source.

    Thank you so much.

  65. live6 said

    Ιt’s remarkable for me to have a web page, which is helpful in support of my knowledge.
    thanks admin

  66. Hey there excellent website! Does running a blog like this require a great deal of work?
    I’ve no understanding of coding however I had been hoping
    to start my own blog in the near future. Anyhow, if you have any
    ideas or techniques for new blog owners please share.
    I understand this is off subject however I simply had
    to ask. Thanks a lot!

  67. A motivating discussion is worth comment. I think that you need to publish
    more on this subject matter, it might not be a taboo subject but typically people don’t
    discuss these subjects. To the next! All the best!!

  68. Hi, I do believe this is an excellent web site.
    I stumbledupon it😉 I may come back yet again since i
    have book-marked it. May and freedom is the best way to change, may you be rich and continue to
    guide other people.

  69. rummy journey

    PL/SQL LDAP over SSL – Please Test « Tyler Muth’s Blog

  70. Thanks for the useful information on credit repair on this amazing site. The thing I would advice people is to give up the particular mentality they will buy at this moment and pay out later. Being a society we all tend to repeat this for many things. This includes trips, furniture, along with items we want. However, you’ll want to separate your current wants from the needs. As long as you’re working to boost your credit score you have to make some trade-offs. For example you’ll be able to shop online to save money or you can look at second hand outlets instead of costly department stores intended for clothing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: