[otrs] Customer Authentication against LDAP Customer Data from local DB

2 May 2012

      Geben Sie Text oder eine Website-Adresse ein oder lassen Sie ein Dokument
übersetzen http://translate.google.de/?tr=f&hl=de.
Abbrechen http://translate.google.de/?tr=t&hl=de
Hi,

i added new columns to teh customer_user table using this instructions
http://doc.otrs.org/3.1/en/html/customer-user-backend.html:

Added fields:

*company
department
manager
office*

these fields are filled bysync_ldap2db.

This is my Config.pm:

*
#--------------------------------------------------------------------------------------------
#
firma                                        #
#--------------------------------------------------------------------------------------------
#--------------------------------------------------------------------------------------------
#                                   Agenten
Authentifizeirung                               #
#--------------------------------------------------------------------------------------------
$Self->{'AuthModule'} = 'Kernel::System::Auth::LDAP';
$Self->{'AuthModule::LDAP::Host'} = 'dc.firma.local';
$Self->{'AuthModule::LDAP::BaseDN'} = 'dc=firma,dc=local';
$Self->{'AuthModule::LDAP::UID'} = 'sAMAccountName';
$Self->{'AuthModule::LDAP::SearchUserDN'} = 'otrsldap@firma.local';
$Self->{'AuthModule::LDAP::SearchUserPw'} = 'Passwort123';
#--------------------------------------------------------------------------------------------
#                                  Kunden
Authentifizeirung                                 #
#--------------------------------------------------------------------------------------------
  $Self->{'Customer::AuthModule'} = 'Kernel::System::CustomerAuth::LDAP';
  $Self->{'Customer::AuthModule::LDAP::Host1'} = 'dc.firma.local';
  $Self->{'Customer::AuthModule::LDAP::BaseDN1'} = 'DC=firma, DC=local';
  $Self->{'Customer::AuthModule::LDAP::UID1'} = 'sAMAccountName';
  $Self->{'Customer::AuthModule::LDAP::SearchUserDN1'} =
'otrsldap@firma.local';
  $Self->{'Customer::AuthModule::LDAP::SearchUserPw1'} = 'Passwort123';
#--------------------------------------------------------------------------------------------

#
Kundendaten                                           #
#--------------------------------------------------------------------------------------------

 #--------------------------------------------------------------------------------------------
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser1} = {
    Name => 'Database Datasource',
    Module => 'Kernel::System::CustomerUser::DB',
    Params => {
        # if you want to use an external database, add the required settings
#            DSN => 'DBI:odbc:yourdsn',
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            Table => 'customer_user',company
            # if your frontend is unicode and the charset of your
            # customer database server is iso-8859-1, use these options.
#           SourceCharset => 'iso-8859-1',
#           DestCharset => 'utf-8',

            # CaseSensitive will control if the SQL statements need LOWER()
            #   function calls to work case insensitively. Setting this to
            #   1 will improve performance dramatically on large databases.
            CaseSensitive => 0,
        },
# customer unique id
CustomerKey => 'login',

# customer #
CustomerID => 'customer_id',
CustomerValid => 'valid_id',
    CustomerUserListFields => ['first_name', 'last_name', 'email'],
    CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
    CustomerUserSearchPrefix => '',
    CustomerUserSearchSuffix => '*',
    CustomerUserSearchListLimit => 500,
    CustomerUserPostMasterSearchFields => ['email'],
    CustomerUserNameFields => ['title','first_name','last_name'],
    CustomerUserEmailUniqCheck => 1,
#    # show not own tickets in customer panel, CompanyTickets
#    CustomerUserExcludePrimaryCustomerID => 0,
#    # generate auto logins
#    AutoLoginCreation => 0,
#    AutoLoginCreationPrefix => 'auto',
#    # admin can change customer preferences
#    AdminSetPreferences => 1,
#    # cache time to live in sec. - cache any database queries
#    CacheTTL => 0,
#    # just a read only source
#    ReadOnly => 1,
    Map => [
        # note: Login, Email and CustomerID needed!
        # var, frontend, storage, shown (1=always,2=lite), required,
storage-type, http-link, readonly, http-link-target
        [ 'UserTitle',      'Title',      'title',      1, 0, 'var', '', 0
],
        [ 'UserFirstname',  'Firstname',  'first_name', 1, 1, 'var', '', 0
],
        [ 'UserLastname',   'Lastname',   'last_name',  1, 1, 'var', '', 0
],
        [ 'UserLogin',      'Username',   'login',      1, 1, 'var', '', 0
],
        [ 'UserPassword',   'Password',   'pw',         0, 0, 'var', '', 0
],
        [ 'UserEmail',      'Email',      'email',      1, 1, 'var', '', 0
],

    [ 'Firma',            'firma',          'company',      1, 0, 'var',
'', 0],
    [ 'Abteilung',           'abteilung',      'department',      1, 0,
'var', '', 0 ],
    [ 'Vorgesetzter',     'vorgesetzter','manager',      1, 0, 'var', '', 0
],
    [ 'Buero',           'buero', 'physicalDeliveryOfficeName', 1, 0,
'var', '', 0 ],

#        [ 'UserEmail',      'Email', 'email',           1, 1, 'var',
'$Env{"CGIHandle"}?Action=AgentTicketCompose&ResponseID=1&TicketID=$Data{"TicketID"}&ArticleID=$Data{"ArticleID"}',
0 ],
        [ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0
],

#        [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var',
'', 0 ],
        [ 'UserPhone',        'Phone',       'phone',        1, 0, 'var',
'', 0 ],
        [ 'UserFax',          'Fax',         'fax',          1, 0, 'var',
'', 0 ],
        [ 'UserMobile',       'Mobile',      'mobile',       1, 0, 'var',
'', 0 ],
        [ 'UserStreet',       'Street',      'street',       1, 0, 'var',
'', 0 ],
        [ 'UserZip',          'Zip',         'zip',          1, 0, 'var',
'', 0 ],
        [ 'UserCity',         'City',        'city',         1, 0, 'var',
'', 0 ],
        [ 'UserCountry',      'Country',     'country',      1, 0, 'var',
'', 0 ],
        [ 'UserComment',      'Comment',     'comments',     1, 0, 'var',
'', 0 ],
        [ 'ValidID',          'Valid',       'valid_id',     0, 1, 'int',
'', 0 ],

    ],

};*

