воскресенье, 17 ноября 2013 г.

Как работать с LDAP в PL/SQL, часть I

Недавно я написал о том, что такое LDAP и с чем его едят. Сегодня, в продолжение этой темы, расскажу, как работать с LDAP сервером из программы на языке PL/SQL. Для создания примеров я использую Oracle 11gR2 и MS Active Directory. В примерах все имена организаций, подразделений и пользователей являются вымышленными, а всякое совпадение с реальностью совершенно случайно :)

Для работы с LDAP на PL/SQL Oracle предоставляет пакет DBMS_LDAP (альтернативой может быть написание PL/SQL-оберток для JNDI). Пакет позволяет работать с различными LDAP-серверами, включая Oracle Internet Directory и Microsoft Active Directory.

В СУБД Oracle 11g требуется явно разрешить пользователю (или роли) обращение к внешним сетевым ресурсам, и Active Directory сервер не исключение. Для этого, как пользователь SYS, нужно создать access control list (ACL) для выбранного пользователя Oracle и предоставить ему доступ к Active Directory:

-- создать ACL для пользователя Oracle
BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'ldap.xml',
  description  => 'ACL to grant access to Active Directory',
  principal    => 'AY',          -- Oracle user's name
  is_grant     => TRUE, 
  privilege    => 'connect'); 
END;
/

-- дать доступ к Active Directory
BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'ldap.xml',
  host        => '192.168.0.16', -- LDAP server IP-address
  lower_port  => 389,            -- LDAP port
  upper_port  => 389); 
END;
/

-- проверить, что получилось
select * from DBA_NETWORK_ACL_PRIVILEGES;

Теперь, под пользователем, которому предоставлен доступ, создам соединение с Active Directory:

DECLARE
    LDAP_HOST CONSTANT VARCHAR2(20) := '192.168.0.16';
    LDAP_PORT CONSTANT VARCHAR2(20) := dbms_ldap.PORT;
    LDAP_USER CONSTANT VARCHAR2(20) := 'trofimov_a@SKY';
    LDAP_PSWD CONSTANT VARCHAR2(20) := 'nooneknows';
    l_dummy PLS_INTEGER;
    l_session dbms_ldap.SESSION;
BEGIN
    -- Инициализировать LDAP сеанс
    l_session := dbms_ldap.init(LDAP_HOST, LDAP_PORT);
    -- Аутентифицировать пользователя
    l_dummy := dbms_ldap.simple_bind_s(l_session, LDAP_USER, LDAP_PSWD);
END;
/

Функция dbms_ldap.init получает в качестве параметров IP-адрес и порт Active Directory сервера и возвращает созданный указатель (handle) на LDAP сеанс. Этот указатель будет использоваться во всех дальнейших операциях c LDAP сервером. С помощью функции dbms_ldap.simple_bind_s присоединяемся к серверу, передавая имя и пароль пользователя для аутентификации. (Символы _s в конце имени функции означают, что функция выполняется синхронно.) Функция возвращает dbms_ldap.SUCCESS в случае успеха, либо выбрасывает исключение.

Создам хранимую функцию на базе вышеприведенного кода, чтобы пользоваться ей в дальнейшем:

CREATE OR REPLACE FUNCTION ldap_open
RETURN dbms_ldap.SESSION
IS
    LDAP_HOST CONSTANT VARCHAR2(20) := '192.168.0.16';
    LDAP_PORT CONSTANT VARCHAR2(20) := dbms_ldap.PORT;
    LDAP_USER CONSTANT VARCHAR2(20) := 'trofimov_a@SKY';
    LDAP_PSWD CONSTANT VARCHAR2(20) := 'nooneknows';
    l_dummy PLS_INTEGER;
    l_session dbms_ldap.SESSION;
BEGIN
    l_session := dbms_ldap.init(LDAP_HOST, LDAP_PORT);
    l_dummy := dbms_ldap.simple_bind_s(l_session, LDAP_USER, LDAP_PSWD);
    RETURN l_session;
END ldap_open;
/

Аутентификация пользователей - одна из базовых функций LDAP сервера. Другие функции: поиск, чтение и модификация данных в каталоге.

С помощью функции dbms_ldap.search_s найдем все подразделения (objectClass=organizationalUnits) верхнего уровня (dbms_ldap.SCOPE_ONELEVEL) для организации "Синее Небо" (O=Синее Небо,DC=org,DC=ru) и вернем атрибут name:

