Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Проблема с UTL_SMTP  [new]
aidynchik
Member

Откуда:
Сообщений: 441
Доброго дня всем!
Помогите плиз разобраться, не знаю что упустил. Встала задача отправлять почту с корпоративного сервера, решил потренироваться на Яндексе.
Зашел Мозиллой на mail.yandex.ru, скачал сертификаты, создал Oracle wallet и подгрузил туда сертификаты.
Затем прошелся командами
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'ninjasmtp.xml',
    description  => 'Permissions to access SMTP Server',
    principal    => 'SYS',
    is_grant     => TRUE,
    privilege    => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'ninjasmtp.xml',
    principal    => 'SYS',
    is_grant     => TRUE, 
    privilege    => 'resolve',
    position     => null);

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'ninjasmtp.xml',
    host         => 'smtp.yandex.ru',
    lower_port    => 1,
    upper_port    => 1024);
   COMMIT;
END;


на команде
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'ninjasmtp.xml',
    principal    => 'SYS',
    is_grant     => TRUE, 
    privilege    => 'use-client-certificates');
  COMMIT;
END;

дает ошибку... Нашел в интернете решение, апдейтом напрямую. Затем указал где лежит валлет
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
    acl         => 'ninjasmtp.xml',
    wallet_path => 'file:C:\1\ynd_wallet');


Вроде все сделал... Но когда в процедуре доходит до места
mail_conn := utl_smtp.open_connection(host => 'smtp.yandex.ru',
                                port => 465,
                                wallet_path => 'file:C:\1\ynd_wallet',
                                wallet_password => 'VCON_2016',
                                secure_connection_before_smtp => FALSE);


зависает намертво... То ли лыжи не едут, то ли ...?
Oracle 11.2.0.2.0
16 мар 17, 14:04    [20302084]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
Vadim Lejnin
Member

Откуда:
Сообщений: 5648
aidynchik
Доброго дня всем!
Помогите плиз разобраться, не знаю что упустил. Встала задача отправлять почту с корпоративного сервера, решил потренироваться на Яндексе.
Зашел Мозиллой на mail.yandex.ru, скачал сертификаты, создал Oracle wallet и подгрузил туда сертификаты.
Затем прошелся командами
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'ninjasmtp.xml',
    description  => 'Permissions to access SMTP Server',
    principal    => 'SYS',
    is_grant     => TRUE,
    privilege    => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'ninjasmtp.xml',
    principal    => 'SYS',
    is_grant     => TRUE, 
    privilege    => 'resolve',
    position     => null);

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'ninjasmtp.xml',
    host         => 'smtp.yandex.ru',
    lower_port    => 1,
    upper_port    => 1024);
   COMMIT;
END;


на команде
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'ninjasmtp.xml',
    principal    => 'SYS',
    is_grant     => TRUE, 
    privilege    => 'use-client-certificates');
  COMMIT;
END;

дает ошибку... Нашел в интернете решение, апдейтом напрямую. Затем указал где лежит валлет
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
    acl         => 'ninjasmtp.xml',
    wallet_path => 'file:C:\1\ynd_wallet');


Вроде все сделал... Но когда в процедуре доходит до места
mail_conn := utl_smtp.open_connection(host => 'smtp.yandex.ru',
                                port => 465,
                                wallet_path => 'file:C:\1\ynd_wallet',
                                wallet_password => 'VCON_2016',
                                secure_connection_before_smtp => FALSE);


зависает намертво... То ли лыжи не едут, то ли ...?
Oracle 11.2.0.2.0


С сервера проходит команда?
telnet smtp.yandex.ru 465
16 мар 17, 14:15    [20302166]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
aidynchik
Member

Откуда:
Сообщений: 441
Vadim Lejnin

С сервера проходит команда?
telnet smtp.yandex.ru 465

нет, тоже зависает, но я подумал, что это потому что соединение SSL, а сертификаты при этом не указываются
16 мар 17, 14:19    [20302192]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
aidynchik
Member

Откуда:
Сообщений: 441
Vadim Lejnin
С сервера проходит команда?
telnet smtp.yandex.ru 465


при этом telnet smtp.yandex.ru 25 и smtp.yandex.ru 587 проходят
16 мар 17, 14:23    [20302227]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
Alexey Zhidkov
Member

Откуда: Москва
Сообщений: 1245
aidynchik
Vadim Lejnin
С сервера проходит команда?
telnet smtp.yandex.ru 465



при этом telnet smtp.yandex.ru 25 и smtp.yandex.ru 587 проходят