With this configuration, the customer are shown in the custom
overviewwithout the additional fields.

I thougt the error is here:

*$Self->{CustomerUser1} = {
(**$Self->{CustomerUserDB}, $Self->{CustomerUserWhatever} works too)*

so i tried

*$Self->{CustomerUser} = {*
like in the manal:-)

now the additional fields are displayed but no customers in the customer
overview.
In the customer_user table the customers are availiable.

I have no more ideas.

Does anybody know where the error is?

Here is the sync_ldap2db script:

*#!/usr/bin/perl -w
# --
# scripts/tools/sync-ldap2db.pl - sync a ldap directory to database
# Copyright (C) 2001-2009 OTRS AG, http://otrs.org/
# --
# $Id: sync-ldap2db.pl,v 1.10 2009/02/16 12:40:23 tr Exp $
# --
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU AFFERO General Public License as published
by
# the Free Software Foundation; either version 3 of the License, or
# any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# or see http://www.gnu.org/licenses/agpl.txt.
# --

# use ../ as lib location
use File::Basename;
use FindBin qw($RealBin);
use lib dirname($RealBin) . "/../";
use lib dirname($RealBin) . "/../Kernel/cpan-lib";

use strict;
use warnings;

use vars qw($VERSION);
$VERSION = qw($Revision: 1.10 $) [1];

use Net::LDAP;
use Kernel::Config;
use Kernel::System::Log;
use Kernel::System::Main;
use Kernel::System::DB;
use Kernel::System::Encode;

# create common objects
my %CommonObject = ();
$CommonObject{ConfigObject} = Kernel::Config->new();
$CommonObject{LogObject}    = Kernel::System::Log->new(
    LogPrefix => 'OTRS-sync-ldap2db',
    %CommonObject,
);
$CommonObject{MainObject}   = Kernel::System::Main->new(%CommonObject);
$CommonObject{EncodeObject} = Kernel::System::Encode->new(%CommonObject);
$CommonObject{DBObject}     = Kernel::System::DB->new(%CommonObject);

my $UidLDAP = 'sAMAccountname';
my $UidDB   = 'login';

my %Map = (

    # db => ldap
    email       => 'mail',
    customer_id => 'mail',
    first_name  => 'sn',
    last_name   => 'givenname',
    pw          => 'test',
    firma       => 'company',
    abteilung   => 'department',
    buero       => 'physicalDeliveryOfficeName',
    phone    => 'telephoneNumber',
    manager     => 'manager',

    #    comments => 'description',
    comments => 'postaladdress',
);

my $LDAPHost    = 'dc.firma.local';
my %LDAPParams  = ();
my $LDAPBaseDN  = 'dc=firma,dc=local';
my $LDAPBindDN  = 'otrsldap@compass.local';
my $LDAPBindPW  = 'Passwort123';
my $LDAPScope   = 'sub';
my $LDAPCharset = 'utf-8';

#my $LDAPFilter = '';
my $LDAPFilter = '(&(objectclass=user)(mail=*.*@firma.de))';

my $DBCharset = 'UTF-8';
my $DBTable   = 'customer_user';

# ldap connect and bind (maybe with SearchUserDN and SearchUserPw)
my $LDAP = Net::LDAP->new( $LDAPHost, %LDAPParams ) or die "$@";
if ( !$LDAP->bind( dn => $LDAPBindDN, password => $LDAPBindPW ) ) {
    $CommonObject{LogObject}->Log(
        Priority => 'error',
        Message  => "Bind failed!",
    );
    exit 1;
}

# split request of all accounts
for (qw(0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n o p q r s t u v w x
y z)) {
    my $Filter = "($UidLDAP=$_*)";
    if ($LDAPFilter) {
        $Filter = "(&$LDAPFilter$Filter)";
    }

    # perform user search
    my $Result = $LDAP->search(
        base   => $LDAPBaseDN,
        scope  => $LDAPScope,
        filter => $Filter,
    );

    #print "F: ($UidLDAP=$_*)\n";
    for my $entry ( $Result->all_entries ) {
        my $UID = $entry->get_value($UidLDAP);
        if ($UID) {

            # check if uid existsis in db
            my $Insert = 1;
            $CommonObject{DBObject}->Prepare(
                SQL => "SELECT $UidDB FROM $DBTable WHERE $UidDB = '"
                    . $CommonObject{DBObject}->Quote($UID) . "'",
                Limit => 1,
            );
            while ( my @Row = $CommonObject{DBObject}->FetchrowArray() ) {
                $Insert = 0;
            }
            my $SQLPre  = '';
            my $SQLPost = '';
            my $Type    = '';
            if ($Insert) {
                $Type = 'INSERT';
            }
            else {
                $Type = 'UPDATE';
            }
            for ( keys %Map ) {
                my $Value = $CommonObject{DBObject}->Quote(
                    _ConvertTo( $entry->get_value( $Map{$_} ) ) || ''
                );
                if ( $Type eq 'UPDATE' ) {
                    if ($SQLPre) {
                        $SQLPre .= ", ";
                    }
                    $SQLPre .= " $_ = '$Value'";
                }
                else {
                    if ($SQLPre) {
                        $SQLPre .= ", ";
                    }
                    $SQLPre .= "$_";
                    if ($SQLPost) {
                        $SQLPost .= ", ";
                    }
                    $SQLPost .= "'$Value'";
                }
            }
            my $SQL = '';
            if ( $Type eq 'UPDATE' ) {
                print "UPDATE: $UID\n";
                $SQL
                    = "UPDATE $DBTable SET $SQLPre, valid_id = 1,
change_time = current_timestamp, change_by = 1 ";
                $SQL .= " WHERE $UidDB = '" .
$CommonObject{DBObject}->Quote($UID) . "'";
            }
            else {
                print "INSERT: $UID\n";
                $SQL
                    = "INSERT INTO $DBTable ($SQLPre, $UidDB, valid_id,
create_time, create_by, change_time, change_by) VALUES ($SQLPost, '"
                    . $CommonObject{DBObject}->Quote($UID)
                    . "', 1, current_timestamp, 1, current_timestamp, 1)";
            }
            $CommonObject{DBObject}->Do( SQL => $SQL );
        }
    }
}

sub _ConvertTo {
    my $Text = shift;

    return if !defined $Text;

    return $CommonObject{EncodeObject}->Convert(
        Text => $Text,
        To   => $DBCharset,
        From => $LDAPCharset,
    );
}*

Boris

[otrs] Customer Authentication against LDAP Customer Data from local DB

Boris Wagener