По следам «спамера» или Oracle DB + UTL_SMTP + SSL-TLS

в 11:04, , рубрики: authentication, oracle, smtp, utl_smtp, Администрирование баз данных, метки:

На написание данной статьи меня сподвигло прочтение публикации «Как я спам слал» от пользователя Nike01. Руководство поставило передо мной похожую задачу по рассылке учетных данных пользователей средствами базы данных Oracle. В качестве SMTP relay должен был выступать сервер исходящих сообщений Яндекса, использующий SSL при авторизации.

По следам «спамера» или Oracle DB + UTL_SMTP + SSL-TLS - 1

Сбор информации

Как и 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 DB + UTL_SMTP + SSL-TLS - 2

Ок. Пробуем установить соединение:

[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 DB + UTL_SMTP + SSL-TLS - 3

Стартуем Oracle Wallet Manager командой ovm, создаем новый wallet/задаем пароль:

По следам «спамера» или Oracle DB + UTL_SMTP + SSL-TLS - 4

Импортируем доверенные сертификаты, Operations > Import Trusted Certificate.:

По следам «спамера» или Oracle DB + UTL_SMTP + SSL-TLS - 5

Сохраняем, по умолчанию 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

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js