Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

1 décembre 2005 4 01 /12 /décembre /2005 21:27

This is a simple PL/SQL package that allows to insert, update and check existence of users in the LDAP.

The package Specification

CREATE OR REPLACE PACKAGE Pkg_Ldap
IS
  /*
   * Package to handle the LDAP From PL/SQL
   * All functions use the DBMS_LDAP package
   *
  */

  -- User Insert --
  FUNCTION Insert_User
  (
    login     IN VARCHAR2,
    pwd       IN VARCHAR2,
    name      IN VARCHAR2,
    givenname IN VARCHAR2,
    company   IN VARCHAR2,
    title     IN VARCHAR2,
    fonction  IN VARCHAR2,
    address   IN VARCHAR2,
    street    IN VARCHAR2,
    cp        IN VARCHAR2,
    city      IN VARCHAR2,
    tel       IN VARCHAR2,
    mobile    IN VARCHAR2,
    mail      IN VARCHAR2
  )
  RETURN VARCHAR2 ;

  -- User Update --
  FUNCTION Modif_User
  (
    login     IN VARCHAR2,
    pwd       IN VARCHAR2,
    name      IN VARCHAR2,
    givenname IN VARCHAR2,
    company   IN VARCHAR2,
    title     IN VARCHAR2,
    fonction  IN VARCHAR2,
    address   IN VARCHAR2,
    street    IN VARCHAR2,
    cp        IN VARCHAR2,
    city      IN VARCHAR2,
    tel       IN VARCHAR2,
    mobile    IN VARCHAR2,
    mail      IN VARCHAR2
  )
  RETURN VARCHAR2 ;

  -- User Authentification --
  FUNCTION existe_user( p_user IN VARCHAR2, p_pwd IN VARCHAR2 )
  RETURN VARCHAR2 ;

  -- Add to group --
  FUNCTION add_in_group (p_session dbms_ldap.SESSION,
    p_group VARCHAR2,
    p_user VARCHAR2)
  RETURN PLS_INTEGER ;


END Pkg_Ldap;
/


The package body

CREATE OR REPLACE PACKAGE BODY Pkg_Ldap
IS

-- globale variables --
GN$Errcode     NUMBER ;
GC$ErrLib      VARCHAR2(2000) ;

GC$ldap_host   VARCHAR2(256)   := 'company.my_dc.org'; -- your LDAP Host url
GC$ldap_port   VARCHAR2(256)   := '3060'; -- your LDAP Port
GC$ldap_user   VARCHAR2(256)   := 'cn=orcladmin';
GC$ldap_passwd VARCHAR2(256)   := 'welcome1';
GC$ldap_base   VARCHAR2(256)   := 'cn=my_cn,dc=my_dc,dc=fr';
GC$Groupe      VARCHAR2(200)   := 'cn=authenticated_users,cn=portal_groups,cn=groups,dc=my_dc,dc=fr';

-------------------------
--  Insert a new user  --
-------------------------
FUNCTION Insert_User
  (
    login     IN VARCHAR2,
    pwd       IN VARCHAR2,
    name      IN VARCHAR2,
    givenname IN VARCHAR2,
    company   IN VARCHAR2,
    title     IN VARCHAR2,
    fonction  IN VARCHAR2,
    address   IN VARCHAR2,
    street    IN VARCHAR2,
    cp        IN VARCHAR2,
    city      IN VARCHAR2,
    tel       IN VARCHAR2,
    mobile    IN VARCHAR2,
    mail      IN VARCHAR2
  )
RETURN VARCHAR2
IS

  retval PLS_INTEGER;

  my_session DBMS_LDAP.SESSION;
  emp_dn     VARCHAR2(256);
  emp_array  DBMS_LDAP.MOD_ARRAY;
  emp_vals   DBMS_LDAP.STRING_COLLECTION;

