create or replace package body ldap_ad_util is -- +============================================================================ -- NAME: ldap_ad_util -- PURPOSE: Read User information from the active directory -- GPI 2016 -- +============================================================================ -- +===========================================================+ -- function : getADPWD -- get the Password for the AD User -- later we will save the pwd encrypted in the database -- +===========================================================+ function getadpwd return varchar2 is begin return g_ldap_passwd; end; -- +===========================================================+ -- function : connectAD -- connect to the AD -- +===========================================================+ function connectad return dbms_ldap.session is v_session dbms_ldap.session; v_retval pls_integer; begin -- choose to raise exceptions. dbms_ldap.use_exception := true; dbms_ldap.utf8_conversion:=false; -- connect to the ldap server. v_session := dbms_ldap.init(hostname => g_ldap_host, portnum => g_ldap_port); -- connect with this user to the AD v_retval := dbms_ldap.simple_bind_s(ld => v_session , dn => g_ldap_user , passwd => getadpwd ); if v_retval != DBMS_LDAP.SUCCESS then dbms_output.put_line('-- Error at::'||$$plsql_unit||' :: dbms_ldap.simple_bind_s for User ::'|| g_ldap_user ); raise_application_error( -21001 , '-- Error at::'||$$plsql_unit||' :: dbms_ldap.simple_bind_s for User ::'|| g_ldap_user ); end if; return v_session; exception when others then dbms_output.put_line('-- Error at::'||$$plsql_unit||' :: '||sqlerrm); raise_application_error( sqlcode , '-- Error at::'||$$plsql_unit||' :: '||sqlerrm); end connectad; -- +===========================================================+ -- procedure : disconnectAD -- +===========================================================+ procedure disconnectad( p_session in out dbms_ldap.session) is v_retval pls_integer; begin v_retval := dbms_ldap.unbind_s(ld => p_session); if v_retval != DBMS_LDAP.SUCCESS then dbms_output.put_line('-- Error at::'||$$plsql_unit||' :: Can not close connection to LDAP'); raise_application_error( -21009 , '-- Error at::'||$$plsql_unit||' :: Can not close connection to LDAP'); end if; exception when others then dbms_output.put_line('-- Error at::'||$$plsql_unit||' :: '||sqlerrm); end disconnectad; -- +===========================================================+ -- procedure : disconnectAD -- Code Logic copied from https://oracle-base.com/articles/9i/ldap-from-plsql-9i . -- Thanks to Tim Hall -- +===========================================================+ function readgroups( p_session dbms_ldap.session , p_username varchar2) return apex_t_varchar2 is v_retval pls_integer; v_attrs dbms_ldap.string_collection; v_message dbms_ldap.message; v_entry dbms_ldap.message; v_attr_name varchar2(256); v_ber_element dbms_ldap.ber_element; v_vals dbms_ldap.string_collection; -- empty collection v_group_tab apex_t_varchar2:=apex_t_varchar2(); v_apex_ary apex_application_global.vc_arr2; begin v_attrs(1) := 'memberOf'; -- retrieve all attributes v_retval := dbms_ldap.search_s(ld => p_session , base => g_ldap_base , scope => dbms_ldap.scope_subtree , filter => g_ad_user_type||p_username , attrs => v_attrs , attronly => 0 , res => v_message ); if v_retval != DBMS_LDAP.SUCCESS then dbms_output.put_line('-- Error at::'||$$plsql_unit||' :: dbms_ldap.search_s for filter ::'|| g_ad_user_type||p_username ); raise_application_error( -21002 , '-- Error at::'||$$plsql_unit||' :: dbms_ldap.search_s for filter ::'|| g_ad_user_type||p_username); end if; if dbms_ldap.count_entries(ld => p_session , msg => v_message) > 0 then -- Get all the entries returned by our search. v_entry := dbms_ldap.first_entry( ld => p_session ,msg => v_message); << entry_loop >> while v_entry is not null loop -- Get all the attributes for this entry. dbms_output.put_line('------------------------------------'); v_attr_name := dbms_ldap.first_attribute(ld => p_session, ldapentry => v_entry, ber_elem => v_ber_element); << attributes_loop >> while v_attr_name is not null loop -- Get all the values for this attribute. v_vals := dbms_ldap.get_values (ld => p_session , ldapentry => v_entry , attr => v_attr_name); begin << values_loop >> for i in v_vals.first .. v_vals.last loop dbms_output.put_line('-- Info: Found: ' || v_attr_name || ' = ' || substr(v_vals(i),1,500)); -- decode memberOf = CN=ORA_ASMDBA,CN=Users,DC=pipperr,DC=local -- to the the group name v_apex_ary:=apex_util.string_to_table(p_string=> v_vals(i),p_separator => ',' ); for y in v_apex_ary.first .. v_apex_ary.last loop if v_apex_ary.exists(y) then dbms_output.put_line('-- Info: Catch Group Details ' || v_apex_ary(y)); if y=1 then v_group_tab.extend; v_group_tab( v_group_tab.last ) := (replace(v_apex_ary(y),'CN=','')); dbms_output.put_line('-- Info: Found Group ' || replace(v_apex_ary(y),'CN=','')); end if; end if; end loop; end loop values_loop; exception when others then dbms_output.put_line('-- Error read Attribute: ' || v_attr_name || ' :: Errror '||sqlerrm); end; v_attr_name := dbms_ldap.next_attribute(ld => p_session , ldapentry => v_entry , ber_elem => v_ber_element); end loop attibutes_loop; v_entry := dbms_ldap.next_entry(ld => p_session , msg => v_entry); end loop entry_loop; end if; return v_group_tab; end readgroups; -- +===========================================================+ -- procedure : setApexGroups -- set in the Apex Session dynamic groups -- +===========================================================+ procedure setapexgroups( p_username varchar2 default sys_context( 'APEX$SESSION', 'APP_USER')) is v_session dbms_ldap.session; v_group_tab apex_t_varchar2; begin -- connect to LDAP v_session:=connectad; -- read the groups into dbms_output.put_line('-- Info: Get AD Groups for ' || p_username); v_group_tab:=readgroups(p_session => v_session, p_username => p_username); -- disconnect the LDAP Session disconnectad(p_session => v_session); -- add this groups to the Apex Session if v_group_tab.count > 0 then for i in v_group_tab.first .. v_group_tab.last loop if v_group_tab.exists(i) then dbms_output.put_line('-- Info: set Group in Apex Session ' || v_group_tab(i)); end if; end loop; else dbms_output.put_line('-- Info: No Groups for this user found' ||p_username ); end if; -- set the groups with the group collection -- apex_t_varchar2('KOSTENSTELLE','EINKAUF') apex_authorization.enable_dynamic_groups ( p_group_names => v_group_tab); -- exception when others then dbms_output.put_line('-- Error at::'||$$plsql_unit||' :: '||sqlerrm); -- check that the connection to the ldap is closed! -- Check if connection ist still open is in the function! disconnectad(p_session => v_session); end setapexgroups; begin -- Initialization null; end ldap_ad_util ; /