I am tring to use the DBMS_LDAP package in 8.1.7.4.3. I would like to query MS Active Directory to pull in current email addresses.

The DBMS_LDAP package was not in my database. I did find dbmsldap.sql and pvrtldap.plb. I ran these as internal but pvrtldap.plb returned a :

PLS-00201: identifier 'DBMS_LDAP_API_LIB' must be declared



Does anything else need to be installed? How can I use DBMS_LDAP in 8.1.7.

Thanks

Ok I think I fixed the problem I had above.
I ran the following as SYS and all compiled correctly.

@c:\oracle\ora81\RDBMS\admin\prvtldh.plb
@c:\oracle\ora81\RDBMS\admin\dbmsldap.sql
@c:\oracle\ora81\RDBMS\admin\prvtldap.plb

Thanks

Tom,

Can you show me how to do a update via LDAP?

Here is what I have

table EMPLOYEE (
emp_id number,
login_name varchar2(25),
first_name varchar2(50),
last_name varchar2(50),
phone_number varchar2(12),
smtp_email varchar2(128));

I will need to query LDAP once a day to keep this table up to date. It would update records that currently exist and insert new records that do not exist. The login_name is the distinct key in LDAP. We use Emp_id in about twenty different tables in our application.

Thanks for your time.

Tom, Love your site. Very good resource.

My question is this. I am trying to use DBMS_LDAP to get employee information from our MS LDAP Server. My packing is working fine with using SIMPLE_BIND_S but I need to use BIND_S. I have not found any docs on the method parameter and I will need to bind using method SSPI.

bind_s ( ld IN SESSION,
dn IN VARCHAR2,
passwd IN VARCHAR2,
meth IN PLS_INTEGER )

I have been using a little tool called LDP to browse the LDAP server. The bind looks like this

res = ldap_bind_s(ld, NULL, &NtAuthIdentity, 1158); // v.3
{NtAuthIdentity: User='NULL'; Pwd= <unavailable>; domain = 'NULL'.}
Authenticated as dn:'NULL'.

Can show how to use dbms_ldap.bind_s?

Thanks

Dear Sir,
I am using Oracle 8.1.7 database running in Solaris and Netscape LDAP Server. I am trying to using DBMS_LDAP package to verify the username and password of a application user but facing the error ORA -31202 : DBMS_LDAP : LDAP client/server error :No such object

The coding is as below :-
ldap_host := 'myldap.com.my' ;
ldap_port := '389';
ldap_user := 'chincl';
ldap_password := '123456';
my_session := DBMS_LDAP.init(ldap_host, ldap_port);
retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);

There is not problem for DBMS_LDAP.init as it is successfully return a session. The problem now is when the DBMS_LDAP.simple_bind_s function is executed and the password is not null (even it is a correct password) then I will get the error msg but if the password is null then the function will return DBMS_LDAP.SUCCESS.

Could somebody tell me what is going wrong will my code or anything else that I missed out?

Thanks in advanced.

Best Regards,
Chin Ching Liang

I am having the same problem as Chin Ching. When I pass any non-null password, then I get an error. Why is that?

Also dbms_ldap should be able to read ANY LDAP server right? Not just the OID. Can you confirm?

I could be way off here - don't have an ldap server at my disposal at the moment... However, the ldap_user parameter to simple_bind_s should be a distinguished name such as "cn=testuser,cn=users,dc=oracle,dc=com" or "cn=orcladmin" or thereabouts. It looks like the code here is just trying to pass a "raw" user name...

Not sure if that is relevant or not. May have to install OID just to find out :)

- Mark

First check the format of the "dn" for your test user in your LDAP using your LDAP browsing tool (Netscape address book can show this info if you use that client). I use a function similar to this to access Netscape LDAP:

