250-AUTH LOGIN PLAIN CRAM-MD5 CRAM-SHA1
250-STARTTLS
AUTH PLAIN bXlfdXNlcl9uYW1lAG15X3VzZXJfbmFtZQBteV9zZWNyZXRfcGFzc3dvcmQ=
AUTH PLAIN my_user_name|my_user_name|my_secret_password
v_plain_string :=
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('my_user_name'||chr(0)||'my_user_name'||chr(0)||'my_secret_password'))
);
v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com'); -- Must use EHLO vs HELO
UTL_SMTP.command(v_connection, 'AUTH', 'PLAIN ' || v_plain_string);
AUTH LOGIN
334 VXNlcm5hbWU6
bXlfdXNlcl9uYW1l
334 UGFzc3dvcmQ6
bXlfc2VjcmV0X3Bhc3N3b3Jk
235 Authed. Go on.
AUTH LOGIN
334 Username:
my_user_name
334 Password:
my_secret_password
235 Authed. Go on.
v_username_b64 :=
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_username))
);
v_password_b64 :=
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_password))
);
v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com'); -- Must use EHLO vs HELO
UTL_SMTP.command(v_connection, 'AUTH', 'LOGIN'); -- should receive a 334 response, prompting for username
UTL_SMTP.command(v_connection, v_username_b64); -- should receive a 334 response, prompting for password
UTL_SMTP.command(v_connection, v_password_b64); -- should receive a 235 response, you are authenticated
SELECT UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(
UTL_RAW.cast_to_raw(
'tim ' -- username
|| LOWER(
RAWTOHEX(
DBMS_CRYPTO.hash(
UTL_RAW.CONCAT(
UTL_RAW.bit_xor(secret, UTL_RAW.copies(HEXTORAW('5C'), 64)),
DBMS_CRYPTO.hash(
UTL_RAW.CONCAT(
UTL_RAW.bit_xor(
secret,
UTL_RAW.copies(HEXTORAW('36'), 64)
),
UTL_ENCODE.base64_decode(
UTL_RAW.cast_to_raw( -- challenge
'PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+'
)
)
),
2 /* 2= MD5 */
)
),
2 /* 2= MD5 */
)
)
)
)
)
)
encoded_cram
FROM (SELECT UTL_RAW.overlay(
UTL_RAW.cast_to_raw('tanstaaftanstaaf'), -- password
UTL_RAW.copies(HEXTORAW('00'), 64)
)
secret
FROM DUAL);
SELECT sdscram(
'tim', --username
'tanstaaftanstaaf', -- password
'PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+' -- challenge from server
)
FROM DUAL;
expected result:
dGltIGI5MTNhNjAyYzdlZGE3YTQ5NWI0ZTZlNzMzNGQzODkw
CREATE OR REPLACE FUNCTION sdscram(p_username IN VARCHAR2,
p_key IN VARCHAR2,
p_challenge IN VARCHAR2,
p_hashmethod IN INTEGER DEFAULT DBMS_CRYPTO.hash_md5
)
RETURN VARCHAR2
IS
/*
Challenge-Response Authentication Mechanism
as in RFC 2195
http://www.ietf.org/rfc/rfc2195.txt
PL/SQL implementation by Sean D. Stuber
example usage with data shown in RFC 2195 doc
select
sdscram(
'tim',
'tanstaaftanstaaf',
'PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+'
)
from dual;
expected result:
dGltIGI5MTNhNjAyYzdlZGE3YTQ5NWI0ZTZlNzMzNGQzODkw
*/
c_blocksize CONSTANT INTEGER := 64;
c_zeroblock CONSTANT RAW(64) := UTL_RAW.copies(HEXTORAW('00'), c_blocksize);
c_outerpad CONSTANT RAW(64) := UTL_RAW.copies(HEXTORAW('5C'), c_blocksize);
c_innerpad CONSTANT RAW(64) := UTL_RAW.copies(HEXTORAW('36'), c_blocksize);
v_outer RAW(64);
v_inner RAW(64);
v_challenge VARCHAR2(32767);
v_key RAW(32767) := UTL_RAW.cast_to_raw(p_key);
v_hash VARCHAR2(64);
BEGIN
-- If the key is bigger than the block size (64) then hash it
-- which will reduce it to a shorter byte stream
IF UTL_RAW.LENGTH(v_key) > c_blocksize
THEN
v_key := DBMS_CRYPTO.hash(v_key, p_hashmethod);
END IF;
-- 0-pad the key to fill a block
IF UTL_RAW.LENGTH(v_key) < c_blocksize
THEN
v_key := UTL_RAW.overlay(v_key, c_zeroblock);
END IF;
-- The challenge will be a base64 encoded string
v_challenge := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(p_challenge));
v_outer := UTL_RAW.bit_xor(v_key, c_outerpad);
v_inner := UTL_RAW.bit_xor(v_key, c_innerpad);
-- append the challenge to the key and hash it
v_inner := DBMS_CRYPTO.hash(UTL_RAW.CONCAT(v_inner, v_challenge), p_hashmethod);
-- append the new inner hash to the outer and hash again
-- return results as a string for use in next step
v_hash :=
LOWER(RAWTOHEX(DBMS_CRYPTO.hash(UTL_RAW.CONCAT(v_outer, v_inner), p_hashmethod)));
-- base64 encode the username with the hash, separated by a space
RETURN UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(p_username || ' ' || v_hash)));
END;
v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com'); -- Must use EHLO vs HELO
v_reply := UTL_SMTP.command(v_connection, 'AUTH', 'CRAM-MD5');
IF v_reply.code = 334
THEN
v_cram_string := sdscram('tim', --username
'tanstaaftanstaaf', -- password
v_reply.text -- challenge from server
);
UTL_SMTP.command(v_connection, v_cram_string);
END IF;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (6)
Author
Commented:I do plan on adding links between this one and other future SMTP-related articles as I complete them, but my other articles, while (mostly) Oracle related aren't really connected to this one.
I guess I could do some cross-advertising anyway. Is there a good way to publish an article list? I know how to search for my own articles but I don't know of a way to create a link for others that could produce the same search results
Commented:
In my Articles, I embed the links using this format:
Rogue-Killer-What-a-great-
For a sample, take a look here: https://www.experts-exchange.com/A_1958.html (MALWARE - "An Ounce of Prevention...")
In my own Articles, I've taken to including links to other malware-specific Articles that may help the reader.
Commented:
Also would be really nice if you can post a public mail integration like Gmail.
Great as usual.
Author
Commented:What more detail would you like to see? I gave examples of all necessary syntax for the authentication methods. No other commands are needed, so I'm not sure what further detail to provide.
For Gmail, you need TLS authentication which is not supported in 11.2.0.1 (the version of Enterprise Edition available from OTN for anyone to use for learning and testing.) When 12c is released I expect it to support that functionality. I plan to post a followup article then.
If you want to explore that functionality now 11.2.0.2 and higher do support it. 11gXE is 11.2.0.2 so you could try it now.
Commented:
View More