This content has been marked as final. Show 17 replies
1. Can APEX_LDAP.AUTHENTICATE only read to this level/location - cn=users,dc=jis,dc=org ?
2. Does it matter that the users have different dn? cn=testuser and uid=anotheruser ?
I think I have isolated the issue.
It appears that Apex authentication and authorization themes have troubling with users who have something other than *"cn="* for the distinguished name.
cn=jdoe,cn=Users,dc=jis,dc=org -> works!
uid=jdoe,cn=Users,dc=jis,dc=org -> will not work
Any advice on how to use these schemes with users that are distinguished by "*uid=*"??
Thanks for any help offered!
Hi Portal Dude,
I also found this issue very recently. Unfortunately, it was too late to include a fix in 4.1.1. The apex_ldap.authenticate function in 4.1.1 and older versions calls something along the lines of
to verify user/password.
dbms_ldap.simple_bind_s ( g_session, 'cn='||p_username||case when p_search_base is not null then ','||p_search_base end, p_password )
As a workaround, you could use dbms_ldap and craft your own authenticate function. Or you could use the - unsupported - wwv_flow_custom_auth_ldap.authenticate function, like this:
begin if wwv_flow_custom_auth_ldap.authenticate ( p_dn => 'cn=Users,dc=jis,dc=org', p_search_filter => 'uid=jdoe', p_password => '...', p_ldap_host => 'jis04951-5.jis.org', p_ldap_port => 389, p_use_exact_dn => 'N' ) then htp.p('auth ok'); else htp.p('auth err'); end if; end; /
Edited by: Christian Neumueller on Mar 7, 2012 2:56 AM
Christian, thanks so much for the info.
Yes, your code for wwv_flow_custom_auth_ldap.authenticate does work in our environment.
The problem is that I really need to use the other built in Apex ldap packages, like "APEX_LDAP.MEMBER_OF".
I really don't want to write these myself, in that this ease of functionality was the driving force for us to choose Apex as a technology.
Do you happen to know if this is fixed in the 4.1.1 patch?
Hi Portal Dude,
sorry if my mail wasn't clear. Although only little code had to be changed, I could not include it in 4.1.1, because of there was no time to test on different LDAP servers. This should be fixed in 4.2.
For group membership, another unsupported api may help you again:
We can not guarantee that these unsupported APIs will be available or work the same in future versions. If you decide to use them today, please encapsulate them, so it's easier to switch to a supported API in the future.
declare g dbms_ldap_utl.string_collection; begin g := apex_ldap.get_groups ( p_username => 'uid=jdoe', p_search_base => 'cn=Users,dc=jis,dc=org', p_ldap_host => 'jis04951-5.jis.org', p_ldap_port => 389 ); htp.p(g.count||' group(s)'); for i in 1 .. g.count loop htp.p('...'||g(i)); end loop; end;
Great, Christian that really works for us nicely.
I do not have much plsql experience, can you provide me a snippet that would return true if the APP_USER is in a current group, so that I could use for an Authentication scheme?
Is there a package similar, something like "apex_ldap.ismember"??
Thanks so much.
You could use code like this:
create or replace package portaldude_ldap_pkg as function is_member ( p_username in varchar2, p_search_base in varchar2, p_group in varchar2, p_ldap_host in varchar2, p_ldap_port in number default 389 ) return boolean; end portaldude_ldap_pkg; / create or replace package body portaldude_ldap_pkg as function is_member ( p_username in varchar2, p_search_base in varchar2, p_group in varchar2, p_ldap_host in varchar2, p_ldap_port in number default 389 ) return boolean is g dbms_ldap_utl.string_collection; l_found boolean := false; begin g := apex_ldap.get_groups ( p_username => p_username, p_search_base => p_search_base, p_ldap_host => p_ldap_host, p_ldap_port => p_ldap_port ); for i in 1 .. g.count loop if upper(p_group) = upper(g(i)) then l_found := true; exit; end if; end loop; return l_found; end is_member; end portaldude_ldap_pkg; /
Christian, again, thanks for you time and patience.
I have created the package and package body, but am still unclear as to what code to use in my Authorization Scheme type "PL/SQL Function Returning Boolean" to call them.
I have tried the following, but it errors out as invalid sql:
function is_member (
p_ldap_port 389 )
l_found boolean := false;
g := apex_ldap.get_groups (
p_username => 'uid=APP_USER',
p_search_base => 'cn=Groups,dc=jis,dc=org',
p_ldap_host => 'jis04951-5.jis.org',
p_ldap_port => 389 );
for i in 1 .. g.count loop
if upper(p_group) = upper(g(i)) then
l_found := true;
The authorization itself can be something like
It has to be a PL/SQL block, not a function with parameters, that returns a boolean value.
return portaldude_ldap_pkg.is_member ( p_username => 'uid='||apex_custom_auth.get_user, p_search_base => 'cn=Users,dc=jis,dc=org', p_group => 'mygroup', p_ldap_host => 'jis04951-5.jis.org' );
Thanks Christian, you are really helping me, I am very close.
I'm now getting this with the authorization scheme applied to my application:
Error processing authorization.
ORA-20000: 0 entries found. The search string must return 1 user.
Technical Info (only visible for developers)
ora_sqlerrm: ORA-20000: 0 entries found. The search string must return 1 user.
component.name: QCLEGACY GROUP - TEST
ORA-06512: at "SYS.WWV_DBMS_SQL", line 904
ORA-06512: at "APEX_040100.WWV_FLOW_DYNAMIC_EXEC", line 588
ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 69
ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 147
ORA-06512: at "APEX_040100.WWV_FLOW_PLUGIN_ENGINE", line 2089
ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 378
Hi Portal Dude,
you seem to be on 4.1. In 4.1.1, the error backtrace should show a bit more detailed information.
Please make sure that the function returns the intended results if you call it in SQL Workshop with the same parameters.
You should probably add debug output before calling the ldap function, to log what parameter values are actually passed to the function. Example:
apex_application.debug('calling portaldude_ldap_pkg.is_member with p_username=> uid='||apex_custom_auth.get_user); return portaldude_ldap_pkg.is_member ( p_username => 'uid='||apex_custom_auth.get_user, p_search_base => 'cn=Users,dc=jis,dc=org', p_group => 'mygroup', p_ldap_host => 'jis04951-5.jis.org' );
The APEX LDAP packages do not do what you would expect. Whereas the command line tools use a search depth of -s sub, the packages only search base.
I have rewritten parts of the packages to allow for '-s sub' type searches.
Soon the source will be on vanbortel.blogspot.com - I'm not quite sure where to end, yet.
Hi Christian, again, excellent information and patience, much appreciated.
I think this is actually working, but my problem seems to be now that my authorization scheme protected page is being run without calling my authentication scheme first.
*"calling portaldude_ldap_pkg.is_member with p_username=> uid=nobody"*
Isn't that a symptom, seen more often in 4.1.1, but not in the previous (4.1.0) version? There are quite a lot of threads here, with similar symptoms.