Tutorial 11: How to use LDAP as a base for user management

This tutorial explains how to use Lightweight Directory Access Protocol(LDAP) as a base for controlling user logon process and creating specific content according to user’s properties.
 
In this tutorial you will create a basic user logon screen and authenticate users by connecting to a remote LDAP directory. Querying LDAP base for the properties of users enables the developers to build user specific content for the authenticated users.
 

The User Interface

As the first step, create a basic login panel named ‘loginPanel’ for your application. The panel has a user name textfield, a password field, a login button and finally a label to be shown when the login attempt was not successful.
 
<panel>
  <xyLayout>
    <cell x="385" y="15" width="140">
      <textField name="unameTF" label="Username :"/>
    </cell>
    <cell x="385" y="40" width="140">
      <passwordField name="pwordPF"  label="Password :"/>
    </cell>
    <cell x="435" y="75" width="90">
      <button label="Login"/>
    </cell>
    <cell x="292" y="100" width="250">
      <textLabel font-color="Red" font-style="Italic,Bold" font-size="14" name="warningLBL" visible="N" label="Username/Password mismatch."/>
    </cell>
  <xyLayout>
</panel>
 
Next, open the ‘mainPanel’ and replace its content with the following XML.
 
<panel>
  <borderLayout>
    <cell docking="North" name="mainTopCell" height="30"/>
    <cell docking="Center" name="mainCenterCell">
      <include panelName="loginPanel"/>
    </cell>
  </borderLayout>
</panel>
 
When the application is started, ‘mainPanel’ will just contain ‘loginPanel’ panel in its ‘mainCenterCell’ cell. After the user logs in successfully, a menu specific for the authenticated user will be added to the ‘mainTopCell’ cell and the ‘loginPanel’ panel will be replaced with a user specific content panel (‘userPanel’).
 
As the next step,  create a basic menu for your application that shows user’s full name gathered from remote LDAP base and a log out button to log out the current user. To do so, create a panel named ‘menuPanel’ and write the following XML in the editor.
 
<panel backgroundColor="BlanchedAlmond" font-color="Black" font-family="Verdana" font-style="Bold" preferredHeight="30">
  <xyLayout>
    <cell x="510" y="5" width="70">
      <textLabel label="Welcome,"/>
    </cell>
    <cell x="580" y="5" width="100">
      <textLabel font-color="Green" name="nameLBL" label="username"/>
    </cell>
    <cell x="700" y="5" width="75">
      <button label="Log out"/>
    </cell>
  </xyLayout>
</panel>
 
Create a datasource definition named ‘EMPLOYEE’, select it as based on ‘Query’ and click ‘Bind Variables’ node and add 4 BindVars as:
 
Name Domain
dep_id DefaultNumber
loc_id DefaultNumber
cou_id DefaultVarchar2
reg_id DefaultNumber
 
Now, click on ‘Query’ node and enter following sql query:
 
select e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.department_id, e.salary, d.department_name, l.city, c.country_name, r.region_name
from employees e, departments d, locations l, countries c, regions r
where e.department_id = d.department_id
   and
         d.location_id = l.location_id
   and
         l.country_id = c.country_id
   and
         c.region_id = r.region_id
   and
         (:dep_id is null or :dep_id = e.Department_id)
   and
         (:loc_id is null or :loc_id = d.location_id)
   and
         (:cou_id is null or :cou_id = c.country_id)
   and
         (:reg_id is null or :reg_id = r.region_id)
 
We will use this query to show first name, last name, email, phone number, salary, department name, city, country and region names of employees within a given department, location, country or region.
 
Click ‘Ok’ to close the datasource definition creation screen and notice that ‘EMPLOYEE1’ datasource is created automatically. Finally, create a panel named ‘userPanel’ and create a grid inside it to show data gathered by the ‘EMPLOYEE1’ data source.
 
