The APEX login screen

Authorization in Oracle APEX with LDAP groups

A while ago I was part of a project team with the task to intodruce Oracle Application Express in our company. This tool is used to simply present database querys graphically in a browser application. While most installation and administration steps are well documented, we couldn’t find anything to have an authorization within an APEX app based on LDAP groups. And that’s why I want to present with which solution we came up. In this article I’m referring to APEX in version 5.1.

1 Creating the authorization scheme

When you select an application, you can configure authorization schemes under ‚Shared Components‘ -> ‚Security‘ -> ‚Authorization schemes‘. There you have to create a new one. In the creation dialogue, it is important to select ‚PL/SQL Function Returning Boolean‘ as the ‚Scheme Type‘. This means we are going to use a PL/SQL function to check if a user is authorzied to see specific parts of an APEX application.

Screenshot of the prompted dialog when creating an authorization scheme

Now you are asked for an PL/SQL Function Body. Here we are doing a function call to a function we are going to create in the next step. Just copy and paste the following code.

The first parameter is the LDAP user name. With v(‚APP_USER‘) we get the user who is currently logged in to the app. The second parameter is the name of the LDAP group in which the user needs to be to be authorized.

2 Writing the PL/SQL function

After creating the authorization we now need the PL/SQL function we are already calling. You can do this in the ‚Object Browser‘. Just press the ‚+‘-symbol on the right side of the screen and choose ‚Function‘. Type in the name of the function which we used earlier, check the ‚Include parameters‘-option and select ‚Boolean‘ as the ‚Return Data Type‘.

Screenshot of the prompted dialog when creating a PL/SQL function

On the next screen fill in the two arguments:

  • p_username as VARCHAR2
  • p_ldap_group as VARCHAR2

Screenshot of the prompted dialog when creating a PL/SQL function

After that you are asked for the function body. Just copy and paste the following code:

2.1 Authenticating against the LDAP server

So.. what do we have here? As I already said, it’s the body of an PL/SQL function. But I want to give you some idea on what’s going on here. In the first few lines we define some variables we are going to use later on in the code. We begin with initialising a new session. To do so we use the DBMS_LDAP PL/SQL package. We are going to use this packages in multiple places here. After connecting to the server we authenticate ourselves with an username and a password.

2.2 Finding the groups of the LDAP user

Since we want to check if an user is in a specific group we first have to find all groups the user belongs to. The search function dbms_ldap.search_s needs different parameters. Besides the session we created and in which we are authenticated at this point, we have the LDAP base which I will explain later and the scope (whether or not to search in subfolders). Then there is the filter parameter. With the given argument I search for all objects in the given LDAP base who have the given username. The attrs parameter is an array of strings defining which attributes of the object we searched for we want to get. In our cases this has to be the ‚member of‘ attribute.

2.3 Checking if user is in the specified group

In the following while loop we iterate over the result we’ve got. Important here is line 63 where we finally got all groups the user belongs to as a string. We convert both this string and the given group to uppercase and then check if the group the user should belong to is in the string. If yes the user should be authorized. The l_authed variable is set to true and we exit the surrounding for and while loop. We disconnenct from the server and then return the value.

The only things you need to change are found in the first few lines before the BEGIN.

  • LDAP_USER is a (technical) user who is used to connect to the LDAP server
  • LDAP_USER_PW is the passwort of this user. Unfortunaly I didn’t find a way to encrypt the password. It must be shown in plain text here. If you know a solution, please let me know in the comments! 🙂
  • LDAP_HOST is the IP address of the LDAP server
  • LDAP_PORT is the used port on the LDAP server
  • LDAP_BASE is the path in LDAP, where the users are stored (e.g.: ‚OU=users,DC=company,DC=com‘

That’s it. After saving this function you are now able to use LDAP groups for authorization. If you want to hide for example a diagram you can do this in the properties of this diagram in the page designer. If you scroll a little bit there is ‚Security‘ where you can select the authentication scheme we created earlier.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.