This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 27, 2012 1:46 PM by rorwessels RSS

APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.

rorwessels Newbie
Currently Being Moderated
I also posted this in the Oracle Support Community, so please forgive me if you have seen it twice now.

Grrr.. This is the second time I am trying to post this. The new communities here seem to have a strange way of failing to post properly.

My hair is going gray and I may soon be bald. I cannot seem to get around a problem with APEX_LDAP and I cannot seem to figure out DBMS_LDAP. It seems that APEX_LDAP is written to be easier to use, but DBMS_LDAP is older and less refined.

Below is some code I am trying to use for an authorization scheme. It works, but only partially. It works for our contractors but not employees. Even though all of our users use the same format for their login name, they are not stored with the same information for their DN in our OID. Now, because DBMS_LDAP has this property called filter, which can be used to target with property to use when searching the LDAP, I can use that to target the UID field instead of the CN and thus all of our users can use our OID for authentication. However, since the APEX_LDAP package does not have that same filter property, my code breaks and I cannot see a way around it.

What I need is an example of code that would pull a property from the LDAP, using DBMS_LDAP and store that in a variable that I could call a little later on as the user name for the APEX_LDAP.IS_MEMBER function. What I want ultimately is for Oracle to add the filter property to the different APEX_LDAP functions.

--- Begin Code Block ---

DECLARE

L_TEST wwv_flow_global.vc_arr2;

L_TEST_VALUES wwv_flow_global.vc_arr2;

L_ATTRIBUTES wwv_flow_global.vc_arr2;

L_ATTRIBUTE_VALUES wwv_flow_global.vc_arr2;

L_CON_ATTRIBUTES wwv_flow_global.vc_arr2;

L_CON_ATTRIBUTE_VALUES wwv_flow_global.vc_arr2;

L_AUTH boolean;

L_VAL boolean;

L_WORKFORCEID NUMBER(8);

L_WORKFORCEID2 VARCHAR2(21);

BEGIN

L_TEST(1) := 'employeetype';

APEX_LDAP.GET_USER_ATTRIBUTES(

p_username => V('APP_USER'),

p_pass => NULL,

p_auth_base => 'cn=users,dc=company,dc=com',

p_host => 'servername',

p_port => '389',

p_attributes => L_TEST,

p_attribute_values => L_TEST_VALUES);

IF L_TEST_VALUES(1) = 'E' THEN

L_ATTRIBUTES(1) := 'workforceid';

APEX_LDAP.GET_USER_ATTRIBUTES(

p_username => V('APP_USER'),

p_pass => NULL,

p_auth_base => 'cn=users,dc=company,dc=com',

p_host => 'servername',

p_port => '389',

p_attributes => L_ATTRIBUTES,

p_attribute_values => L_ATTRIBUTE_VALUES);

L_WORKFORCEID := (L_ATTRIBUTE_VALUES(1));

L_WORKFORCEID2 := TO_CHAR(L_WORKFORCEID, '00000000');

L_AUTH := APEX_LDAP.IS_MEMBER(

p_username => (L_WORKFORCEID2),

p_pass => NULL,

p_auth_base => 'cn=users,dc=company,dc=com',

p_host => 'servername',

p_port => 389,

p_use_ssl => 'N',

p_group => 'BlahBlah_Test_Group',

p_group_base => 'cn=Test,cn=groups,dc=company,dc=com');

ELSE

L_CON_ATTRIBUTES(1) := 'UID';

APEX_LDAP.GET_USER_ATTRIBUTES(

p_username => V('APP_USER'),

p_pass => NULL,

p_auth_base => 'cn=users,dc=company,dc=com',

p_host => 'servername',

p_port => '389',

p_attributes => L_CON_ATTRIBUTES,

p_attribute_values => L_CON_ATTRIBUTE_VALUES);

L_AUTH := APEX_LDAP.IS_MEMBER(

p_username => (L_CON_ATTRIBUTE_VALUES(1)),

p_pass => NULL,

p_auth_base => 'cn=users,dc=company,dc=com',

p_host => 'servername',

p_port => 389,

p_use_ssl => 'N',

p_group => 'BlahBlah_Test_Group',

p_group_base => 'cn=Test,cn=groups,dc=company,dc=com');

END IF;

IF L_AUTH = FALSE

THEN

L_VAL := FALSE;

ELSE

L_VAL := TRUE;

END IF;

IF L_VAL = TRUE THEN

htp.p('SUCCESS');

ELSE

htp.p('YOU FAIL');

END IF;

END;

--- End Code Block ---

Any assistance would be appreciated.