<panel>
  <borderLayout>
    <cell docking="Center">
      <grid name="employeeGrid" dataSource="EMPLOYEE1">
       <column name="fname" headerLabel="First Name">
         <textField column="FIRST_NAME"/>
        </column>
        <column name="lname" headerLabel="Last Name">
        <textField column="LAST_NAME"/>
        </column>
        <column name="email" headerLabel="E-mail">
          <textField column="EMAIL"/>
        </column>
        <column name="pno" headerLabel="Phone Number">
          <textField column="PHONE_NUMBER"/>
        </column>
        <column name="salary" headerLabel="Salary">
          <textField column="SALARY"/>
        </column>
        <column name="department" headerLabel="Department">
          <textField column="DEPARTMENT_NAME"/>
        </column>
       <column name="city" headerLabel="City">
         <textField column="CITY"/>
       </column>
       <column name="country" headerLabel="Country">
         <textField column="COUNTRY_NAME"/>
       </column>
       <column name="region" headerLabel="Region">
         <textField column="REGION_NAME"/>
       </column>
     </grid>
    </cell>
  </borderLayout>
</panel>
 

The Code

 
In order to implement the authentication mechanism and diplay user specific content, create a package named ‘ldaptutorial’ that has ‘login’, ‘logout’, ‘checkuser’, ‘getDepartmentID’, ‘getLocationID’, ‘getCountryID’ and ‘getRegionID’ procedures and functions.
 