DECLARE
    l_dummy PLS_INTEGER;
    l_session dbms_ldap.SESSION;
    l_attrlist dbms_ldap.STRING_COLLECTION;
    l_results dbms_ldap.MESSAGE;
BEGIN
    l_session := ldap_open;

    -- Выполнить поиск
    l_attrlist(1) := 'name';
    l_dummy := dbms_ldap.search_s(
        l_session,
        'O=Синее Небо,DC=org,DC=ru',         -- base DN
        dbms_ldap.SCOPE_ONELEVEL,            -- scope
        '(objectClass=organizationalUnit)',  -- filter
        l_attrlist,                          -- attributes
        0,                                   -- retrieve attrs and values
        l_results                            -- результат
    );
    dbms_output.put_line('Найдено ' || dbms_ldap.count_entries(l_session, l_results) || ' записей');

    l_dummy := dbms_ldap.unbind_s(l_sess);
END;
/

Найдено 4 записей

Значения, которые может принимать параметр scope (в соответствии со спецификацией LDAP):

  • dbms_ldap.SCOPE_BASE - запись base DN,
  • dbms_ldap.SCOPE_ONELEVEL - дочерние записи base DN,
  • dbms_ldap.SCOPE_SUBTREE - поддерево с вершиной base DN.

Функция search_s помещает результат поиска в переменную типа dbms_ldap.MESSAGE. Для работы с результатом поиска в пакете dbms_ldap есть ряд функций, одной из них, count_entries, я воспользовался выше, чтобы получить количество записей в результате. Вот другие функции:

  • first_entry и next_entry для обхода всех записей результата,
  • get_dn для получения уникального имени DN записи,
  • first_attribute и next_attribute для обхода всех атрибутов записи,
  • get_values для получения всех значений атрибута.

Создам процедуру ldap_print для вывода результата поиска на экран:

CREATE OR REPLACE PROCEDURE ldap_print(
    p_session dbms_ldap.SESSION,
    p_results dbms_ldap.MESSAGE)
IS
    l_entry dbms_ldap.MESSAGE;
    l_attr VARCHAR2(256);
    l_values dbms_ldap.STRING_COLLECTION;
    l_berelem dbms_ldap.ber_element;
    i PLS_INTEGER;
BEGIN
    l_entry := dbms_ldap.first_entry(p_session, p_results);
    WHILE l_entry IS NOT NULL LOOP
        dbms_output.put_line('DN = ' || dbms_ldap.get_dn(p_session, l_entry));
        l_attr := dbms_ldap.first_attribute(p_session, l_entry, l_berelem);
        WHILE l_attr IS NOT NULL LOOP
            l_values := dbms_ldap.get_values(p_session, l_entry, l_attr);
            i := l_values.first;
            WHILE i IS NOT NULL LOOP
                dbms_output.put_line(LPAD(l_attr, 15) || ' = ' || l_values(i));
                i := l_values.next(i);
            END LOOP;
            l_attr := dbms_ldap.next_attribute(p_session, l_entry, l_berelem);
        END LOOP;
        l_entry := dbms_ldap.next_entry(p_session, l_entry);
    END LOOP;
END ldap_print;

И другую процедуру, для выполнения поиска и вывода результата:

CREATE OR REPLACE PROCEDURE ldap_search_and_print(
    p_session dbms_ldap.SESSION,
    p_base   IN VARCHAR2,
    p_scope  IN PLS_INTEGER,
    p_filter IN VARCHAR2,
    p_attrs  IN dbms_ldap.STRING_COLLECTION)
IS
    l_dummy PLS_INTEGER;
    l_results dbms_ldap.MESSAGE;
BEGIN
    -- Выполнить поиск
    BEGIN
        l_dummy := dbms_ldap.search_s(
            p_session,
            p_base,
            p_scope,
            p_filter,
            p_attrs,
            0,
            l_results
        );
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Не найдена запись с DN ' || p_base);
        RETURN;
    END;
    -- Вывести результат
    ldap_print(p_session, l_results);
END ldap_search_and_print;
/

Теперь с помощью созданных процедур можно относительно легко экспериментировать с поиском в каталоге LDAP. Получим все подразделения организации "Синее Небо":

