Two-factor authentication with YubiKey - Authentication Package
create or replace package APEXTRAS_YUBICO is
-- Author : Roger Cohen
-- Created : 18/03/2009
-- Purpose : APEXtras blog code Yuibico example
/*
--SQL to create simple demonstration user table
create table YUBICO_USER
(
YUBICO_USER_ID NUMBER not null,
FIRSTNAME VARCHAR2(50),
LASTNAME VARCHAR2(50),
PASSWORD VARCHAR2(200),
SALT VARCHAR2(200),
YUBICO_ID VARCHAR2(12),
LOGIN_NAME VARCHAR2(50)
)
*/
YUBICO_VERIFY_URL CONSTANT VARCHAR2(200) := 'http://api.yubico.com/wsapi/verify';
--The following two constants should be copied from
--your Yubico management site - log in to https://api.yubico.com/yms/
YUBICO_USER_ID CONSTANT NUMBER := 9999;
YUBICO_API_KEY CONSTANT VARCHAR2(200) := 'YourAPIKEY=';
PROCEDURE YubicoUserInfo(p_yubico_user_id IN OUT YUBICO_USER.YUBICO_USER_ID%TYPE,
p_yubico_id IN OUT YUBICO_USER.YUBICO_ID%TYPE,
p_firstname OUT YUBICO_USER.FIRSTNAME%TYPE,
p_lastname OUT YUBICO_USER.LASTNAME%TYPE,
p_login_name OUT YUBICO_USER.LOGIN_NAME%TYPE,
p_hashed_password OUT YUBICO_USER.PASSWORD%TYPE,
p_salt OUT YUBICO_USER.SALT%TYPE);
PROCEDURE NewYubicoUser(p_yubico_id IN YUBICO_USER.YUBICO_ID%TYPE,
p_firstname IN YUBICO_USER.FIRSTNAME%TYPE,
p_lastname IN YUBICO_USER.LASTNAME%TYPE,
p_login_name IN YUBICO_USER.LOGIN_NAME%TYPE,
p_password IN YUBICO_USER.PASSWORD%TYPE);
FUNCTION YubicoVerify(p_otp IN VARCHAR2,
p_include_signature IN BOOLEAN := TRUE) RETURN VARCHAR2;
FUNCTION auth (p_username IN VARCHAR2,
p_password IN VARCHAR2) RETURN BOOLEAN;
end APEXTRAS_YUBICO;
/
create or replace package body APEXTRAS_YUBICO is
-- Forward declarations
FUNCTION get_token(p_the_list CLOB,
p_the_index NUMBER,
p_delim VARCHAR2 := ',') RETURN VARCHAR2;
PROCEDURE Content_Attribute(p_txt IN VARCHAR2,
p_content OUT VARCHAR2,
p_content_attribute OUT VARCHAR2,
p_delimiter IN VARCHAR2);
--Yubico section
FUNCTION hash_sh1 (p_string IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN dbms_crypto.HASH (src => utl_i18n.string_to_raw (p_string,'AL32UTF8'),
typ => dbms_crypto.hash_sh1);
END;
FUNCTION Make_Salt(p_seed IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
Return hash_sh1 ('--' || CURRENT_TIMESTAMP || '--' || p_seed || '--');
END;
FUNCTION Hashed_Password(p_password IN VARCHAR2,p_salt IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
Return hash_sh1 ('--' ||p_salt || '--' || p_password || '--');
END;
PROCEDURE YubicoUserInfo(p_yubico_user_id IN OUT YUBICO_USER.YUBICO_USER_ID%TYPE,
p_yubico_id IN OUT YUBICO_USER.YUBICO_ID%TYPE,
p_firstname OUT YUBICO_USER.FIRSTNAME%TYPE,
p_lastname OUT YUBICO_USER.LASTNAME%TYPE,
p_login_name OUT YUBICO_USER.LOGIN_NAME%TYPE,
p_hashed_password OUT YUBICO_USER.PASSWORD%TYPE,
p_salt OUT YUBICO_USER.SALT%TYPE) IS
BEGIN
BEGIN
IF (NVL(p_yubico_user_id,0) != 0) THEN
SELECT
yu.firstname,
yu.lastname,
yu.login_name,
yu.password,
yu.salt,
yu.yubico_id
INTO
p_firstname,
p_lastname,
p_login_name,
p_hashed_password,
p_salt,
p_yubico_id
FROM YUBICO_USER yu
WHERE (yu.yubico_user_id = p_yubico_user_id);
ELSIF (p_yubico_id IS NOT NULL) THEN
SELECT
yu.firstname,
yu.lastname,
yu.login_name,
yu.password,
yu.salt,
yu.yubico_user_id
INTO
p_firstname,
p_lastname,
p_login_name,
p_hashed_password,
p_salt,
p_yubico_user_id
FROM YUBICO_USER yu
WHERE (yu.yubico_id = p_yubico_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
FUNCTION authBody (p_yubico_user_id IN NUMBER,
p_password IN VARCHAR2) RETURN BOOLEAN AS
l_result BOOLEAN;
l_login_name YUBICO_USER.LOGIN_NAME%TYPE;
l_usr_id YUBICO_USER.YUBICO_USER_ID%TYPE;
l_yubico_user_id YUBICO_USER.YUBICO_USER_ID%TYPE;
l_yubico_id YUBICO_USER.YUBICO_ID%TYPE;
l_firstname YUBICO_USER.FIRSTNAME%TYPE;
l_lastname YUBICO_USER.LASTNAME%TYPE;
l_hashed_password YUBICO_USER.PASSWORD%TYPE;
l_salt YUBICO_USER.SALT%TYPE;
BEGIN
l_yubico_user_id := p_yubico_user_id;
APEXTRAS_YUBICO.YubicoUserInfo(p_yubico_user_id => l_yubico_user_id ,
p_yubico_id => l_yubico_id ,
p_firstname => l_firstname ,
p_lastname => l_lastname ,
p_login_name => l_login_name ,
p_hashed_password => l_hashed_password ,
p_salt => l_salt );
--Check the stored password hash against the hash of the entered password
l_result := ('Z' || UPPER (Hashed_Password(p_password,l_salt)) = 'Z' || UPPER (l_hashed_password));
RETURN l_result;
END;
-- The auth function is called from the APEX Autheintication Scheme and must have parameters with exactly these names
-- We are using it to authenticate a user ID rather than a name
FUNCTION auth (p_username IN VARCHAR2,
p_password IN VARCHAR2) RETURN BOOLEAN AS
BEGIN
RETURN authBody(TO_NUMBER(p_username),p_password);
END;
PROCEDURE NewYubicoUser(p_yubico_id IN YUBICO_USER.YUBICO_ID%TYPE,
p_firstname IN YUBICO_USER.FIRSTNAME%TYPE,
p_lastname IN YUBICO_USER.LASTNAME%TYPE,
p_login_name IN YUBICO_USER.LOGIN_NAME%TYPE,
p_password IN YUBICO_USER.PASSWORD%TYPE) IS
l_hashed_password YUBICO_USER.PASSWORD%TYPE;
l_salt YUBICO_USER.SALT%TYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
l_salt := make_salt(p_password);
l_hashed_password := Hashed_Password(p_password,l_salt);
INSERT INTO YUBICO_USER
(firstname,
lastname,
login_name,
password,
salt,
yubico_id)
VALUES
(p_firstname,
p_lastname,
p_login_name,
l_hashed_password,
l_salt,
p_yubico_id);
COMMIT;
END;
-- The Yubico API key is provided in base64 encoded form, so must be decoded before use in the dbms_crypto.Mac function
-- The signature must be returned to Yubico encoded back into base64 form
FUNCTION YubicoSignature(p_string IN VARCHAR2,
p_key IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN (utl_i18n.raw_to_char(UTL_ENCODE.base64_encode(dbms_crypto.Mac(src => utl_i18n.string_to_raw (p_string,'AL32UTF8'),
typ => dbms_crypto.HMAC_SH1,
key => UTL_ENCODE.base64_decode( utl_i18n.string_to_raw(p_key,'AL32UTF8')))),'AL32UTF8'));
END;
-- Send the client ID and OTP to the Yubico verification server
-- The server will accept queries without a signature, so that capability has been allowed for
-- Note that doing that in real life is unsafe - in a properly secure system all communication is signed, or verified by certificate over an HTTPS connection
FUNCTION YubicoResponse(p_id IN NUMBER,
p_otp IN VARCHAR2,
p_api_key IN VARCHAR2,
p_include_signature IN BOOLEAN := TRUE) RETURN VARCHAR2 IS
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_text VARCHAR2(32767);
l_verify_url VARCHAR2(1000);
l_querystring VARCHAR2(1000);
l_signature VARCHAR2(1000);
l_key RAW(256);
BEGIN
l_querystring := 'id=' || TO_CHAR(p_id) || '&otp=' || TO_CHAR(p_otp);
-- The signature must be derived from the querystring with items in alphabetical order
l_signature := YubicoSignature(l_querystring,p_api_key);
l_verify_url := APEXTRAS_YUBICO.YUBICO_VERIFY_URL || '?' || l_querystring;
IF p_include_signature THEN
l_verify_url := l_verify_url || '&h=' || utl_url.escape(l_signature,TRUE);
END IF;
l_http_request := UTL_HTTP.begin_request(l_verify_url, 'GET', 'HTTP/1.0');
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response,
l_text,
500000);
UTL_HTTP.end_response(l_http_response);
RETURN l_text;
END;
-- Unsigned queries are allowed but unsafe - see comment above
FUNCTION YubicoVerify(p_otp IN VARCHAR2,
p_include_signature IN BOOLEAN := TRUE) RETURN VARCHAR2 IS
l_response_delimiter VARCHAR2(2) := UTL_TCP.CRLF;
l_response_part_delimiter VARCHAR2(1) := '=';
l_response_part VARCHAR2(100);
l_name VARCHAR2(100);
l_value VARCHAR2(100);
l_index INTEGER := 1;
l_text VARCHAR2(32767);
l_signature VARCHAR2(100);
l_return VARCHAR2(50);
l_querystring VARCHAR2(1000);
l_t_section VARCHAR2(100);
l_status_section VARCHAR2(100);
BEGIN
l_querystring := '';
l_return := '';
-- Get the response from the Yubico server
l_text := YubicoResponse(APEXTRAS_YUBICO.YUBICO_USER_ID,p_otp,APEXTRAS_YUBICO.YUBICO_API_KEY,p_include_signature);
-- Parse the response
l_response_part := get_token(l_text,l_index,l_response_delimiter);
WHILE (l_response_part IS NOT NULL)
LOOP
l_index := l_index + 1;
Content_Attribute(l_response_part,l_name,l_value,l_response_part_delimiter);
CASE LOWER(l_name)
WHEN 'h' THEN
l_signature := l_value;
WHEN 'status' THEN
l_return := l_value;
l_status_section := l_name || '=' || l_value;
WHEN 't' THEN
l_t_section := l_name || '=' || l_value;
END CASE;
l_querystring := l_status_section || '&' || l_t_section;
l_response_part := get_token(l_text,l_index,l_response_delimiter);
END LOOP;
-- Check the returned signature against a locally calculated hash using our API key
IF (p_include_signature AND (YubicoSignature(l_querystring,APEXTRAS_YUBICO.YUBICO_API_KEY) != l_signature)) THEN
l_return := l_return || '_SIGNATURES_DO_NOT_MATCH';
END IF;
RETURN l_return;
END;
--End Yubico
--UTILITY
FUNCTION InList(p_string IN VARCHAR2,p_list IN VARCHAR2,p_delimiter IN VARCHAR2) RETURN BOOLEAN IS
l_index PLS_INTEGER;
l_field VARCHAR2(4000);
l_return BOOLEAN;
BEGIN
l_index := 1;
l_return := False;
l_field := get_token(p_list,l_index,p_delimiter);
WHILE ((l_field IS NOT NULL) AND (NOT l_return))
LOOP
l_return := (UPPER(p_string) = UPPER(l_field));
l_index := l_index + 1;
l_field := get_token(p_list,l_index,p_delimiter);
END LOOP;
RETURN l_return;
END;
FUNCTION FrontChop(p_string IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF (LENGTH(p_string) > 1) THEN
RETURN SUBSTR(p_string,2);
ELSE
RETURN '';
END IF;
END;
FUNCTION get_delimited_token(p_the_list CLOB,
p_the_index NUMBER,
p_delim1 VARCHAR2 := '[',
p_delim2 VARCHAR2 := ']') RETURN VARCHAR2 IS
l_start_pos NUMBER;
l_end_pos NUMBER;
l_index NUMBER;
l_output VARCHAR2(32767);
BEGIN
IF (p_delim1 = p_delim2) THEN
l_index := p_the_index - 1;
ELSE
l_index := p_the_index;
END IF;
IF (p_the_index = 1) THEN
IF (p_delim1 = p_delim2) THEN
l_start_pos := 1;
ELSE
l_start_pos := instr(p_the_list,p_delim1,1,1) + length(p_delim1);
END IF;
ELSE
l_start_pos := instr(p_the_list,p_delim1,1,l_index);
IF l_start_pos = 0 THEN
l_output := NULL;
ELSE
l_start_pos := l_start_pos + length(p_delim1);
END IF;
END IF;
IF (l_start_pos != 0) THEN
l_end_pos := instr(p_the_list,p_delim2,l_start_pos,1);
IF l_end_pos = 0 THEN
l_output := substr(p_the_list,l_start_pos);
ELSE
l_output := substr(p_the_list,l_start_pos,l_end_pos - l_start_pos);
END IF;
END IF;
RETURN l_output;
END get_delimited_token;
FUNCTION get_token(p_the_list CLOB,
p_the_index NUMBER,
p_delim VARCHAR2 := ',') RETURN VARCHAR2 IS
BEGIN
RETURN get_delimited_token(p_the_list,p_the_index,p_delim,p_delim);
END get_token;
--Return the parts of a string before and after a given delimiter
PROCEDURE Content_Attribute(p_txt IN VARCHAR2,
p_content OUT VARCHAR2,
p_content_attribute OUT VARCHAR2,
p_delimiter IN VARCHAR2) IS
l_pos INTEGER;
BEGIN
l_pos := instr(p_txt,p_delimiter);
IF (l_pos > 1) THEN
p_content := SUBSTR(p_txt,1,l_pos - 1);
p_content_attribute := SUBSTR(p_txt,l_pos + 1);
ELSIF (l_pos = 1) THEN
p_content := 'NONE';
p_content_attribute := SUBSTR(p_txt,l_pos + 1);
ELSE
p_content := p_txt;
p_content_attribute := 'NONE';
END IF;
END;
END APEXTRAS_YUBICO;
/
Back to Integrating YubiKey two-factor authentication with APEX login
[...] Authentication Package APEX After Submit process [...]
Integrating YubiKey two-factor authentication with APEX login at APEXtras
25 Jul 09 at 12:14