BEGIN
 retval := -1;
 
 -- Insert into LDAP --
 
 DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || GC$ldap_host);
 DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || GC$ldap_port);
 
 DBMS_LDAP.USE_EXCEPTION := TRUE;
 
 my_session := DBMS_LDAP.init(GC$ldap_host,GC$ldap_port);
 
 DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
 RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');
 
 retval := DBMS_LDAP.simple_bind_s(my_session, GC$ldap_user,GC$ldap_passwd);
 
 DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));
 
 emp_array := DBMS_LDAP.create_mod_array(20);
 
 -- Properties --
 emp_vals(1) := login;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'uid',emp_vals);
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'cn',emp_vals);
 emp_vals(1) := name;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'sn',emp_vals);
 emp_vals(1) := givenname;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'givenname',emp_vals);
 emp_vals(1) := pwd;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'userpassword',emp_vals);
 emp_vals(1) := company;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'st',emp_vals);
 emp_vals(1) := title;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'title',emp_vals);
 emp_vals(1) := address;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'homepostaladdress',emp_vals);
 emp_vals(1) := street;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'street',emp_vals);
 emp_vals(1) := cp;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'postalcode',emp_vals);
 emp_vals(1) := city;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'l',emp_vals);
 emp_vals(1) := tel;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'telephonenumber',emp_vals);
 emp_vals(1) := mobile;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'mobile',emp_vals);
 emp_vals(1) := mail;
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'mail',emp_vals);
 
 emp_vals(1) := 'top';
 emp_vals(2) := 'person';
 emp_vals(3) := 'organizationalPerson';
 emp_vals(4) := 'inetOrgPerson';
 emp_vals(5) := 'orcluser';
 emp_vals(6) := 'orcluserv2';
 
 DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'objectclass',emp_vals);
 
 emp_dn := 'cn=' || login || ', ' || GC$ldap_base;
 
 DBMS_OUTPUT.PUT_LINE(RPAD('Adding Entry for DN ',25,' ') ||': [' || emp_dn || ']');
 
 retval := DBMS_LDAP.add_s(my_session,emp_dn,emp_array);
 DBMS_OUTPUT.PUT_LINE(RPAD('add_s Returns ',25,' ') || ': '|| TO_CHAR(retval));
 
 DBMS_LDAP.free_mod_array(emp_array);
 
 
 -- Add the user to the portal group --
 retval := add_in_group (my_session, GC$Groupe, emp_dn) ;
 
 retval := DBMS_LDAP.unbind_s(my_session);
 
 DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' ||
 TO_CHAR(retval));
 
 DBMS_OUTPUT.PUT_LINE('Resultt -> OK');
 
 RETURN 'OK' ;

-- Handle Exceptions
EXCEPTION

  WHEN OTHERS THEN

  GN$ErrCode := SQLCODE ;
  GC$ErrLib  := SQLERRM ;
  DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(GN$ErrCode));
  DBMS_OUTPUT.PUT_LINE(' Error Message : ' || GC$ErrLib);
  DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
  RETURN ( GC$ErrLib ) ;

END Insert_User;

-----------------------------
--   Update existing user  --
-----------------------------
FUNCTION Modif_User
  (
    login     IN VARCHAR2,
    pwd       IN VARCHAR2,
    name      IN VARCHAR2,
    givenname IN VARCHAR2,
    company   IN VARCHAR2,
    title     IN VARCHAR2,
    fonction  IN VARCHAR2,
    address   IN VARCHAR2,
    street    IN VARCHAR2,
    cp        IN VARCHAR2,
    city      IN VARCHAR2,
    tel       IN VARCHAR2,
    mobile    IN VARCHAR2,
    mail      IN VARCHAR2
  )
RETURN VARCHAR2
IS

  retval       PLS_INTEGER;

  my_session   DBMS_LDAP.SESSION;
  emp_session  DBMS_LDAP.SESSION;
  emp_dn       VARCHAR2(256);
  emp_array    DBMS_LDAP.MOD_ARRAY;
  emp_vals     DBMS_LDAP.STRING_COLLECTION;

