На написание данной статьи меня сподвигло прочтение публикации «Как я спам слал» от пользователя Nike01. Руководство поставило передо мной похожую задачу по рассылке учетных данных пользователей средствами базы данных Oracle. В качестве SMTP relay должен был выступать сервер исходящих сообщений Яндекса, использующий SSL при авторизации.
Сбор информации
Как и Nike01, я в первую очередь стал смотреть в сторону пакета UTL_MAIL. Данный пакет, по сути, представляет из себя «обертку» над UTL_SMTP и UTL_TCP и предназначен в основном для отправки простых почтовых сообщений, позволяя пользователю особо не вдаваться в тонкости протокола SMTP. К сожалению UTL_MAIL имеет ряд ограничений, в их числе — отсутствие поддержки SMTP аутентификации и следовательно для решения моей задачи он не подходит.
Далее, я обратился к документации по пакету UTL_SMTP, как выяснилось — это довольно мощный и гибкий инструмент, который позволяет задействовать практически все команды протокола SMTP, в том числе имеет поддержку SMTP авторизации, а начиная с версии БД 11.2.0.2 умеет и STARTTLS. То, что надо!
Переходим к почтовому серверу. Из справки по использованию smtp.yandex.ru узнаем, что сервис слушает 465 порт:
Ок. Пробуем установить соединение:
[oracle@ora_db ~]$openssl s_client -connect smtp.yandex.ru:465
CONNECTED(00000003)
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, emailAddress = pki@yandex-team.ru
verify return:1
---
Certificate chain
0 s:/C=RU/O=Yandex LLC/OU=ITO/L=Moscow/ST=Russian Federation/CN=smtp.yandex.ru/emailAddress=pki@yandex-team.ru
i:/C=RU/O=Yandex LLC/OU=Yandex Certification Authority/CN=Yandex CA
1 s:/C=RU/O=Yandex LLC/OU=Yandex Certification Authority/CN=Yandex CA
i:/C=PL/O=Unizeto Technologies S.A./OU=Certum Certification Authority/CN=Certum Trusted Network CA
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIGtzCCBZ+gAwIBAgIQeNdVGMktXbH0GDaX1lgg9TANBgkqhkiG9w0BAQsFADBf
MQswCQYDVQQGEwJSVTETMBEGA1UEChMKWWFuZGV4IExMQzEnMCUGA1UECxMeWWFu
ZGV4IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MRIwEAYDVQQDEwlZYW5kZXggQ0Ew
HhcNMTUxMDEyMTI0MTI0WhcNMTcxMDExMTI0MTI0WjCBmjELMAkGA1UEBhMCUlUx
EzARBgNVBAoMCllhbmRleCBMTEMxDDAKBgNVBAsMA0lUTzEPMA0GA1UEBwwGTW9z
Y293MRswGQYDVQQIDBJSdXNzaWFuIEZlZGVyYXRpb24xFzAVBgNVBAMMDnNtdHAu
eWFuZGV4LnJ1MSEwHwYJKoZIhvcNAQkBFhJwa2lAeWFuZGV4LXRlYW0ucnUwggEi
MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDfdYJIwjSdhSwHdNxNJ+2TH8jc
l2Skm4k6Z9UTDcxCMbe8rvkEg3bu7EUPnftNmBGxB3KXNj73irZ6PLRu3bO7psi3
hbWNRVDzQlcdQZmW/Iucd8IQlvN1gwDZ79eu1fWmRMThmNmiiKK1AjtZNTc+Dq/9
kTl4TkRzdge6KpkOn3lJFwiTozixxKKfawxQSlFpRadzLuYt2Cj88Huo3R5frwwH
8nTMDITtHZVSSYw1cYNIWj+y5G3D1TA1pKcyIPI8WHqJeHIekI3t4uK9JQNEaJI0
clabA3fn1wdJ4jCWajdLcssJ9PJ6eJB+l4HZFt3EDhe8XiQ/69gZjQVIVrcpAgMB
AAGjggMxMIIDLTAMBgNVHRMBAf8EAjAAMGkGA1UdHwRiMGAwL6AtoCuGKWh0dHA6
Ly9jcmxzLnlhbmRleC5uZXQvY2VydHVtL3ljYXNoYTIuY3JsMC2gK6AphidodHRw
Oi8veWFuZGV4LmNybC5jZXJ0dW0ucGwveWNhc2hhMi5jcmwwcQYIKwYBBQUHAQEE
ZTBjMCwGCCsGAQUFBzABhiBodHRwOi8veWFuZGV4Lm9jc3AtcmVzcG9uZGVyLmNv
bTAzBggrBgEFBQcwAoYnaHR0cDovL3JlcG9zaXRvcnkuY2VydHVtLnBsL3ljYXNo
YTIuY2VyMB8GA1UdIwQYMBaAFDdc4xngso6hqE7Sz6vQ3OMLXDVNMB0GA1UdDgQW
BBQYy748oNmcZPHfTrybOrdqeu/mTDAOBgNVHQ8BAf8EBAMCBaAwggE/BgNVHSAE
ggE2MIIBMjCCAS4GDCqEaAGG9ncCBQEKAjCCARwwJQYIKwYBBQUHAgEWGWh0dHBz
Oi8vd3d3LmNlcnR1bS5wbC9DUFMwgfIGCCsGAQUFBwICMIHlMCAWGVVuaXpldG8g
VGVjaG5vbG9naWVzIFMuQS4wAwIBAhqBwFVzYWdlIG9mIHRoaXMgY2VydGlmaWNh
dGUgaXMgc3RyaWN0bHkgc3ViamVjdGVkIHRvIHRoZSBDRVJUVU0gQ2VydGlmaWNh
dGlvbiBQcmFjdGljZSBTdGF0ZW1lbnQgKENQUykgaW5jb3Jwb3JhdGVkIGJ5IHJl
ZmVyZW5jZSBoZXJlaW4gYW5kIGluIHRoZSByZXBvc2l0b3J5IGF0IGh0dHBzOi8v
d3d3LmNlcnR1bS5wbC9yZXBvc2l0b3J5LjAdBgNVHSUEFjAUBggrBgEFBQcDAQYI
KwYBBQUHAwIwEQYJYIZIAYb4QgEBBAQDAgbAMHoGA1UdEQRzMHGCDnNtdHAueWFu
ZGV4LmJ5gg5zbXRwLnlhbmRleC5reoIPc210cC55YW5kZXguY29tgg5zbXRwLnlh
bmRleC51YYISc210cC55YW5kZXguY29tLnRyggpzbXRwLnlhLnJ1gg5zbXRwLnlh
bmRleC5ydTANBgkqhkiG9w0BAQsFAAOCAQEAoVcDTfHCdx1N6IF0rBtZTxJBu2Tf
WcSYGu/Uif6EqqVsy44kQ8Yxresaqb8bR0FkJxSP1HXDwQuXm18sdZDb4f/ealF4
Hb6ZmF5ilP0SNUccka4wI0DDTuMuBDstGbCvLJPxTjHTWP9HYrYoAJsUnux92blR
Uc11XnKQE9ltWEpKys9RQhTLDqPNrALa423zvN5jLKqMK6vVzuTIOnrOQFZMDxmQ
KVVUtvGS7ZBmL8Z8ripEDBUFywAcxHC9DJpTUwxZUs2ceJbuN6J0qKjP2F/aKJZR
GTgW095M/H1tEGjouYfSj21cy9ZDNiUqN1Yutg0+nGNexiRDghg44yWMeQ==
-----END CERTIFICATE-----
subject=/C=RU/O=Yandex LLC/OU=ITO/L=Moscow/ST=Russian Federation/CN=smtp.yandex.ru/emailAddress=pki@yandex-team.ru
issuer=/C=RU/O=Yandex LLC/OU=Yandex Certification Authority/CN=Yandex CA
---
No client certificate CA names sent
Server Temp Key: ECDH, prime256v1, 256 bits
---
SSL handshake has read 3581 bytes and written 373 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES128-GCM-SHA256
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
SSL-Session:
Protocol : TLSv1.2
Cipher : ECDHE-RSA-AES128-GCM-SHA256
Session-ID: C64B0AE4863B7F1A44ED0981E29F4D1E796F98F7FB0EF950A7A1588232F11ABC
Session-ID-ctx:
Master-Key: F704953E4A95AD4A1C06BBDF1425F4E5B1E2B8BE5EE5214484AC0DF9219E7185ABB38573904FA3CFD0B88A8FF3185061
Key-Arg : None
Krb5 Principal: None
PSK identity: None
PSK identity hint: None
TLS session ticket lifetime hint: 300 (seconds)
TLS session ticket:
0000 - b3 08 e7 79 5b 86 ba 5a-7e 71 bf f8 5f ad 66 4a ...y[..Z~q.._.fJ
0010 - 5b 4b c6 fe e4 ae 95 21-6e 60 b8 84 bd 09 8e 91 [K.....!n`......
0020 - ba a4 1e 29 b8 fc 7c 5b-72 16 ae 7e f6 f7 a9 e5 ...)..|[r..~....
0030 - 69 0a 4e 41 03 cc 79 3a-b4 f7 6d 99 81 a6 a2 ef i.NA..y:..m.....
0040 - c8 27 02 50 1d 68 0b 70-1f d1 d4 1a b7 d9 99 b8 .'.P.h.p........
0050 - b0 6d 47 ee 89 7d 5d 37-bd ab 89 a7 d2 d2 3d 1d .mG..}]7......=.
0060 - 2f e1 4b 85 ae eb ab de-3a f7 e2 18 ce 55 48 3b /.K.....:....UH;
0070 - 15 14 8f 69 45 bc 8a a4-77 b1 a0 8f 41 96 ea c7 ...iE...w...A...
0080 - f2 2d be e2 26 26 5a 06-1f f3 c0 fe 6b f7 de ab .-..&&Z.....k...
0090 - ab 04 f8 74 e5 b3 c9 25-c6 24 ba 88 44 50 3b 57 ...t...%.$..DP;W
Start Time: 1470992045
Timeout : 300 (sec)
Verify return code: 0 (ok)
---
220 smtp2h.mail.yandex.net ESMTP (Want to use Yandex.Mail for your domain? Visit http://pdd.yandex.ru)Отлично, сертификаты из вывода нам понадобятся позже.
Реализация
Для начала создадим Oracle wallet, в нем мы будем хранить доверенные сертификаты почтового сервера. Сертификаты можно взять тут:
Стартуем Oracle Wallet Manager командой ovm, создаем новый wallet/задаем пароль:
Импортируем доверенные сертификаты, Operations > Import Trusted Certificate.:
Сохраняем, по умолчанию wallet хранится тут $ORACLE_HOME/owm/wallets/oracle/. Далее необходимо дать права на исполнение пакета UTL_SMTP:
GRANT EXECUTE on UTL_SMTP to SMTP_USER;Создаём ACL, даём права на connect:
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
(
acl => 'smtp_acl.xml',
principal => 'SMTP_USER',
is_grant => TRUE,
privilege => 'connect',
);
end;
/На доступ к SMTP Яндекса:
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL
(
acl => 'smtp_acl.xml',
host => 'smtp.yandex.ru',
lower_port => 1,
upper_port => 1024
);
end;
/На использование wallet'а:
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
(
acl => 'smtp_acl.xml',
principal => 'SMTP_USER',
is_grant => TRUE,
privilege => 'use-client-certificates'
);
end;
/Задаём путь до wallet'а:
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL
(
acl => 'smtp_acl.xml',
wallet_path => 'file:/oracle_home/owm/wallets/oracle/'
);
end;
/Так же нам понадобится сконвертировать учетные данные по доступу к почтовому ящику(логин, пароль) в base64:
select UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('user@yandex.ru'))) as username,
UTL_RAW.cast_to_varchar2 (
UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('password'))) as password
from dual;Полученные хеши будем применять при SMTP аутентификации.
Тестируем!
DECLARE
c utl_smtp.connection;
l_mailhost VARCHAR2 (64) := 'smtp.yandex.ru';
l_from VARCHAR2 (64) := 'user@yandex.ru';
l_to VARCHAR2 (64) := 'user@gmail.com';
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:/oracle_home/owm/wallets/oracle/',
wallet_password => 'password',
secure_connection_before_smtp => FALSE);
UTL_SMTP.STARTTLS(c);
UTL_SMTP.EHLO(c, 'oracle');
utl_smtp.command( c, 'AUTH LOGIN');
utl_smtp.command( c, 'хеш на логин');
utl_smtp.command( c, 'хеш на пароль');
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;
/
PL/SQL procedure successfully completed.Сообщение было успешно доставлено на gmail.com. Пункт 4.с реализован.
Спасибо за внимание!
Автор: aleksey_beloliptsev
