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.
‘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.
‘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’.
