APEXtras

a development team dedicated to Oracle APEX

Two-factor authentication with YubiKey – Authentication Package

leave a comment »

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

Advertisements

Written by Roger

19 March, 2009 at 12:43 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: