Введение
В этой небольшой статье я хочу рассказать о том, как я настраивал отправку почты из Oracle и чем это закончилось. Хочу сразу уточнить две вещи: во-первых, делал я это не для рассылки спама, во-вторых, ранее я не имел дела с настройкой почтовых серверов, поскольку это лежит вне области моей профессиональной деятельности.
Настройка Oracle
Решая одну из своих задач, я столкнулся с необходимостью рассылать пользователям данные для аутентификации, т.е. логины и пароли, которые лежат в таблице. Скажем откровенно, в данный момент пользователей не сотни и не тысячи, так что можно было бы каждому отослать письмо руками, но это пару часов ручного труда – интересней ведь потратить в два-три раза больше времени, но все автоматизировать, не так ли?
На прошлом месте работы мне уже приходилось слать джобом ежемесячные отчеты на почту пользователям, так что задача не выглядела для меня чем-то экзотичным, более того, я рассчитывал, что справлюсь за соизмеримое с ручным трудом время. С другой стороны там была вся необходимая инфраструктура под рукой + специалисты нужных тематик, с которыми можно было поговорить. Здесь же не было ничего — нужно разбираться самому.
Oracle имеет множество методов отсылки почты из БД, вот некоторые из пакетов, имеющие нужную функциональность:
- UTL_MAIL
- UTL_SMTP
- UTL_TCP
- UTL_HTTP
1. спецификация пакета находится в файле @$ORACLE_HOME\rdbms\admin\utlmail.sql
2. тело пакета зашифровано и лежит в файле @$ORACLE_HOME\rdbms\admin\prvtmail.sql
Накатывать это нужно из-под пользователя SYS, нормально установить пакет из Command Window PL/SQL Developer’a мне не удалось, т.е. он установился, но выдавал ошибки компиляции. Из sqlplus все установилось отлично.
После наката нужно выдать гранты на использование пакета. Некоторые авторы предлагают давать гранты на Public, но это не есть хорошая идея по причинам безопасности. После выдачи грантов нужному пользователю наша работа под пользователем SYS закончена (однако он нам еще понадобиться ситуации, когда нужно делать alter system).
Почтовый сервер
Как оказалось, для отправки почты все же нужна инфраструктура, т.е. кроме Oracle нужен почтовый сервер, которого у меня не было, а уж устанавливать и настраивать я его вообще не представлял как, да и нужно ли?
После недолгих поисков я нашел следующую статью http://jiri.wordpress.com/2010/03/24/send-emails-using-utl_mail-and-google-gmail-smtp-server/, описывающую как слать почту через GMAIL. Автор статьи попал в аналогичную моей ситуацию и предлагал простое решение, которым я решил воспользоваться. Завел новый ящик на GMAIL, который будет использоваться для автоматической отсылки писем, скачал и установил E-MailRelay. Сразу у меня все не заработало, пришлось немного почитать документацию и поискать еще статей, описывающих взаимодействие с данным сервером. В итоге все нужное параметры были внесены в bat-файл, данные почтового ящика в файл emailrelay.auth и сервер успешно запущен.
Отправка писем
После установки почтового сервера (или SMTP-прокси, как в нашем случае) нужно указать Oracle его адрес:
alter system set smtp_out_server = 'ip-address:port' scope=Both; Попытаемся теперь отправить письмо с помощью utl_mail.send, для этого пришлось еще немного поиграться с настройками, например, оказалось, что в поле sender обязательно нужен адрес в следующем формате:
"<any_mail@any_mail_server.com>", там же можно указать имя отправителя:
" Sender Name <my_mail@any_mail_server.com>", но письмо придет все равно с вашего реального gmail-ящика, и выглядеть это будет так:
" Sender Name <my_mail@any_mail_server.com>" <real_mailbox@gmail.com>
Немного экспериментов с mime_type и отсылкой на разные почтовые серверы показало, что для русского текста желательно использовать 'text/html; charset=«UTF-8»'.
После контрольной отсылки писем себе была написана процедура, которая выбирала нужные данные и слала каждому пользователю его логин/пароль. Запустив эту процедуру для отсылки данных первым двадцати пользователям, я ушел обедать.
Минут через 40 оказалось, что ушло только первых 4 письма, остальные где-то застряли. Готовясь к дебагу, я отправил той же процедурой 10 писем себе на ящик. Новый пакет «протолкнул» потерянные письма и все они успешно ушли. В общем SMTP-прокси работал, хотя и с некоторым непостоянством.
Продолжение истории
Через день, занимаясь своими делами на сервере, я увидел, что SMTP-прокси отослал около 80 писем, хотя я после первой рассылки больше через него ничего не отправлял. Решил проверить ящик на gmail’e. Во входящих красовалось три пачки писем:
Все три цепочки содержали ответы почтовых серверов об отправке сообщения на несуществующие ящики. В отправленных сообщениях наблюдалась следующая картина:
Получалось, что с моего ящика ушло по два письма на ящики vbibiorm@gmail.com и w852@ymail.com, причем каждое письмо в теме содержало IP-адрес машины, на которой стоял мой SMTP-прокси, тело писем было пустым, но письма имели по одному аттачу с именем noname и размером ноль байт.
Очевидно, что SMTP-прокси имел уязвимость, и мне неизвестно, проявлялась ли она только при отправке писем с аттачем, или аттач должен был содержать какую-то дополнительную информацию относительно уязвимостей/характеристик машины. Поскольку время отправки сообщений на оба ящика совпадает, я делаю вывод, что они принадлежат одному и тому же человеку. В результате найденной уязвимости через SMTP-прокси были посланы письма такого вида:
Гугл-транслейт определяет язык писем как китайский. Поиск в гугле информации по данным е-мейлам показал, что пользователи часто жалуются на непонятную активность почтовых серверов, связанную с отсылкой писем на эти ящики. В общем, так у меня в словаре появилось словосочетание «open relay». SMTP-прокси я выключил, но остался вопрос, что с этим делать дальше? Я видел три варианта:
- Поскольку почту отправлять планировалось не часто и целыми пакетами, то можно оставить все как есть, т.е. включать почтовый прокси только на время отправки писем – наиболее дешевое по времени решение.
- Можно досконально разобраться с темой почтовых серверов и либо корректно настроить данный, либо сменить его на более надежный – довольно дорогой по времени вариант, к тому же в дальнейшем мне эти знания будут абсолютно бесполезны.
- Найти воркэраунд, с использованием имеющихся знаний – затраты по времени не известны.
- Найти разбирающегося в почтовых серверах знакомого.
Варианты
Самым простым и идеальным вариантом является отправка почты сразу через Gmail, без дополнительных серверов. Попробуем:
alter system set smtp_out_server = 'smtp.gmail.com:587' scope=Both; - Gmail и Yahoo требуют использования STARTTLS(другое название SSL).
- UTL_MAIL не поддерживает STARTTLS.
- Особо упорные товарищи пробовали использовать пакет utl_smtp и команду utl_smtp.command(conn,'STARTTLS'), но потерпели фиаско. Где-то в документации я нашел, что STARTTLS поддерживается Ораклом начиная с версии 11.2.
- Общее мнение сообщества сводилось к следующему:
- Используйте не Gmail и не Yahoo и не морочьте себе голову.
- Если вы хотите использовать Gmail, поставьте себе SMTP-прокси (вариант. описанный в начале статьи).
- Если вы понимаете что делаете, можете попытаться авторизоваться на почтовом сервере через пакет utl_smtp.
Понимаю, что пора с этим заканчивать, во время очередного поиска нахожу простую мысль: а не закрыт ли у меня порт 25? Проверяю (оказывается, я теперь умею это делать) – закрыт. Делаю alter system с указанием моего альтернативного почтового сервера и порта 587. Пробую отправить письмо с помощью UTL_MAIL и получаю ошибку «ORA-29279: Постоянная ошибка SMTP: 501 sender address must contain a domain». Почти получилось, избавляюсь в имени отправителя от необходимых Gmail’y "<>", и письмо уходит.
Заключение
В этой статье я описал свой опыт работы с отправкой почты в полевых условиях и при отсутствии необходимых знаний о работе почтовых серверов. Полученный результат не является вполне удовлетворительным, поскольку я нашел такой же «дырявый» почтовый сервер, как и ранее установленный мною SMTP-прокси. Потратив еще минуту, я выяснил, что могу отправить письмо от имени любого существующего на почтовом сервере ящика, просто указав, например
v_sender varchar2(200):='Вася Пупкин <admin@mail.******>'