In the wake of "spammer" or Oracle DB + UTL_SMTP + SSL / TLS

Writing this article encouraged me to read the publication “How I Spam Spam” from user Nike01 . The management has set me a similar task of distributing user credentials using the Oracle database. The SMTP relay was supposed to be a Yandex outgoing server using SSL for authorization.

Collection of information

Like Nike01 , I first began to look in the direction of the UTL_MAIL package. This package, in fact, is a “wrapper” over UTL_SMTP and UTL_TCP and is intended mainly for sending simple mail messages, allowing the user not to go into the details of the SMTP protocol. Unfortunately, UTL_MAIL has a number of limitations, including the lack of support for SMTP authentication and, therefore, it does not suit my task.

Next, I turned to the documentation on the UTL_SMTP package, as it turned out - it is quite a powerful and flexible tool that allows you to use almost all the commands of the SMTP protocol, including SMTP authorization support, and since the version of DB 11.2.0.2 it can also STARTTLS. What you need!
')
Go to the mail server. From the help on using smtp.yandex.ru we learn that the service is listening to port 465:


OK. We are trying to establish a connection:

[oracle@ora_db ~]$openssl s_client -connect smtp.yandex.ru:465 CONNECTED(00000003) depth=2 C = PL, O = Unizeto Technologies SA, 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 SA/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 .'.Php....... 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) 

Great, we'll need the certificates from the output later.

Implementation

To begin with, we will create an Oracle wallet, in which we will store trusted mail server certificates. Certificates can be taken here :


We start Oracle Wallet Manager with the ovm command, create a new wallet / set the password:


Import trusted certificates, Operations> Import Trusted Certificate. :


Save, by default the wallet is stored here $ORACLE_HOME/owm/wallets/oracle/ . Next, you need to give rights to execute the UTL_SMTP package:

 GRANT EXECUTE on UTL_SMTP to SMTP_USER; 

We create ACL, we grant the rights to connect:

 begin DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'smtp_acl.xml', principal => 'SMTP_USER', is_grant => TRUE, privilege => 'connect', ); end; / 

To access Yandex SMTP:

 begin DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'smtp_acl.xml', host => 'smtp.yandex.ru', lower_port => 1, upper_port => 1024 ); end; / 

To use a wallet:

 begin DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'smtp_acl.xml', principal => 'SMTP_USER', is_grant => TRUE, privilege => 'use-client-certificates' ); end; / 

Set the path to the wallet:

 begin DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL ( acl => 'smtp_acl.xml', wallet_path => 'file:/oracle_home/owm/wallets/oracle/' ); end; / 

We also need to convert the credentials for access to the mailbox (login, password) to 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; 

The resulting hashes will be used for SMTP authentication.

We are testing!

 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. 

The message was successfully delivered to gmail.com. Clause 4.c is implemented.

Thanks for attention!