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