BEGIN
 retval := -1;


 emp_dn := 'cn=' || login || ', ' || GC$ldap_base ;

 DBMS_OUTPUT.PUT('ADD Example: ');
 DBMS_OUTPUT.PUT_LINE('Connecting to directory .. ');
 DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || GC$ldap_host);
 DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || GC$ldap_port);


  -- Unabling exceptions --
  DBMS_LDAP.USE_EXCEPTION := TRUE;

  -- Initialization --
  my_session := DBMS_LDAP.init(GC$ldap_host,GC$ldap_port);

  DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ')  || ': ' ||
     RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');

  -- Connexion au LDAP --
  retval := DBMS_LDAP.simple_bind_s(my_session, GC$ldap_user,GC$ldap_passwd);

  DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));


  -- Tableau pour les modifications --
  emp_array := DBMS_LDAP.create_mod_array(20);

  -- Populate the varray --
  IF login IS NOT NULL THEN
    emp_vals(1) := login;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'uid',emp_vals);
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'cn',emp_vals);
  END IF ;

  IF name IS NOT NULL THEN
    emp_vals(1) := name;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'sn',emp_vals);
  END IF ;
  IF givenname IS NOT NULL THEN
    emp_vals(1) := givenname;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'givenname',emp_vals);
  END IF ;
  IF company IS NOT NULL THEN
    emp_vals(1) := company;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'st',emp_vals);
  END IF ;
  IF address IS NOT NULL THEN
    emp_vals(1) := address;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'homepostaladdress',emp_vals);
  END IF ;
  IF street IS NOT NULL THEN
    emp_vals(1) := street;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'street',emp_vals);
  END IF ;
  IF cp IS NOT NULL THEN
    emp_vals(1) := cp;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'postalcode',emp_vals);
  END IF ;
  IF city IS NOT NULL THEN
    emp_vals(1) := city;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'l',emp_vals);
  END IF ;
  IF title IS NOT NULL THEN
    emp_vals(1) := title;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'title',emp_vals);
  END IF ;
  IF tel IS NOT NULL THEN
    emp_vals(1) := tel;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'telephonenumber',emp_vals);
  END IF ;
  IF mobile IS NOT NULL THEN
    emp_vals(1) := mobile;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'mobile',emp_vals);
  END IF ;
  IF pwd IS NOT NULL THEN
    emp_vals(1) := pwd;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'userpassword',emp_vals);
  END IF ;
  IF mail IS NOT NULL THEN
    emp_vals(1) := mail;
    DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'mail',emp_vals);
  END IF ;

  -- Update LDAP --
  retval := DBMS_LDAP.modify_s(my_session,emp_dn,emp_array);
  DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ')  || ': ' || TO_CHAR(retval));

  -- Free the varray --
  DBMS_LDAP.free_mod_array(emp_array);

 -- Logout from LDAP --
 retval := DBMS_LDAP.unbind_s(my_session);

 DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') ||  ': ' || TO_CHAR(retval));

 DBMS_OUTPUT.PUT_LINE('Résultat -> OK');

RETURN 'OK' ;

-- Handle Exceptions
EXCEPTION

  WHEN OTHERS THEN

  GN$ErrCode := SQLCODE ;
  GC$ErrLib  := SQLERRM ;
  DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(GN$ErrCode));
  DBMS_OUTPUT.PUT_LINE(' Error Message : ' || GC$ErrLib);
  DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
  RETURN ( GC$ErrLib ) ;

END Modif_User;


  /*
  *****************************************************************
  -- Function : add_in_group
  -- Parameters : group name / user name (DN) / session
  -- Return     : 0 = Ok / others KO
  *****************************************************************
  */
  FUNCTION add_in_group (p_session dbms_ldap.SESSION,
    p_group VARCHAR2,
    p_user VARCHAR2)
    RETURN PLS_INTEGER IS
    my_vals  dbms_ldap.string_collection;
    v_array  dbms_ldap.mod_array;
    retval      PLS_INTEGER;

    BEGIN
      -- Initialize the pl/sql table for the new entry
      my_vals(1) := p_user;

      -- Initialize the varray for the modify command
      v_array := dbms_ldap.create_mod_array(num => 1);

      IF v_array = NULL THEN
   dbms_output.put_line('Error add_in_group: v_array not initialized.');
   NULL;
      END IF;
      dbms_output.put_line ('v_array initialisee avec succes.');

      -- Populate the varray
      dbms_ldap.populate_mod_array(v_array,
                       dbms_ldap.mod_add,
                       'uniquemember',
                       my_vals);

      dbms_output.put_line(p_user);
      -- Group Modification
      retval := dbms_ldap.modify_s(p_session, p_group, v_array);


      -- Free the varray
      dbms_ldap.free_mod_array(v_array);

      RETURN retval;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('add_in_group : '|| SQLCODE||' '||SQLERRM);
   RETURN -1 ;
  END add_in_group;

  -----------------------------
  --  User Authentification  --
  -----------------------------
FUNCTION existe_user( p_user IN VARCHAR2, p_pwd IN VARCHAR2 )
  RETURN VARCHAR2
