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.
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.
BEGIN return LDAP_AUTHORIZATION(v('APP_USER'), 'LDAP_ADMIN'); END;
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‘.
On the next screen fill in the two arguments:
- p_username as VARCHAR2
- p_ldap_group as VARCHAR2
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; BEGIN -- 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; EXCEPTION 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; END;
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.