I wasn’t able to find a decent tutorial on how to do this anywhere, so I figured I’d post it here. This is my first APEX program, so there may be a better way.
Basically what I wanted to do is to create an application that uses two points of user authentication. Their user name and password would be verified using LDAP, and their group membership would be verified in an Oracle database.
If you are using APEX, then you probably know the difference between Authentication and Authorization but I’ll give a quick example anyway. The simplest way to think about it is that Authentication controls access to your program, and Authorization controls the resources in your program. So if you were trying to make a distinction between an administrator and a user, that would be done at the Authorization level. Since I couldn’t set up two types of Authentication, I decided I would use LDAP to authenticate, and would handle access from the Oracle account at the authorization level.
First, LDAP. In your APEX program, go to Shared Components -> Authentication Schemes. You may have already set up some basic security automatically when you create the application, so click on the one marked as current. If not, just hit Create and go through the wizard.
When you have chosen or created an authentication scheme, go to the Login Processing tab and set it up similar to the following…
Obviously the host, port, and dn string will need to be changed to your settings, however note the %LDAP_USER% string which containts the user name of the attempted login. You’ll need to use that. And if you need to test your settings, click the LDAP Test Tool.
For our Oracle database, let’s go back to the Shared Components but go to Authorization Schemes this time. From there, Create your scheme.
Name it whatever you want, but keep it in mind for later. For Scheme Type, choose “Exists SQL Query”. And in the Expression 1 field, type out your SQL query. For mine, we are checking to make sure the user has an entry in the table. So I would have something like…
SELECT USER_NAME FROM USER_TABLE WHERE USER_ID =:APP_USER
In this case, :APP_USER has the same value that %LDAP_USER% had. Keep in mind the above SQL is just a bare bones minimal example. There is plenty of room to get fancy if you need.
The last step is to apply our new schema to the entire app. Go to Shared Components -> Edit Security Attributes. Under the Authorization tab, choose the name of the scheme that we just created and then apply your changes.
And there you have it. Our login page now checks for a correct LDAP password and an entry in an Oracle database.
it’s all covered nicely in a book titled “Pro Oracle Application Express”.
Nice, have been thinking about picking up an APEX book, may have to look into that one.
Thanks. I’ve dabbled in mod_ldap variations with Apache, but this is very handy.
Just wondering, how would one go about setting this up if they had 2 seperate containers that users were in. For example…
ou=users,o=mycompany
and
ou=external,o=mycompany
Graham, I an suffering the same issue.
Please let me know if get an answer, I have been searching google everywhere!!
Hi Graham,
So, were you able to figure it out; and authenticate users if you had two separate containers. Even I am facing the same issue. Please let me know if have any suggestions for me or email me raghu607@gmail.com
Thanks,
Raghu
Unfortunately not raghu. Not sure if you can maybe use Annabele’s solution below and use a pl/sql function?
I just figured something out ….
it worked for me to just authenticated without mutiple ou’s
Found a great site on how to do this via PL/SQL call: http://www.niall.litchfield.dial.pipex.com/2005/11/using-active-directory-in-oracle.html
Here’s the code:
create or replace function authenticate_aduser(
p_username in varchar2,
p_password in varchar2)
return boolean
is
l_user varchar2(256);
l_ldap_server varchar2(256) := ”;
l_domain varchar2(256) := ”;
l_ldap_port number := 389;
l_retval pls_integer;
l_session dbms_ldap.session;
l_cnt number;
begin
l_user := p_username||’@'||l_domain;
l_session := dbms_ldap.init( l_ldap_server, l_ldap_port ); — start session
l_retval := dbms_ldap.simple_bind_s( l_session, l_user, p_password ); — auth as user
l_retval := dbms_ldap.unbind_s( l_session ); — unbind
return true;
exception when others then
l_retval := dbms_ldap.unbind_s( l_session );
return false;
end;
Hi Annabele. Do you mean this worked for you by hard codeing your ou into the script or it allowed you to define your dn without specifying an ou?
I have tried ldap wildcards, sql wildcards and nothing seems to work.
I might be able to write a script that checks the format of the username and then appends the ou onto the username then call this in “LDAP Username Edit Function” but that’s quite a dirty way to do it.
[...] providing the LDAP host, port, whether or not to use SSL, and finally the DN string. See here and here for posts about APEX LDAP [...]