IS
 ldap_host            VARCHAR2(256);
 ldap_port            PLS_INTEGER;
 ldap_user            VARCHAR2(256);
 ldap_passwd          VARCHAR2(256);
 ldap_base            VARCHAR2(256);
 retval               PLS_INTEGER;
 my_session           DBMS_LDAP.SESSION;
 subscriber_handle    DBMS_LDAP_UTL.HANDLE;
 sub_type             PLS_INTEGER;
 subscriber_id        VARCHAR2(2000);
 my_pset_coll         DBMS_LDAP_UTL.PROPERTY_SET_COLLECTION;
 my_property_names    DBMS_LDAP.STRING_COLLECTION;
 my_property_values   DBMS_LDAP.STRING_COLLECTION;
 user_handle          DBMS_LDAP_UTL.HANDLE;
 user_id              VARCHAR2(2000);
 user_type            PLS_INTEGER;
 user_password        VARCHAR2(2000);
 my_mod_pset          DBMS_LDAP_UTL.MOD_PROPERTY_SET;
 my_attrs             DBMS_LDAP.STRING_COLLECTION;
 user_dn              VARCHAR2(256);
BEGIN
 -- Please customize the following variables as needed
 ldap_user     := 'cn=orcladmin';
 ldap_passwd   := 'welcome1';
 sub_type      := DBMS_LDAP_UTL.TYPE_DEFAULT;
 subscriber_id := NULL;
 user_type     := DBMS_LDAP_UTL.TYPE_NICKNAME;
 user_id       := p_user;
 user_password := p_pwd;
 -- Choosing exceptions to be raised by DBMS_LDAP library.
 DBMS_LDAP.USE_EXCEPTION := TRUE;

 DBMS_OUTPUT.PUT_LINE('Input Parameters: ');
 DBMS_OUTPUT.PUT_LINE('LDAP HOST: '      || GC$ldap_host );
 DBMS_OUTPUT.PUT_LINE('LDAP PORT: '      || GC$ldap_port);
 DBMS_OUTPUT.PUT_LINE('LDAP BIND USER: ' || ldap_user);
 DBMS_OUTPUT.PUT_LINE('USER ID : '       || user_id);
 DBMS_OUTPUT.PUT_LINE('----------------------------------');
 -----------------------------------------------
 -- Connect to the LDAP server
 -- and obtain and ld session.
 -----------------------------------------------
 DBMS_OUTPUT.PUT('Connecting to ' || GC$ldap_host || ' ...');

 my_session := DBMS_LDAP.init(GC$ldap_host, GC$ldap_port);

 DBMS_OUTPUT.PUT_LINE(': Connected.');
 -----------------------------------------------
 -- Bind to the directory
  -----------------------------------------------
 DBMS_OUTPUT.PUT('Binding to directory as ' || ldap_user || ' ... ');
 retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);
 DBMS_OUTPUT.PUT_LINE(': Successful.');
 ---------------------------------------------------------------------
 -- Create Subscriber Handle
  ---------------------------------------------------------------------
 DBMS_OUTPUT.PUT('Creating Realm Handle ... ');
 retval := DBMS_LDAP_UTL.create_subscriber_handle(subscriber_handle,
                                                  sub_type,
                                                  subscriber_id);
 IF retval != DBMS_LDAP_UTL.SUCCESS THEN
    -- Handle Errors
    DBMS_OUTPUT.PUT_LINE('Error: create_subscriber_handle returns : ' || TO_CHAR(retval));
    RETURN('Error: create_subscriber_handle returns : ' || TO_CHAR(retval));
 END IF;
 DBMS_OUTPUT.PUT_LINE(': Successful.');
 ---------------------------------------------------------------------
 -- Create User Handle
  ---------------------------------------------------------------------
 DBMS_OUTPUT.PUT('Creating user handle for ' || user_id || ' ... ');
 retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
 IF retval != DBMS_LDAP_UTL.SUCCESS THEN
    -- Handle Errors
    DBMS_OUTPUT.PUT_LINE('Error: create_user_handle returns : ' || TO_CHAR(retval));
    RETURN('Error: create_user_handle returns : ' || TO_CHAR(retval));
 END IF;
 DBMS_OUTPUT.PUT_LINE(': Successful.');
 ---------------------------------------------------------------------
 -- Set user handle properties
 -- (link subscriber to user )
 ---------------------------------------------------------------------
 retval := DBMS_LDAP_UTL.set_user_handle_properties(user_handle,
                                                    DBMS_LDAP_UTL.SUBSCRIBER_HANDLE,
                                                    subscriber_handle);
 IF retval != DBMS_LDAP_UTL.SUCCESS THEN
    -- Handle Errors
    DBMS_OUTPUT.PUT_LINE('Error: set_user_handle_properties returns : ' || TO_CHAR(retval));
 RETURN('Error: set_user_handle_properties returns : ' || TO_CHAR(retval));
 END IF;
 ---------------------------------------------------------------------
 -- Authenticate User
 ---------------------------------------------------------------------
 DBMS_OUTPUT.PUT('Authenticating user ' || user_id || ' ... ');
 retval := DBMS_LDAP_UTL.authenticate_user(my_session,
                                           user_handle,
                                           DBMS_LDAP_UTL.AUTH_SIMPLE,
                                           user_password,
                                           NULL);
 IF retval != DBMS_LDAP_UTL.SUCCESS THEN
    -- Handle Errors
    DBMS_OUTPUT.PUT_LINE('Authentification error :  ' || TO_CHAR(retval));
 IF retval = -5 THEN
   RETURN('User unknown');
 ELSIF retval = -16 THEN
   RETURN ('Incorrect password');

 ELSIF retval = DBMS_LDAP_UTL.PARAM_ERROR THEN
   RETURN (' Invalid input parameters.');

 ELSIF retval = DBMS_LDAP_UTL.GENERAL_ERROR THEN
   RETURN (' Authentication failed.');

 ELSIF retval = DBMS_LDAP_UTL.NO_SUCH_USER THEN
   RETURN (' USER doesn''t exist.');

 ELSIF retval = DBMS_LDAP_UTL.MULTIPLE_USER_ENTRIES THEN
   RETURN (' Multiple NUMBER OF USER DN entries exist IN the DIRECTORY FOR the given USER.');

 ELSIF retval = DBMS_LDAP_UTL.INVALID_SUBSCRIBER_ORCL_CTX THEN
   RETURN (' Invalid Subscriber Oracle Context.');

 ELSIF retval = DBMS_LDAP_UTL.NO_SUCH_SUBSCRIBER THEN
   RETURN (' Subscriber doesn''t exist.');

 ELSIF retval = DBMS_LDAP_UTL.MULTIPLE_SUBSCRIBER_ENTRIES THEN
   RETURN (' Multiple NUMBER OF subscriber DN entries exist IN the DIRECTORY FOR the given subscriber.');

 ELSIF retval = DBMS_LDAP_UTL.INVALID_ROOT_ORCL_CTX THEN
   RETURN (' Invalid Root Oracle Context.');

 ELSIF retval = DBMS_LDAP_UTL.AUTH_PASSWD_CHANGE_WARN THEN
   RETURN (' PASSWORD should be changed.');

 ELSIF retval = DBMS_LDAP_UTL.ACCT_TOTALLY_LOCKED_EXCEPTION THEN
   RETURN (' USER account IS locked.');

 ELSIF retval = DBMS_LDAP_UTL.PWD_EXPIRED_EXCEPTION THEN
   RETURN (' USER PASSWORD has expired.');

 ELSIF retval = DBMS_LDAP_UTL.PWD_GRACELOGIN_WARN THEN
   RETURN (' Grace login FOR USER.');

 ELSE
   RETURN('Authentification error : ' || TO_CHAR(retval));
 END IF ;
 ELSE
    DBMS_OUTPUT.PUT_LINE(': Successful.');
 END IF;


 ------------------------------------------
 -- Free Mod Propertyset
 ------------------------------------------
 DBMS_LDAP_UTL.free_mod_propertyset(my_mod_pset);
 ---------------------------------------------------------------------
 -- Free handles
 ---------------------------------------------------------------------
 DBMS_LDAP_UTL.free_handle(subscriber_handle);
 DBMS_LDAP_UTL.free_handle(user_handle);

 -- unbind from the directory
 DBMS_OUTPUT.PUT('Unbinding from directory ... ');
 retval := DBMS_LDAP.unbind_s(my_session);
 IF retval != DBMS_LDAP_UTL.SUCCESS THEN
    -- Handle Errors
    DBMS_OUTPUT.PUT_LINE('unbind_s returns : ' || TO_CHAR(retval));
 RETURN('unbind_s returns : ' || TO_CHAR(retval));
 ELSE
    DBMS_OUTPUT.PUT_LINE(': Successful.');
 END IF;

 RETURN( 'OK' ) ;

 -- Handle Exceptions
 EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(SQLCODE));
    DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
    RETURN SQLERRM ;
