You can use APEX_LDAP to perform various operations related to Lightweight Directory Access Protocol (LDAP) authentication.
The AUTHENTICATE function returns a boolean TRUE if the user name and password can be used to perform a SIMPLE_BIND_S, call using the provided search base, host, and port.
APEX_LDAP.AUTHENTICATE(
p_username IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL,
p_search_base IN VARCHAR2,
p_host IN VARCHAR2,
p_port IN VARCHAR2 DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N')
RETURN BOOLEAN;
Table 17-1 describes the parameters available in the AUTHENTICATE function.
Table 17-1 AUTHENTICATE Parameters
| Parameter | Description |
|---|---|
|
|
Login name of the user. |
|
|
Password for |
|
|
LDAP search base, for example, |
|
|
LDAP server host name. |
|
|
LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL (default). |
The following example demostrates how to use the APEX_LDAP.AUTHENTICATE function to verify user credentials against an LDAP Server.
IF APEX_LDAP.AUTHENTICATE(
p_username =>'firstname.lastname',
p_password =>'abcdef',
p_search_base => 'cn=user,l=amer,dc=my_company,dc=com',
p_host => 'our_ldap_sever.my_company.com',
p_port => 389) THEN
dbms_output.put_line('authenticated');
ELSE
dbms_output.put_line('authentication failed');
END IF;
The GET_ALL_USER_ATTRIBUTES procedure returns two OUT arrays of user_attribute names and values for the user name designated by p_username (with password if required) using the provided auth base, host, and port.
APEX_LDAP.GET_ALL_USER_ATTRIBUTES(
p_username IN VARCHAR2 DEFAULT NULL,
p_pass IN VARCHAR2 DEFAULT NULL,
p_auth_base IN VARCHAR2 DEFAULT NULL,
p_host IN VARCHAR2,
p_port IN VARCHAR2 DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N',
p_attributes OUT apex_application_global.vc_arr2,
p_attribute_values OUT apex_application_global.vc_arr2);
Table 17-2 describes the parameters for the GET_ALL_USER_ATTRIBUTES procedure.
Table 17-2 GET_ALL_USER_ATTRIBUTES Parameters
| Parameter | Description |
|---|---|
|
|
Login name of the user. |
|
|
Password for |
|
|
LDAP search base, for example, |
|
|
LDAP server host name. |
|
|
LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL (default). |
|
|
An array of attribute names returned. |
|
|
An array of values returned for each corresponding attribute name returned in p_attributes. |
The following example demonstrates how to use the APEX_LDAP.GET_ALL_USER_ATTRIBUTES procedure to retrieve all attribute value's associated to a user.
DECLARE
L_ATTRIBUTES apex_application_global.vc_arr2;
L_ATTRIBUTE_VALUES apex_application_global.vc_arr2;
BEGIN
APEX_LDAP.GET_ALL_USER_ATTRIBUTES(
p_username => 'firstname.lastname',
p_pass => 'abcdef',
p_auth_base => 'cn=user,l=amer,dc=my_company,dc=com',
p_host => 'our_ldap_sever.my_company.com',
p_port => '389',
p_attributes => L_ATTRIBUTES,
p_attribute_values => L_ATTRIBUTE_VALUES);
FOR i IN L_ATTRIBUTES.FIRST..L_ATTRIBUTES.LAST LOOP
htp.p('attribute name: '||L_ATTRIBUTES(i));
htp.p('attribute value: '||L_ATTRIBUTE_VALUES(i));
END LOOP;
END;
The GET_USER_ATTRIBUTES procedure returns an OUT array of user_attribute values for the user name designated by p_username (with password if required) corresponding to the attribute names passed in p_attributes using the provided auth base, host, and port.
APEX_LDAP.GET_USER_ATTRIBUTES(
p_username IN VARCHAR2 DEFAULT NULL,
p_pass IN VARCHAR2 DEFAULT NULL,
p_auth_base IN VARCHAR2,
p_host IN VARCHAR2,
p_port IN VARCHAR2 DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N',
p_attributes IN apex_application_global.vc_arr2,
p_attribute_values OUT apex_application_global.vc_arr2);
Table 17-3 describes the parameters available in the GET_USER_ATTRIBUTES procedure.
Table 17-3 GET_USER_ATTRIBUTES Parameters
| Parameter | Description |
|---|---|
|
|
Login name of the user. |
|
|
Password for |
|
|
LDAP search base, for example, |
|
|
LDAP server host name. |
|
|
LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL (default). |
|
|
An array of attribute names for which values are to be returned. |
|
|
An array of values returned for each corresponding attribute name in |
The following example demonstrates how to use the APEX_LDAP.GET_USER_ATTRIBUTES procedure to retrieve a specific attribute value associated to a user.
DECLARE
L_ATTRIBUTES apex_application_global.vc_arr2;
L_ATTRIBUTE_VALUES apex_application_global.vc_arr2;
BEGIN
L_ATTRIBUTES(1) := 'xxxxxxxxxx'; /* name of the employee number attribute */
APEX_LDAP.GET_USER_ATTRIBUTES(
p_username => 'firstname.lastname',
p_pass => NULL,
p_auth_base => 'cn=user,l=amer,dc=my_company,dc=com',
p_host => 'our_ldap_sever.my_company.com',
p_port => '389',
p_attributes => L_ATTRIBUTES,
p_attribute_values => L_ATTRIBUTE_VALUES);
END;
The IS_MEMBER function returns a boolean TRUE if the user named by p_username (with password if required) is a member of the group specified by the p_group and p_group_base parameters using the provided auth base, host, and port.
APEX_LDAP.IS_MEMBER(
p_username IN VARCHAR2,
p_pass IN VARCHAR2 DEFAULT NULL,
p_auth_base IN VARCHAR2,
p_host IN VARCHAR2,
p_port IN VARCHAR2 DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N',
p_group IN VARCHAR2,
p_group_base IN VARCHAR2)
RETURN BOOLEAN;
Table 17-4 describes the parameters available in the IS_MEMBER function.
Table 17-4 IS_MEMBER Parameters
| Parameter | Description |
|---|---|
|
|
Login name of the user. |
|
|
Password for |
|
|
LDAP search base, for example, |
|
|
LDAP server host name. |
|
|
LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL. |
|
|
Name of the group to be search for membership. |
|
|
The base from which the search should be started. |
The following example demonstrates how to use the APEX_LDAP.IS_MEMBER function to verify whether a user is a member of a group against an LDAP server.
DECLARE
L_VAL boolean;
BEGIN
L_VAL := APEX_LDAP.IS_MEMBER(
p_username =>'firstname.lastname',
p_pass =>'abcdef',
p_auth_base => 'cn=user,l=amer,dc=my_company,dc=com',
p_host => 'our_ldap_sever.my_company.com',
p_port => 389,
p_group => 'group_name',
p_group_base => 'group_base');
IF L_VAL THEN
htp.p('Is a member.');
ELSE
htp.p('Not a member.');
END IF;
END;
The MEMBER_OF function returns an array of groups the user name designated by p_username (with password if required) belongs to, using the provided auth base, host, and port.
APEX_LDAP.MEMBER_OF(
p_username IN VARCHAR2 DEFAULT NULL,
p_pass IN VARCHAR2 DEFAULT NULL,
p_auth_base IN VARCHAR2,
p_host IN VARCHAR2,
p_port IN VARCHAR2 DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N')
RETURN apex_application_global.vc_arr2;
Table 17-5 describes the parameters available in the MEMBER_OF function.
Table 17-5 MEMBER_OF Parameters
| Parameter | Description |
|---|---|
|
|
Login name of the user. |
|
|
Password for |
|
|
LDAP search base, for example, |
|
|
LDAP server host name. |
|
|
LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL (default). |
The following example demonstrates how to use the APEX_LDAP.MEMBER_OF function to retrieve all the groups designated by the specified username.
DECLARE
L_MEMBERSHIP apex_application_global.vc_arr2;
BEGIN
L_MEMBERSHIP := APEX_LDAP.MEMBER_OF(
p_username => 'firstname.lastname',
p_pass => 'abcdef',
p_auth_base => 'cn=user,l=amer,dc=my_company,dc=com',
p_host => 'our_ldap_sever.my_company.com',
p_port => '389');
FOR i IN L_MEMBERSHIP.FIRST..L_MEMBERSHIP.LAST LOOP
htp.p('Member of: '||L_MEMBERSHIP(i));
END LOOP;
END;
The MEMBER_OF2 function returns a VARCHAR2 colon delimited list of groups the user name designated by p_username (with password if required) belongs to, using the provided auth base, host, and port.
APEX_LDAP.MEMBER_OF2(
p_username IN VARCHAR2 DEFAULT NULL,
p_pass IN VARCHAR2 DEFAULT NULL,
p_auth_base IN VARCHAR2,
p_host IN VARCHAR2,
p_port IN VARCHAR2 DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N')
RETURN VARCHAR2;
Table 17-6 describes the parameters available in the MEMBER_OF2 function.
Table 17-6 MEMBER_OF2 Parameters
| Parameter | Description |
|---|---|
|
|
Login name of the user. |
|
|
Password for |
|
|
LDAP search base, for example, |
|
|
LDAP server host name. |
|
|
LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL (default). |
The following example demonstrates how to use the APEX_LDAP.MEMBER_OF2 function to retreive all the groups designated by the specified username.
DECLARE
L_VAL varchar2(4000);
BEGIN
L_VAL := APEX_LDAP.MEMBER_OF2(
p_username => 'firstname.lastname',
p_pass => 'abcdef',
p_auth_base => 'cn=user,l=amer,dc=my_company,dc=com',
p_host => 'our_ldap_sever.my_company.com',
p_port => 389);
htp.p('Is Member of:'||L_VAL);
END;
The APEX_LDAP.SEARCH function searches the LDAP repository. The result is an object table of (dn, name, val) that can be used in table queries.
function search (
p_username IN VARCHAR2 DEFAULT NULL,
p_pass IN VARCHAR2 DEFAULT NULL,
p_auth_base IN VARCHAR2 DEFAULT NULL,
p_host IN VARCHAR2,
p_port IN NUMBER DEFAULT 389,
p_use_ssl IN VARCHAR2 DEFAULT 'N',
p_search_base IN VARCHAR2,
p_search_filter IN VARCHAR2,
p_scope IN BINARY_INTEGER DEFAULT SYS.DBMS_LDAP.SCOPE_SUBTREE,
p_timeout_sec IN BINARY_INTEGER DEFAULT 3,
p_attribute_names IN VARCHAR2 )
RETURN APEX_T_LDAP_ATTRIBUTES PIPELINED;
Table 17-7 describes the parameters available in SEARCH function.
| Parameter | Descriptions |
|---|---|
|
|
Login name of the user (can be |
|
|
The password for |
|
|
The authentication base dn for |
|
|
The LDAP server host name. |
|
|
The LDAP server port number. |
|
|
Set to 'Y' to use SSL in bind to LDAP server. Set to 'A' to use SSL with one way authentication (requires LDAP server certificate configured in an Oracle wallet). Set to 'N' to not use SSL (default). |
|
|
dn base for the search. |
|
|
LDAP search filter expression. |
|
|
Search scope (default descends into subtrees). |
|
|
Timeout for the search (default is 3 seconds) |
|
|
Comma separated list of return attribute names |
SELECT val group_dns
FROM table(apex_ldap.search (
p_host => 'ldap.example.com',
p_search_base => 'dc=example,dc=com',
p_search_filter => 'uid='||apex_escape.ldap_search_filter(:APP_USER),
p_attribute_names => 'memberof' ));
SELECT dn, mail, dispname, phone
from ( select dn, name, val
from table(apex_ldap.search (
p_host => 'ldap.example.com',
p_search_base => 'dc=example,dc=com',
p_search_filter => '&(objectClass=person)(ou=Test)',
p_attribute_names => 'mail,displayname,telephonenumber' )))
pivot (min(val) for name in ( 'mail' mail,
'displayname' dispname,
'telephonenumber' phone ))