In 2015, I wrote an entry about doing multifactor authentication with PL/SQL (link: https://storm-petrel.com/orablog/2015/11/29/oracle-apex-multifactor-authentication/)
The SMS services that I based my work on has disappeared from the internet landscape, rendering my code and efforts futile.
So it was time to start again. The advantage was that I got to use better RESTful tools and JSON parsers.
The variance this time is that I put 100% of the code on Github
Why use multifactor authentication (two-factor or 2FA) authentication? Well, frankly it is the right thing to do as I wrote in 2015…
The United States Office of Personnel Management is notifying 2.7 million people that their data were stolen. The blame lays at the feet of the people responsible for these data. While the ultimate responsibility rests with the executives who did not fund investment into data security, I shoulder that responsibility daily.
Nexmo Multifactor Authentication
The last time I visited this topic, I used some random generators to create a 4-digit code that I then sent via text message to a phone. With the assistance of an Oracle global temporary table and precise timestamps, I did the heavy-ish lift on generating the code, storing the code, sending the code, and verifying the code and the timestamps. Not tough, but the Nexmo approach takes some of the effort.
The process initiates with MFA request to the phone. You get a message ID back. You do NOT get the code.
When the user keys in the code, you (the programmer) has to take the message ID generated from the request and the 4-digit code entered by the user. You send these off to Nexmo. They send back a status code. Zero is good. Not zero is bad.
Interesting Annoying Stuff
The tool apex_json.get_varchar2() does NOT like a dash in the “field name”. The vendor, Nexmo, put dashes in the field names for JSON, but did NOT for the XML interface. So a little replace() action is need to make Oracle happy.
To recap – this is acceptable but does not match Nexmo’s data definition
r_response.client_ref := apex_json.get_varchar2( p_values => l_values, p_path => 'messages[' || trim(to_char(json_row)) ||'].clientRef' );
This is not acceptable to Oracle, but does match Nexmo’s data definition
r_response.client_ref := apex_json.get_varchar2( p_values => l_values, p_path => 'messages[' || trim(to_char(json_row)) ||'].client-ref' );
Oh well, the solution is just as easy as changing the JSON data:
l_return := replace(l_return, 'client-ref', 'clientRef');
Oracle PL/SQL SMS and MFA
A few quick hints about the code…
Sending SMS text looks rather like this:
begin -- validate the parameters sms_ok(p_sms,'send_sms','Y'); l_text := msg_ok(p_msg,'send_sms'); -- structure the URL l_url := g_sms_uri || 'api_key=' || g_key || amp || 'api_secret=' || g_secret || amp || 'to=' || p_sms || amp || 'from=' || g_sender || amp || 'text=' || l_text ; if p_client_ref is not null then l_url := l_url || amp || 'client-ref=' || p_client_ref; end if; -- client reference is not null l_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST( p_url => l_url ,p_http_method => 'GET' ,p_wallet_path => g_wallet_path ,p_wallet_pwd => g_wallet_pwd ); ...
I whipped up a quick APEX 5.1 application to show the integration of the package and the Nexmo API. It is also posted at Github. It will send an SMS message, do two-factor authentication, and show you your balance.
- You MUST have an account with Nexmo.com. You can get a demo account for free.
- You MUST put your own Oracle Wallet Path and Wallet password into the packages and compile them (G_WALLET_PATH and G_WALLET_PWD)
- You MUST manually install the Root and Intermediate SSL certs for nexmo.com in your wallet
- ROOT: Digicert Global Root CA, v3, valid through 09NOV2031
- Intermediate: Digicert SHA2 Secure Server CA, valid thorugh 08MAR2013
- If you are on Oracle 12c, DO NOT install the SSL cert for *.nexmo.com
- You’ll need to create the table that is in the package body. It is in the commented out section.
- You’ll need to put in your own G_KEY and G_SECRET at the top of the package body.
Proin gravida nibh vel velit auctor aliquet aenean sollicitudin, lorem quis bibendum
Mauris ut orci dapibus, sollicitudin metus id, facilisis magna. Praesent pellentesque consequat nibh. Ut egestas velit quis ante tincidunt, eget luctus orci tincidunt. Cras massa augue, facilisis sit amet mattis vel, mollis vel neque. Nam maximus laoreet erat, a sagittis risus auctor non. Interdum et malesuada fames ac ante ipsum primis in faucibus. Praesent at interdum felis. Pellentesque facilisis vulputate justo, in euismod turpis aliquet id. Nam ut nibh eros. Vivamus lacus orci, rhoncus quis sodales et.
Donec interdum felis fringilla posuere pellentesque. Duis elementum blandit justo, eget tincidunt diam sollicitudin sed. Nulla hendrerit tempor nisi non feugiat. Curabitur a auctor nisl. Sed sagittis purus ac felis venenatis, at sagittis ante suscipit. Nulla lacinia eros odio, sit amet fringilla nisl porttitor sit amet. Nunc tempus, nunc a rutrum ullamcorper, lacus purus porttitor lacus, vitae ullamcorper erat erat quis nibh. Sed vitae diam sed quam malesuada gravida sed ac nisl. Fusce sed accumsan purus.