DECLARE
    l_dummy PLS_INTEGER;
    l_session dbms_ldap.SESSION;
    l_attrlist dbms_ldap.STRING_COLLECTION;
BEGIN
    l_session := ldap_open;

    -- Подготовить список атрибутов
    l_attrlist(1) := 'name';
    -- Выполнить поиск и вывести результат
    ldap_search_and_print(
        p_session => l_session,
        p_base => 'O=Синее Небо,DC=org,DC=ru',
        p_scope => dbms_ldap.SCOPE_ONELEVEL,
        p_filter => '(objectClass=organizationalUnit)',
        p_attrs => l_attrlist
    );

    l_dummy := dbms_ldap.unbind_s(l_session);
END;
/

DN = OU=Коммерческий отдел,O=Синее Небо,DC=org,DC=ru
           name = Коммерческий отдел
DN = OU=Общее руководство,O=Синее Небо,DC=org,DC=ru
           name = Общее руководство
DN = OU=Отдел обеспечения,O=Синее Небо,DC=org,DC=ru
           name = Отдел обеспечения
DN = OU=Производственный отдел,O=Синее Небо,DC=org,DC=ru
           name = Производственный отдел

А теперь найдем запись о пользователе (objectClass=user), указав среди возвращаемых атрибутов objectClass, - этот атрибут имеет несколько значений:

DECLARE
    l_dummy PLS_INTEGER;
    l_session dbms_ldap.SESSION;
    l_attrlist dbms_ldap.STRING_COLLECTION;
BEGIN
    l_session := ldap_open;

    -- Подготовить список атрибутов
    l_attrlist(1) := 'sAMAccountName';
    l_attrlist(2) := 'mail';
    l_attrlist(3) := 'objectClass';
    -- Выполнить поиск и вывести результат
    ldap_search_and_print(
        p_session => l_session,
        p_base => 'O=Синее Небо,DC=org,DC=ru',
        p_scope => dbms_ldap.SCOPE_SUBTREE,
        p_filter => '(&(cn=Трофимов Андрей*)(objectClass=user))',
        p_attrs => l_attrlist
    );

    l_dummy := dbms_ldap.unbind_s(l_session);
END;
/

DN = CN=Трофимов Андрей,OU=Производственный отдел,O=Синее Небо,DC=org,DC=ru
    objectClass = top
    objectClass = person
    objectClass = organizationalPerson
    objectClass = user
 sAMAccountName = trofimov_a
           mail = trofimov_a@sineenebo.org.ru

Несколько значений атрибута objectClass отражают тот факт, что класс user является наследником классов organizationalPerson, person и top. Таким образом, запись класса user является одновременно записью каждого из классов-предков.

Атрибут sAMAccountName содержит имя пользователя, под которым пользователь регистрируется в корпоративном домене, атрибут mail - адрес электронной почты.

Следующий скрипт выводит на экран имя и адрес электронной почты всех активных пользователей организации "Синее Небо". Приблизительно то же самое делает адресная книга вашего почтового клиента, чтобы вывести доступных адресатов электронной почты.

DECLARE
    l_dummy PLS_INTEGER;
    l_session dbms_ldap.SESSION;
    l_attrlist dbms_ldap.STRING_COLLECTION;
BEGIN
    l_session := ldap_open;

    -- Подготовить список атрибутов
    l_attrlist(1) := 'cn';
    l_attrlist(2) := 'mail';
    -- Выполнить поиск и вывести результат
    ldap_search_and_print(
        p_session => l_session,
        p_base => 'O=Синее Небо,DC=org,DC=ru',
        p_scope => dbms_ldap.SCOPE_SUBTREE,
        p_filter => '(&(objectClass=user)(cn=*)(mail=*))',
        p_attrs => l_attrlist
    );

    l_dummy := dbms_ldap.unbind_s(l_session);
END;
/

Этот запрос возвращает также пользователей с отключенными учетными записями! Чтобы их исключить, нужно использовать в фильтре расширенную проверку: выражение (!(userAccountControl:1.2.840.113556.1.4.803:=2)) выбирает только активных пользователей в Active Directory. К сожалению, PL/SQL API для LDAP не поддерживает расширенные проверки с идентификаторами правил. При добавлении в фильтр указанного выражения запрос не возвращает ничего.