фаервол...
16 мар 17, 15:42    [20302655]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
aidynchik
Member

Откуда:
Сообщений: 441
Alexey Zhidkov
aidynchik
пропущено...


при этом telnet smtp.yandex.ru 25 и smtp.yandex.ru 587 проходят

фаервол...


выключил - то же самое... еще идеи?
17 мар 17, 09:47    [20304728]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5422
aidynchik,

tcpdump/wireshark
17 мар 17, 09:50    [20304747]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
aidynchik
Member

Откуда:
Сообщений: 441
вообще команда telnet smtp.yandex.ru 465 не подходит для тестирования порта 465...
Для этого нужно писать команду
"openssl s_client -crlf -connect smtp.yandex.ru:465"
и она отрабатывает на ура... коннект проходит, и я могу даже залогиниться.
17 мар 17, 09:53    [20304756]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
RyanKG
Member

Откуда:
Сообщений: 9
aidynchik, День добрый)

Столкнулся с такой же задачей и точно такой же проблемой.
Удалось найти решение!
29 сен 17, 17:13    [20832344]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
RyanKG
Member

Откуда:
Сообщений: 9
последнее был вопрос)
29 сен 17, 17:23    [20832356]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
aidynchik
Member

Откуда:
Сообщений: 441
RyanKG,

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

