1 2 Previous Next 22 Replies Latest reply on Nov 26, 2013 9:46 PM by Richard Harrison .

    LDAP Authentication Via Groups


      I have been hitting my head against this problem for awhile now... and have had very little success.

      What i am trying to do is have my application authenticate via our Active Directory Server (LDAP). I have managed to get as far as giving every user in Active Directory the ability to logon. What i want, however, is only a select group to login, not the whole domain.

      Here is what i have so far for Authentication (LDAP):
      HOST: [Server Name]
      Port: 389
      No SSL
      Use Exact DN: [Domain]\%LDAP_USER%

      As i said this works fine for all users in Active Directory but i am lost in trying to get only a specific group to have access.

      I have tired all manner of DN strings (and have had trouble with all that i have tried) as well as some code in the Login Page using the APEX_LDAP functions. These functions would work, i believe, but they keep returning an error:
      ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db0

      Considered using the Post-Authentication Process for this but am not sure...

      Any Help on this would be great!!

        • 1. Re: LDAP Authentication Via Groups
          Frank van Bortel
          Active Directory does NOT allow anonymous binds, so you will have to provide valid credetials to AD before trying to read AD.
          As you are in the process of authentication, using the offered credentials is a good idea.
          If they fail, the credentials are no good!
          • 2. Re: LDAP Authentication Via Groups
            Hi, thanks for the response!

            Could you explain a bit more though? I am new to LDAP configuration...

            But if i am getting your meaning when I authenticate using the [Domain]\%LDAP_USER% then use a post-process to verify that user is in the right AD group?

            Something like:
                 p_username => %LDAP_USER%,
                 p_pass => ,
                 p_auth_base => [DOMAIN]\%LDAP_USER%,
                 p_host => '[AD SERVER]',
                 p_port => 389,
                 p_group => '[Group Name]',
                 p_group_base => '[...?]')
                 return true;
            END IF;     
            • 3. Re: LDAP Authentication Via Groups
              Frank van Bortel
              You could combine the two processes of authentication and authorization by providing the password in your call.
              Don't forget: the APEX_LDAP package does not search, it expects the group dn to be in the base you provide.

              ~As I did not like that, I rewrote portaions of the APEX_LDAP package~
              • 4. Re: LDAP Authentication Via Groups
                Tom Petrus
                I struggled a long time over this. I should probably revisit my authentication/authorisation and see if i could write a custom authentication (or plugin).
                Anyway, below is my code i put in the source field of my authentication scheme. Post-Authentication Procedure Name is set to "ad_post_auth".
                A user logs in and gets authenticated. My post auth process then retrieves all groups the user is a part of, and stores these as a comma-seperated value in an application item (cant be set from browser option) (the item i use is called AI_USER_AD_GROUPS).
                procedure ad_post_auth is
                   v_username     varchar2(250);
                   v_password     varchar2(250);
                   v_groups       varchar2(4000);
                   v_retval       BINARY_INTEGER;
                   ldap_session   DBMS_LDAP.session;
                   l_attributes   DBMS_LDAP.string_collection;
                   return_message DBMS_LDAP.message;
                   my_entry       DBMS_LDAP.message;
                   entry_index    BINARY_INTEGER;
                   attr_index     BINARY_INTEGER;
                   my_vals        DBMS_LDAP.STRING_COLLECTION;
                   my_ber_elmt    DBMS_LDAP.ber_element;
                   my_attr_name   VARCHAR2(256);   
                   v_username := apex_util.get_session_state('P101_USERNAME');
                   v_password := apex_util.get_session_state('P101_PASSWORD');
                   --apex_debug_message.log_message('begin post auth');
                   --user or password may not be null
                   IF v_username IS NULL OR v_password IS NULL THEN
                   END IF;
                   --apex_debug_message.log_message('user and pw not empty');
                   -- Choosing exceptions to be raised by DBMS_LDAP library.
                   DBMS_LDAP.USE_EXCEPTION := TRUE;
                   --create a session
                   ldap_session := DBMS_LDAP.init(<AD SERVER>,'389');
                   --apex_debug_message.log_message('session created');
                   --authenticate user through simple bind
                   v_retval := DBMS_LDAP.simple_bind_s(ldap_session,
                   --apex_debug_message.log_message('bind return: '||v_retval);
                   IF v_retval &lt;&gt; DBMS_LDAP.SUCCESS THEN
                      RETURN; --authentication failed
                   END IF;
                   --apex_debug_message.log_message('bind ok');
                   --get the groups the user is a member of and store them
                   --this must be done in this authentication process because we need
                   --a valid session. The AD server does not allow anonymous binds.
                   l_attributes(1) := 'memberOf';
                   v_retval := DBMS_LDAP.search_s(ldap_session, 
                   IF v_retval &lt;&gt; DBMS_LDAP.SUCCESS THEN
                      --user is authenticated, some error popped up during the search
                      -- you'll have to manually debug this situation!
                      -- This error doesn't stop the user from authenticating. He simply
                      -- won't have any groups assigned.
                   END IF;
                   -- count the number of entries returned
                   v_retval := DBMS_LDAP.count_entries(ldap_session, return_message);
                   -- If ever multiple attributes are being queried, the following code can handle them
                   -- Just provide a handling block for your attribute.
                   -- get the first entry
                   my_entry := DBMS_LDAP.first_entry(ldap_session, return_message);
                   entry_index := 1;
                   -- Loop through each of the entries one by one
                   while my_entry IS NOT NULL loop  -- print the current entry      
                      my_attr_name := DBMS_LDAP.first_attribute(ldap_session,my_entry, my_ber_elmt);
                      attr_index := 1;
                      while my_attr_name IS NOT NULL loop
                         my_vals := DBMS_LDAP.get_values (ldap_session, my_entry, my_attr_name);
                         if my_vals.COUNT > 0 then
                            FOR i in my_vals.FIRST..my_vals.LAST loop            
                               v_groups := v_groups ||lower(substr(my_vals(i), 4, instr(my_vals(i), ',') - 4))||';';
                            end loop;
                         end if;
                         my_attr_name := DBMS_LDAP.next_attribute(ldap_session,my_entry, my_ber_elmt);
                         attr_index := attr_index+1;
                      end loop;
                      my_entry := DBMS_LDAP.next_entry(ldap_session, my_entry);
                      entry_index := entry_index+1;
                   end loop; 
                   APEX_UTIL.set_session_state('AI_USER_AD_GROUPS', v_groups);
                   -- unbind from the directory 
                   v_retval := DBMS_LDAP.unbind_s(ldap_session); 
                I then have an authorisation scheme (or schemes) which tests if the user can access pages depending on the groups he belongs to (and those groups are stored in AI_USER_AD_GROUPS)
                Scheme Type: Exists SQL Query
                SELECT 1
                  FROM (
                   SELECT substr(split_string, position_from + 1, position_to - position_from - 1) groep
                     FROM (SELECT split_string,
                                  decode(rownum - 1, 0, 0, instr(split_string, ';', 1, rownum - 1)) position_from,
                                  instr(split_string, ';', 1, rownum) position_to
                             FROM (SELECT :ai_user_ad_groups split_string
                                     FROM dual)
                          CONNECT BY LEVEL <= length(split_string)
                   WHERE position_to > 0
                WHERE groep = lower('<GROUPNAME>');
                ----- (Edit Feb 2013)
                Meanwhile, I've created an authorisation type plugin which i can easily use in all my applications i use this post-auth code in (or simply copy-subscribed to the authentication scheme that has this code). It allows to check multiple groups at once.
                With PLSQL code:
                function check_ad_group_membership (
                   p_authorization in apex_plugin.t_authorization,
                   p_plugin        in apex_plugin.t_plugin )
                   return apex_plugin.t_authorization_exec_result
                   l_test_group  varchar2(4000) := lower(p_authorization.attribute_01);
                   l_user_groups varchar2(4000) := lower(:ai_user_ad_groups);
                   l_count  number;
                   l_result apex_plugin.t_authorization_exec_result;
                  WITH user_groups AS
                    (SELECT val1
                      xmltable('/root/e/text()' passing xmltype('<root><e>'
                      || REPLACE(l_user_groups,';','</e><e>')
                      || '</e></root>') columns val1 VARCHAR2(50) path '/' )
                    test_groups AS
                    (SELECT val2
                      xmltable('/root/e/text()' passing xmltype('<root><e>'
                      || REPLACE(l_test_group,',','</e><e>')
                      || '</e></root>') columns val2 VARCHAR2(50) path '/' )
                  SELECT count(*)
                  INTO l_count
                  FROM user_groups  
                  WHERE EXISTS
                    (SELECT 1 FROM test_groups WHERE val2 = val1);
                   l_result.is_authorized := l_count > 0;
                   return l_result;
                end check_ad_group_membership;
                And 1 Custom Attribute
                <li>Scope: Component
                <li>Label: AD Group Name(s)
                <li>Type: Text
                <li>Required: Yes
                (End Edit Feb 2013) -----

                May not be ideal but works well so far :)

                Edited by: Tom on Feb 13, 2013 9:40 AM
                Fixed the &lt;&gt; sign not showing up, edited in my updated group check.
                • 5. Re: LDAP Authentication Via Groups
                  Patrick Wolf-Oracle

                  BTW for those who want to use LDAP for password verification but only allow a limited number of people to actually use an application. In such a case these two actions should be separate.
                  It's not a good practice to put such security checks into the "Post Authentication".

                  1) For LDAP password verification use an authentication. In most cases you should be fine to use one of our out-of-the-box authentication schemes like LDAP authentication, SSO, Header Variable, ...

                  2) To restrict the number of which are actually allowed to use your application use the application level authorization attribute. (Shared Components > Security Attributes > Authorization). This will be checked after a successful login and will prevent unauthorized users your application. This authorization scheme could perform a LDAP lookup with our APEX_LDAP package or check agains a local user table and restrict the SQL statement to :APP_USER which returns the currently logged in user.

                  My Blog: http://www.inside-oracle-apex.com
                  APEX Plug-Ins: http://apex.oracle.com/plugins
                  Twitter: http://www.twitter.com/patrickwolf
                  1 person found this helpful
                  • 6. Re: LDAP Authentication Via Groups
                    Tom Petrus
                    But if your AD server does not allow anonymous binds, and you need the password? The password gets blanked out after the authentication (which is good), but you can't do a bind then anymore, unless you use some hardcoded credentials?
                    I know i tried to work with apex_ldap for the group membership checks, but for the hell of it, i couldn't get this to work. Doesn't it muck around with the oracle directory groups?
                    And when not using the 'cn=,dn=...', but want another field eg uid or the logon name (samaccountname), wouldn't we have similar problems to what portaldude is having in this thread: Help with Authentication (APEX_LDAP.AUTHENTICATE) ?
                    • 7. Re: LDAP Authentication Via Groups
                      Hi All,

                      Thanks for all your responses!

                      I came up with a method that seems to work quite well with the authentication. I am not entirely sure how secure it is as far as other people on the domain logging in but given the nature of this project we were not too concerned with it (people on the domain are trusted as is its just we didn't want everyone to go in and start making changes).

                      I have a copy of Pro Oracle Application Express by John Scott and Scott Spendolini (2008 edition), which has an entire chapter devoted to LDAP and Single Sign-On. The chapter goes back and forth on topics for authentication for both Microsoft Active Directory and Oracle Internet Directory. It gives detailed descriptions and code examples for both systems which was very helpful. However it specifically states:

                      ...we can’t just use the APEX_LDAP routine to check group membership against a non-OID directory. This is because OID stores the relationship between users an groups in a particular way that other LDAP servers may not follow (and, in fact, typically do not).

                      The book continues on to form examples of how to access the data in AD without the APEX_LDAP functions. However when i attempted to use the DBMS_LDAP functions (which the book suggests) I continued to get something similar to the following error for all the LDAP functions i tried:

                      31202. 00000 - "DBMS_LDAP: LDAP client/server error: %s"
                      Cause: There is a problem either on the LDAP server or on the client.
                      Action: Please report this error to the LDAP server administrator or
                      your Database administrator.

                      I am not the DB administrator so don't have access to see whats going on under the hood... so this forced me to find an alternative route.

                      Just to reiterate here is a summary of my problem in working to get my application working with LDAP (to authenticate with our Active Directory Server):

                      First, while APEX has the ability to use LDAP it seems very limited when interacting with a non-Oracle LDAP server. We can have everyone in the whole domain login using the following settings in a Authentication Scheme:
                      •     Host: [Server Name]
                      •     Port: 389
                      •     DN: [Domain]\%LDAP_USER%

                      Note: while the DN string +“CN=%LDAP_USER%, CN=Users, DC=[Domain], DC=com”+ authenticates, it only works for the Full Name of the user – it DOES NOT authenticate on the username (i.e. the user must put in Jim S. Walker instead of JSW).

                      My solution to limit the users only to a specific set, then, was to use an existing "EMAIL_SUBSCRIPTION" table, which had a list of all the usernames of those who got email connectivity from the system. Now on the login page i created the following item validation:
                      SELECT EMAIL_USERNAME
                      FROM EMAIL_SUBSCRIPTION
                      WHERE EMAIL_USERNAME = UPPER(:P101_USERNAME)
                      This way the authentication is handled by the AD server while the actual set of users who can log in is determined by this locally-managed table (the Application Users' Group as it were).

                      The way I see it, this method is still secure since it uses our AD to authenticate while saving lots of headache in trying to create, manage, and execute some complex LDAP routine that might not even work due to our server setup. The only other downside is having to locally manage a table of users but the way i see it this should not be a big problem as it would have to be handled that way in AD otherwise.

                      So far i have not had any problems with this implementation, though i know it may seem a bit jury rigged. Therefore, as far as i am concerned this problem has been solved but i am open to further suggestions so wont mark the thread as "answered" quite yet.

                      Thanks again for all your responses,

                      • 8. Re: LDAP Authentication Via Groups
                        hi Orc,

                        I am new to Oracle APEX and I am also trying to implement AD authentication for a user group for a particular application. Can you please advice me like how my approach should be and I am using Oracle APEX 4.0

                        Can I just create an authentication schema (using shared components - security) for my application using the LDAP Directory option and then just provide the details Host/Port/DN string.

                        Can you please advice me on this.

                        • 9. Re: LDAP Authentication Via Groups
                          Tom Petrus
                          Hi Orc555,
                          your post makes a very nice summary of problems i too ran into. I didn't have the book, which would have saved me a lot of headache. Indeed, apex_ldap mucks around with the OID, and i only found out after digging around on the mighty internet.The same goes for the DBMS_LDAP and DMPS_LDAP_UTL packages.
                          With your DN String set to DOMAIN\%LDAP_USER%, can your users use their windows logon-name to log in? I myself use %LDAP_USER%@DOMAIN, and this works well!
                          And while i agree with Patrick about my code not being the ideal place, i so far can't think of any other place to perform my code. I am not authorizing in my post-auth code, only getting the user AD groups. We really didn't want to maintain yet another table of user information, and not many people here are familiar with Oracle, SQL and in the least Apex. The less maintenance to do in another table, the better.
                          Very informative thread, thanks for the infos :)
                          • 10. Re: LDAP Authentication Via Groups

                            As I mentioned in my summary post above, APEX and AD LDAP don’t get along… while it would be nice to use the functions APEX provided in APEX_LDAP, it just doesn't work. However, there are still lots of workarounds.

                            To start, yes you should be able to use an authentication scheme but for the “Exact DN” use:
                            [Your Domain Name]\%LDAP_USER%
                            That should authenticate for all users on the domain. Also this authenticates via their username not the name of their user object in AD. (whereas the normal DN strings do not authenticate on username).

                            To authenticate only a select group, you have a couple of options:

                            Firstly, my method works pretty well (explained above) – with using a separate table that has all the group usernames in it. This method works as a great, rather simple, workaround – and I am confident that it is secure. Let me know if you want me to go into more detail with what I did.

                            The other option is to create a custom LDAP check which would download the user’s information, parse it, and then decide if it is in the group. As I said for whatever reason I couldn’t get any of the DBMS_LDAP functions to work on my system… but if they do for you, this would allow you to build a fairly comprehensive AD_LDAP – APEX connection. (see Tom’s Post/Code above).

                            With this I would suggest looking into Pro Oracle Application Express by John Scott and Scott Spendolini. It’s a great reference for all things APEX and there is a whole section dedicated to this exact topic: Group Authentication for AD. They have full code examples on how to test and parse your AD data with custom LDAP functions.

                            Let me know if you have any specific questions.


                            Interesting, I have not tried the +%LDAP_USER%@DOMAIN+ syntax in APEX but DOMAIN\%LDAP_USER% seems to work fine – authenticating off of the Windows username.

                            Totally understand not wanting to have to manage another table… that was the big con when implementing my method above. But due to the nature of my project the easier way was the better one for us. I may yet play with it later to see if I can get it to work with a post-auth process like you did as that’s what I was trying to do before I came up with the page validation.

                            One thing with the table though is you could create a separate page to modify the user’s table that way you could manage the group fairly easily – I am tempted to do this myself and will try to do so when I get some spare time. For now we don’t have all that many users so managing the rather small table from SQL Developer or APEX itself is not too much of a problem.

                            Other thought: yes I noticed how little information there was online about LDAP AD Authentication with Groups… so I am glad to see all my notes and these comments in a single thread – hopefully will help others who have the same setup.

                            • 11. Re: LDAP Authentication Via Groups

                              for anyone taking your code from the March 2012 post, the following would be good:

                              currently we only see

                              IF v_retval  <> DBMS_LDAP.SUCCESS THEN

                              but what you mean is

                              IF v_retval  &lt;&gt; DBMS_LDAP.SUCCESS THEN

                              Would you please consider editing your code sample this way?

                              Thanks a lot
                              • 12. Re: LDAP Authentication Via Groups
                                Tom Petrus
                                I've fixed them up, I wasn't even aware of it!
                                • 13. Re: LDAP Authentication Via Groups

                                  There is another question that might be interesting for most implementing a post authentication procedure: in your authorization plugin for group checking, you are referring to p_authorization.attribute_01.

                                  I tried to address p_authentication.attribute_01 in the code for the post authentication procedure, in an attempt to get the configured host name from the LDAP authorization plugin into the code.
                                  At lease host and port would need to be adjusted only once.

                                  I'm getting an error though:

                                  PLS-00201: identifier 'P_AUTHENTICATION.ATTRIBUTE_01' must be declared

                                  Obviously, p_authentication.attribute_01, is not in the scope for a post authentication procedure.

                                  Regards, Tom
                                  • 14. Re: LDAP Authentication Via Groups
                                    Tom Petrus
                                    The post-authentication code still has to be the first block of code which binds to the ldap directory. The second code block is the code i originally used in an authorisation scheme. Afterwards however i made a plugin which contains the third code block, because i didn't want the whole code block in each authorisation scheme and maintaining a plugin is a lot easier. The "p_authentication.attribute_01" part is only valid in the plugin code, and it references the first custom attribute of the plugin, which is meant to contain a comma-delimited list of groups to check for. So yes, you can not use "p_authentication.attribute_01" in post-authentication code as that is indeed (way) out of scope! Not only because the attribute is only valid in the plugin code, but also because authorisations are ran after the complete authentication process.
                                    1 2 Previous Next