1 2 3 Previous Next 30 Replies Latest reply on Jul 8, 2009 7:50 PM by 60437

    LDAP (MS AD) Group Authentication

    610880
      Hi to all,
      Right now I am stock in configuring Authentication Scheme for my APP. I am looking for solution on how to use Authentication Scheme to check AD LDAP for group membership to allow to login to my APP.
      I did spend last 2 days going over forums and didnt find a solution, but I know that it is possible. I am useing 11g DB + 3.2. Apex
      My current Authentication Scheme is set up as regular LDAP which means I have it pointed in DN String to domainname\%LDAP_USER% and it works perfectly. Using my current scheme all AD users are able to login to my app which I don�t wont.
      Could you please suggest me some kind of solution and steps I have to take to accomplish my task.

      Thanks
        • 1. Re: LDAP (MS AD) Group Authentication
          610880
          any ideas?????.....

          I think I am getting closer to get my problem solved by creating a special function.
          Using Sql I did create a function (see below) and then I create new Authentication Scheme from Scratch and did specify "return ldap_authenticate;" in Authentication Function area as well as set Session Not Valid Page to 101. The function was created under my workspace. Seen OK, but I cannot login to my application...just getting "Invalid session credentials" error......could you please explain what is wrong...

          create or replace function ldap_authenticate(p_username in varchar2,p_password in varchar2)
          return boolean
          is
          b_result boolean;
          c_auth_base varchar2(100) := 'cn=users,dc=domain,dc=local';
          c_group_base varchar2(100) := 'ou=groups,dc=domain,dc=local';
          c_host varchar2(100) := 'x.x.x.x';
          c_port varchar2(100) := '389';
          c_group varchar2(100) := 'apex_ad_group';
          begin
          b_result := apex_ldap.authenticate(
          p_username => p_username,
          p_password => p_password,
          p_search_base => c_auth_base,
          p_host => c_host,
          p_port => c_port);
          /* if authentication successful check group membership */
          if (b_result) then
          b_result := apex_ldap.is_member(
          p_username => p_username,
          p_pass => null,
          p_auth_base => c_auth_base,
          p_host => c_host,
          p_port => c_port,
          p_group => c_group,
          p_group_base => c_group_base);
          end if;
          return b_result;
          end ldap_authenticate;
          • 2. Re: LDAP (MS AD) Group Authentication
            Chris.F
            The APEX_LDAP package assumes that you are working with OID. The AD tree structure is different, groups are not stored in the same way. you'll need to use the DBMS_LDAP package and create your own procedures/function to query MS AD.

            Take a look at the latest PRO Oracle Application Express Book, it covers this in great detail.
            1 person found this helpful
            • 3. Re: LDAP (MS AD) Group Authentication
              610880
              yep, you were right.....
              I have the book you are talking about. Went to "Working with Groups in Microsoft Active Directory" section and under "Examining Active Directory’s Group Structure" did create LDAPWalk procedure....ther ran test :
              begin
              2 LDAPWalk(p_host => 'win2003vm',
              3 p_port => '389',
              4 p_user_dn => 'cn=test,cn=Users,dc=domain,dc=localdomain',
              5 p_password => 'password',
              6 p_base_dn => 'cn=Users,dc=domain,dc=localdomain',
              7 p_filter => 'cn=jes');
              8* end;
              and it works fine
              then I ran "Listing 13-25. Querying Groups in AD Using LDAPWalk" - I hope we have the same book :-) - it is all good
              The I did creaate whatever is in section "Listing 13-26. Creating the User Group Object Types", it is just object types:

              create or replace type
              ty_ldap_user_group as object(
              user_name varchar2(30),
              group_name varchar2(30));
              Type created.

              create or replace type
              ty_ldap_user_group_tbl as table of ty_ldap_user_group;
              Type created.


              And did create functiton from "Listing 13-27. Creating the get_user_groups Function to Show LDAP Groups"

              that all good....how and what should I specify in my Authentication Scheme????
              Thanks
              • 4. Re: LDAP (MS AD) Group Authentication
                Chris.F
                cool it looks like the same book,

                you still call a function within your Authentication Scheme but instead you have to create your own package to do login and check group membership.

                take a look at the DBMS_LDAP.compare_s function. It dose what you want you need, just fetch it's augments first before you do the compare.

                l_retval :=
                DBMS_LDAP.compare_s (
                ld => an active session opened with DBMS_LDAP.simple_bind_s that has rights to the AD attributes you want to compare,
                dn => users distinguishedName Ad attribute,
                attr => 'memberOf', The AD attribute that holds the users group membership
                VALUE => 'fully qualified group name');
                • 5. Re: LDAP (MS AD) Group Authentication
                  610880
                  quick question.....do I still need to complete steps from my previous post :
                  LDAPWalk procedure;
                  ty_ldap_user_group object;
                  ty_ldap_user_group_tbl table;
                  and get_user_groups Function.....do I still need to have all of this created??? As you said I still need to create my own package>>>

                  another question is.....as I am new to APEX , where should I put code which you gave me in your last post.....should I put it directly to authorization scheme or should create function first and in scheme just specify :

                  BTW:to create these object/table/function I went to my workspace to sql workshop and ran all these statements from there so it create objects ubder my worksapce. Another think is when I ran get_user_groups Function I got an error that attr_index := 1; is not declared. So I did add attr_index as PLS_INTEGER before BEGIN. Right?
                  One more is...should i change v_attrs (1) := 'cn'; value to my LDAP value like cn=users,dn=domain,db=local ????

                  Edited by: Yasen® on Apr 16, 2009 4:21 PM
                  • 6. Re: LDAP (MS AD) Group Authentication
                    Chris.F
                    Hi

                    hope you find this useful..

                    The LDAP WALK procedure and get_user_groups from the book just demo how you can connect to and query AD from Oracle. You don't need them for your authorization function. Although it helps to be able to look at your AD tree and see what value have actually been set or created.

                    the dbms.ldap_compare_s will need to be inside the function you call, so basicly you do this:

                    1) within your app, create a new Authentication scheme

                    2) for the authentication function assumming you create function call IS_LOGIN_VAILD call the function you create like this:
                    RETRUN my_package_name.IS_MY_LOGIN_VAILD;

                    your might find that it is easier to create your functions if you use [SQl developer|http://www.oracle.com/technology/products/database/sql_developer/index.html]

                    your function might look something like this pseudo code with some more if then logic. and exception handling.

                    FUNCTION is_my_login_vaild (
                    p_username IN VARCHAR2,
                    p_password IN VARCHAR2
                    )
                    RETURN BOOLEAN
                    IS
                    l_session DBMS_LDAP.session;
                    l_attrs DBMS_LDAP.string_collection;
                    l_message DBMS_LDAP.MESSAGE;
                    l_entry DBMS_LDAP.MESSAGE;
                    l_vals DBMS_LDAP.string_collection;
                    l_return BOOLEAN := FALSE;
                    BEGIN

                    -- first bind to LDAP and check the password

                    -- choose to raise exception
                    DBMS_LDAP.use_exception := TRUE;
                    -- Connect to ldap server and bind
                    l_session := DBMS_LDAP.init (hostname => 'LDAP HOST', portnum => '389');
                    l_retval := DBMS_LDAP.simple_bind_s (ld => l_session,
                    dn => p_username,
                    passwd => p_password);
                    -- find the full DN for the user
                    l_attrs (1) := 'distinguishedName';
                    l_retval :=
                    DBMS_LDAP.search_s (ld => l_session,
                    base => 'dc=foo,dc=bar',
                    SCOPE => DBMS_LDAP.scope_subtree,
                    filter =>'&(userPrincipalName='
                    || p_username
                    || ')(objectClass=user)',
                    attrs => l_attrs,
                    attronly => 0,
                    res => l_message
                    );
                    -- get DN enty retuned by our search
                    l_entry :=
                    DBMS_LDAP.first_entry (ld => l_session,
                    msg => l_message);
                    l_vals :=
                    DBMS_LDAP.get_values (ld => l_session,
                    ldapentry => l_entry,
                    attr => l_attrs (1));

                    -- with DN check and compare that user is member of group
                    l_retval :=
                    DBMS_LDAP.compare_s (ld => l_session,
                    dn => l_vals (0),
                    attr => 'memberOf',
                    VALUE => 'cn=mygroup,ou=foo....'
                    );

                    IF l_retval = DBMS_LDAP.compare_true
                    THEN
                    l_return := TRUE;
                    END IF;

                    return l_return;

                    EXCEPTION
                    WHEN OTHERS
                    THEN
                    BEGIN
                    DBMS_OUTPUT.put_line (' Error code : ' || TO_CHAR (SQLCODE));
                    DBMS_OUTPUT.put_line (' Error Message : ' || SQLERRM);
                    RETURN FALSE;
                    END;
                    END is_my_login_vaild;

                    Edited by: c4b0t42 on Apr 17, 2009 1:37 PM
                    1 person found this helpful
                    • 7. Re: LDAP (MS AD) Group Authentication
                      610880
                      thanks a lot, I'll try it ASAP. Suddenly, my DB got an ORC-12505 error.... as soon as I'll fix it - i'll try you solution.
                      BTW, shoud I use " create or replace FUNCTION is_my_login_vaild (...... " to create this function using SQLDeveloper? and what is " l_attrs (1) := 'distinguishedName'; it gives me error on ":=" " ???
                      Thanks

                      Edited by: Yasen® on Apr 17, 2009 9:23 AM

                      Edited by: Yasen® on Apr 17, 2009 9:37 AM
                      • 8. Re: LDAP (MS AD) Group Authentication
                        Chris.F
                        hi,

                        the code is not exact, you'll need to edit it, if it works out of the box I'd be amazed.

                        l_attrs (1) is an DBMS_LDAP.string_collection, it's a string array of AD attributes that your interested in and want to fetch in the search. you can also add others if you need them. like this:

                        l_attrs(1) := 'distinguishedName';
                        l_attr(2) := 'givenName';
                        l_attr(3) := 'sAMAccountName';

                        or

                        if you want to return all the attributes use
                        l_attrs(1) := '*';

                        The error is from above the call above to DBMS_LDAP.simple_bind_s was just missing a closing bracket and semicolon ');'

                        yes you can use create or replace function
                        1 person found this helpful
                        • 9. Re: LDAP (MS AD) Group Authentication
                          610880
                          here is what I have.....please see my comment in BOLD

                          create or replace FUNCTION is_my_login_vaild (
                          p_username IN VARCHAR2,
                          p_password IN VARCHAR2
                          )
                          RETURN BOOLEAN
                          IS
                          l_session DBMS_LDAP.session;
                          l_attrs DBMS_LDAP.string_collection;
                          l_message DBMS_LDAP.MESSAGE;
                          l_entry DBMS_LDAP.MESSAGE;
                          l_vals DBMS_LDAP.string_collection;
                          l_return BOOLEAN := FALSE;
                          BEGIN

                          -- first bind to LDAP and check the password

                          -- choose to raise exception
                          DBMS_LDAP.use_exception := TRUE;
                          -- Connect to ldap server and bind
                          l_session := DBMS_LDAP.init (hostname => 'my_ldap_host.donain.local', portnum => '389'); ----- MY LDAP HOST AND PORT
                          l_retval := DBMS_LDAP.simple_bind_s (ld => l_session,
                          dn => p_username,
                          passwd => p_password);
                          -- find the full DN for the user
                          l_attrs (1) := 'distinguishedName';
                          l_retval :=
                          DBMS_LDAP.search_s (ld => l_session,
                          base => 'dc=domain,dc=local', -----DN STRING OF MY DOMAIN or should I do 'cn=%LDAP_USERS%,cn=users,dc=domain,dc=local' ???
                          SCOPE => DBMS_LDAP.scope_subtree,
                          filter =>'&(userPrincipalName='
                          || p_username
                          || ')(objectClass=user)',
                          attrs => l_attrs,
                          attronly => 0,
                          res => l_message
                          );
                          -- get DN enty retuned by our search
                          l_entry :=
                          DBMS_LDAP.first_entry (ld => l_session,
                          msg => l_message);
                          l_vals :=
                          DBMS_LDAP.get_values (ld => l_session,
                          ldapentry => l_entry,
                          attr => l_attrs (1));

                          -- with DN check and compare that user is member of group
                          l_retval :=
                          DBMS_LDAP.compare_s (ld => l_session,
                          dn => l_vals (0),
                          attr => 'memberOf',
                          VALUE => 'cn=apex_app,ou=groups,dc=domain,dc=local' -------HERE IS DN STRING TO MY GROUP
                          );

                          IF l_retval = DBMS_LDAP.compare_true
                          THEN
                          l_return := TRUE;
                          END IF;

                          return l_return;

                          EXCEPTION
                          WHEN OTHERS
                          THEN
                          BEGIN
                          DBMS_OUTPUT.put_line (' Error code : ' || TO_CHAR (SQLCODE));
                          DBMS_OUTPUT.put_line (' Error Message : ' || SQLERRM);
                          RETURN FALSE;
                          END;
                          END is_my_login_vaild;


                          After I am running it under APEX>SQL Workshop>SQL Commands I am getting this error:

                          Error at line 54: PL/SQL: Statement ignored

                          *3. p_password IN VARCHAR2*

                          *4. )*
                          *5. RETURN BOOLEAN*
                          *6. IS*
                          *7. l_session DBMS_LDAP.session;*


                          and I have no idea what is that...sorry about that
                          Thanks

                          P.S. Do I need to change anything else in your code to modify it for my AD???
                          • 10. Re: LDAP (MS AD) Group Authentication
                            60437
                            Andrew.
                            base => 'dc=domain,dc=local', -----DN STRING OF MY DOMAIN or should I do 'cn=%LDAP_USERS%,cn=users,dc=domain,dc=local' ???
                            It's %LDAP_USER% and that is never used outside the DN String attribute of the applicaiton's LDAP authentication scheme.

                            Scott
                            1 person found this helpful
                            • 11. Re: LDAP (MS AD) Group Authentication
                              Chris.F
                              Hi,

                              see below function that will compile. It was missing a declaration for l_retval. Change the values and you should be set to go.

                              The function assumes that your login name is the same as your "userPrincipalName" attribite in AD. If your login name is the order pre 2000 login then I think you need to use "sAMAccountName" attribute instead. if that's the case then the filter =>'' needs to be changed to reflect that. I don't know of the top of my head what that change would need to be.
                              "MY LDAP HOST AND PORT"
                              yes use your LDAP host and port.
                              DN STRING OF MY DOMAIN or should I do 'cn=%LDAP_USERS%,cn=users,dc=domain,dc=local' ???
                              I use the DN string of my domain
                              HERE IS DN STRING TO MY GROUP
                              yes, just be sure you include you domain as well.


                              CREATE OR REPLACE FUNCTION is_my_login_vaild (
                              p_username IN VARCHAR2,
                              p_password IN VARCHAR2
                              )RETURN boolean IS
                              l_session DBMS_LDAP.session;
                              l_attrs DBMS_LDAP.string_collection;
                              l_message DBMS_LDAP.MESSAGE;
                              l_entry DBMS_LDAP.MESSAGE;
                              l_vals DBMS_LDAP.string_collection;
                              l_retval PLS_INTEGER;
                              l_return BOOLEAN := FALSE;
                              BEGIN
                              -- first bind to LDAP and check the password

                              -- choose to raise exception
                              DBMS_LDAP.use_exception := TRUE;
                              -- Connect to ldap server and bind
                              l_session := DBMS_LDAP.init (hostname => 'ldap host', portnum => '389');
                              l_retval := DBMS_LDAP.simple_bind_s (ld => l_session,
                              dn => p_username,
                              passwd => p_password);
                              -- find the full DN for the user
                              l_attrs (1) := 'distinguishedName';
                              l_retval :=
                              DBMS_LDAP.search_s (ld => l_session,
                              base => 'DC=foo,DC=bar,DC=com',
                              SCOPE => DBMS_LDAP.scope_subtree,
                              filter =>'&(userPrincipalName='
                              || p_username
                              || ')(objectClass=user)',
                              attrs => l_attrs,
                              attronly => 0,
                              res => l_message
                              );
                              -- get DN enty retuned by our search
                              l_entry :=
                              DBMS_LDAP.first_entry (ld => l_session,
                              msg => l_message);
                              l_vals :=
                              DBMS_LDAP.get_values (ld => l_session,
                              ldapentry => l_entry,
                              attr => l_attrs (1));

                              -- with DN check and compare that user is member of group
                              l_retval :=
                              DBMS_LDAP.compare_s (ld => l_session,
                              dn => l_vals (0),
                              attr => 'memberOf',
                              VALUE => 'CN=groupname,OU=foo,OU=bar,DC=foo,DC=bar,DC=com'
                              );

                              IF l_retval = DBMS_LDAP.compare_true
                              THEN
                              l_return := TRUE;
                              END IF;

                              return l_return;

                              EXCEPTION
                              WHEN OTHERS
                              THEN
                              BEGIN
                              DBMS_OUTPUT.put_line (' Error code : ' || TO_CHAR (SQLCODE));
                              DBMS_OUTPUT.put_line (' Error Message : ' || SQLERRM);
                              RETURN FALSE;
                              END;
                              END is_my_login_vaild;

                              Edited by: c4b0t42 on Apr 17, 2009 3:52 PM
                              1 person found this helpful
                              • 12. Re: LDAP (MS AD) Group Authentication
                                Chris.F
                                Hi,

                                you don't have to use AD groups to limit who can login to your app. You can create an Authorization Scheme that grants the authorization to use your app. Then you don't have to worry about a login function for a custom Authentication Scheme.
                                • 13. Re: LDAP (MS AD) Group Authentication
                                  610880
                                  WOOOOOOOW, it works!!! Thank a lot.
                                  The only question is about filtering userPrincipalName.
                                  With current code I can login using "username@domain.local" scheme......how would I go for just "username"?
                                  I did change in filter userPrincipalName to sAMAccountName (as I see from my AD configuration I have to use sAMAccountName to proceed with login).
                                  Here is my LDAP settings on this:

                                  sAMAccountName is apex_test (that is the username i want to use to login)
                                  userPrincipalName is apex_test@domain.local
                                  distinguishedName is CN=test\, apex,CN=Users,DC=nursing,DC=local

                                  I did try to change l_attrs (1) := 'distinguishedName' to l_attrs (1) := 'sAMAccountName' .... not working
                                  thanks
                                  • 14. Re: LDAP (MS AD) Group Authentication
                                    Chris.F
                                    hi,

                                    You can just add the string '@domain' to the user name. I don't know if you can use the sAMAccountName as a valid login name.

                                    eg:
                                    l_username := p_username || '@domain.local';
                                    1 2 3 Previous Next