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.