Package Body:

 
package body ldaptutorial is
  -- Authenticate user by binding username and password to LDAP base.
  function checkUser (in_uname_tx in varchar2, in_pword_tx in varchar2)
    return varchar2
  is
    v_retval   pls_integer;
  begin
    v_retval :=
      dbms_ldap.simple_bind_s (ld => g_session, dn => in_uname_tx,
      passwd => in_pword_tx);

    return 'Y';
  exception
    when others
    then
      return 'N';
  end;
  --------------------------------------------------------------------------------
  -- Get the department id of a given user id by querying HR schema tables
  function getDepartmentID (in_userId_nr in number)
    return number
  is
    v_dep_id   number;
  begin
    select   department_id
      into   v_dep_id
      from   employees
     where   employee_id = in_userId_nr;

    return v_dep_id;
  exception
    when no_data_found
    then
      return null;
  end;
  --------------------------------------------------------------------------------
  -- Get the location id of a given user id by querying HR schema tables
  function getLocationID (in_userId_nr in number)
    return number
  is
    v_loc_id   number;
  begin
    select   d.location_id
      into   v_loc_id
      from   employees e, departments d
     where   employee_id = in_userId_nr and e.department_id = d.department_id;

    return v_loc_id;
  exception
    when no_data_found
    then
      return null;
  end;
  --------------------------------------------------------------------------------
  -- Get the country id of a given user id by querying HR schema tables
  function getCountryID (in_userId_nr in number)
    return char
  is
    v_cou_tx   char (2);
  begin
    select   l.country_id
      into   v_cou_tx
      from   employees e, departments d, locations l
     where       employee_id = in_userId_nr
             and e.department_id = d.department_id
             and d.location_id = l.location_id;

    return v_cou_tx;
  exception
    when no_data_found
    then
      return null;
  end;
  --------------------------------------------------------------------------------
  -- Get the region id of a given user id by querying HR schema tables
  function getRegionID (in_userId_nr in number)
    return number
  is
    v_reg_id   number;
  begin
    select   c.region_id
      into   v_reg_id
      from   employees e, departments d, locations l, countries c
     where       employee_id = in_userId_nr
             and e.department_id = d.department_id
             and d.location_id = l.location_id
             and l.country_id = c.country_id;

    return v_reg_id;
  exception
    when no_data_found
    then
      return null;
  end;
  --------------------------------------------------------------------------------
  -- Get the display name, user admin type and user id of a given username by qerying LDAP base
  procedure getUserInfo (in_username_tx in varchar2, v_displayName_tx out varchar2,
  v_userType_tx out varchar2, v_userId_nr out number)
  is
    l_attrs     dbms_ldap.string_collection;
    l_retval    pls_integer;
    l_message   dbms_ldap.message;
    l_entry     dbms_ldap.message;
    l_types     dbms_ldap.string_collection;
  begin
    -- Bind to LDAP base with admin username and password to query user data.
    l_retval :=
      dbms_ldap.simple_bind_s (ld => g_session, dn => g_ldap_admin_uname_tx,
      passwd => g_ldap_admin_pword_tx);
    -- Get 3 attributes from user object
    l_attrs (1) := 'displayName';
    l_attrs (2) := 'adminDescription';
    l_attrs (3) := 'employeeID';

    -- We have the CN=Username object under Users object which is under our base directory.
    l_retval :=
      dbms_ldap.search_s (ld => g_session,
      base => 'CN=' || in_username_tx || ',CN=Users,' || g_ldap_base_tx,
      scope => dbms_ldap.scope_subtree, filter => 'objectclass=user',
      attrs => l_attrs, attronly => 0, res => l_message);

    if dbms_ldap.count_entries (ld => g_session, msg => l_message) > 0
    then
      l_entry := dbms_ldap.first_entry (ld => g_session, msg => l_message);

      -- Display Name
      l_types :=
        dbms_ldap.get_values (ld => g_session, ldapentry => l_entry,
        attr => l_attrs (1));

      v_displayName_tx := substr (l_types (l_types.first), 1, 200);

      -- User Type
      l_types :=
        dbms_ldap.get_values (ld => g_session, ldapentry => l_entry,
        attr => l_attrs (2));

      v_userType_tx := substr (l_types (l_types.first), 1, 200);

      -- User ID
      l_types :=
        dbms_ldap.get_values (ld => g_session, ldapentry => l_entry,
        attr => l_attrs (3));

      v_userId_nr := to_number (substr (l_types (l_types.first), 1, 200));
    end if;
  end;
  --------------------------------------------------------------------------------
  procedure login
  is
    v_uname_tx         varchar2 (155);
    v_pword_tx         varchar2 (155);
    v_loginResult_yn   varchar2 (1);
    v_displayName_tx   varchar2 (155);
    v_userType_tx      varchar2 (155);
    v_userId_nr        number;
  begin
    -- choose to raise exceptions
    dbms_ldap.use_exception := true;

    -- store ldap session in a global variable
    g_session :=
      dbms_ldap.init (hostname => g_ldap_host_url_tx,
      portnum => g_ldap_port_tx);

    -- get username
    v_uname_tx := api_component.getValueTX ('loginPanel.unameTF');
    -- get password
    v_pword_tx := api_component.getValueTX ('loginPanel.pwordPF');

    -- authenticate user by checking if he can bind to remote ldap base
    v_loginResult_yn := checkUser (v_uname_tx, v_pword_tx);

    if v_loginResult_yn = 'Y'
    then
      -- Clear warning about username/password mismatch
      api_component.setVisible ('loginPanel.warningLBL', 'N');

      -- Add menu
      api_panel.addPanel ('mainPanel', 'mainTopCell', 'menuPanel');

      -- Remove login panel and show user specific user panel
      api_panel.replacePanel ('mainPanel', 'loginPanel', 'userPanel');

      -- get users display name, admin type and and id from ldap base
      getUserInfo (v_uname_tx, v_displayName_tx, v_userType_tx, v_userId_nr);

      -- set display name to the label in the menubar
      api_component.setValue ('menuPanel.nameLBL', v_displayName_tx);

      if v_userType_tx = 'local' then
        -- if the user has local admin type he can see all the employees within his location
        api_datasource.setbindvar ('EMPLOYEE1.dep_id', to_number (null));
        api_datasource.setbindvar ('EMPLOYEE1.loc_id', getLocationID (v_userId_nr));
        api_datasource.setbindvar ('EMPLOYEE1.cou_id', '');
        api_datasource.setbindvar ('EMPLOYEE1.reg_id', to_number (null));
        api_datasource.executeQuery ('EMPLOYEE1');
      elsif v_userType_tx = 'country' then
        -- if the user has country admin type he can see all the employees within his country
        api_datasource.setbindvar ('EMPLOYEE1.dep_id', to_number (null));
        api_datasource.setbindvar ('EMPLOYEE1.loc_id', to_number (null));
        api_datasource.setbindvar ('EMPLOYEE1.cou_id', getCountryID (v_userId_nr));
        api_datasource.setbindvar ('EMPLOYEE1.reg_id', to_number (null));
        api_datasource.executeQuery ('EMPLOYEE1');
      elsif v_userType_tx = 'region' then
        -- if the user has region admin type he can see all the employees within his region
        api_datasource.setbindvar ('EMPLOYEE1.dep_id', to_number (null));
        api_datasource.setbindvar ('EMPLOYEE1.loc_id', to_number (null));
        api_datasource.setbindvar ('EMPLOYEE1.cou_id', '');
        api_datasource.setbindvar ('EMPLOYEE1.reg_id', getRegionID (v_userId_nr));
        api_datasource.executeQuery ('EMPLOYEE1');
      else
        -- if the user has no admin type he can see all the employees within his department only
        api_datasource.setbindvar ('EMPLOYEE1.dep_id',
        getDepartmentID (v_userId_nr));
        api_datasource.setbindvar ('EMPLOYEE1.loc_id', to_number (null));
        api_datasource.setbindvar ('EMPLOYEE1.cou_id', '');
        api_datasource.setbindvar ('EMPLOYEE1.reg_id', to_number (null));
        api_datasource.executeQuery ('EMPLOYEE1');
      end if;
    else
      -- Show warning about username/password mismatch
      api_component.setVisible ('loginPanel.warningLBL', 'Y');
    end if;
  end;
  --------------------------------------------------------------------------------
  procedure logout
  is
  begin
    -- Add menu
    api_panel.removePanel ('mainPanel', 'menuPanel');
    -- Remove login panel and show login panel
    api_panel.addPanel ('mainPanel', 'mainCenterCell', 'loginPanel');
  end;