R. Otto R. Wessels
  • 1. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Otto,

    I'm not sure if I understand your request correctly. Do you want some kind of API like dbms_ldap.search_st (which contains a filter parameter, for ldap search)? Our ldap api package does indeed have room for improvement. Let's see what can be done for the next release.

    Regardless of that, I noticed a possible improvement in your code. I think it can be reduced to this:
    DECLARE
        L_TEST        wwv_flow_global.vc_arr2;
        L_TEST_VALUES wwv_flow_global.vc_arr2;
        L_DN          varchar2(4000);
        L_AUTH        boolean;
    BEGIN
        L_TEST(1) := 'employeetype';
        L_TEST(2) := 'workforceid';
        L_TEST(3) := 'UID';
    
        APEX_LDAP.GET_USER_ATTRIBUTES(
            p_username         => V('APP_USER'),
            p_pass             => NULL,
            p_auth_base        => 'cn=users,dc=company,dc=com',
            p_host             => 'servername',
            p_port             => '389',
            p_attributes       => L_TEST,
            p_attribute_values => L_TEST_VALUES);
    
        L_DN := CASE L_TEST_VALUES(1)
                  WHEN 'E' THEN TO_CHAR(L_TEST_VALUES(2), '00000000') -- workforceid
                  ELSE L_TEST_VALUES(3)                               -- UID
                END;
    
        L_AUTH := APEX_LDAP.IS_MEMBER(
            p_username   => L_DN,
            p_pass       => NULL,
            p_auth_base  => 'cn=users,dc=company,dc=com',
            p_host       => 'servername',
            p_port       => 389,
            p_use_ssl    => 'N',
            p_group      => 'BlahBlah_Test_Group',
            p_group_base => 'cn=Test,cn=groups,dc=company,dc=com');
    
        IF L_AUTH THEN
            htp.p('SUCCESS');
        ELSE
            htp.p('YOU FAIL');
        END IF;
    END;
    Btw, you can syntax highlight code in the forums by enclosing it in { code } tags, as explained here:
    https://wikis.oracle.com/display/Forums/Forums+FAQ#ForumsFAQ-Arethereanyusefulformattingoptionsnotshownonthesidebar%3F

    Regards,
    Christian

    Edited by: Christian Neumueller on Dec 7, 2012 5:57 AM
  • 2. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    OMG, that is so much cleaner then what I slapped together. So thank you for that. And I am going to use it. However, I still am left with my original issue, which is this.

    In our OID, some users have a DN that is all numeric and is also represented in the workforceid property in our OID. Hence the reason I am trying to target that property. Now, the user does not know that their DN is a numeric string because they use their "normal" alphanumeric login name, which is stored in the UID field of the OID.

    Since APEX has a place for creating the authentication scheme and that function supports the filter function, I was able to use the UID field and thus make this transparent to the users.

    The APEX_LDAP functions however, do not support this function, so what I need is a way, I am guessing by using something with DBMS_LDAP, to take the name stored in v('APP_USER') and pull those same variables that your code pulled with APEX_LDAP.GET_USER_ATTRIBUTES.

    I am not a developer, which must have been painfully obvious with the code I posted, I am an admin. But APEX_LDAP was easy enough to use and there where samples that where clear enough that even I could follow them. Unfortunately, the same seems can not be said for DBMS_LDAP. I could stumble through APEX_LDAP, but DBMS_LDAP is far beyond my current comprehension.

    So here is what I am trying to demonstrate here.

    If the user is an employee

    Name: Jane Doe
    UID: JDOE02
    CN: 000123456
    WORKFORCEID: 000123456
    Stored in groups as: 000123456
    Result: Authorization Scheme does not work.

    If the user is a contractor

    Name: John Doe
    UID: JDOE
    CN: JDOE
    WORKFORCEID: 999123456
    Stroed in groups as: JDOE
    Result: Authorization scheme works perfectly.

    I hope this sheds more light on my issue and why I am about to tear my hair out. I also have access to an AD instead of the OID, but everything I have read about that sounds even worse.

    NOTE: I do have authentication schemes setup for both OID and AD and they work just fine. This is for an authorization scheme and to provide an example to my devs of just how they would add this to their current and future applications.

    Sorry for the rambling post.

    I very much appreciate any assistance.

    R. Otto R. Wessels
  • 3. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Otto,

    which Apex version are you using? Because in 4.2, apex_ldap got a little flexibility in binding. If p_username contains 'uid=jdoe02' and p_auth_base contains 'cn=users,dc=company,dc=com', it will try to bind with a DN of

    uid=jdoe02,cn=users,dc=company,dc=com

    However, if p_username just contains 'jdoe02', it will first attempt a bind with

    cn=jdoe02,cn=users,dc=company,dc=com

    and if that fails with

    uid=jdoe02,cn=users,dc=company,dc=com

    If I got the requirements right, this should solve the problem.

    Regards,
    Christian
  • 4. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    That is exactly my requirement. I am using APEX 4.2 with the patch from Doc ID 1507390.1 also applied to fix the issues with APEX_LDAP.IS_MEMBER.

    I have tried placing the string 'uid=jdoe' in the p_username value for APEX_LDAP.GET_USER_ATTRIBUTES and it just comes back with an Oracle error "ORA-01403: no data found".

    BTW, you assistance has been phenomenal. I cannot thank you enough.

    R. Otto R. Wessels
  • 5. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Christian Neumueller Expert
    Currently Being Moderated
    Hi,

    that most likely comes from one of the L_TEST_VALUES being uninitialized. I just noticed that the indexes of L_TEST and L_TEST_VALUES will not always correspond. First, the order of the retrieved values is independent of the order in L_TEST. Second, if one attribute has multiple values, they will get returned in multiple rows in L_TEST_VALUES. Room for improvement, indeed...

    You are probably better off by using APEX_LDAP.GET_ALL_USER_ATTRIBUTES, like this:
    DECLARE
        L_TEST        wwv_flow_global.vc_arr2;
        L_TEST_VALUES wwv_flow_global.vc_arr2;
        L_EMPLOYEE_TYPE varchar2(4000);
        L_WORKFORCE_ID varchar2(4000);
        L_UID varchar2(4000);
        L_DN          varchar2(4000);
        L_AUTH        boolean;
    BEGIN
        APEX_LDAP.GET_ALL_USER_ATTRIBUTES(
            p_username         => V('APP_USER'),
            p_pass             => NULL,
            p_auth_base        => 'cn=users,dc=company,dc=com',
            p_host             => 'servername',
            p_port             => '389',
            p_attributes       => L_TEST,
            p_attribute_values => L_TEST_VALUES);
        FOR i in 1 .. L_TEST.COUNT LOOP
            IF L_TEST.EXISTS(i) THEN
                CASE LOWER(L_TEST(i))
                    WHEN 'employeetype' THEN L_EMPLOYEE_TYPE := L_TEST_VALUES(i);
                    WHEN 'workforceid' THEN L_WORKFORCE_ID := L_TEST_VALUES(i);
                    WHEN 'uid' THEN L_UID := L_TEST_VALUES(i);
                    ELSE NULL;
                 END CASE;
            END IF;
        END LOOP;
     
        L_DN := CASE L_EMPLOYEE_TYPE
                  WHEN 'E' THEN TO_CHAR(L_WORKFORCE_ID, '00000000')
                  ELSE L_UID
                END;
     
        L_AUTH := APEX_LDAP.IS_MEMBER(
            p_username   => L_DN,
            p_pass       => NULL,
            p_auth_base  => 'cn=users,dc=company,dc=com',
            p_host       => 'servername',
            p_port       => 389,
            p_use_ssl    => 'N',
            p_group      => 'BlahBlah_Test_Group',
            p_group_base => 'cn=Test,cn=groups,dc=company,dc=com');
     
        IF L_AUTH THEN
            htp.p('SUCCESS');
        ELSE
            htp.p('YOU FAIL');
        END IF
    Regards,
    Christian

    Edited by: Christian Neumueller on Dec 7, 2012 9:26 AM
  • 6. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    Well, I am not getting the error I was getting, but it appears that if I try to use 'uid=jdoe' in the p_username property for apex_ldap.get_all_user_attributes it does not return anything and the authorization fails. If I enter the CN for a contractor, it works perfectly, if I enter the CN for an employee, it fails. maybe I misunderstood, but you said that the APEX_LDAP package now allows the p_username property to have a uid= in the value?

    If this is true, and I can get it to work, it would solve the issue. Right now, I am not getting that behavior.

    Oh, so close and yet so far away... BTW, I am honored to have worked with you today. I just realized that you are the same person that added this functionality to APEX_LDAP. But, I have to ask, is it possible that the patch described in Doc ID 1507390.1 created a regression where the uid= now does not work?

    R. Otto R. Wessels

    Edited by: RORWessels on Dec 7, 2012 11:38 AM
  • 7. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Christian Neumueller Expert
    Currently Being Moderated
    Hi,

    I just realized that the code which supports 'uid=jdoe' only gets triggered if you support a password. It's not a regression, but it seems like my changes are a bit inconsistent. Hopefully, this can still be changed for 4.2.1.

    If you can not wait for that, you should be able to use this functionality if you supply the user's password to the function. The value of P101_PASSWORD is available in the POST-AUTHENTICATION function of the authentication scheme. You could for example put the call to apex_ldap.get_all_user_attributes in there and save the relevant user attributes in application items. The authorization schemes could then be based on the values in the items.

    Regards,
    Christian
  • 8. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    I apologize for not getting back to you sooner. I very much appreciate your assistance and time here. I read your last message and I have MUCH hope that this will be addressed in 4.2.1. It also occurs to me that this would solve a lot of the issues users have when trying to use an AD. I look forward to the say when APEX_LDAP works with AD and OID.

    I must admit that although I have an uderstanding of what you said in that last messsage and it makes some sense to me. I am at a loss as to how to actually do it. I am looking at my authentication scheme right now and because it is pointing to an OID right now, the only thing I had to do was add the search filter uid=%LDAP_USER.

    I see a field there for PL/SQL code and another field further down that asks for a post-authentication procedure name, but I am now sort of at a loss as to where the code you suggested would go and how it would be called.

    I have to again ask for your assistance in this regard.

    Also, as an idle though, is it possible that an interim patch could / would be issued to address / add the funtionality we discussed? If so, I would be ever so happy to download and apply it.

    Again, with many thanks...

    R. Otto R. Wessels
  • 9. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Tom Petrus Expert
    Currently Being Moderated
    Hi Otto,

    Christian means something like the code i used here: {message:id=10197833}
    You would need to adapt this code of course. Basically: perform a bind, retrieve values, put values in an application item (or items). Then create authorization schemes which test the values in these application items.
  • 10. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Christian Neumueller Expert
    Currently Being Moderated
    Hi,

    it should be fixed in 4.2.1. I hope to be able to address a few of the issues with AD in the next release. Especially a generic ldap search function should be useful, because the group membership functions are so OID specific.

    Regarding the post-authentication procedure, you can just provide the name of a procedure here. The procedure can either be pasted into the PL/SQL Code attribute, or you create a standalone or packaged procedure in your app schema. In case of a packaged proc, you have to prefix the procedure name by the package name in the post-authentication procedure name field. The procedure can not have arguments, but you can reference items via v('P101_USERNAME'), for example.

    I'm sorry, but we probably will not get approval for an interim patch because a workaround exists.

    Regards,
    Christian
  • 11. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    Again, you have my thanks. If this was indeed added in time to 4.2.1, then my immediate issues are solved... I am installing the 4.2.1 patchset update right now and will test as soon as that is done. With any luck I will be able to come back here and celebrate success and mark this issue ANSWERED.

    I can not thank you enough for your assistance and patience.

    P.S. Why the separate installers for those who had 4.2.0 already vs those doing a clean install of 4.2.1 from a previous version. Is it because you guys did not want to change the name of the APEX_0040200 schema / user?

    R. Otto R. Wessels

    Edited by: RORWessels on Dec 19, 2012 9:47 AM
  • 12. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    CARP... I installed the 4.2.1 patchset update and it apparently did not get any fixes for this issue.. Which is strange because the LDAP.PLB file shows it was modified on 12.12.2012 but the ldap.sql file does not show any changes I guess since the last time you updated it.

    Guess I will have to try and figure out this work around after all. I was so hopeful that the uid= fix was going to be there. I will look for it in 4.2.2 I guess.

    I thank you once again for your help, and I may be asking for it again in trying to figure out this work around. Creating the procedure should not be too difficult I do not think, but saving something as an application object and making sure this is done so that information between sessions and users is not confused is what makes my head spin a bit.

    I assume that I should create this procedure in the APEX_040200 schema so that it is available to APEX?
  • 13. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    Christian Neumueller Expert
    Currently Being Moderated
    Strange, this fixed the issue in my local tests. Maybe we have a misunderstanding here. When the post-authentication code is in place, we could run the app in debug mode to see what's happening. The procedure has to go into the application's schema, not APEX_040200. Apex executes all app-specific code as the app schema. For testing, you can also simply paste the procedure into the "PL/SQL Code" textarea of the authentication scheme.

    Regards,
    Christian
  • 14. Re: APEX_LDAP vs DBMS_LDAP and authorization schemes in APEX.
    rorwessels Newbie
    Currently Being Moderated
    My ability to cause confusion and to be confused is legendary. I was working under the assumption that the UID = fix made it into 4.2.1 for APEX so that I could do anonymous lookups using the UID for the user. I updated my APEX install to 4.2.1 from 4.2.0, but it did not seem to work any different and when I looked at the package on the server it did not show any updates where made to the package.

    So, I am left with the creation of a procedure and I have already confused that as well because if I am going to create a procedure, it needs to be accessible and usable by all the APEX workspaces and schemas, just like APEX_LDAP is.

    You mentioned putting the code in question in the PL/SQL text section on the authentication scheme. What would that look like and would it be using the DBMS_LDAP code. How does it get stored as an application object that can then be called by the authorization scheme?

    I know, I am helpless right now... I need this to be simple though becuase this is something we will have to replicate for a bunch of apps now and going forward.

    Many thanks as always...

    R. Otto R. Wessels
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points