Home » SQL & PL/SQL » SQL & PL/SQL » Get information from LDAP
Get information from LDAP [message #412294] Wed, 08 July 2009 09:45 Go to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Dear All,

Can we get information from ldap in oracle using the query below.

select distinguishedName FROM 'ldap://192.158.21.43/OU=developer,DC=IB,DC=COM'

Please Help me solve this one...

Best regards,
Srivardhan
Re: Get information from LDAP [message #412295 is a reply to message #412294] Wed, 08 July 2009 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you can't.

Regards
Michel
Re: Get information from LDAP [message #412307 is a reply to message #412295] Wed, 08 July 2009 10:57 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Michel,

IS there any alternative to get information of "LDAP" into oracle table

Best Regards,
Srivardhan
Re: Get information from LDAP [message #412310 is a reply to message #412307] Wed, 08 July 2009 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select distinct object_name from dba_objects where object_name like 'DBMS_LDAP%' order by 1;
OBJECT_NAME
------------------------------
DBMS_LDAP
DBMS_LDAP_API_FFI
DBMS_LDAP_API_LIB
DBMS_LDAP_UTL

4 rows selected.

Regards
Michel
Re: Get information from LDAP [message #412311 is a reply to message #412310] Wed, 08 July 2009 11:07 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are example programs for DBMS_LDAP in $ORACLE_HOME/ldap/demo/plsql.

The complete documentation is here.

(9i version. The 10g version of that book seems to be down/lost at the moment.)

Re: Get information from LDAP [message #412313 is a reply to message #412311] Wed, 08 July 2009 11:14 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Michel,

With Regards to your reply...

It will be great help.. if you tell how to get users and groups from the active directory...('ldap://192.158.21.43/OU=developer,DC=IB,DC=COM')i want to connect this oracle table contains users and groups to give authentication to other application created in codecharge studio..

Reply is appreciated.

Regards,
Srivardhan

[Updated on: Wed, 08 July 2009 11:15]

Report message to a moderator

Re: Get information from LDAP [message #412317 is a reply to message #412313] Wed, 08 July 2009 11:34 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, here is a package I have written for a similar purpose:

PROMPT CREATE OR REPLACE PACKAGE ldap
CREATE OR REPLACE PACKAGE ldap
AS

    l_ldap_host VARCHAR2(256) := 'XXX.XXX.XXX.XXX';
    l_ldap_port VARCHAR2(256) := '389';
    l_ldap_user VARCHAR2(256) := 'CN=xxxxxxx,OU=xxxxxxx,DC=xxxxxxx,DC=xxxxxxx';
    l_ldap_passwd VARCHAR2(256) := 'xxxxxxx';
    l_ldap_base VARCHAR2(256) := 'DC=xxxxxxx,DC=xxxxxxx';

    TYPE usr_rec_type IS RECORD (
         user_login     VARCHAR2(50),
         user_fullname  VARCHAR2(50),
         user_id        VARCHAR2(50)
     );

     TYPE usr_attr_type IS RECORD (
         id       NUMBER(20),
         login    VARCHAR2(256),
         attr     VARCHAR2(256),
         val      VARCHAR2(256)
     );

    TYPE usr_tab_type IS TABLE OF usr_rec_type;

    TYPE usr_tab_attr IS TABLE OF usr_attr_type;

    FUNCTION get_userdata ( searchstring IN VARCHAR2 )
      RETURN usr_tab_attr PIPELINED;

END;
/

PROMPT CREATE OR REPLACE PACKAGE BODY ldap
CREATE OR REPLACE PACKAGE BODY ldap
AS

FUNCTION get_userdata( searchstring IN VARCHAR2 )
RETURN usr_tab_attr pipelined
IS

    user_row usr_attr_type;

    l_filter VARCHAR2(256) := searchString;


    l_retval PLS_INTEGER;
    l_session DBMS_LDAP.session;
    l_attrs DBMS_LDAP.string_collection;

    v_entry_id NUMBER(12);
    l_message DBMS_LDAP.message;
    l_entry DBMS_LDAP.message;
    l_attr_name VARCHAR2(256);
    l_ber_element DBMS_LDAP.ber_element;
    l_vals DBMS_LDAP.string_collection;


BEGIN
    DBMS_LDAP.USE_EXCEPTION := TRUE;

    l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                                portnum  => l_ldap_port);

    l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
                                        dn => l_ldap_user,
                                        passwd => l_ldap_passwd
                                        );

    l_attrs(1)  := 'sAMAccountName';
    l_attrs(2)  := 'employeeNumber';
    l_attrs(3)  := 'displayName';
    l_attrs(4)  := 'description';
    l_attrs(5)  := 'telephoneNumber';
    l_attrs(6)  := 'facsimileTelephoneNumber';
    l_attrs(7)  := 'department';
    l_attrs(8)  := 'company';
    l_attrs(9)  := 'employeeID';
    l_attrs(10) := 'streetAddress';
    l_attrs(11) := 'mail';
    l_attrs(12) := 'c';
    l_attrs(13) := 'l';
    l_attrs(14) := 'postalCode';

    l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                   base     => l_ldap_base,
                                   scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                   filter   => l_filter,
                                   attrs    => l_attrs,
                                   attronly => 0,
                                   res      => l_message
                                   );

IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0
THEN

l_entry := DBMS_LDAP.first_entry(ld => l_session,
                                 msg => l_message);


<< entry_loop >>
v_entry_id := 0;
WHILE l_entry IS NOT NULL LOOP
    v_entry_id := v_entry_id + 1;
    -- Get all Attributes of the Entry
    l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
                                             ldapentry => l_entry,
                                             ber_elem =>
                                             l_ber_element);
    << attributes_loop >>
    WHILE l_attr_name IS NOT NULL
       --AND l_attr_name  IN('sAMAccountName','employeeNumber','displayName')
       LOOP

        l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                        ldapentry => l_entry,
                                        attr      => l_attr_name);


        << values_loop >>
        user_row.login := NULL;
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
            IF l_attr_name = 'sAMAccountName' THEN
                user_row.login := l_vals(i);
            ELSE
                user_row.login := NULL;
            END IF;
            user_row.id   := v_entry_id;
            user_row.attr := l_attr_name;
            user_row.val  := l_vals(i);
            pipe ROW ( user_row );

        END LOOP values_loop;
        l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                ldapentry => l_entry,
                                                ber_elem  => l_ber_element);
    END LOOP attibutes_loop;

    l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                    msg => l_entry);