end;
 

Details

 
login procedure: This procedure reads the values of ‘unameTF’ textfield and ‘pwordPF’ passwordfield components in ‘loginPanel’ panel and calls checkUser function to authenticate the user values. If it is successful,  ‘menuPanel’ and ‘userPanel’ are placed into the associated cells in ‘mainPanel’ panel. By using ‘getUserInfo’ procedure with the authenticated username, user’s display name, admin type and id is gathered from the remote LDAP base.
 
User’s display name is set as a value of the ‘nameLBL’ textLabel component in ‘menuPanel’ panel to enable displaying welcome message to the user.
 
Using the user’s admin type associated bindVars are set to enable displaying all the employees located in the same location with the user if his admin type is ‘local’, employees located in the same country with the user if his admin type is ‘country’, employees located in the same region with the user if his admin type is ‘region’ and employees located in the same department with the user if he is not an admin.
 
If the login attempt is unsuccessful, ‘warningLBL’ textLabel’s  visibility is set as ‘Y’ to display
‘Username/Password mismatch.’ message.
 
logout procedure: We are not using sessions in the tutorial so in this procedure we just  remove the ‘menuPanel’ panel from the ‘mainPanel’ and replace the ‘userPanel’ with the ‘loginPanel’ to return application to its original state. Notice that we are not clearing the contents of username and password field so they are still displayed.
 
checkuser function: This function authenticates user’s by attempting to bind input username and password to the ldap session. If a successful binding occurs, it returns ‘Y’, otherwise ‘N’.
 
getUserInfo procedure: This procedure connects to the remote LDAP base with LDAP admin account to gather user’s information which in our case situated as
‘CN=UserName,CN=Users,OU=FS,DC=dsa,DC=org,DC=et’ canonical name. This object has ‘user’ object type and has ‘displayName’, ‘adminDescription’ and ‘employeeID’ attributes which we query and return as out parameter.
 