Три раза воспользовавшись процедурой ldap_search_and_print несложно заметить, что вызывать ее было бы проще, если бы она принимала список атрибутов как строку VARCHAR2, где имена атрибутов разделены, например, запятыми. Имеет смысл создать альтернативный вариант ldap_search_and_print со списком атрибутов VARCHAR2. Впрочем, это уже заявка на создание пакета с более удобным в использовании API, чем тот, что предлагает пакет dbms_ldap.

В следующий раз я рассмотрю модификацию данных в каталоге LDAP с помощью пакета dbms_ldap и предложу пакет ldap_helper, который будет фасадом, скрывающим сложности dbms_ldap.

12 комментариев:

  1. функция ldap_to_strcol не описана

    ОтветитьУдалить
  2. Спасибо, я убрал ldap_to_strcol из процедуры ldap_search_and_print, она там не нужна.

    ОтветитьУдалить
  3. Добрый день!
    Пытаюсь подключиться к MS AD.
    Получаю ошибку:
    ORA-31202: DBMS_LDAP: ошибка клиента/сервера LDAP: Invalid credentials. 80090308: LdapErr: DSID-0C090334, comment: AcceptSecurityContext error, data 525, vece
    ORA-06512: на "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: на "SYS.DBMS_LDAP", line 1487
    ORA-06512: на "SYS.DBMS_LDAP", line 79
    ORA-06512: на line 12

    View program sources of error stack?

    Через Active Directory Explorer нормально подключается.

    select * from v$version;
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE 11.2.0.4.0 Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    ОтветитьУдалить
  4. Добрый день!

    Могу лишь сослаться на гугл: код ошибки 525 во фрагменте "data 525, vece" означает "user not found". То есть, AD не находит пользователя по имени, указанному в параметре dn функции dbms_ldap.simple_bind_s. В моем случае работают форматы "имяпользователя@домен" и "домен\имяпользователя", как вариант, можно попробовать формат distinguished name.

    Вот здесь обсуждение, которое может оказаться полезным http://superuser.com/questions/249975/how-to-specify-ldap-user-name-for-connecting-to-active-directory

    ОтветитьУдалить
  5. Андрей, проблема была в формате:
    LDAP_USER CONSTANT VARCHAR2(256) := 'CN=xxx,OU=xxx,DC=xxx,DC=local';
    Подключаюсь к AD нормально. Атрибуты пользователей получаю корректно.
    Но есть еще некоторые моменты по которым хотел получить ответы:
    1) Как посмотреть статус акаунта пользователя? Блокирован или нет? Каким атрибутом?
    2) Как переоброзвать атрибуты с форматами даты в оракле например (9223372036854775807). Это тип INTEGER8 в AD.
    3) Как изменить пароль пользователя ?

    ОтветитьУдалить
  6. Сорбон, к сожалению, у меня нет ответов на эти вопросы.
    Мой опыт работы с атрибутом userAccountControl, это статус аккаунта, показал, что DBMS_LDAP с ним не дружит (я упоминал в посте), так что PL/SQL API не предоставляет полноценной поддержки LDAP. Если нужно полноценно работать с AD из хранимых процедур, я бы попробовал посмотреть в сторону JNDI и хранимых процедур на Java.

    ОтветитьУдалить
  7. "DBMS_LDAP с ним не дружит"
    Жаль... Спасибо, Андрей, за отзыв...

    ОтветитьУдалить
  8. Добрый день
    Спасибо, как раз то, что нужно!
    Хочу еще раз уточнить про выбор активных аккаунтов (выбор самого статуса меня не интересует), не могу дойти куда в последнем примере нужно поставить выражение (!(userAccountControl:1.2.840.113556.1.4.803:=2)) и что вообще значат эти магические цифры? Буду признателен...

    ОтветитьУдалить
  9. Добрый день, Роман.

    Если в последний пример добавить эту проверку, то получится выражение '(&(objectClass=user)(cn=*)(mail=*)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))'.

    Эти магические цифры - идентификатор правила, реализованного в Active Directory. Это расширение LDAP, описанное в документации по Active Directory. Но DBMS_LDAP не поддерживает работу с такими расширениями.

    ОтветитьУдалить
    Ответы
    1. Гм... с таким фильтром у меня вообще ни одного пользователя с домена не тянется:(

      Удалить
  10. Этот комментарий был удален автором.

    ОтветитьУдалить
  11. Можно ли работать с dbms_ldap в Forms 11.1.2.2.0?

    ОтветитьУдалить