END LOOP entry_loop;
END IF;

-- Close Connection to LDAP Server
l_retval := DBMS_LDAP.unbind_s(ld => l_session);

RETURN;
END;
END;
/


It returns a pipelined table with all the user information that is present in the l_attrs array, and can be used like this:

SELECT * FROM TABLE(ldap.get_userdata('(&(objectClass=user)(sAMAccountName=<Login>))'));




Re: Get information from LDAP [message #412325 is a reply to message #412317] Wed, 08 July 2009 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice one! ./fa/2115/0/

Regards
Michel
icon14.gif  Re: Get information from LDAP [message #412329 is a reply to message #412325] Wed, 08 July 2009 12:58 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Dear All,

You people has done a great help for me...you people are doing a great job..Keep Going....Smile


Best Regards,
Srivardhan
Re: Get information from LDAP [message #412333 is a reply to message #412329] Wed, 08 July 2009 13:20 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks for the feedback and the flowers.

./fa/2877/0/

Thomas

Re: Get information from LDAP [message #412377 is a reply to message #412333] Thu, 09 July 2009 00:46 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Thomas,

Sorry for eating your head too much.

Right now your query is working great, able to retrive users from the active directory. can you please tell me how to retrive groups also. Please help me in this because i want to give user level and also group level security.

Best Regards,
Srivardhan
Re: Get information from LDAP [message #412403 is a reply to message #412377] Thu, 09 July 2009 03:22 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, that is defined by the LDAP query string. You can basically get a group the same way you get the users, just change the objectClass to "group".

SELECT * FROM TABLE(ldap.get_userdata('(&(objectClass=group)(sAMAccountName=<groupname>))'));


The user also has an additional "memberOf" attribute, which might be of use, you could add that as an additional field in l_attrs in the code like this :

-----<snip>------
    l_attrs(13) := 'l';
    l_attrs(14) := 'postalCode';
    l_attrs(15) := 'memberOf';

    l_retval := DBMS_LDAP.search_s(ld       => l_session,
-----<snip>------


Then you get the groups where the user is a memberOf DIRECTLY. If you also need the groups the groups are in that he is a memberOf, then you would have to go through them recursively some way.

Re: Get information from LDAP [message #412418 is a reply to message #412403] Thu, 09 July 2009 04:21 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Thomas,

You are really great... Great solution........

Sorry for disturbing you too much....

Can we get passowrd of the users also similarly as we got users and groups. because i have to link passwords column to the application where user can get password.

Please..Please help me solving in this.....

Best regards,
Srivardhan

[Updated on: Thu, 09 July 2009 04:21]

Report message to a moderator

Re: Get information from LDAP [message #412423 is a reply to message #412418] Thu, 09 July 2009 04:43 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't get the password this way, since the password isn't stored, a password HASH is stored.

However, you DO log into the LDAP server with the user and password specified in l_ldap_user and l_ldap_passwd.

The same way you could have an additional procedure that just tries to log into the LDAP server with a user supplied username and password, and let the LDAP server to the authentication thing.

Like in this example.
Re: Get information from LDAP [message #412439 is a reply to message #412423] Thu, 09 July 2009 06:32 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Thomas,

The problem what we are facing now is we are using this oracle table(users,groups) and connecting to other application.

In the application we are linking this users column to login text in application.

In the application similarly we have to link AD passwords to the password text in the application.

Can we get that password in the oracle table and connect to the application. So, that it will solve our problem.

Please help me in this...

Best regards,
Srivardhan

[Updated on: Thu, 09 July 2009 06:38]

Report message to a moderator

Re: Get information from LDAP [message #412443 is a reply to message #412439] Thu, 09 July 2009 06:58 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can not get the password from the LDAP server. The password is stored nowhere on the LDAP server.

Re: Get information from LDAP [message #441104 is a reply to message #412443] Thu, 28 January 2010 20:10 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Thomas,

I am searching the net about how to migrate the data from ORALCE to Sun LDAP and have seen this chain and thought may be you can help me with my problem as well.
Could you please guide me or sugest me what is the best way to migrate the Oracle data from 8i to SUN LDAP.
Thanks alot in advance for your help.
Warm Regards
Sydney
Re: Get information from LDAP [message #612746 is a reply to message #441104] Thu, 24 April 2014 07:55 Go to previous messageGo to next message
faisalhaleem
Messages: 4
Registered: December 2013
Junior Member
Hi Thomas,

You API is just wonderful, I am trying to user that with group, I have updated the code as per your instruction but I am not able to get the group details when I run this can you please help.
SELECT * FROM TABLE(ldap.get_userdata('(&(objectClass=group)(memberOf=CN=VPN - EBS,OU=VPN,OU=Security Groups,OU=Domain Groups,DC=ace-ins,DC=com))'));

Regards
Faisal
Re: Get information from LDAP [message #612747 is a reply to message #612746] Thu, 24 April 2014 08:39 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Probably the search string doesn't match your AD structure. Does the search string return results in the Windows AD search interface?

Re: Get information from LDAP [message #612752 is a reply to message #412439] Thu, 24 April 2014 09:52 Go to previous messageGo to next message
EdStevens
Messages: 684
Registered: September 2013
Senior Member
srivardhan wrote on Thu, 09 July 2009 06:32

Can we get that password in the oracle table and connect to the application. So, that it will solve our problem.


Would you want the password to your bank account stored in such a fashion that it could so easily be retrieved?

Any password that can actually be retrieved by any method is, by definition, non-secure.

I once cancelled my subscription to a well-known web site when I called the support line and the tech actually told me what my password was.
Re: Get information from LDAP [message #612755 is a reply to message #612752] Thu, 24 April 2014 10:01 Go to previous messageGo to next message
Roachcoach
Messages: 1401
Registered: May 2010
Location: UK
Senior Member
EdStevens wrote on Thu, 24 April 2014 15:52
srivardhan wrote on Thu, 09 July 2009 06:32

Can we get that password in the oracle table and connect to the application. So, that it will solve our problem.


Would you want the password to your bank account stored in such a fashion that it could so easily be retrieved?

Any password that can actually be retrieved by any method is, by definition, non-secure.

I once cancelled my subscription to a well-known web site when I called the support line and the tech actually told me what my password was.



Indeed, and yet there are still startling numbers of places who ask for characters x, y & z of the password. Terrifying as it's usually banks.
Re: Get information from LDAP [message #612756 is a reply to message #612755] Thu, 24 April 2014 10:07 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Roachcoach wrote on Thu, 24 April 2014 17:01
Terrifying as it's usually banks.


I wonder if there are any historical reasons. Was a "password told to and verified by a person" ever used for some sort of "phone banking" in the days before computerized banking took over?

Kinda like security companies ask for the password if they call you when the alarm went off?


Re: Get information from LDAP [message #612759 is a reply to message #612755] Thu, 24 April 2014 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, 2 weeks ago I had to register to net enterprise site to declare the profit of my enterprise and get the social taxes we have to pay.
Guess what, the password can contain only [a-z][A-Z][0-9] characters, no special one, not even our French accented characters. Gee! I'm pretty sure they'll be hacked before long.

[Updated on: Thu, 24 April 2014 10:27]

Report message to a moderator

Re: Get information from LDAP [message #612761 is a reply to message #612756] Thu, 24 April 2014 11:05 Go to previous messageGo to next message
EdStevens
Messages: 684
Registered: September 2013
Senior Member
ThomasG wrote on Thu, 24 April 2014 10:07

Kinda like security companies ask for the password if they call you when the alarm went off?




Well, they could be entering what you give them into a hash routine that then matches the calculated hash against the stored hash -- the way oracle handles passwords.
But I doubt that's how it's done.
Re: Get information from LDAP [message #612762 is a reply to message #612759] Thu, 24 April 2014 11:10 Go to previous message
EdStevens
Messages: 684
Registered: September 2013
Senior Member
Michel Cadot wrote on Thu, 24 April 2014 10:25

Guess what, the password can contain only [a-z][A-Z][0-9] characters, no special one, . . .


ANDnot longer than 6 characters .... Shocked


Yep, I deal with a site like that.
Previous Topic: Case Statement - Best Practice - Close with Else Null?
Next Topic: Find out last four digits
Goto Forum:
  


Current Time: Fri Sep 30 15:53:18 CDT 2016

Total time taken to generate the page: 0.15892 seconds