To update an active directory attribute from the Oracle Database, you can use the function below...
This function has been tested by me, it works without any problems..
Actually, this function has been written to update some attributes of the OID users from the EBS database, more specifically this function have been used as an integrated solution for updating OID attributes of the EBS users, during EBS user creation phase...That's why, the variable names are p_user and etc.. You can modify it if you like..
Function modifyUser accepts two inputs , p_user_dn -> for locating the attribute in active directory and p_attr -> for the attribute name..
Basically in this function, we create a ldap session by dbms_ldap and use mod_array to populate the attribute in question..
SPEC:
function modifyUser (p_user_dn in varchar2, p_attr in varchar2) RETURN VARCHAR2;
BODY:
FUNCTION modifyUser (p_user_dn in varchar2, p_attr in varchar2) RETURN VARCHAR2
IS
-- modify User by Erman Arslan 03.10.2013
v_ldap_port VARCHAR2(256);
v_ldap_user VARCHAR2(256);
v_ldap_passwd VARCHAR2(256);
retval PLS_INTEGER;
v_host_zaehler NUMBER := 0;
v_retval PLS_INTEGER;
v_session DBMS_LDAP.session;
v_user_array dbms_ldap.mod_array;
v_user_values DBMS_LDAP.STRING_COLLECTION;
begin
DBMS_LDAP.USE_EXCEPTION := TRUE;
v_session := DBMS_LDAP.init('ldapservername', ldapport);
retval := DBMS_LDAP.simple_bind_s (v_session,'cn=adminuser','adminpassword');
v_user_array := DBMS_LDAP.create_mod_array(100);
v_user_values(1) := NVL(p_attr,'NULL');
dbms_ldap.populate_mod_array(v_user_array, dbms_ldap.mod_replace, 'orclSourceObjectDN', v_user_values);
v_retval := dbms_ldap.modify_s(v_session, p_user_dn, v_user_array);
dbms_ldap.free_mod_array(v_user_array);
v_retval := dbms_ldap.unbind_s(v_session);
RETURN 'OK' ;
EXCEPTION
WHEN OTHERS THEN
dbms_ldap.free_mod_array(v_user_array);
IF v_session IS NOT NULL THEN
v_retval := DBMS_LDAP.unbind_s(ld => v_session);
END IF;
RETURN SQLERRM;
END modifyUser;
/
This function has been tested by me, it works without any problems..
Actually, this function has been written to update some attributes of the OID users from the EBS database, more specifically this function have been used as an integrated solution for updating OID attributes of the EBS users, during EBS user creation phase...That's why, the variable names are p_user and etc.. You can modify it if you like..
Function modifyUser accepts two inputs , p_user_dn -> for locating the attribute in active directory and p_attr -> for the attribute name..
Basically in this function, we create a ldap session by dbms_ldap and use mod_array to populate the attribute in question..
SPEC:
function modifyUser (p_user_dn in varchar2, p_attr in varchar2) RETURN VARCHAR2;
BODY:
FUNCTION modifyUser (p_user_dn in varchar2, p_attr in varchar2) RETURN VARCHAR2
IS
-- modify User by Erman Arslan 03.10.2013
v_ldap_port VARCHAR2(256);
v_ldap_user VARCHAR2(256);
v_ldap_passwd VARCHAR2(256);
retval PLS_INTEGER;
v_host_zaehler NUMBER := 0;
v_retval PLS_INTEGER;
v_session DBMS_LDAP.session;
v_user_array dbms_ldap.mod_array;
v_user_values DBMS_LDAP.STRING_COLLECTION;
begin
DBMS_LDAP.USE_EXCEPTION := TRUE;
v_session := DBMS_LDAP.init('ldapservername', ldapport);
retval := DBMS_LDAP.simple_bind_s (v_session,'cn=adminuser','adminpassword');
v_user_array := DBMS_LDAP.create_mod_array(100);
v_user_values(1) := NVL(p_attr,'NULL');
dbms_ldap.populate_mod_array(v_user_array, dbms_ldap.mod_replace, 'orclSourceObjectDN', v_user_values);
v_retval := dbms_ldap.modify_s(v_session, p_user_dn, v_user_array);
dbms_ldap.free_mod_array(v_user_array);
v_retval := dbms_ldap.unbind_s(v_session);
RETURN 'OK' ;
EXCEPTION
WHEN OTHERS THEN
dbms_ldap.free_mod_array(v_user_array);
IF v_session IS NOT NULL THEN
v_retval := DBMS_LDAP.unbind_s(ld => v_session);
END IF;
RETURN SQLERRM;
END modifyUser;
/
This comment has been removed by the author.
ReplyDeleteErman,
ReplyDeleteGreat post. Your function seems super easy compared to other options I have seen. I'm a bit unclear on how you manage the actual value being updated, as you only have username and attribute being updated passed in, but not the value to update. Any further insight, would be appreciated.
Hi,
ReplyDeleteyou see the v_user_values there? So, set this variable to the value which you want to set for the attribute. Modify the code according to your needs, test it and see :)
Here is a spec of the procedure for you...
ReplyDeletePROCEDURE populate_mod_array (String Version)
Populates one set of attribute information for add or modify operations.
Syntax
PROCEDURE populate_mod_array
(
modptr IN DBMS_LDAP.MOD_ARRAY,
mod_op IN PLS_INTEGER,
mod_type IN VARCHAR2,
modval IN DBMS_LDAP.STRING_COLLECTION
);
Parameters
Parameter Description
modptr
The data structure holds a pointer to an LDAP mod array.
mod_op
This field specifies the type of modification to perform.
mod_type
This field indicates the name of the attribute type to which the modification applies.
modval
This field specifies the attribute values to add, delete, or replace. It is for the string values only.
Erman,
ReplyDeleteThanks so much for your help on this.
Does this require DN(Distinguished Name) or samaccountname?
AD and the whole DN, as always been a bit of a black box to me.
dbms_ldap.populate_mod_array(v_user_array, dbms_ldap.mod_replace, 'orclSourceObjectDN', v_user_values);
v_retval := dbms_ldap.modify_s(v_session, p_user_dn, v_user_array);
I figured it out. Looks like it requires DN. Which at least in our case is somewhat a complex name. Was hoping I could do with just the samaccountname. But I've got it working for now.
ReplyDelete