FUNCTION ldap_login (
p_user IN VARCHAR2,
p_pass IN VARCHAR2,
p_server IN VARCHAR2 DEFAULT 'myserver.xyz.com',
p_port IN PLS_INTEGER DEFAULT 389
)
RETURN NUMBER
IS
v_sess DBMS_LDAP.SESSION;
v_dn VARCHAR2 (80) := 'uid='||p_user||','||'ou=FINANCE,o=ACME Corp,c=us';
retval PLS_INTEGER;
ignore PLS_INTEGER;
v_errm VARCHAR2 (1000);
BEGIN
DBMS_LDAP.use_exception := TRUE;
v_sess := DBMS_LDAP.init (p_server, p_port);
retval := DBMS_LDAP.simple_bind_s (v_sess, v_dn, p_pass);
ignore := DBMS_LDAP.unbind_s (v_sess);
RETURN retval; -- 0=success. check doc for other values returned
EXCEPTION
WHEN OTHERS
THEN
v_errm := SQLERRM;
ignore := DBMS_LDAP.unbind_s (v_sess);
IF v_errm LIKE '%Invalid credentials%' -- bad pass
OR v_errm LIKE '%No such object%' -- bad user or invalid base
THEN
RETURN 1; -- try again
ELSE
RETURN 2; -- see SQLERRM for more details
END IF;
END;


See $ORACLE_HOME/ldap/demo/plsql dir for examples.

Does LDAP require a seperate license?

Hitom,

we are trying to retreive LDAP data using DBMS_LDAP in a PL/SQL procedure. I got an error that says : ORA-12703: this character set conversion is not supported.

I have conversion problems with that line

my_vals := DBMS_LDAP.get_values(my_session, my_entry, my_attr_name);

Is there any otcher way tro retreive data from LDAP without usign DBMS_LDAP.GET_VALUES ? or please can you suggest how to use the convert function here, i am not sure how to specify the source and target NLS settings

Thanks in advance

Hi Tom,

We have successfully implmented LDAP using Oracle 10g database. Now recently we have migrated our data to EXADATA. Now the same piece of code giving error: ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.

Below is the sample code that we are using. Error is appearing at "lv_session := DBMS_LDAP.init (lv_ldap_server, lv_ldap_port);"


PROCEDURE ldap_authentication
AS
lv_retval PLS_INTEGER;
lv_retstr VARCHAR2 (4000);
lv_session DBMS_LDAP.SESSION;
lv_ldap_server VARCHAR2 (50) := 'Some Server Info';
lv_ldap_port VARCHAR2 (50) := 386;
lv_ldap_domain VARCHAR2 (50);
lv_ldap_base VARCHAR2 (256);
lv_dname VARCHAR2 (100);
lv_passwd VARCHAR2 (100);
lv_attrs DBMS_LDAP.string_collection;
lv_message DBMS_LDAP.MESSAGE;
lv_entry DBMS_LDAP.MESSAGE;
lv_entry_index PLS_INTEGER;
lv_attr_index PLS_INTEGER;
lv_ber_elmt DBMS_LDAP.ber_element;
lv_vals DBMS_LDAP.string_collection;

BEGIN
DBMS_LDAP.use_exception := TRUE;

BEGIN
lv_session := DBMS_LDAP.init (lv_ldap_server, lv_ldap_port);
EXCEPTION
WHEN OTHERS
THEN
p_errnum := -1;
p_errmsg := '1-While checking LDAP server ' || SQLERRM;
END;

lv_retstr := DBMS_LDAP.simple_bind_s (lv_session, lv_dname, lv_passwd);
.
.

lv_retval := DBMS_LDAP.search_s (lv_session, lv_ldap_base, DBMS_LDAP.scope_subtree, 'mail=' || p_uid || '*', lv_attrs, 0, lv_message);
.
.
.
.
lv_retval := DBMS_LDAP.unbind_s (lv_session);
END;


Please help me to understand is there any particular handling required on EXADATA or it is due to some SSL/Security setting.

@Anand : the 11g security model changed for ldap, mail and family, unless you run your scripts as SYS.

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/authorization.htm#DBSEG40012

Hi Laurent Schneider,

Thank you so much. The said link helped me lot and we able to close production issue quickly.

Below are two steps, with the help of them we can define ACL:-

Step 1: Create the Access Control List and Its Privilege Definitions


BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'FinnOneNetworkPrivilege.xml',
description => 'FinnOne specific user privilege to execute Network Utilities',
principal => 'REL_SSO',
is_grant => TRUE,
privilege => 'connect',
start_date => '2011-10-25 12:00:00.00 IST',
end_date => '2099-10-25 12:00:00.00 IST');
END;
/



Step 2: Assign the Access Control List to One or More Network Hosts
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'FinnOneNetworkPrivilegeMachines.xml',
host => '10.216.19.27',
lower_port => 389,
upper_port => 389);
END;
/


Regards, Anand Singh

Great job!

An additional 11g requirement is to have XDB installed, you probably already had it

Cheers
Laurent