END;


END Pkg_Ldap;
/


Calling from PL/SQL

--------------------
--  Insert entry  --
--------------------
Declare
    LC$Return Varchar2(2000) ;
BEGIN
    LC$Return := PKG_LDAP.Insert_User
  (
   
'f.degrelle@free.fr',
    'musicisthebest',
    'degrelle',
    'francois',
    'znort',
    'researcher',
    'analyst',
    '10 World place',
    '',
    '75000',
    'Paris',
    '0102030405',
    '0606060606',
   
'f.degrelle@free.fr'
  );

  If LC$Return <> 'OK' Then
    message( LC$Return ) ;
  End if ;
END;

--------------------
--  Update entry  --
--------------------
DECLARE
    LC$Return Varchar2(2000) ;
BEGIN

    LC$Return := PKG_LDAP.Modif_User
  (
   
'f.degrelle@free.fr',
    'musicisthebest',
    'degrelle',
    'francois',
    'znort',
    'researcher',
    'analyst',
    '10 World place',
    '',
    '75000',
    'Paris',
    '0102030405',
    '0606060606',
   
'f.degrelle@free.fr'
  );

  If LC$Return <> 'OK' Then
    message( LC$Return ) ;
  End if ;


END;

Calling from Java

  /*---------------------------------------
   * Check existence of user in the LDAP
   *--------------------------------------*/
  public String Check_User (OracleConnection conn, String p_user, String p_pwd)
       throws Exception, SQLException
  {

    String sReturn = "" ;

    // Call the stored function
    CallableStatement call = conn.prepareCall ("{ ? = call PKG_LDAP.Existe_User (?,?)}");

    call.registerOutParameter (1, Types.VARCHAR);
    call.setString (2, p_user);
    call.setString (3, p_pwd);   
    call.execute ();
    sReturn  = call.getString (1) ;

    if( ! sReturn.equals("OK") ) System.out.println("*** Unknown user *** " + sReturn) ;

    call.close();
   
    return( sReturn ) ;

  }


  /*-------------------------
   *   Update the LDAP
   *------------------------*/
  public String SetUserInfo (OracleConnection conn, int iUtiId)
  throws Exception, SQLException
  {
      String sReturn = "" ;

      // Call the stored function
      CallableStatement call = conn.prepareCall ("{ ? = call PKG_LDAP.Modif_User (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

      call.registerOutParameter (1, Types.VARCHAR);
      call.setString (2,  "login");
      call.setString (3,  "pwd");
      call.setString (4,  "name");
      call.setString (5,  "givenname");   
      call.setString (6,  "company");
      call.setString (7,  "title");   
      call.setString (8,  "function");
      call.setString (9,  "address");   
      call.setString (10, "street");
      call.setString (11, "cp");   
      call.setString (12, "city");
      call.setString (13, "tel");         
      call.setString (14, "mobile");         
      call.setString (15, "mail");               
     
      call.execute ();
      sReturn  = call.getString (1) ;
     
      if( ! sReturn.equals("OK") ) System.out.println("*** LDAP update error *** " + sReturn) ;

      call.close();     
     
      return sReturn ;

  }


You can download the source code here

Francois

Partager cet article

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article

commentaires

Mohammad 03/06/2013 09:27

Dear Francois ,

Can we get Any way to delete the user completely from the LDAP using DBMS_LDAP ?

If you know a solution for that case, please help us.

Regards,
Mohammad.

Francois 03/06/2013 10:07

Why don't you read the documentation?
http://docs.oracle.com/cd/B10501_01/network.920/a96577/dbmsldap.htm

Mohammad 22/05/2013 13:56

Hello ,

Sorry for that, Source files doesn't exist in the link , can you re-upload it ?

Big thanks.

Francois 22/05/2013 23:53

Refresh that page, then download the zip from the new source.

Arnaud 01/12/2010 16:49



Hi François


A very interesting code, by the way.


However, I wrote my own 'existe_user' function using ldap_retrieve instead of authenticate_user, it allows the OID Admin guy to check the existence of a user without knowing its password.


Regards


Arnaud



Francois Degrelle 01/12/2010 19:01



Ok. And so ?...



majo 21/06/2010 14:30



When I call this function:  retval := DBMS_LDAP_UTL.authenticate_user(my_session,
                                          
user_handle,
                                          
DBMS_LDAP_UTL.AUTH_SIMPLE,
                                          
user_password,
                                          
NULL);


I got error number 1? Do you know what does it mean?


Thank you again!



majo 21/06/2010 09:09



Okay, thank you very much!


I am trying now to create package in database...because after I granted execute on, I had some problems with dbms_ldap package in forms.


Please can you explain me what is "Subscriber Handle", why I need that part, and also, "User Handle"? I don't understand purpose of that :(


thanks again!