procedure SEND_MAIL(P_M_ABONENT_ADDR_        in GL_EMAIL.EMAIL%TYPE,
                      P_MAIL_TEXT_             in CLOB,
                    	Err_Code          OUT ADM.TYPES.TErr_Code,
                    	Err_Msg           OUT ADM.TYPES.TErr_Msg)
  IS
    -- переменная, представляющая smtp-соединение
    ProcName       constant ADM.Types.TProc_Name := 'MAIN.PKG_MAIL.SEND_MAIL';
    pnt_           varchar2(5);
    SENDER_        VARCHAR2(64)    := 'test@test.kz';
    SENDER_DESCRIPTION_            VARCHAR2(64):= 'АИП ''TEST'' <test@test.kz>';
    MAIL_CONN                      UTL_SMTP.connection;
    SERVER_ADDR_                   VARCHAR2(64) := '10.10.10.40';  -- здесь ip почтового сервера
    SERVER_PORT_                   NUMBER := 25;
    b blob;
    buffer          raw(32767);
    amount          integer := 16383;
  BEGIN
    -- установка соединения
    pnt_ := '01';
    mail_conn := UTL_SMTP.open_connection(server_addr_, server_port_);

    -- подтверждение установки связи
    UTL_SMTP.helo(mail_conn, server_addr_);

    -- установка адреса отправителя
    pnt_ := '02';
    UTL_SMTP.mail(mail_conn, SENDER_);
    -- установка адреса получателя
    UTL_SMTP.rcpt(mail_conn, P_M_ABONENT_ADDR_);
    -- отправка команды data, после которой можно начать передачу письма
    UTL_SMTP.open_data(mail_conn);

    -- отправка заголовков письма: дата, "от", "кому", "тема"
    pnt_ := '03';
    UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss', 'NLS_DATE_LANGUAGE = RUSSIAN') || UTL_TCP.crlf);
    utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw('From: ' || convert(SENDER_DESCRIPTION_, 'AL32UTF8') || utl_tcp.crlf));
    UTL_SMTP.write_data(mail_conn, 'To: '|| P_M_ABONENT_ADDR_ || UTL_TCP.crlf);
    UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw('Subject: '|| convert(MH_NOTIFY, 'AL32UTF8') || UTL_TCP.crlf));

    -- Кодировка
    pnt_ := '04';
    UTL_SMTP.write_data(mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
    UTL_SMTP.write_data(mail_conn, 'Content-Type: text/plain; charset="UTF-8"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(mail_conn, 'Content-Transfer-Encoding: quoted-printable' || UTL_TCP.crlf);

    -- текст письма
    pnt_ := '04a';
    DBMS_LOB.CREATETEMPORARY(b, TRUE);
    b := RK1048_TO_UNICODE_UTF8(P_MAIL_TEXT_);
    dbms_lob.read(b, amount, 1, buffer);

    pnt_ := '04b';
    UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw(UTL_TCP.CRLF));
    UTL_SMTP.write_raw_data(mail_conn, buffer);

    -- передача сигнала о завершении передачи сообщения
    pnt_ := '06';
    UTL_SMTP.close_data(mail_conn);
    -- завершение сессии и закрытие соединения с сервером
    UTL_SMTP.quit(mail_conn);
  EXCEPTION
    -- если произошла ошибка передачи данных, закрыть соединение и вернуть
    -- ошибку передачи письма
    WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
      BEGIN
        UTL_SMTP.quit(mail_conn);
      EXCEPTION
        -- Если SMTP сервер недоступен, соединение с сервером отсутствует.
        -- Вызов QUIT приводит к ошибке. Обработка исключения позволяет
        -- игнорировать эту ошибку.
        WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
          NULL;
      END;
      ROLLBACK;
      Err_Code := SQLCODE;
      Err_Msg  := ProcName || ' pnt_ ' || pnt_ ||
        ADM.ERROR_PACK.Get_Err_Msg('0000', Err_Code, SQLERRM) ||
          'Ошибка при отправке почты';

    WHEN OTHERS THEN
      ROLLBACK;
      Err_Code := SQLCODE;
      Err_Msg  := ProcName || ' pnt_ ' || pnt_ ||
        ADM.ERROR_PACK.Get_Err_Msg('0000', Err_Code, SQLERRM) ||
          'Ошибка при отправке почты';
  END;
6 окт 17, 13:27    [20848086]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
RyanKG
Member

Откуда:
Сообщений: 9
Спасибо за ответ!
Судя по коду, вы все-таки ходите не через ssl.
Нам нужно именно через ssl с использованием сертификатов туда достучаться.
Пока не выходит. Если получится, отпишусь.
20 окт 17, 16:54    [20886672]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5422
RyanKG,

telnet проходит на 465-й порт?
20 окт 17, 17:02    [20886707]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с UTL_SMTP  [new]
RyanKG
Member

Откуда:
Сообщений: 9
Удалось пробиться,
Основная проблема была в правильной настройке сертификатов.
Вот комментарий нашего админа:

Вот что было пошагово сделано:

1. Создал wallet при помощи orapki;
2. При помощи команды openssl s_client -connect smtp.yandex.ru:465 –showcerts
Посмотрел используемые сертификаты доверия:

depth=3 C = PL, O = Unizeto Sp. z o.o., CN = Certum CA verify return:1
depth=2 C = PL, O = Unizeto Technologies S.A., OU = Certum Certification Authority, CN = Certum Trusted Network CA verify return:1
depth=1 C = RU, O = Yandex LLC, OU = Yandex Certification Authority, CN = Yandex CA
verify return:1
depth=0 C = RU, O = Yandex LLC, OU = ITO, L = Moscow, ST = Russian Federation, CN = smtp.yandex.ru
verify return:1

В wallet нужно добавлять только все сертификаты кроме 0. Он то нам и мешал, я потом в документации нашёл рекомендации по этому поводу.
3. Далее даём грант GRANT EXECUTE on UTL_SMTP to <<<>>>
4. Создаём необходимые ACL.
5. Ну ещё в БД поменял параметр сервера исходящей почты.

Далее в скрипте по отправке обязательно указать параметр secure_connection_before_smtp => true.
Вот пример скрипта:

DECLARE
  c utl_smtp.connection;
  l_mailhost    VARCHAR2 (64) := 'smtp.yandex.ru';
  l_from        VARCHAR2 (64) := 'user@yandex.ru';
  l_to          VARCHAR2 (64) := 'receiver@mail.ru';
  l_subject     VARCHAR2 (64) := 'utl_smtp test';
  crlf varchar2(2) := UTL_TCP.CRLF;
BEGIN
  c := utl_smtp.open_connection(
            host => l_mailhost,
            port => 465,
            wallet_path => 'file:/wallet_path/',
            wallet_password => 'wallet_password',
            secure_connection_before_smtp => true,
            tx_timeout => 20);

  utl_smtp.ehlo(c, 'oracle');

  utl_smtp.command( c, 'AUTH LOGIN');
  utl_smtp.command( c, 'user login in base64');
  utl_smtp.command( c, 'user password in base64');  
 
  utl_smtp.mail (c, l_from);
  utl_smtp.rcpt (c, l_to);
  utl_smtp.open_data (c);
  utl_smtp.write_data (c, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf);
  utl_smtp.write_data (c, 'From: ' || l_from || crlf);
  utl_smtp.write_data (c, 'Subject: ' || l_subject || crlf);
  utl_smtp.write_data (c, 'To: ' || l_to || crlf);
  utl_smtp.write_data (c, 'message test' || crlf);
  utl_smtp.close_data (c);
  utl_smtp.quit (c);
END;


Почта ходит без проблем, надеюсь кому-то будет полезно)
сегодня, 13:17    [20892121]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить