Wednesday, October 9, 2013

PLSQL--DBMS_LDAP -- updating active directory attributes from the database.

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;

/

6 comments :

  1. This comment has been removed by the author.

    ReplyDelete
  2. Erman,
    Great 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.

    ReplyDelete
  3. Hi,

    you 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 :)

    ReplyDelete
  4. Here is a spec of the procedure for you...

    PROCEDURE 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.

    ReplyDelete
  5. Erman,
    Thanks 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);

    ReplyDelete
  6. 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