Softwareentwickler / Software Developer
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:

  retval        PLS_INTEGER;
  l_session     dbms_ldap.session;
  l_attrs       dbms_ldap.string_collection;
  l_message     dbms_ldap.message;
  l_entry       dbms_ldap.message;
  l_attr_name   varchar2(256 );
  l_vals        dbms_ldap.string_collection;
  l_ber_element dbms_ldap.ber_element;
  ldap_user     varchar2(256) := LDAP_USER;
  ldap_user_pw  varchar2(256) := LDAP_USER_PW;
  ldap_host     varchar2(256) := LDAP_HOST;
  ldap_port     varchar2(256) := LDAP_PORT
  ldap_base     varchar2(256) := LDAP_USER_BASE;
  l_not_authenticated varchar2(100) := 'Wrong username/password combination';
  l_not_authorized    varchar2(100) := 'You are not authorized!';
  l_authed      boolean := false;
  l_memberof    dbms_ldap.string_collection;

  -- Raise exceptions on failure
  dbms_ldap.use_exception := true;

  -- Connect to the LDAP server
  l_session := dbms_ldap.init( hostname => ldap_host 
                             , portnum  => ldap_port );

  -- Authenicate the user -- raises an exception on failure
  retval := dbms_ldap.SIMPLE_BIND_S( ld     => l_session 
                                   , dn     => ldap_user
                                   , passwd => ldap_user_pw); 
  -- Once you are here you are authenticated

  -- Get all "memberOf" attributes    
  l_attrs(1) := 'memberOf';
  -- Searching for the user info using his samaccount (windows login )
  retval := dbms_ldap.search_s( ld       => l_session 
                              , base     => ldap_base 
                              , scope    => dbms_ldap.SCOPE_SUBTREE
                              , filter   => '(&(objectClass=*)(sAMAccountName=' || lower(p_username) || '))'
                              , attrs    => l_attrs
                              , attronly => 0
                              , res      => l_message );

  -- There is only one entry but still have to access that
  l_entry := dbms_ldap.first_entry( ld  => l_session 
                                  , msg => l_message );

  -- Get the first Attribute for the entry
  l_attr_name := dbms_ldap.first_attribute( ld        => l_session
                                          , ldapentry => l_entry       
                                          , ber_elem  => l_ber_element );

  -- Loop through all "memberOf" attributes  
  while l_attr_name is not null loop

    -- Get the values of the attribute
    l_vals := dbms_ldap.get_values( ld        => l_session
                                  , ldapentry => l_entry 
                                  , attr      => l_attr_name );
    -- Check the contents of the value
    for i in l_vals.first..l_vals.last loop
      -- A user gets access when assigned to given group
      l_authed := (instr(upper(l_vals(i)), upper(p_ldap_group)) > 0);
      exit when l_authed;
    end loop;
    exit when l_authed;    

    l_attr_name := dbms_ldap.next_attribute( ld        => l_session
                                           , ldapentry => l_entry       
                                           , ber_elem  => l_ber_element );
  end loop;

  retval := dbms_ldap.unbind_s( ld => l_session );

  if not l_authed
  then -- Although username / password was correct, user isn't authorized for this application
    apex_util.set_custom_auth_status ( p_status => l_not_authorized );
  end if;  

  -- Return Authenticated  
  return l_authed;

  when others then
  retval := dbms_ldap.unbind_s( ld => l_session );
  -- Return NOT Authenticated  
  apex_util.set_custom_auth_status ( p_status => l_not_authenticated );
  return false; 

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.

Ein Gedanke zu “Authorization in Oracle APEX with LDAP groups”