create or replace PACKAGE BODY MY_SECURITY IS -- This package uses a call to a logging package called MY_LOG_MESSAGE. -- This has two main procedures, one called LOG and one called ERR. -- They write the specified messages to tables called MY_LOG and MY_ERRORS. -- Global variables - set for your setup -- LDAP_HOST VARCHAR2(256) := 'url.for.my.server'; LDAP_PORT VARCHAR2(256) := 'port'; -- normally 3060 LDAP_USER VARCHAR2(256) := 'cn=orcladmin,cn=users,dc=myserver,dc=com'; -- or a user with appropriate privs. LDAP_PASSWD VARCHAR2(256) := 'password'; -- the correct password LDAP_BASE VARCHAR2(256) := 'dc=myserver,dc=com'; ------------------------------------------------------------------------------------- FUNCTION CHECK_USER_IN_GROUP(USER_ID VARCHAR2, GROUP_ID VARCHAR2) RETURN BOOLEAN IS /*** THIS FUNCTION CHECKS TO SEE IF THE USER THAT HAS SIGNED ON WITH SINGLE SIGN ON IS A VALID USER IN THAT GROUP. IT WILL RETURN TRUE IF THE USER IS WITHIN THAT GROUP OTHERWISE IT WILL RETURN FAlSE. ***/ P_SESSION DBMS_LDAP.SESSION; RETVAL PLS_INTEGER; LV_SUBSCRIBER_HANDLE DBMS_LDAP_UTL.HANDLE; LV_SUB_TYPE PLS_INTEGER; LV_SUBSCRIBER_ID VARCHAR2(2000); LV_USER_HANDLE DBMS_LDAP_UTL.HANDLE; LV_USER_TYPE PLS_INTEGER; LV_USER_ID VARCHAR2(2000); LV_GROUP_HANDLE DBMS_LDAP_UTL.HANDLE; LV_GROUP_TYPE PLS_INTEGER; LV_GROUP_ID VARCHAR2(2000); LV_PORTAL_GROUP_HANDLE DBMS_LDAP_UTL.HANDLE; LV_PORTAL_GROUP_ID VARCHAR2(2000); BEGIN --ASSIGN THE LOCAL VARIABLES LV_SUB_TYPE := DBMS_LDAP_UTL.TYPE_DN; LV_SUBSCRIBER_ID := LDAP_BASE; LV_USER_TYPE := DBMS_LDAP_UTL.TYPE_DN; LV_USER_ID := 'cn=' || USER_ID || ',cn=users,' || LDAP_BASE; LV_GROUP_TYPE := DBMS_LDAP_UTL.TYPE_DN; LV_GROUP_ID := 'cn=' || GROUP_ID || ',cn=groups,' || LDAP_BASE; /*** WHEN A GROUP IS CREATED WITHIN PORTAL THIS IS LOCAL WITHIN PORTAL. TO FIND OUT THE VALUE OF WHAT YOU SHOULD USE BELOW PLEASE CONNECT TO THE APPLICATION SERVER AND RUN THE FOLLOWING QUERY: SELECT wwsec_oid.get_group_install_base FROM dual; THIS WILL RETURN WHAT YOU SHOULD USE FOR YOUR VALUE WHEN LOOKING FOR A GROUP THAT WAS CREATED IN PORTAL ***/ LV_PORTAL_GROUP_ID := 'cn=' || GROUP_ID || ',cn=portal.123456.1234,cn=groups,' || LDAP_BASE; --TELL DBMS_LDAP TO REPORT ANY EXCEPTIONS THAT COULD BE RAISED DBMS_LDAP.USE_EXCEPTION := TRUE; --CONNECT TO THE LDAP SERVER WITH THE VALUES THAT HAVE BEEN DEFINED GLOBALLY P_SESSION := DBMS_LDAP.init(LDAP_HOST, LDAP_PORT); RETVAL := DBMS_LDAP.simple_bind_s(P_SESSION, LDAP_USER,LDAP_PASSWD); RETVAL := DBMS_LDAP_UTL.create_subscriber_handle(LV_SUBSCRIBER_HANDLE, LV_SUB_TYPE, LV_SUBSCRIBER_ID); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN -- HANDLE ERRORS MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The create subscriber handle has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; RETVAL := DBMS_LDAP_UTL.create_user_handle(LV_USER_HANDLE, LV_USER_TYPE, LV_USER_ID); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN -- HANDLE CREATE USER HANDLE ERRORS MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The create user handle has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; RETVAL := DBMS_LDAP_UTL.set_user_handle_properties(LV_USER_HANDLE, DBMS_LDAP_UTL.SUBSCRIBER_HANDLE, LV_SUBSCRIBER_HANDLE); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN -- HANDLE SET USER HANDLE PROPERTIES ERRORS MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The set user handle properties has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; RETVAL := DBMS_LDAP_UTL.create_group_handle(LV_GROUP_HANDLE, LV_GROUP_TYPE, LV_GROUP_ID); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN -- HANDLE THE CREATE GROUP HANDLE ERRORS MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The create group handle has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; RETVAL := DBMS_LDAP_UTL.set_group_handle_properties(LV_GROUP_HANDLE, DBMS_LDAP_UTL.SUBSCRIBER_HANDLE, LV_SUBSCRIBER_HANDLE); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN -- HANDLE THE SET GROUP HANDLE PROPERTIES ERRORS MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The set group handle properties has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; RETVAL := DBMS_LDAP_UTL.check_group_membership(P_SESSION, LV_USER_HANDLE, LV_GROUP_HANDLE, DBMS_LDAP_UTL.NESTED_MEMBERSHIP); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN --WILL CHECK TO SEE IF THE USER IS IN A PORTAL GROUP ... RETVAL := DBMS_LDAP_UTL.create_group_handle(LV_PORTAL_GROUP_HANDLE, LV_GROUP_TYPE, LV_PORTAL_GROUP_ID); --CREATE A GROUP HANDLE FOR THE PORTAL SEARCH.... IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN --HANDLE ERRORS.. MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The create group handle for the portal has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; --SET THE GROUP HANDLE PROPERTIES FOR THE PORTAL SEARCH.... RETVAL := DBMS_LDAP_UTL.set_group_handle_properties(LV_PORTAL_GROUP_HANDLE, DBMS_LDAP_UTL.SUBSCRIBER_HANDLE, LV_SUBSCRIBER_HANDLE); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN --HANDLE ERRORS... MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The set group handle properties for the portal search has returned: ' || TO_CHAR(RETVAL)); RETURN FALSE; END IF; --CHECK TO SEE IF THE USER IS IN A GROUP THAT PORTAL HAS CREATED.... RETVAL := DBMS_LDAP_UTL.check_group_membership(P_SESSION, LV_USER_HANDLE, LV_PORTAL_GROUP_HANDLE, DBMS_LDAP_UTL.NESTED_MEMBERSHIP); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN --STILL NOT FOUND WITHIN THE GROUP THAT IS CREATED BY PORTAL MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The check group memership function has returned: ' || TO_CHAR(RETVAL) || ' The user: ' || LV_USER_ID || ' can not be found in group: ' || LV_GROUP_ID ||' or in the portal group: ' || LV_PORTAL_GROUP_ID); RETURN FALSE; ELSE --FOUND USER -- DBMS_OUTPUT.PUT_LINE('The user: ' || LV_USER_ID || ' is in the group: ' -- || LV_PORTAL_GROUP_ID); RETURN TRUE; END IF; DBMS_LDAP_UTL.free_handle(LV_PORTAL_GROUP_HANDLE); ELSE -- DBMS_OUTPUT.PUT_LINE('The user: ' || LV_USER_ID || ' is in the group: ' -- || LV_GROUP_ID); RETURN TRUE; END IF; DBMS_LDAP_UTL.free_handle(LV_SUBSCRIBER_HANDLE); DBMS_LDAP_UTL.free_handle(LV_GROUP_HANDLE); DBMS_LDAP_UTL.free_handle(LV_USER_HANDLE); RETVAL := DBMS_LDAP.unbind_s(P_SESSION); IF RETVAL != DBMS_LDAP_UTL.SUCCESS THEN -- HANDLE UNBIND ERRORS MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'The unbind function return: ' || TO_CHAR(RETVAL)); END IF; --HANDLE EXCEPTIONS EXCEPTION WHEN OTHERS THEN MY_LOG_MESSAGE.ERR('CHECK USER WITHIN OID GROUP', 'Error running the check user within oid group function' || ' in MY security for user: ' || USER_ID || 'ERROR CODE: ' || TO_CHAR(SQLCODE) || ' ERROR MESSGAE: ' || SQLERRM); END CHECK_USER_IN_GROUP; ------------------------------------------------------------------------------------- FUNCTION CHECK_USER_IN_GROUP_YN(USER_ID VARCHAR2, GROUP_ID VARCHAR2) RETURN VARCHAR2 IS BEGIN if CHECK_USER_IN_GROUP(user_id, group_id) then -- success! return 'Y'; end if; -- failure return 'N'; END CHECK_USER_IN_GROUP; ------------------------------------------------------------------------------------- PROCEDURE ADD_USER_TO_GROUP(USER_ID VARCHAR2, GROUP_ID VARCHAR2) IS P_SESSION DBMS_LDAP.SESSION; RETVAL PLS_INTEGER; LV_ATTRS DBMS_LDAP.STRING_COLLECTION; LV_MESSAGE DBMS_LDAP.MESSAGE; LV_ENTRY DBMS_LDAP.MESSAGE; LV_FOUND_DN VARCHAR2(256); LV_GROUP_DN VARCHAR2(256); LV_GROUP_NAME VARCHAR2(256); LV_GROUP_ARRAY DBMS_LDAP.MOD_ARRAY; LV_GROUP_VALS DBMS_LDAP.STRING_COLLECTION; LV_GROUP_ID VARCHAR2(2000); LV_PORTAL_GROUP_ID VARCHAR2(2000); LV_USER_ID VARCHAR2(2000); BEGIN --ASSIGN SOME LOCAL VARIABLES... LV_GROUP_ID := 'cn=' || LOWER(GROUP_ID) || ',cn=groups,' || LDAP_BASE; LV_PORTAL_GROUP_ID := 'cn=' || LOWER(GROUP_ID) || ',cn=portal.123456.1234,cn=groups,' || LDAP_BASE; LV_USER_ID := 'cn=' || LOWER(USER_ID) || ',cn=users,' || LDAP_BASE; --TELL DMBS_LDAP TO RAISE ANY EXCEPTIONS THAT CAN HAPPEN WHEN CALLED... DBMS_LDAP.USE_EXCEPTION := TRUE; --CONNECT TO THE LDAP SERVER... P_SESSION := DBMS_LDAP.init(LDAP_HOST,LDAP_PORT); RETVAL := DBMS_LDAP.simple_bind_s(P_SESSION,LDAP_USER,LDAP_PASSWD); --SEARCH FOR THE GROUP THAT HAS BEEN SUPPLIED TO THE PROCEDURE... LV_ATTRS(1) := 'uid'; RETVAL := DBMS_LDAP.search_s(P_SESSION, LDAP_BASE, DBMS_LDAP.SCOPE_SUBTREE, 'cn=' || TRIM(GROUP_ID), LV_ATTRS, 0, LV_MESSAGE); --COUNT THE ENTRIES IF DBMS_LDAP.count_entries(P_SESSION, LV_MESSAGE) = 0 THEN --NO ENTRIES FOUND IN THE LDAP FOR THE GIVEN GROUP. WILL RAISE AN ERROR IN THE LOG... DBMS_OUTPUT.put_line('The search has not found a group called: ' || GROUP_ID); MY_LOG_MESSAGE.ERR('ADD USER TO A OID GROUP','The group called ' || GROUP_ID || ' could not be found. So the user ' || USER_ID || ' was not added to the group.'); ELSIF DBMS_LDAP.count_entries(P_SESSION, LV_MESSAGE) > 0 THEN --AN ENTRY HAS BEEN FOUND IN THE LDAP... DBMS_OUTPUT.put_line('The search has found the group called: ' || GROUP_ID); --GET THE FIRST ENTRY IN THE MESSAGE... LV_ENTRY := DBMS_LDAP.first_entry(P_SESSION,LV_MESSAGE); -- LV_ENTRY_LOOP WHILE LV_ENTRY IS NOT NULL LOOP LV_FOUND_DN := DBMS_LDAP.get_dn(P_SESSION,LV_ENTRY); LV_GROUP_NAME := SUBSTR(lv_found_dn, 4, INSTR(lv_found_dn, ',') - 4); DBMS_OUTPUT.put_line('The value of group name is: ' || LV_GROUP_NAME); IF UPPER(LV_GROUP_NAME) = UPPER(GROUP_ID) THEN --ASSIGN VARRIABLES FOR THE MODIFYING OF THE GROUP... LV_GROUP_DN := LV_FOUND_DN; --CREATING THE GROUP ARRAY... LV_GROUP_ARRAY := DBMS_LDAP.create_mod_array(1); --ASSIGNING THE USER ID TO THE VALUES THAT WILL BE USED... LV_GROUP_VALS(1) := LV_USER_ID; --ASSIGNING THE VALUES TO THE GROUP ARRAY... DBMS_LDAP.populate_mod_array(LV_GROUP_ARRAY, DBMS_LDAP.MOD_ADD, 'uniquemember',LV_GROUP_VALS); --MODIFYING THE VALUE IN THE LDAP SERVER... RETVAL := DBMS_LDAP.modify_s(P_SESSION,LV_GROUP_DN,LV_GROUP_ARRAY); IF RETVAL != DBMS_LDAP.SUCCESS THEN --HANDLE ERRORS... MY_LOG_MESSAGE.ERR('ADD USER TO A OID GROUP', 'The modify_s function returned: ' || TO_CHAR(RETVAL)); END IF; --FREEING UP THE ARRAY IN MEMORY... DBMS_LDAP.free_mod_array(LV_GROUP_ARRAY); END IF; LV_ENTRY := DBMS_LDAP.next_entry(P_SESSION,LV_ENTRY); END LOOP LV_ENTRY_LOOP; END IF; --CLOSE THE CONNECTION TO THE LDAP SERVER... RETVAL := DBMS_LDAP.unbind_s(P_SESSION); IF RETVAL != DBMS_LDAP.SUCCESS THEN --HANDLE THE ERROR... MY_LOG_MESSAGE.ERR('ADD USER TO A OID GROUP', 'The unbind function return: ' || TO_CHAR(RETVAL)); END IF; --HANDLE EXCEPTIONS... EXCEPTION WHEN OTHERS THEN MY_LOG_MESSAGE.ERR('ADD USER TO A OID GROUP', 'Error running the add user to group procedure' || ' in MY security for user: ' || USER_ID || 'ERROR CODE: ' || TO_CHAR(SQLCODE) || ' ERROR MESSGAE: ' || SQLERRM); END ADD_USER_TO_GROUP; ------------------------------------------------------------------------------------- FUNCTION GET_USER_GROUPS(USER_ID VARCHAR2) RETURN VARCHAR2 IS P_SESSION DBMS_LDAP.SESSION; RETVAL PLS_INTEGER; LV_ATTRS DBMS_LDAP.STRING_COLLECTION; LV_MESSAGE DBMS_LDAP.MESSAGE; LV_ENTRY DBMS_LDAP.MESSAGE; LV_USER_ID VARCHAR2(2000); LV_FOUND_DN VARCHAR2(256); LV_GROUP VARCHAR2(2000); BEGIN --TELL DBMS_LDAP TO RAISE ANY EXCEPTIONS... DBMS_LDAP.USE_EXCEPTION := TRUE; --CONNCET TO THE LDAP SERVER... P_SESSION := DBMS_LDAP.init(LDAP_HOST,LDAP_PORT); RETVAL := DBMS_LDAP.simple_bind_s(P_SESSION,LDAP_USER,LDAP_PASSWD); --SEARCH THE LDAP DIRECTORY... LV_ATTRS(1) := 'uid'; LV_USER_ID := 'cn=' || LOWER(USER_ID) || ',cn=users,' || LDAP_BASE; RETVAL := DBMS_LDAP.search_s(P_SESSION, LDAP_BASE, DBMS_LDAP.SCOPE_SUBTREE, 'uniquemember='||TRIM(LV_USER_ID), LV_ATTRS, 0, LV_MESSAGE); --CHECK TO SEE WHOW MANY ENTRIES THE SEARCH HAS RETURNED... IF DBMS_LDAP.count_entries(P_SESSION,LV_MESSAGE) = 0 THEN --NO GROUP FOUND FOR THE USER GIVEN. WILL RAISE AN ERROR IN THE LOG... DBMS_OUTPUT.put_line('The search has found no groups for the user: ' || USER_ID); MY_LOG_MESSAGE.ERR('GET USER GROUP', 'The search has found no groups for the user: ' || USER_ID); LV_GROUP := 'None Found!'; RETURN LV_GROUP; ELSIF DBMS_LDAP.count_entries(P_SESSION,LV_MESSAGE) > 0 THEN --THE SEARCH HAS FOUND SOMETHING... DBMS_OUTPUT.put_line('We have found something....'); --GET THE FIRST ENTRY IN THE SEARCH THEN... LV_ENTRY := DBMS_LDAP.first_entry(P_SESSION,LV_MESSAGE); LV_GROUP := NULL; -- LV_ENTRY_LOOP WHILE LV_ENTRY IS NOT NULL LOOP --GET THE NAME OF THE GROUP THAT THE ENTRY HAS... LV_FOUND_DN := DBMS_LDAP.get_dn(P_SESSION,LV_ENTRY); -- DBMS_OUTPUT.put_line('The DN is: ' || -- SUBSTR(lv_found_dn, 4, INSTR(lv_found_dn, ',') - 4)); --CHECK TO SEE IF WE ARE AT THE FIRST RECORD OR NOT... IF LV_GROUP IS NULL THEN LV_GROUP := SUBSTR(lv_found_dn, 4, INSTR(lv_found_dn, ',') - 4); ELSE LV_GROUP := LV_GROUP || ',' || SUBSTR(lv_found_dn, 4, INSTR(lv_found_dn, ',') - 4); END IF; --GO TO THE NEXT ENTRY IN THE MESSAGE BUNDLE... LV_ENTRY := DBMS_LDAP.next_entry(P_SESSION,LV_ENTRY); END LOOP LV_ENTRY_LOOP; RETURN LV_GROUP; END IF; --CLOSE THE CONNECTION TO THE LDAP SERVER... RETVAL := DBMS_LDAP.unbind_s(P_SESSION); IF RETVAL != DBMS_LDAP.SUCCESS THEN --HANDLE THE ERROR... MY_LOG_MESSAGE.ERR('ADD USER TO A OID GROUP', 'The unbind function return: ' || TO_CHAR(RETVAL)); END IF; --HANDLE EXCEPTIONS... EXCEPTION WHEN OTHERS THEN MY_LOG_MESSAGE.ERR('ADD USER TO A OID GROUP', 'Error running the add user to group procedure' || ' in MY security for user: ' || USER_ID || 'ERROR CODE: ' || TO_CHAR(SQLCODE) || ' ERROR MESSGAE: ' || SQLERRM); END GET_USER_GROUPS; END;