I struggled a long time over this. I should probably revisit my authentication/authorisation and see if i could write a custom authentication (or plugin).
Anyway, below is my code i put in the source field of my authentication scheme. Post-Authentication Procedure Name is set to "ad_post_auth".
A user logs in and gets authenticated. My post auth process then retrieves all groups the user is a part of, and stores these as a comma-seperated value in an application item (cant be set from browser option) (the item i use is called AI_USER_AD_GROUPS).
procedure ad_post_auth is
begin
DECLARE
v_username varchar2(250);
v_password varchar2(250);
v_groups varchar2(4000);
v_retval BINARY_INTEGER;
ldap_session DBMS_LDAP.session;
l_attributes DBMS_LDAP.string_collection;
return_message DBMS_LDAP.message;
my_entry DBMS_LDAP.message;
entry_index BINARY_INTEGER;
attr_index BINARY_INTEGER;
my_vals DBMS_LDAP.STRING_COLLECTION;
my_ber_elmt DBMS_LDAP.ber_element;
my_attr_name VARCHAR2(256);
BEGIN
v_username := apex_util.get_session_state('P101_USERNAME');
v_password := apex_util.get_session_state('P101_PASSWORD');
--apex_debug_message.log_message('begin post auth');
--user or password may not be null
IF v_username IS NULL OR v_password IS NULL THEN
RETURN;
END IF;
--apex_debug_message.log_message('user and pw not empty');
-- Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;
--create a session
ldap_session := DBMS_LDAP.init(<AD SERVER>,'389');
--apex_debug_message.log_message('session created');
--authenticate user through simple bind
v_retval := DBMS_LDAP.simple_bind_s(ldap_session,
v_username||'@<DOMAIN>',
v_password);
--apex_debug_message.log_message('bind return: '||v_retval);
IF v_retval <> DBMS_LDAP.SUCCESS THEN
RETURN; --authentication failed
END IF;
--apex_debug_message.log_message('bind ok');
--get the groups the user is a member of and store them
--this must be done in this authentication process because we need
--a valid session. The AD server does not allow anonymous binds.
l_attributes(1) := 'memberOf';
v_retval := DBMS_LDAP.search_s(ldap_session,
'dc=<******>,dc=<*****>,dc=com',
DBMS_LDAP.SCOPE_SUBTREE,
'userPrincipalName='||v_username||'@<DOMAIN>',
l_attributes,
0,
return_message);
IF v_retval <> DBMS_LDAP.SUCCESS THEN
NULL;
--user is authenticated, some error popped up during the search
-- you'll have to manually debug this situation!
-- This error doesn't stop the user from authenticating. He simply
-- won't have any groups assigned.
END IF;
-- count the number of entries returned
v_retval := DBMS_LDAP.count_entries(ldap_session, return_message);
-- If ever multiple attributes are being queried, the following code can handle them
-- Just provide a handling block for your attribute.
-- get the first entry
my_entry := DBMS_LDAP.first_entry(ldap_session, return_message);
entry_index := 1;
-- Loop through each of the entries one by one
while my_entry IS NOT NULL loop -- print the current entry
my_attr_name := DBMS_LDAP.first_attribute(ldap_session,my_entry, my_ber_elmt);
attr_index := 1;
while my_attr_name IS NOT NULL loop
my_vals := DBMS_LDAP.get_values (ldap_session, my_entry, my_attr_name);
if my_vals.COUNT > 0 then
FOR i in my_vals.FIRST..my_vals.LAST loop
v_groups := v_groups ||lower(substr(my_vals(i), 4, instr(my_vals(i), ',') - 4))||';';
end loop;
end if;
my_attr_name := DBMS_LDAP.next_attribute(ldap_session,my_entry, my_ber_elmt);
attr_index := attr_index+1;
end loop;
my_entry := DBMS_LDAP.next_entry(ldap_session, my_entry);
entry_index := entry_index+1;
end loop;
APEX_UTIL.set_session_state('AI_USER_AD_GROUPS', v_groups);
-- unbind from the directory
v_retval := DBMS_LDAP.unbind_s(ldap_session);
END;
end;
I then have an authorisation scheme (or schemes) which tests if the user can access pages depending on the groups he belongs to (and those groups are stored in AI_USER_AD_GROUPS)
Scheme Type: Exists SQL Query
Query:
SELECT 1
FROM (
SELECT substr(split_string, position_from + 1, position_to - position_from - 1) groep
FROM (SELECT split_string,
decode(rownum - 1, 0, 0, instr(split_string, ';', 1, rownum - 1)) position_from,
instr(split_string, ';', 1, rownum) position_to
FROM (SELECT :ai_user_ad_groups split_string
FROM dual)
CONNECT BY LEVEL <= length(split_string)
)
WHERE position_to > 0
)
WHERE groep = lower('<GROUPNAME>');
----- (Edit Feb 2013)
Meanwhile, I've created an authorisation type plugin which i can easily use in all my applications i use this post-auth code in (or simply copy-subscribed to the authentication scheme that has this code). It allows to check multiple groups at once.
With PLSQL code:
function check_ad_group_membership (
p_authorization in apex_plugin.t_authorization,
p_plugin in apex_plugin.t_plugin )
return apex_plugin.t_authorization_exec_result
is
l_test_group varchar2(4000) := lower(p_authorization.attribute_01);
l_user_groups varchar2(4000) := lower(:ai_user_ad_groups);
l_count number;
l_result apex_plugin.t_authorization_exec_result;
begin
WITH user_groups AS
(SELECT val1
FROM
xmltable('/root/e/text()' passing xmltype('<root><e>'
|| REPLACE(l_user_groups,';','</e><e>')
|| '</e></root>') columns val1 VARCHAR2(50) path '/' )
),
test_groups AS
(SELECT val2
FROM
xmltable('/root/e/text()' passing xmltype('<root><e>'
|| REPLACE(l_test_group,',','</e><e>')
|| '</e></root>') columns val2 VARCHAR2(50) path '/' )
)
SELECT count(*)
INTO l_count
FROM user_groups
WHERE EXISTS
(SELECT 1 FROM test_groups WHERE val2 = val1);
l_result.is_authorized := l_count > 0;
return l_result;
end check_ad_group_membership;
And 1 Custom Attribute
<ul>
<li>Scope: Component
<li>Label: AD Group Name(s)
<li>Type: Text
<li>Required: Yes
</ul>
(End Edit Feb 2013) -----
May not be ideal but works well so far :)
Edited by: Tom on Feb 13, 2013 9:40 AM
Fixed the <> sign not showing up, edited in my updated group check.