getDepartmentID,  getLocationID,  getCountryID,  getRegionID functions: These are simple functions that queries HR schema tables to return requested ids.
 

Package Spec:

 
package ldaptutorial is
  -- LDAP host url
  g_ldap_host_url_tx      varchar2 (255) := '192.168.1.100';

  -- LDAP host port
  g_ldap_port_tx          varchar2 (255) := '389';

  -- LDAP admin username
  g_ldap_admin_uname_tx   varchar2 (255) := 'admin';

  -- LDAP admin password
  g_ldap_admin_pword_tx   varchar2 (255) := 'admin';

  -- LDAP directory base
  g_ldap_base_tx          varchar2 (255) := 'OU=FS,DC=dsa,DC=org,DC=et';

  -- LDAP session variable
  g_session               dbms_ldap.session;

  -- Procedures that are called by actions in the application
  procedure login;
  procedure logout;
end;
 

Details

 
Modify global variables in the package spec according to your LDAP host.
 

Adding Required Actions

 
We created two buttons, one in the ‘loginPanel’ panel for logging users in and one in the ‘menuPanel’ panel for logging users out. So, add actions to these buttons in order to make application work. Create an action named ‘login’ that calls ‘ldaptutorial.login’ procedure and modify the ‘loginPanel’ panel as follow:
 
<panel>
  <xyLayout>
    <cell x="385" y="15" width="140">
      <textField name="unameTF" label="Username :"/>
    </cell>
    <cell x="385" y="40" width="140">
      <passwordField name="pwordPF"  label="Password :"/>
    </cell>
    <cell x="435" y="75" width="90">
      <button label="Login">
        <events>
          <buttonPress action="login"/>
        </events>
      </button>
      </cell>
      <cell x="292" y="100" width="250">
        <textLabel font-color="Red" font-style="Italic,Bold" font-size="14" name="warningLBL" visible="N" label="Username/Password mismatch."/>
      </cell>
    </xyLayout>
 </panel>
 
Create an action named ‘logout’ that calls ‘ldaptutorial.logout’ procedure and modify the ‘menuPanel’ panel as follow:
 
<panel backgroundColor="BlanchedAlmond" font-color="Black" font-family="Verdana" font-style="Bold" preferredHeight="30">
  <xyLayout>
    <cell x="510" y="5" width="70">
      <textLabel label="Welcome,"/>
    </cell>
    <cell x="580" y="5" width="100">
      <textLabel font-color="Green" name="nameLBL" label="username"/>
    </cell>
    <cell x="700" y="5" width="75">
      <button label="Log out">
        <events>
          <buttonPress action="logout"/>
        </events>
      </button>
    </cell>
  </xyLayout>
</panel>
 

What’s just happened

 
We created an application that authenticates users using a remote LDAP directory and using user’s ‘displayName’, ‘adminDescription’ and ‘employeeID’ attributes stored in LDAP directory displays user specific content.

Login Screen
 

User specific content for ‘bernst’ user.  Notice that he is not an admin so he can see all the employees in his department only.

 

User specific content for ‘ncochhar’ user.  Notice that she is a local admin so she can see all the employees in her location which is ‘Seattle’ city.

 

User specific content for ‘mweiss’ user.  Notice that he is a country admin so he can see all the employees in his country which is ‘USA’.

 

User specific content for ‘hbaer’ user.  Notice that he is a region admin so he can see all the employees in his region which is ‘Europa’.

  • http://www.facebook.com/emaksimov Emil Maksimov

    Package body code gives error:

    Error(10,38): PLS-00201: identifier ‘G_SESSION’ must be declared
    Why?

    • İbrahim Sandallı

      Hi,

      The ‘G_SESSION’ variable is declared in the package spec, you can find the package spec code under the “Package Spec:” section of the tutorial. Please make sure that you have created the package spec following the code specified under this section.

      Best Regards

      Ibrahim